Tuesday, May 13, 2008

Function to return Nested table

Function to return Nested table

CREATE OR REPLACE FUNCTION func_get_emp

RETURN emp_nest_tab

AS

p_sal emp.sal%TYPE;

old_dept emp.deptno%TYPE := 0;

new_dept emp.deptno%TYPE;

iteration NUMBER (2) := 0;

flag NUMBER := 0;

emp_nest emp_nest_tab;

new_sal NUMBER := 0;

CURSOR cur_emp

IS

SELECT empno

FROM emp

ORDER BY deptno;

empn emp.empno%TYPE;

--emp_ret emp_nest_tab;

BEGIN

OPEN cur_emp;

LOOP

FETCH cur_emp INTO empn;

EXIT WHEN cur_emp%NOTFOUND;

--------------procedure----------------

SELECT sal

INTO p_sal

FROM emp

WHERE empno = empn;

SELECT deptno

INTO new_dept

FROM emp

WHERE empno = empn;

----------- Code to initialize emp_nest-----------

IF flag = 0

THEN

emp_nest := emp_nest_tab ();

flag := 1;

END IF;

----------- Code to initialize emp_nest-----------

emp_nest.EXTEND;

IF old_dept = new_dept

THEN

new_sal := new_sal + p_sal;

emp_nest (emp_nest.COUNT) :=

emp_tab_type (new_dept, p_sal, new_sal, iteration);

iteration := iteration + 1;

ELSE

new_sal := p_sal;

emp_nest (emp_nest.COUNT) :=

emp_tab_type (new_dept, p_sal, new_sal, iteration);

old_dept := new_dept;

iteration := iteration + 1;

END IF;

--------------procedure----------------

END LOOP;

CLOSE cur_emp;

/*FOR cur IN (SELECT *

FROM TABLE (CAST (emp_nest AS emp_nest_tab)))

LOOP

DBMS_OUTPUT.put_line ( CHR (9)

|| cur.ini

|| CHR (9)

|| cur.deptno

|| CHR (9)

|| cur.sal

|| CHR (10)

);

END LOOP;*/

RETURN emp_nest;

END func_get_emp;

/

SELECT *

FROM TABLE (CAST (func_get_emp AS emp_nest_tab))

DEPTNO

SAL

TOT_SAL

INI

10

2,450.00

2,450.00

0

10

5,000.00

7,450.00

1

10

1,300.00

8,750.00

2

20

800.00

800.00

3

20

1,100.00

1,900.00

4

20

3,000.00

4,900.00

5

20

3,000.00

7,900.00

6

20

2,975.00

10,875.00

7

30

1,600.00

1,600.00

8

30

2,850.00

4,450.00

9

30

1,250.00

5,700.00

10

30

950.00

6,650.00

11

30

1,500.00

8,150.00

12

30

1,250.00

9,400.00

13

Friday, May 9, 2008

Using PL/SQL procedure to generate workbooks/report in Discoverer

Suppose you have a procedure that does a calculation and send the table as an out parameter.

create or replace type emp_type as object

(

EMPNO NUMBER(4),

ENAME VARCHAR2(10),

JOB VARCHAR2(9),

MGR NUMBER(4),

HIREDATE DATE,

SAL NUMBER(7,2),

COMM NUMBER(7,2),

DEPTNO NUMBER(2)

)

/

create or replace type emp_nested_table as table of emp_type;

/

CREATE OR REPLACE PROCEDURE ret_type_out (ret_table OUT emp_nested_table)

AS

BEGIN

SELECT CAST (MULTISET (SELECT *

FROM emp) AS emp_nested_table)

INTO ret_table

FROM DUAL;

END;

/

Since procedures are not supported in Discoverer, so, we create a function that executes the above procedure and returns nested table as below: -

CREATE OR REPLACE FUNCTION fun_out_param

RETURN emp_nested_table

AS

ret_table emp_nested_table;

BEGIN

ret_type_out (ret_table);

RETURN ret_table;

END;

Create a custom folder in discoverer admin using the following sql query

select * from table(fun_out_param)

Finally, create a new workbook using the above custom folder.

With collections, it is possible to return a table from a pl/sql function.

First, we need to create a new object type that contains the fields that are going to be returned:

Click here for more help on Object type and Collection.

*************************************

create or replace type emp_type as object

(

EMPNO NUMBER(4),

ENAME VARCHAR2(10),

JOB VARCHAR2(9),

MGR NUMBER(4),

HIREDATE DATE,

SAL NUMBER(7,2),

COMM NUMBER(7,2),

DEPTNO NUMBER(2)

)

/
*************************************
 

Then, out of this new type, a nested table type is created.

*************************************

create or replace type emp_nested_table as table of emp_type;

/
*************************************
 

Now, we're ready to actually create the function:

*************************************

/* Formatted on 2008/05/09 14:37 (Formatter Plus v4.7.0) */

create or replace function return_table return emp_nested_table as

v_return_tab emp_nested_table ;

begin

v_return_tab := emp_nested_table();

v_return_tab.extend;

v_return_tab(v_return_tab.count) := emp_type(

7369, 'SMITH', 'CLERK', 7902, TO_Date( '12/17/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')

, 800, NULL, 20);

v_return_tab.extend;

v_return_tab(v_return_tab.count) := emp_type(

7499, 'ALLEN', 'SALESMAN', 7698, TO_Date( '02/20/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')

, 1600, 300, 30);

v_return_tab.extend;

v_return_tab(v_return_tab.count) := emp_type(

7788, 'SCOTT', 'ANALYST', 7566, TO_Date( '12/09/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')

, 3000, NULL, 20);

return v_return_tab;

end return_table;

/

*************************************

Here's how the function is used:

*************************************

select * from table(return_table);
 

EMPNO

ENAME

JOB

MGR

HIREDATE

SAL

COMM

DEPTNO

7369

SMITH

CLERK

7902

17-12-2008

800.00

20.00

7499

ALLEN

SALESMAN

7698

20-02-1981

1,600.00

300.00

30.00

7788

SCOTT

ANALYST

7566

09-12-2008

3,000.00

20.00

 
*************************************

Returning a dynamic set

Now, the function is extended so as to return a dynamic set.

The function will return the row values from emp whose hiredate is in the range that is passed to the function.

*************************************

/* Formatted on 2008/05/09 16:17 (Formatter Plus v4.7.0) */

CREATE OR REPLACE FUNCTION ret_type (frhiredate varchar2, tohiredate varchar2)

RETURN emp_nested_table

AS

ret_table emp_nested_table;

BEGIN

SELECT CAST (MULTISET (SELECT *

FROM emp

WHERE to_char(hiredate,'DD-MM-YYYY') BETWEEN frhiredate AND tohiredate) AS emp_nested_table

)

INTO ret_table

FROM DUAL;

RETURN ret_table;

END;

/
*************************************

And here's how the function is called.

select * from table(ret_type(ret_type ('01-01-2008','01-12-2008'));

EMPNO

ENAME

JOB

MGR

HIREDATE

SAL

COMM

DEPTNO

7369

SMITH

CLERK

7902

17-11-2008

800.00

20.00

7499

ALLEN

SALESMAN

7698

20-02-2008

1,600.00

300.00

30.00

7788

SCOTT

ANALYST

7566

09-07-2008

3,000.00

20.00