Home » SQL & PL/SQL » SQL & PL/SQL » Stick the contents of a cursor into a temp table
Stick the contents of a cursor into a temp table [message #279673] Fri, 09 November 2007 09:51 Go to next message
togakangaroo
Messages: 18
Registered: November 2007
Junior Member
I'm sure this has a very simple answer but I've been unable to find the syntax appropriate to do this.

I am trying to appened data columns to a cursor so I would like to create a table, place the contents of the cursor into it and then join with that table to create a new cursor to return.

Pretty simple I would think, but I can't figure out how to do this.

For clarification here's what I'm trying to do:
PROCEDURE someproc(o_cur refcur, myparam INTEGER);
stripped_down_info_c refcur;


BEGIN
get_stripped_down_info(stripped_down_info_c, myparam);

<somehow create a temporary table temp_tbl and insert the contents of stripped_down_info_c into it>

OPEN o_cur FOR
SELECT *
FROM temp_tbl LEFT OUTER JOIN other_info_tbl ON <some condition>;
END;

Anyone have any ideas?
Re: Stick the contents of a cursor into a temp table [message #279694 is a reply to message #279673] Fri, 09 November 2007 11:53 Go to previous messageGo to next message
togakangaroo
Messages: 18
Registered: November 2007
Junior Member
This is getting views but no answer, do I need to clarify what I'm trying to do?

Please please please don't tell me this is impossible to do in any simple manner. At the very least I know I can fetch the cursor row by row and insert it into the table but I might have to kill someone over at oracle if I have to do it in such a slow and inefficient manner.
Re: Stick the contents of a cursor into a temp table [message #279698 is a reply to message #279673] Fri, 09 November 2007 12:14 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
As a general rule, one should not use PL/SQL when the data can be obtained by only using SQL.
In My Opinion, the result set can be obtained by only using SQL & certainly WITHOUT (ab)using a "temporary" table.
Re: Stick the contents of a cursor into a temp table [message #279700 is a reply to message #279698] Fri, 09 November 2007 12:29 Go to previous messageGo to next message
togakangaroo
Messages: 18
Registered: November 2007
Junior Member
Ok, perhaps you could recommend a better method for me to do this.

There are water tanks.
Each tank can recieve transfers into it or out of it
(actually its sifinificantly more complicated but let's just say this is it)
Now transfers in ARE in many ways different from a transfer out and because of the silly way the database was designed they are actually storred in two different tables (ugh).

I would like to compile a single table of all activity on a given tank in a given month. Now previously in the code this has been done using UNION ALL but this method is getting seriously old. There's single sql queries thousands of lines long over the place and every time I go out to make a minor change I need to spend hours drawing charts before I can even understand whats going on.

It would therefore be nice if I could have a function (or procedure) that returned some basic information on transfers in,
and a function that returned basic information on transfers out.

Then I could just call those functions and union their results and tack on columns that apply to both in a single table.

One more note on why I am interested in using cursors. My application works with the OracleClient in .NET which reads in ref cursors so if I impelmented the above functions to return a table I would not be able to call that function from .NET. Not a big deal, I could always write .NET wrappers for that stuff but if there's a way to do it with cursors I'd perfer it.
Re: Stick the contents of a cursor into a temp table [message #279702 is a reply to message #279698] Fri, 09 November 2007 12:35 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Maybe don't use cursors, but a Global Temporary Table (GTT)?

Create a GTT with the columns you need, and then modify the data in that GTT instead of modifying it in the cursors.

That way it may also be easier to break the processing down into simpler steps.

[Updated on: Fri, 09 November 2007 12:35]

Report message to a moderator

Re: Stick the contents of a cursor into a temp table [message #279703 is a reply to message #279673] Fri, 09 November 2007 12:36 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
Alternatively, you should be able to CREATE VIEW & then create the refcursor against the new view.
Re: Stick the contents of a cursor into a temp table [message #279705 is a reply to message #279703] Fri, 09 November 2007 12:41 Go to previous messageGo to next message
togakangaroo
Messages: 18
Registered: November 2007
Junior Member
ah that leads into another question I had.
Can you have a parameter with a view?

Umm...all parameters look nice to me, I mean can you have a view's output depend on a parameter? I couldn't immediately find any examples of how to do this.
Re: Stick the contents of a cursor into a temp table [message #279707 is a reply to message #279705] Fri, 09 November 2007 12:46 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
A view is a stored SQL statement; why do you need a parameter when you can use it in the WHERE clause while selecting from a view?
SQL> create view v_emp as select ename, sal, deptno from emp;

View created.

SQL> select * from v_emp
  2  where deptno = 10;     --> this emulates "parameter 'deptno' = 10

ENAME             SAL     DEPTNO
---------- ---------- ----------
CLARK            2450         10
KING             5000         10
MILLER           1300         10

SQL>
Re: Stick the contents of a cursor into a temp table [message #279708 is a reply to message #279673] Fri, 09 November 2007 12:46 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>Can you have a parameter with a view?
You can not have a parameter with a view, but you could "dynamically" create the desired view just before using it.
Because the creation of the view does not "move" any data it would be more efficient than loading any type of actual table.
Re: Stick the contents of a cursor into a temp table [message #279714 is a reply to message #279708] Fri, 09 November 2007 12:55 Go to previous messageGo to next message
togakangaroo
Messages: 18
Registered: November 2007
Junior Member
anacedent, I'm not sure what you mean. I understand Littlefoot's example but it would seem that if v_emp has a great deal of joins in it and returns a huge amount of records, it is quite a waste of resources to only specify the WHERE criteria after all that data has been generated.
Re: Stick the contents of a cursor into a temp table [message #279725 is a reply to message #279714] Fri, 09 November 2007 14:59 Go to previous message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
Your requirements are pretty confusing and I don't know much about .net, so I am not sure if this is the best way to do things or not, but it is more of a response to your original request, except that it uses a pipelined function instead of a global temporary table, but it does use a function and a procedure and ref cursors. So, here is a demo for your consideration.

SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION get_stripped_down_info
  2    (myparam IN INTEGER)
  3    RETURN SYS_REFCURSOR
  4  AS
  5    stripped_down_info_c SYS_REFCURSOR;
  6  BEGIN
  7    OPEN stripped_down_info_c FOR
  8    SELECT deptno, dname
  9    FROM   dept
 10    WHERE  deptno >= myparam;
 11    RETURN stripped_down_info_c;
 12  END get_stripped_down_info;
 13  /

Function created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE somepkg
  2  AS
  3    TYPE outrec_typ IS      RECORD
  4  	 (deptno	       dept.deptno%TYPE,
  5  	  dname 	       dept.dname%TYPE);
  6    TYPE outrecset  IS      TABLE OF outrec_typ;
  7    TYPE refcur     IS      REF CURSOR RETURN outrec_typ;
  8    FUNCTION pipe_it
  9  	 (stripped_down_info_c refcur)
 10  	 RETURN 	       outrecset PIPELINED;
 11    PROCEDURE someproc
 12  	 (o_cur        IN OUT  SYS_REFCURSOR,
 13  	  myparam      IN      INTEGER);
 14  END somepkg;
 15  /

Package created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE BODY somepkg
  2  AS
  3    FUNCTION pipe_it
  4  	 (stripped_down_info_c	refcur)
  5  	 RETURN 		outrecset PIPELINED
  6    IS
  7  	 out_rec outrec_typ;
  8    BEGIN
  9  	 LOOP
 10  	   FETCH stripped_down_info_c INTO out_rec;
 11  	   EXIT WHEN stripped_down_info_c%NOTFOUND;
 12  	   PIPE ROW (out_rec);
 13  	 END LOOP;
 14  	 CLOSE stripped_down_info_c;
 15  	 RETURN;
 16    END pipe_it;
 17    PROCEDURE someproc
 18  	 (o_cur        IN OUT  SYS_REFCURSOR,
 19  	  myparam      IN      INTEGER)
 20    IS
 21    BEGIN
 22  	 OPEN	o_cur FOR
 23  	 SELECT *
 24  	 FROM	TABLE (somepkg.pipe_it (get_stripped_down_info (myparam))) temp_tbl
 25  	 LEFT OUTER JOIN (SELECT deptno, empno, ename FROM emp) other_info_tbl
 26  	 ON temp_tbl.deptno = other_info_tbl.deptno;
 27    END someproc;
 28  END somepkg;
 29  /

Package body created.

SCOTT@orcl_11g> sHOW ERRORS
No errors.
SCOTT@orcl_11g> VARIABLE g_ref REFCURSOR
SCOTT@orcl_11g> EXECUTE somepkg.someproc (:g_ref, 30)

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> PRINT g_ref

    DEPTNO DNAME              DEPTNO      EMPNO ENAME
---------- -------------- ---------- ---------- ----------
        30 SALES                  30       7900 JAMES
        30 SALES                  30       7844 TURNER
        30 SALES                  30       7698 BLAKE
        30 SALES                  30       7654 MARTIN
        30 SALES                  30       7521 WARD
        30 SALES                  30       7499 ALLEN
        40 OPERATIONS

7 rows selected.

SCOTT@orcl_11g> 


Previous Topic: ORA-00907 missing right parenthesis
Next Topic: Extracting variable length string
Goto Forum:
  


Current Time: Sat Dec 03 14:21:07 CST 2016

Total time taken to generate the page: 0.06384 seconds