REF CURSOR limitation(s)

[williey]

Below is an example of creating a package and procedure using REF CURSOR.

In the package "test_procedure", only test_table is selected. Can I have table joins or views in the SELECT query? Am I only limited to single table query?

create or replace package test_package
AS TYPE test_type IS REF CURSOR RETURN test_table%ROWTYPE;
END test_package;
/


create or replace procedure test_procedure
(
  test_cursor IN OUT test_package.test_type,
  test_parameter IN test_table.id%TYPE)
AS
  BEGIN
    OPEN test_cursor FOR
       SELECT * 
       FROM test_table
       WHERE test_table.id = test_parameter;
END test_procedure;
/

------------------------------------------
There are 10 kinds of people in this world. One that understands binary and the other one that does not.

 

[SantaMufasa]

Williey,

The SELECT that defines a CURSOR can be as elaborate as you need, with as many WHEREs, joins, GROUP BYs, et cetera as your logic calls for.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[williey]

How do you define this line when using complex SELECT statement? What is the valid syntax for "test_table%ROWTYPE?

AS TYPE test_type IS REF CURSOR RETURN test_table%ROWTYPE;

------------------------------------------
There are 10 kinds of people in this world. One that understands binary and the other one that does not.

 

[SantaMufasa]

There are multiple methods for you to define the returning record description, but without knowing the specifics of the record you want to return, perhaps the simplest method is for you to create an empty table (i.e., "WILLIEY_TABLE"), which has the structural components (i.e., columns) that you want to return, then just replace "test_table%ROWTYPE" with "williey_table%rowtype".

Does that sound reasonable?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[williey]

Humor me..This is a sample of what my query looks like
From your example above, do I have to fill the empty table or not?

SELECT   a.field1, 
         function1 (f.field2),
         function2(a.field3),
         a.field4,
         SUM (a.field5),
         SUM (a.field6) ,
         SUM (d.field7),
         SUM (c.field8),
         SUM (function3 (a.field9, a.field10),
         SUM (c.field11),
         SUM (b.field12), 
	 SUM (b.field13)
    FROM table1 a,
         view1 b,
         view2 c,
         view3 d,
         table2 e,
         table3 f
   WHERE a.field0 = b.field0(+)
     AND a.field0 = c.field0(+)
     AND a.field0 = d.field0(+)
     AND a.field0 = e.field0(+)
     AND e.field0 = f.field0(+)
GROUP BY function1 (f.field2),
         a.field1,
         function2 (a.field3),
         field4
ORDER BY function1 (f.field2)

------------------------------------------
There are 10 kinds of people in this world. One that understands binary and the other one that does not.

 

[SantaMufasa]

...do I have to fill the empty table or not?

Not...It exists just to provide the format (via "...%rowtype") for the returning record.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]