Two ways to use a cursor in a SQL query

To use a cursor in a query, there are two possible ways I’ve used so far:

The first one is using the nested table(or varray). The idea is to read the cursor into the nested table and convert the nest table into a normal table in SQL.
Here is an example:

set serveroutput on;

-- These two types has to be global types, otherwise it will give “PLS-00642 local collection types not allowed in SQL statements”
-- Or maybe you can try using “record” instead of “object”, if it works, it will ease the cursor->nested table part(i.e. using “fetch bulk into” instead of the loop).
-- I’ve tried for a few hours and I gave up…
--create or replace type csrRow as object ( user_nm varchar2(20) );
--create or replace type csrTable as table of csrRow;

declare
cursor csr is select user_nm from table1; -- this is a sample cursor
tmpCsrRow table1.user_nm%type;
csrRow1 csrRow;
csrTable1 csrTable := csrTable(); -- initialize the nested table
counter number;
begin

-- Read the cursor into the nested table(csrTable1)
open csr;
loop
fetch csr into tmpCsrRow;
exit when csr%notfound;
csrTable1.extend;
csrTable1(csrTable1.last) := csrRow(tmpCsrRow);
end loop;
close csr;

--Use the nested table in a regular SQL statement
select count(*) into counter from table(cast(csrTable1 as csrTable));
dbms_output.put_line('Line count:' || counter);

end;



Another way is using pipelined table.
The idea is to create a wrapper for the ref cursor, the wrapper is a pipelined table. And in sql, use “select * from table(pipelinedTable)” to get the data.
Example:

create or replace package pipelinedTest as
type refCsrRec is record ( user_nm varchar2(20) );
type refCsrTable is table of refCsrRec;
function wrapper return refCsrTable pipelined;
end pipelinedTest;
/
create or replace package body pipelinedTest as
function wrapper return refCsrTable pipelined is --This wrapper function converts a cursor into a pipelined table
cursor csr is select user_nm from table1;
tmpRec refCsrRec;
begin
open csr;
loop
fetch csr into tmpRec;
exit when csr%notfound;
pipe row(tmpRec);
end loop;
close csr;
return;
end wrapper;
end pipelinedTest;
/
set serveroutput on;

declare
counter number;
begin
select count(*) into counter from table( pipelinedTest.wrapper() );
dbms_output.put_line('Line count:' || counter);
end;

I tried to write the wrapper() function as an inner function, but got some problems(I think it’s about SQL engine can’t see the dynamically defined PL/SQL function) so I just put it in a package like what we did for Cognos.


Conclusion
PL/SQL is short for a Pathetic Language/Seems Quite Lame

Software Development, $ by time makes simple things complex

A software development company shouldn't be like a garage - doing repeating jobs and charge clients on how much time developers have spent on it.

This makes a simple job complex, this impairs efficiency, discourages innovation... I don't think it is necessary to give any examples to proof this. You know what it is like if you've worked in a traditional software company.

Then what should software development look like?
I don't know yet.