Jason Bennett's Developer Corner

CREATE OR REPLACE PROCEDURE ExcelHyperlinkHR
IS

– This cursor will be used to generate the individual Department worksheets.
CURSOR crsrDepatmentWS IS
SELECT
department_id,
SUBSTR(UPPER(DEPARTMENT_NAME),1,20) sheet_name
FROM
hr.departments dp
WHERE
EXISTS (select ‘x’ from HR.employees where DEPARTMENT_ID = dp.department_id)
ORDER BY
dp.department_name;

– Base SQL statement for loading each departmental worksheet
v_employee_base_sql VARCHAR2(500) := ‘SELECT last_name||”, ”||first_name,email,phone_number,to_char(hire_date,”MM/DD/YYYY”) ‘||
‘FROM hr.employees ‘||
‘WHERE department_id = :id ‘||
‘ORDER BY last_name,first_name’;

– SQL statement based on v_employee_base_sql, but with the :id replaced with department_id.
v_employee_dept_sql VARCHAR2(500) := NULL;

– SQL statement used to generate hyperlinked worksheet listing all departments containing employees
v_department_sql    VARCHAR2(500) := ‘SELECT ExcelDocTypeUtils.createWorksheetLink(UPPER(department_name),department_name) ‘||
‘FROM hr.departments dp ‘||
‘WHERE EXISTS (select ”x” from HR.employees where DEPARTMENT_ID = dp.department_id) ‘||
‘ORDER BY DP.DEPARTMENT_NAME ‘;

excelReport         ExcelDocumentType := ExcelDocumentType();
documentArray       ExcelDocumentLine := ExcelDocumentLine();

v_worksheet_rec     ExcelDocTypeUtils.T_WORKSHEET_DATA := NULL;
v_worksheet_array   ExcelDocTypeUtils.WORKSHEET_TABLE  := ExcelDocTypeUtils.WORKSHEET_TABLE();

v_sheet_title       ExcelDocTypeUtils.T_SHEET_TITLE := NULL;

– Objects for Defining Document Styles (Optional)

v_style_def         ExcelDocTypeUtils.T_STYLE_DEF := NULL;
v_style_array       ExcelDocTypeUtils.STYLE_LIST  := ExcelDocTypeUtils.STYLE_LIST();

BEGIN

– Add Styles

v_style_def.p_style_id     := ‘HyperlinkStyle’;
v_style_def.p_text_color   := ‘Blue’;

ExcelDocTypeUtils.addStyleType(v_style_array,v_style_def);

v_style_def := NULL;
v_style_def.p_style_id          := ‘SheetTitleStyle’;
v_style_def.p_align_horizontal  := ‘Center’;
v_style_def.p_bold              := ‘Y’;
v_style_def.p_text_color        := ‘Black’;

ExcelDocTypeUtils.addStyleType(v_style_array,v_style_def);

v_style_def := NULL;
v_style_def.p_style_id          := ‘SheetTitleStyleLink’;
v_style_def.p_align_horizontal  := ‘Center’;
v_style_def.p_bold              := ‘Y’;
v_style_def.p_text_color        := ‘Blue’;

ExcelDocTypeUtils.addStyleType(v_style_array,v_style_def);

– Define Sheet Title
v_sheet_title.title      := ‘Employee Listing By Department’;
v_sheet_title.cell_span  := ’4′;
v_sheet_title.style      := ‘SheetTitleStyle’;

v_worksheet_rec.query             := v_department_sql;
v_worksheet_rec.worksheet_name    := ‘Departments’;
v_worksheet_rec.title             := v_sheet_title;
v_worksheet_rec.col_count         := 1;
v_worksheet_rec.col_width_list    := ’40′;
v_worksheet_rec.col_header_list   := ‘Department’;
v_worksheet_rec.col_datatype_list := ‘String’;
v_worksheet_rec.col_style_list    := ‘HyperlinkStyle’;

ExcelDocTypeUtils.addWorksheetType(v_worksheet_array,v_worksheet_rec);

– Construct Department worksheets

FOR data_rec IN crsrDepatmentWS LOOP

v_worksheet_rec      := NULL;
v_employee_dept_sql  := REPLACE(v_employee_base_sql,’:id’,data_rec.department_id);

v_sheet_title.title      := ExcelDocTypeUtils.createWorksheetLink(‘Departments’,'Back to Department Listing’);
v_sheet_title.cell_span  := ’3′;
v_sheet_title.style      := ‘SheetTitleStyleLink’;

v_worksheet_rec.query             := v_employee_dept_sql;
v_worksheet_rec.worksheet_name    := data_rec.sheet_name;
v_worksheet_rec.title             := v_sheet_title;
v_worksheet_rec.col_count         := 4;
v_worksheet_rec.col_width_list    := ’50,20,20,15′;
v_worksheet_rec.col_header_list   := ‘Name,Email,Phone Number,Hire Date’;
v_worksheet_rec.col_datatype_list := ‘String,String,String,String’;

ExcelDocTypeUtils.addWorksheetType(v_worksheet_array,v_worksheet_rec);

END LOOP;

excelReport := ExcelDocTypeUtils.createExcelDocument(v_worksheet_array,v_style_array);

excelReport.displayDocument;

END;

CREATE OR REPLACE FUNCTION MOON_PHASE_NUMERIC(p_date DATE := SYSDATE) RETURN NUMBER
IS

v_age NUMBER(7,5) := 0;

v_phase NUMBER(1) := 0;

BEGIN

v_age := TRUNC(MOD(((TO_NUMBER(to_char(p_date,’J'))-2451550.1)/29.530588853) * 29.53,29.53),5);

CASE

WHEN v_age < 1.84566 THEN v_phase := 1;
WHEN v_age < 5.53699 THEN v_phase := 2;
WHEN v_age < 9.22831 THEN v_phase := 3;
WHEN v_age < 12.91963 THEN v_phase := 4;
WHEN v_age < 16.61096 THEN v_phase := 5;
WHEN v_age < 20.30228 THEN v_phase := 6;
WHEN v_age < 23.99361 THEN v_phase := 7;
WHEN v_age < 27.68493 THEN v_phase := 8;
ELSE v_phase := 1;

END CASE;

RETURN v_phase;

END;

CREATE OR REPLACE FUNCTION MOON_PHASE_TEXT(p_date DATE := SYSDATE) RETURN VARCHAR2
IS

v_phase_ind NUMBER(1) := 0;

v_phase VARCHAR2(30) := 0;

BEGIN

v_phase_ind := MOON_PHASE_NUMERIC(p_date);

CASE

WHEN v_phase_ind = 1 THEN v_phase := ‘New Moon’;
WHEN v_phase_ind = 2 THEN v_phase := ‘Waxing Crescent’;
WHEN v_phase_ind = 3 THEN v_phase := ‘First Quarter’;
WHEN v_phase_ind = 4 THEN v_phase := ‘Waxing Gibbous’;
WHEN v_phase_ind = 5 THEN v_phase := ‘Full Moon’;
WHEN v_phase_ind = 6 THEN v_phase := ‘Waning Gibbous’;
WHEN v_phase_ind = 7 THEN v_phase := ‘Last Quarter’;
WHEN v_phase_ind = 8 THEN v_phase := ‘Waning Crescent ‘;
ELSE v_phase := ‘New Moon’;

END CASE;

RETURN v_phase;

END;

SQL*Plus: Release 10.2.0.4.0 – Production on Sun Dec 20 09:58:46 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 – Production

SQL> select MOON_PHASE_NUMERIC(’25-DEC-2009′) from dual;

MOON_PHASE_NUMERIC(’25-DEC-2009′)
———————————
3

SQL> select MOON_PHASE_TEXT(’25-DEC-2009′) from dual;

MOON_PHASE_TEXT(’25-DEC-2009′)
———————————————————————-
First Quarter