Procedure Call

procedure create_excel_xml(pi_SelectText varchar2 -- Select text
,pi_ParamList varchar2 -- Parameters XML
,po_ReturnCode out integer
,po_Msg out varchar2);

Parameters XML

<PARAMS_LIST>

<DIR_NAME> </DIR_NAME>

<FILE_NAME> </FILE_NAME>

<OUT_TYPE> </OUT_TYPE> -- Excel|HTML|CSV|XML

<LIMIT_ROWS> </LIMIT_ROWS> -- 64000

<LIMIT_LEN> </LIMIT_LEN> --20 Mb

<EXCEL_TITLE> </EXCEL_TITLE>

<SUBTITLE></SUBTITLE>

<SUBTITLE2></SUBTITLE2>

<DIRECTION></DIRECTION> --ltr|rtl

<CHARSET> </CHARSET>

<LITERAL_PARAMS> </LITERAL_PARAMS> -- Y|N

<DIVIDE_BY> </DIVIDE_BY> -- FILES|SHEETS

<PARAM_TITLE> </PARAM_TITLE>

<PAR_NAME_HEAD> </PAR_NAME_HEAD>

<PAR_VALUE_HEAD> </PAR_VALUE_HEAD>

<NOT_FOUND_MSG> </NOT_FOUND_MSG>

<LONG_OUT> </LONG_OUT> -- Y|N

<MULTI_VALUE_DELIMITER> </MULTI_VALUE_DELIMITER>

<CURR_DATE_PROMPT> </CURR_DATE_PROMPT>

<DEFAULT_DATE_MASK> </DEFAULT_DATE_MASK>

<OUTPUT_DATE_FORMAT></OUTPUT_DATE_FORMAT>

<CURRENT_SHEET></CURRENT_SHEET>

<TOTAL_SHEETS></TOTAL_SHEETS>

<PARAMS>

<PARAM>

<NAME>EMP_ID</NAME>

<DATATYPE>number</DATATYPE>

<FORMAT_MASK> </FORMAT_MASK>

<PROMPT>Emploee Id</PROMPT>

<LABEL></ LABEL >

<VALUE> </VALUE>

</PARAM>

<PARAM>

<NAME>LOC_ID</NAME>

<DATATYPE>number</DATATYPE>

<FORMAT_MASK></FORMAT_MASK>

<PROMPT>Location</PROMPT>

<LABEL></ LABEL >

<VALUE>123 </VALUE>

</PARAM>

<PARAM>

<NAME>DEP_ID</NAME>

<DATATYPE>number</DATATYPE>

<FORMAT_MASK></FORMAT_MASK>

<PROMPT>Department Id</PROMPT>

<LABEL></ LABEL >

<VALUE> 30 </VALUE>

</PARAM>

<PARAM>

<NAME>FUNCTION</NAME>

<DATATYPE>char</DATATYPE>

<FORMAT_MASK></FORMAT_MASK>

<PROMPT>Job name</PROMPT>

<LABEL></ LABEL >

<VALUE>SALESPERSON</VALUE>

</PARAM>

<PARAM>

<NAME>HIRE_DATE</NAME>

<DATATYPE>date</DATATYPE>

<FORMAT_MASK>yyyy-mm-dd</FORMAT_MASK>

<PROMPT>Hire Date</PROMPT>

<LABEL></ LABEL >

<VALUE>1985-09-08</VALUE>

</PARAM>

</PARAMS>

</PARAMS_LIST>

Select text

/*! VAR :Max_salary_dep number {select department_id

from (select ee.department_id,

sum(ee.salary)

from employee ee

/*! IS_NOT_NULL :emp_id {where ee.employee_id = :emp_id}; !*/

group by ee.department_id

order by sum(ee.salary) desc)

where rownum = 1} !*/

select e.first_name "First <br>Name",

e.last_name,

d.name "Department <br> name",

j.function,e.hire_date,e.salary,e.commission

/*! IS_NOT_NULL :loc_id {,l.regional_group}; !*/

from department d,employee e,job j

/*! IS_NOT_NULL :loc_id {,loc l}; !*/

where e.department_id=d.department_id

and e.job_id=j.job_id

/*! IS_NOT_NULL :loc_id { and l.loc_id=d.loc_id}; !*/

/*! IS_NOT_NULL :hire_date { and hire_date >= :hire_date}; !*/

/*! IS_NOT_NULL :function { and j.function=upper( :function)}; !*/

/*! IIF_EXPR { :dep_id is not null} {and d.department_id = :dep_id}

{and d.department_id = :Max_salary_dep}; !*/

/*! IS_NOT_NULL :emp_id {and employee_id = :emp_id}; !*/

/*! ROW_SUM {Total row} F G; !*/

/*! BOTTOM_SUM Total F G

/*! IS_NOT_NULL :loc_id I ; !*/

/*! IS_NULL :loc_id H; !*/

; !*/

Select and output according to various parameters

Parameters
Name	Value
Emploee Id
Location
Department Id
Job name
Hire Date

select e.first_name "First <br>Name",

e.last_name,

d.name "Department <br> name",

j.function,e.hire_date,e.salary,e.commission

from department d,employee e,job j

where e.department_id=d.department_id

and e.job_id=j.job_id

and d.department_id = 20

Result

Parameters
Name	Value
Emploee Id
Location	123
Department Id	30
Job name	SALESPERSON
Hire Date	1985-09-08

select e.first_name "First <br>Name",

e.last_name,

d.name "Department <br> name",

j.function,e.hire_date,e.salary,e.commission

,l.regional_group

from department d,employee e,job j

,loc l

where e.department_id=d.department_id

and e.job_id=j.job_id

and l.loc_id=d.loc_id

and hire_date >= to_date('1985-09-08','yyyy-mm-dd')

and j.function=upper('SALESPERSON')

and d.department_id = 30

Result

Parameters
Name	Value
Emploee Id
Location
Department Id	30
Job name	SALESPERSON
Hire Date	1985-09-08

select e.first_name "First <br>Name",

e.last_name,

d.name "Department <br> name",

j.function,e.hire_date,e.salary,e.commission

from department d,employee e,job j

where e.department_id=d.department_id

and e.job_id=j.job_id

and hire_date >= to_date('1985-09-08','yyyy-mm-dd')

and j.function=upper('SALESPERSON')

and d.department_id = 30

Result

Parameters
Name	Value
Emploee Id
Location
Department Id
Job name	SALESPERSON
Hire Date	1985-09-08

select e.first_name "First <br>Name",

e.last_name,

d.name "Department <br> name",

j.function,e.hire_date,e.salary,e.commission

from department d,employee e,job j

where e.department_id=d.department_id

and e.job_id=j.job_id

and hire_date >= to_date('1985-09-08','yyyy-mm-dd')

and j.function=upper('SALESPERSON')

and d.department_id = 20

Result

Parameters
Name	Value
Emploee Id
Location
Department Id
Job name
Hire Date	1985-09-08

select e.first_name "First <br>Name",

e.last_name,

d.name "Department <br> name",

j.function,e.hire_date,e.salary,e.commission

from department d,employee e,job j

where e.department_id=d.department_id

and e.job_id=j.job_id

and hire_date >= to_date('1985-09-08','yyyy-mm-dd')

and d.department_id = 20

Result