Re: Returning data from temp_tables

On Aug 6, 1:48 pm, BGT <bgt0..._at_optonline.net> wrote:
> Actually it's not that simple. That was my first approach. I cannot
> define the cursor with a dynamic SQL bind variable, I get an error;
> The cursor I'm trying to pass back is dependent on the results of a
> previous cursor loop.
>
> create or replace procedure foo(bar OUT sys_refcursor) is
>
> sqlstring := 'select empno from emp' where emp.empno = :1
> c1rec emp.empno%type;
>
> begin
>
> open C1 for select empno from emp;
> LOOP
> FETCH c1 INTO c1rec;
> EXIT WHEN C1%NOTFOUND;
>
> open bar for sqlstring
> Using c1rec;
> Loop
> Fetch bar into ???
> exit when bar%NOTFOUND;
> End LOOP;
> END LOOP;
> Close C1;
>
> end;

It IS that simple. It is just because you don't read *carefully*

Read this *carefully*

create or replace procedure foo(bar in out sys_refcursor) is sqlstr varchar2(1000);
empno number(10);
begin
empno := 7369;
open bar for 'select * from emp where empno = :1 ' using empno; end;

SQL> begin
  2 foo(:bar);
  3 end;
  4 /

PL/SQL procedure successfully completed.

SQL> print bar

     EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ----------

    DEPTNO

      7369 SMITH      CLERK           7902 17-12-80        800
        20

See, it is THAT simple.

--
Sybrand Bakker
Senior Oracle DBA