Selects, Cursors, RefCursors, Cursor Expressions, some simple examples of rowset manipulation in SQL and PL/SQL

Kevin Meade's picture
AttachmentSize
Plain text icon ray_rowsets.sql_.txt6.02 KB
articles: 

So a buddy of mine, Ray, asked me for some examples of how data could be returned from a PL/SQL procedure. After a short discussion and some fiddling in SQL*Plus, we produced a neat document with some easy examples of what he could do. I still don't know how he is hooking this up to his coding tools, but it is a good demonstration of alternatives for manipulating sets of rows so I figured I'd post it. Hope someone besides Ray finds it useful. Ray's original question was "Kev, is there a way to return data from PL/SQL code so I can use it like a set of rows?". If you are an advanced PL/SQL developer you probabely already know this stuff but then again, it might be worth a five minute look for you.

SQL> set doc on
SQL> 
SQL> /*
DOC>drop function f_ray
DOC>/
DOC>drop function f_ray2
DOC>/
DOC>drop function f_ray3
DOC>/
DOC>drop function f_ray4
DOC>/
DOC>drop type c_ray
DOC>/
DOC>drop type o_ray
DOC>/
DOC>*/
SQL> 
SQL> --
SQL> -- starting with some simple data
SQL> --
SQL> select 1 c1,2 c2,3 c3 from dual
  2  /

        C1         C2         C3
---------- ---------- ----------
         1          2          3

1 row selected.

SQL> 
SQL> --
SQL> -- create an object type in oracle in 2 steps
SQL> -- 1) make a "record" type so to speak (called an OBJECT in Oracle parlance)
SQL> -- 2) make a "table" type (called a COLLECTION, which is a set of objects (aka. a set of rows?))
SQL> --
SQL> create type o_ray is object (a number,b number,c number)
  2  /

Type created.

SQL> 
SQL> create type c_ray is table of o_ray
  2  /

Type created.

SQL> 
SQL> --
SQL> -- create a function that returns the collection type
SQL> -- notice we are initializing the collection to an empty set
SQL> -- not actually necessary in this case because we are using whole object assignment here
SQL> -- but if you want to put rows into the collection one at a time you would have to init it first
SQL> --
SQL> -- then we have a wrapper select gather and convert a set of rows into the collection variable
SQL> -- by using special operators that tell Oracle SQL it is collecting a set of rows (multiset)
SQL> -- and that these should be converted into "such and such" collection (cast... c_ray)
SQL> --
SQL> -- we can then return the collection once full
SQL> --
SQL> -- it should be noted that the collection is housed in memory, so there is a practical limit
SQL> -- the to number of rows and row widths that can be manipulated with simple "table" functions
SQL> -- like this one
SQL> --
SQL> create function f_ray return c_ray is
  2  	c_ray_v c_ray := c_ray();
  3  begin
  4  	select cast(multiset(
  5  			     select 1,2,3 from dual
  6  			    ) as c_ray
  7  		   )
  8  	into c_ray_v
  9  	from dual
 10  	;
 11  	return (c_ray_v);
 12  end;
 13  /

Function created.

SQL> show errors
No errors.
SQL> 
SQL> --
SQL> -- call the function in a from clause and convert its resulting collection to a set of rows
SQL> --
SQL> select *
  2  from table(cast(f_ray as c_ray))
  3  /

         A          B          C
---------- ---------- ----------
         1          2          3

1 row selected.

SQL> 
SQL> --
SQL> -- as an alternative, make the function pipelined to reduce memory consumption if collection is large
SQL> -- or if you want to create a sequence of "piped" functions working together as in some kind of ETL
SQL> -- pipelined functions returns rows of the collection one at a time (or groups at a time maybe)
SQL> -- thus the only memory needed is that necessary to hold data till the return (normally one row)
SQL> -- this is far more memory efficient and when done in series with multiple such functions
SQL> -- can lead to excellent parallel processing.  It can be very useful for ETL processes.
SQL> --
SQL> create function f_ray2 return c_ray pipelined is
  2  	o_ray_v o_ray;
  3  begin
  4  	for r1 in (
  5  		   select 1 c1,2 c2,3 c3 from dual
  6  		  ) loop
  7  	   o_ray_v := o_ray(r1.c1,r1.c2,r1.c3);
  8  	   pipe row (o_ray_v );
  9  	end loop;
 10  	return;
 11  end;
 12  /

Function created.

SQL> show errors
No errors.
SQL> 
SQL> --
SQL> -- this function is referenced the same way as its non-pipelined version
SQL> --
SQL> select *
  2  from table(cast(f_ray2 as c_ray))
  3  /

         A          B          C
---------- ---------- ----------
         1          2          3

1 row selected.

SQL> 
SQL> --
SQL> -- another thing we can do...
SQL> -- we can create a refcursor as the return type of the function
SQL> -- but this is more normally done when sending data outside of oracle
SQL> -- as in: a result set heading for a java routine
SQL> --
SQL> create function f_ray3 return sys_refcursor is
  2  	c1 sys_refcursor;
  3  begin
  4  	open c1 for select 1 c1,2 c2,3 c3 from dual;
  5  	return c1;
  6  end;
  7  /

Function created.

SQL> show errors
No errors.
SQL> 
SQL> --
SQL> -- unfortunately you cannot treat refcursors like rowsets in Oracle SQL (at least not yet)
SQL> -- because it just won't work: Oracle sql can't select directly from refcursors in the from clause
SQL> -- and there are not built in transform functions for such a thing
SQL> -- I think it has something to do with refcursors not being "self describing" or some such thing
SQL> -- so the following sql will raise an error
SQL> --
SQL> --from table(cast(f_ray3 as c_ray))
SQL> -- 	       *
SQL> --ERROR at line 2:
SQL> --ORA-00932: inconsistent datatypes: expected - got CURSER
SQL> --
SQL> --
SQL> select *
  2  from table(cast(f_ray3 as c_ray))
  3  /
from table(cast(f_ray3 as c_ray))
                *
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected - got CURSER


SQL> 
SQL> --
SQL> -- you may however reference the refcursor as a column value
SQL> -- because it is a scalar comming out of the function
SQL> -- it is a special datatype perhapes, but it is still a scalar datatype
SQL> --
SQL> select f_ray3 c1
  2  from dual
  3  /

C1
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

        C1         C2         C3
---------- ---------- ----------
         1          2          3

1 row selected.


1 row selected.

SQL> 
SQL> --
SQL> -- our collections were scalars too
SQL> --
SQL> select f_ray2 c1
  2  from dual
  3  /

C1(A, B, C)
---------------------
C_RAY(O_RAY(1, 2, 3))

1 row selected.

SQL> 
SQL> 
SQL> --
SQL> -- our collections were scalars too
SQL> --
SQL> select f_ray c1
  2  from dual
  3  /

C1(A, B, C)
---------------------
C_RAY(O_RAY(1, 2, 3))

1 row selected.

SQL> 
SQL> --
SQL> -- there are also things called cursor expressions
SQL> -- these are fast and easy ways to turn sets of rows into scalars
SQL> -- very handy for generating XML data
SQL> -- and although not shown here, these can be correlated to driving table(s)
SQL> -- thus accepting datavalues from the rows of driving table(s) in order to drive their execution
SQL> --
SQL> select cursor(select 1,2,3 from dual) c1
  2  from dual
  3  /

C1
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

         1          2          3
---------- ---------- ----------
         1          2          3

1 row selected.


1 row selected.

------------------------------------------------------------
SQL> 
SQL> --
SQL> -- jumping outside our main discussion for just a moment, here is a correlated cursor expression
SQL> -- by examining the results you can see how the col_list scalar (a set of rows loaded in one column value)
SQL> -- is correct for the corresponding table
SQL> --
SQL> select a.table_name,cursor(select column_name from user_tab_columns b where b.table_name = a.table_name) col_list
  2  from user_tables a
  3  /

TABLE_NAME                     COL_LIST
------------------------------ --------------------
MY_ALL_DEPENDENCIES            CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

COLUMN_NAME
------------------------------
OWNER
NAME
TYPE
REFERENCED_OWNER
REFERENCED_NAME
REFERENCED_TYPE
REFERENCED_LINK_NAME
DEPENDENCY_TYPE

8 rows selected.

MY_ALL_OBJECTS                 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

COLUMN_NAME
------------------------------
OWNER
OBJECT_NAME
SUBOBJECT_NAME
OBJECT_ID
DATA_OBJECT_ID
OBJECT_TYPE
CREATED
LAST_DDL_TIME
TIMESTAMP
STATUS
TEMPORARY
GENERATED
SECONDARY

13 rows selected.
--
-- lots of edited out data went here
--
------------------------------------------------------------

SQL> 
SQL> --
SQL> -- ok, back to the original thought
SQL> --
SQL> -- for more fun, we can combine mechanims so that we have a totally open
SQL> -- way of passing any query, executing it, and returning the results back via sql
SQL> -- as long as the result set fits the collection type
SQL> --
SQL> create function f_ray4 (string_p in varchar2) return c_ray pipelined is
  2  	c1 sys_refcursor;
  3  	o_ray_v o_ray;
  4  begin
  5  	open c1 for string_p;
  6  	loop
  7  	   o_ray_v := o_ray(null,null,null);
  8  	   fetch c1 into o_ray_v.a,o_ray_v.b,o_ray_v.c;
  9  	   if c1%notfound then exit; end if;
 10  	   pipe row (o_ray_v);
 11  	end loop;
 12  	close c1;
 13  	return;
 14  end;
 15  /

Function created.

SQL> show errors
No errors.
SQL> 
SQL> --
SQL> -- looks just like the other stuff only we pass the query into the function call as a string
SQL> -- rather than embedding it in the code directly
SQL> --
SQL> select *
  2  from table(cast(f_ray4('select 1 c1,2 c2,3 c3 from dual') as c_ray))
  3  /

         A          B          C
---------- ---------- ----------
         1          2          3

1 row selected.

SQL> 
SQL> --
SQL> -- of course one has to ask if you are doing dynamic sql this way then why not just generate the entire
SQL> -- query in a simpler format at runtime
SQL> --
SQL> select *
  2  from (select 1 c1,2 c2,3 c3 from dual)
  3  /

        C1         C2         C3
---------- ---------- ----------
         1          2          3

1 row selected.

SQL> 
SQL> --
SQL> -- or, why not just use the even simpler version
SQL> --
SQL> select 1 c1,2 c2,3 c3 from dual
  2  /

        C1         C2         C3
---------- ---------- ----------
         1          2          3

1 row selected.

SQL> 
SQL> spool off

Kevin

Comments

Kevin,

Good info. I wanted to pass along to your readers an issue I uncovered when evaluating a packaged application that made heavy use of ref cursors. We found that, even though the same SQL statements were being executed over and over via ref cursors, the engine was parsing the statements every time they were executed. Thus, even though the packaged app was using stored procedures, our hit rate on the SQL cache was only about 70%.

So, I would recommend using pipelined stored functions to return collections of objects rather than using ref cursors, not only for the reason listed above, but because the collection returned by the stored function can be used in SQL statements (via the TABLE operator) whereas ref cursors may not.

Regards.

Kevin Meade's picture

Tom of asktomhome fame, has a good post of parsing and refcursors. In short he points out this:

1) refcursors must parse every time (for various reasons)
2) you can alleviate much of the pain by good use of init.ora parameter session_cached_cursors
3) unless there is a good reason not to, use static cursors over dynamic cursors and refcursors

(try:) http://asktom.oracle.com (searching with the phrase:) Parsing Refcursor

Thanks, Kevin

send me as early as possible

Kevin Meade's picture

Hierarchies usually require traversing a parent/child chain. You can always write your own functions to do this though they would be special purpose each time. Here is an example (maybe not very effiencent but you will ge the idea). Let us list employees and their management hierarchy.

create table emp (emp_id number not null, ename varchar2(30) not null, mgr_emp_id number);

create unique index emp_pk on emp (emp_id);
create unique index emp_uk1 on emp (ename);
create index emp_fk1 on emp (mgr_emp_id);

alter table emp
   add constraint emp_pk primary key (emp_id)
   add constraint emp_uk1 unique (ename)
   add constraint emp_fk1 foreign key (mgr_emp_id) references emp
/

create or replace type o_emp_tree is object (emp_id number,mgr_emp_id number,levelno number)
/
create or replace type c_emp_tree is table of o_emp_tree
/

create or replace
function f_get_emp_tree(emp_id_p in number) return c_emp_tree is
   c_emp_tree_final_v c_emp_tree := c_emp_tree();
   c_emp_tree_work_v c_emp_tree := c_emp_tree();
   current_level_v number;
begin
--
-- add the focus employee to our employee tree
--
   current_level_v := 0;
   select cast(multiset(
                        select emp_id,mgr_emp_id,current_level_v
                        from emp
                        where emp_id = emp_id_p
                       ) as c_emp_tree
              )
   into c_emp_tree_final_v
   from dual
   ;
   if nvl(c_emp_tree_final_v.count,0) = 0 then goto theend; end if;
--
-- go down the tree and add returned rows to our result set
--
   loop
      current_level_v := current_level_v + 1;
      c_emp_tree_work_v.delete;
      select cast(multiset(
                           select emp_id,mgr_emp_id,current_level_v
                           from emp
                           where mgr_emp_id in (
                                                select emp_id
                                                from table(cast(c_emp_tree_final_v as c_emp_tree))
                                                where levelno = current_level_v - 1
                                               )
                          ) as c_emp_tree
                 )
      into c_emp_tree_work_v
      from dual
      ;
      if nvl(c_emp_tree_work_v.count,0) = 0 then exit; end if;
      select cast(multiset(select * from (
                                          select *
                                          from table(cast(c_emp_tree_final_v as c_emp_tree))
                                          union all
                                          select *
                                          from table(cast(c_emp_tree_work_v as c_emp_tree))
                                         )
                          ) as c_emp_tree
                 )
      into c_emp_tree_final_v
      from dual;
   end loop;
--
-- go up the tree and add returned rows to our result set
--
   current_level_v := 0;
   loop
      current_level_v := current_level_v - 1;
      c_emp_tree_work_v.delete;
      select cast(multiset(
                           select emp_id,mgr_emp_id,current_level_v
                           from emp
                           where emp_id in (
                                            select mgr_emp_id
                                            from table(cast(c_emp_tree_final_v as c_emp_tree))
                                            where levelno = current_level_v + 1
                                           )
                          ) as c_emp_tree
                 )
      into c_emp_tree_work_v
      from dual
      ;
      if nvl(c_emp_tree_work_v.count,0) = 0 then exit; end if;
      select cast(multiset(select * from (
                                          select *
                                          from table(cast(c_emp_tree_final_v as c_emp_tree))
                                          union all
                                          select *
                                          from table(cast(c_emp_tree_work_v as c_emp_tree))
                                         )
                          ) as c_emp_tree
                 )
      into c_emp_tree_final_v
      from dual;
   end loop;

<<theend>> null;
   return (c_emp_tree_final_v);
end;
/

insert into emp values (1,'emp1',null);
insert into emp values (2,'emp2',1);
insert into emp values (3,'emp3',2);

select lpad(' ',abs(x.levelno)*5,' ')||x.emp_id emp_id
from table(cast(f_get_emp_tree(2) as c_emp_tree)) x
order by levelno,emp_id
/

EMP_ID
-------------------------
     1
2
     3

3 rows selected.

Listing is centered around the employee of interest with management chain above the employee listed and indented above, and management chain below them (their reports recursively) listed below.

Hi Kevin

I used your useful indications on using pipelined functions approach to generate records in custom types, and using them as if they were real tables to query.
The following package enabled to me not to create working table, nor to know in advance the dimension and particular form of the object required by the user during his runtime queries.
This particular use of pipelined function is new to me, and I hope this package may be useful for someone else too.

Let me know what do you think about...and what others concrete uses of the pipelined function you experienced during your professional experience as DBA.

Thank you in advance

Federico Giallombardo

CREATE OR REPLACE PACKAGE DATE_GENERATOR AS
/******************************************************************************
NAME: DATE_GENERATOR
PURPOSE:

REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 15/02/2007 Federico Giallombardo
******************************************************************************/
--With the next is possible to describe an ordered set of weeks since a fixed week, back or forth, and then those
--between a fixed couple of input week date.
--In particular the next describe a single week with the week number as input param.In this case the weeks are
--evaluated with a positive index if we consider the weeks in the past, and with a negative
--index if we go into the future.
--In the actual approach the weeks are described from Monday to next Sunday.

FUNCTION WEEK_DESCR_BY_NUMBER(week_back IN NUMBER) RETURN VARCHAR2;

--Service function to read the language settings in chosing the date format.
FUNCTION Find_Nls_Date_Language RETURN VARCHAR2;

--service function to give the week description according to the date considered.

--The next give a complex type MyDate to enable orking with pipelined function.

/**
CREATE OR REPLACE
TYPE myDate AS OBJECT (
weekIndex NUMBER,
initDate Date,
endDate Date,
weekDescr VARCHAR2(250)
);
--...
**/

FUNCTION WEEK_DESCR_BY_DATE(initDate IN DATE, week_back IN NUMBER) RETURN myDate;

/**
The following functions return a table type myDateTable composed with single row type myDate
CREATE OR REPLACE TYPE myDateTable IS TABLE OF myDate;
**/

--Returns the n weeks preceding the current, in table form
FUNCTION DATES_BEFORE(initDate IN DATE, numWeek IN NUMBER) RETURN myDateTable PIPELINED;

--Returns the n weeks following the current, in table form
FUNCTION DATES_AFTER(initDate IN DATE, numWeek IN NUMBER) RETURN myDateTable PIPELINED;

--Returns the weeks between the first passed week and the second
--The first week begins the first day of the first date week, the last week ends with the end of the second date week.
FUNCTION DATES_BETWEEN(initdate IN DATE, endDate IN DATE) RETURN myDateTable PIPELINED;

--The following Returns the first day in the week to which the date parameter belongs, and the last day in the week
--to which the date parameter belongs.
FUNCTION FIRST_DAY_IN_WEEK(pDate IN DATE) RETURN DATE;

FUNCTION LAST_DAY_IN_WEEK(pDate IN DATE) RETURN DATE;

END DATE_GENERATOR;
/

CREATE OR REPLACE PACKAGE BODY DATE_GENERATOR AS
/******************************************************************************
NAME: DATE_GENERATOR
PURPOSE:

REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 15/02/2007 Federico Giallombardo
******************************************************************************/

FUNCTION Find_Nls_Date_Language
RETURN VARCHAR2
IS
date_language_setting VARCHAR2(1000);
BEGIN
select SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language
INTO date_language_setting
from Dual;

DBMS_OUTPUT.put_line('Date language Settings: ' || date_language_setting);
RETURN date_language_setting;
END;

FUNCTION WEEK_DESCR_BY_NUMBER(week_back IN NUMBER)
RETURN VARCHAR2
IS
week_number VARCHAR2(1000);
first_day_week VARCHAR2(1000);
last_day_week VARCHAR2(1000);
msg VARCHAR2(1000);
first_day_descr VARCHAR2(100);
last_day_descr VARCHAR2(100);
lang VARCHAR2(100);
BEGIN
SELECT to_char((sysdate - (7 * week_back)), 'WW')
INTO week_number
FROM dual;

lang := Find_Nls_Date_Language();

IF (lang = 'AMERICAN' OR lang = 'ENGLISH') THEN
first_day_descr := 'Mon';
last_day_descr := 'Sun';
ELSE
first_day_descr := 'Lun';
last_day_descr := 'Dom';
END IF;

SELECT TO_CHAR(next_day(trunc(
(
SELECT TO_DATE(TO_CHAR(TRUNC(SYSDATE - (7 * (week_back + 1)), 'WW'), 'DD MONTH YYYY'), 'DD-MON-YYYY')
FROM DUAL
), 'WW'), first_day_descr), 'DD MONTH YYYY')
INTO first_day_week
from Dual;

SELECT TO_CHAR(next_day(trunc(
(
SELECT TO_DATE(TO_CHAR(TRUNC(SYSDATE - (7 * (week_back)), 'WW'), 'DD MONTH YYYY'), 'DD-MON-YYYY')
FROM DUAL
), 'WW'), last_day_descr), 'DD MONTH YYYY')
INTO last_day_week
FROM DUAL;

msg := 'Week n.'||week_number||' from '||first_day_week|| ' to ' || last_day_week;

DBMS_OUTPUT.put_line(msg);
RETURN msg;

EXCEPTION
WHEN OTHERS THEN
RETURN TO_DATE('01-01-1900', 'MM-DD-YYYY');
END;

FUNCTION WEEK_DESCR_BY_DATE(initDate IN DATE, week_back IN NUMBER) RETURN myDate
IS
week_number VARCHAR2(1000);
first_day_week VARCHAR2(1000);
last_day_week VARCHAR2(1000);
msg VARCHAR2(1000);
first_day_descr VARCHAR2(100);
last_day_descr VARCHAR2(100);
lang VARCHAR2(100);
dt myDate;
BEGIN
SELECT to_char((initDate - (7 * week_back)), 'WW')
INTO week_number
FROM dual;
DBMS_OUTPUT.put_line('week_number: ' || week_number);

lang := Find_Nls_Date_Language();

IF (lang = 'AMERICAN' OR lang = 'ENGLISH') THEN
first_day_descr := 'Mon';
last_day_descr := 'Sun';
ELSE
first_day_descr := 'Lun';
last_day_descr := 'Dom';
END IF;

SELECT TO_CHAR(next_day(
(
SELECT TO_DATE(TO_CHAR(initDate - (7 * (week_back + 1)), 'DD MONTH YYYY'), 'DD-MON-YYYY')
FROM DUAL
), first_day_descr), 'DD MONTH YYYY')
INTO first_day_week
from Dual;

SELECT TO_CHAR(next_day(
(
SELECT TO_DATE(TO_CHAR(initDate - (7 * (week_back + 0.1)), 'DD MONTH YYYY'), 'DD-MON-YYYY') --( )
FROM DUAL
), last_day_descr), 'DD MONTH YYYY')
INTO last_day_week
FROM DUAL;

msg := 'Week n.'||week_number||' dal '||first_day_week|| ' al ' || last_day_week;

DBMS_OUTPUT.put_line(msg);
dt := myDate(
week_number,
TO_DATE(first_day_week, 'dd/mm/yyyy'),
TO_DATE(last_day_week, 'dd/mm/yyyy'),
msg
);

RETURN dt;

EXCEPTION
WHEN OTHERS THEN
dt := myDate(
0,
TO_DATE('01-01-1900', 'MM-DD-YYYY'),
TO_DATE('01-01-1900', 'MM-DD-YYYY'),
'Week description composed with error.'
);

RETURN dt;
END;

FUNCTION DATES_BEFORE(initDate IN DATE, numWeek IN NUMBER)
RETURN myDateTable
PIPELINED
IS
curDate myDate;
BEGIN
IF (numWeek >= 1) THEN
FOR i IN 1..numWeek LOOP
curDate := WEEK_DESCR_BY_DATE(initDate, i);
pipe ROW(
curDate
);
END LOOP;
RETURN;
ELSE
DBMS_OUTPUT.put_line('It''s necessary to chose a number of weeks >=1 !!');
END IF;
END;



FUNCTION DATES_AFTER(initDate IN DATE, numWeek IN NUMBER)
RETURN myDateTable
PIPELINED
IS
curDate myDate;
BEGIN
IF (numWeek >= 1) THEN
FOR i IN 0..numWeek LOOP
curDate := WEEK_DESCR_BY_DATE(initDate, -i);
pipe ROW(
curDate
);
END LOOP;
RETURN;
ELSE
DBMS_OUTPUT.put_line('It''s necessary to chose a number of weeks >= 1 !!');
END IF;
END;

/**
select * from TABLE(DATE_GENERATOR.DATES_AFTER(TO_DATE('15/02/2007', 'dd/mm/yyyy'), 12))
WHERE WEEKINDEX IN (7, 8, 9) AND INITDATE = TO_DATE('12/02/2007', 'dd/mm/yyyy')
ORDER BY WEEKINDEX
**/

FUNCTION FIRST_DAY_IN_WEEK(pDate IN DATE)
RETURN DATE
IS
lang VARCHAR2(100);--variable to control the db language settings.
first_day_week VARCHAR2(1000);
first_date_week DATE;
first_day_descr VARCHAR2(1000);
BEGIN
lang := Find_Nls_Date_Language();

IF (lang = 'AMERICAN' OR lang = 'ENGLISH') THEN
first_day_descr := 'Mon';
ELSE
first_day_descr := 'Lun';
END IF;

SELECT TO_CHAR(next_day(trunc(
(
SELECT TO_DATE(TO_CHAR(TRUNC(pDate - 7, 'WW'), 'DD MONTH YYYY'), 'DD-MON-YYYY')
FROM DUAL
), 'WW'), first_day_descr), 'DD MONTH YYYY')
INTO first_day_week
from Dual;

first_date_week := TO_DATE(first_day_week, 'dd/mm/yyyy');
RETURN first_date_week;

END;

FUNCTION LAST_DAY_IN_WEEK(pDate IN DATE)
RETURN DATE
IS
lang VARCHAR2(100);--Variable to control data language settings.
last_day_week VARCHAR2(1000);
last_date_week DATE;
last_day_descr VARCHAR2(1000);
BEGIN
lang := Find_Nls_Date_Language();

IF (lang = 'AMERICAN' OR lang = 'ENGLISH') THEN
last_day_descr := 'Sun';
ELSE
last_day_descr := 'Dom';
END IF;

SELECT TO_CHAR(next_day(trunc(
(
SELECT TO_DATE(TO_CHAR(TRUNC(pDate, 'WW'), 'DD MONTH YYYY'), 'DD-MON-YYYY')
FROM DUAL
), 'WW'), last_day_descr), 'DD MONTH YYYY')
INTO last_day_week
from Dual;

last_date_week := TO_DATE(last_day_week, 'dd/mm/yyyy');
RETURN last_date_week;

END;

FUNCTION DATES_BETWEEN(initdate IN DATE, endDate IN DATE)
RETURN myDateTable
PIPELINED
IS
curDate myDate;
indexInitWeek NUMBER;
indexEndWeek NUMBER;
numWeekElapsed INT;
BEGIN
IF (initDate <= endDate) THEN
select to_char(initDate, 'WW')
INTO indexInitWeek
from dual;

DBMS_OUTPUT.put_line('indexInitWeek ' || indexInitWeek);

select to_char(endDate, 'WW')
INTO indexEndWeek
from dual;


DBMS_OUTPUT.put_line('initDate: ==>> ' || initDate);
DBMS_OUTPUT.put_line('endDate: ==>> ' || endDate);

DBMS_OUTPUT.put_line(' first week date in period: ' || FIRST_DAY_IN_WEEK(initDate));
DBMS_OUTPUT.put_line(' last week date in period: ' || LAST_DAY_IN_WEEK(endDate));

select
trunc(LAST_DAY_IN_WEEK(endDate) - FIRST_DAY_IN_WEEK(initDate))/7 "Weeks"
INTO numWeekElapsed
from dual;

--numWeekElapsed := mod((endDate - initDate), 7);


DBMS_OUTPUT.put_line('numWeeksElapsed ' || numWeekElapsed);

FOR j IN 0..numWeekElapsed - 1 LOOP
curDate := WEEK_DESCR_BY_DATE(initDate, -j);
pipe ROW(curDate);
END LOOP;
RETURN;
ELSE
DBMS_OUTPUT.put_line('The init week must be prior to or at most equals to the end week !!');
END IF;
END;

/**
Es. using PIPELINED FUNCTION returning TABLE TYPES....

SELECT * FROM TABLE(DATE_GENERATOR.DATES_BETWEEN(
TO_DATE('12/11/2006', 'dd/mm/yyyy'),
TO_DATE('15/02/2007', 'dd/mm/yyyy')
)
) WHERE INITDATE BETWEEN
(TO_DATE('01/12/2006', 'dd/mm/yyyy')) AND
(TO_DATE('11/01/2007', 'dd/mm/yyyy'))


SELECT * FROM TABLE(DATE_GENERATOR.DATES_BEFORE(
TO_DATE('12/11/2006', 'dd/mm/yyyy'),
11))

ORDER BY INITDATE ASC


SELECT * FROM TABLE(DATE_GENERATOR.DATES_AFTER(
TO_DATE('12/11/2006', 'dd/mm/yyyy'),
11
))
**/

END DATE_GENERATOR;
/

Kevin Meade's picture

so I figure the prior solution I gave was actually special purpose. This one is likely more along the lines of what was asked for.

create table emp (emp_id number not null, ename varchar2(30) not null, mgr_emp_id number);

create unique index emp_pk on emp (emp_id);
create unique index emp_uk1 on emp (ename);
create index emp_fk1 on emp (mgr_emp_id);

alter table emp
add constraint emp_pk primary key (emp_id)
add constraint emp_uk1 unique (ename)
add constraint emp_fk1 foreign key (mgr_emp_id) references emp
/

create or replace type o_emp_tree is object (emp_id number,mgr_emp_id number,levelno number)
/
create or replace type c_emp_tree is table of o_emp_tree
/

insert into emp values (1,'emp1',null);
insert into emp values (2,'emp2',1);
insert into emp values (3,'emp3',2);

create or replace package pkg_emptree is

function f_get_emptree (emp_id_p in number) return c_emp_tree;

end;
/
show errors

create or replace package body pkg_emptree is

function f_get_emptree (emp_id_p in number) return c_emp_tree is
c_emp_tree_v c_emp_tree := c_emp_tree();
o_emp_tree_v o_emp_tree;
current_level_v number;
procedure go_up (emp_id_p in number) is
begin
for r1 in (
select emp_id,mgr_emp_id,current_level_v-1 levelno
from emp
where emp_id = emp_id_p
) loop
c_emp_tree_v.extend;
c_emp_tree_v(c_emp_tree_v.last) := o_emp_tree(r1.emp_id,r1.mgr_emp_id,r1.levelno);
current_level_v := current_level_v - 1;
go_up(r1.mgr_emp_id);
end loop;
end;
procedure go_down (emp_id_p in number) is
begin
for r1 in (
select emp_id,mgr_emp_id,current_level_v+1 levelno
from emp
where mgr_emp_id = emp_id_p
) loop
c_emp_tree_v.extend;
c_emp_tree_v(c_emp_tree_v.last) := o_emp_tree(r1.emp_id,r1.mgr_emp_id,r1.levelno);
current_level_v := current_level_v - 1;
go_down(r1.emp_id);
end loop;
end;
begin
--
-- get the starting employee
--
select o_emp_tree(emp_id,mgr_emp_id,0)
into o_emp_tree_v
from emp
where emp_id = emp_id_p
;
--
-- add manager hierarchy
--
current_level_v := 0;
go_up(o_emp_tree_v.mgr_emp_id);
--
-- add the employee
--
c_emp_tree_v.extend;
c_emp_tree_v(c_emp_tree_v.last) := o_emp_tree_v;
--
-- add the employee's subordinate hierachy
--
current_level_v := 0;
go_down(o_emp_tree_v.emp_id);
--
-- send the results back
--
return (c_emp_tree_v);
end;

end;
/
show errors

select lpad(' ',abs(x.levelno)*5,' ')||x.emp_id emp_id
from table(cast(pkg_emptree .f_get_emptree(2) as c_emp_tree)) x
order by levelno,emp_id
/