Home » SQL & PL/SQL » SQL & PL/SQL » Insert from cursor
Insert from cursor [message #271512] Mon, 01 October 2007 16:22 Go to next message
kalkumar
Messages: 8
Registered: July 2007
Junior Member
I have one function which is returnign the table name

Create Procedure Proc1
( ..)
AS
vQuery varchar2(200);
vTableName varchar2(30);
vCursor sys_Refcursor;
Begin

vTableName := Function1(..);

Now I need to frame the query which will select from the above table and need to insert that result into another table(in my case need to insert into temp table)

vQuery := 'Select * from ' || vTableName ||' Where ';
Open vCursor for vQuery;
Can I do like this:

Insert into Table2( ) (Open vCursor for vQuery);

Or How to the above type of insert. I have to fetch each record from the above cursor. Can anyone help me on this?
Thanks
Re: Insert from cursor [message #271514 is a reply to message #271512] Mon, 01 October 2007 16:31 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Insert into Table2( ) (Open vCursor for vQuery);
Which book/manual showed you the above was correct syntax; or are you just throwing characters together hoping that some thing might work.

You can find the SQL Reference Manual at http://tahiti.oracle.com
Re: Insert from cursor [message #271520 is a reply to message #271514] Mon, 01 October 2007 17:09 Go to previous messageGo to next message
kalkumar
Messages: 8
Registered: July 2007
Junior Member
Hi,
I am not s aying I got that statement from the book. My requirement is like that insert values from cursor. Any way I solved that problem.

Thanks
Re: Insert from cursor [message #271521 is a reply to message #271512] Mon, 01 October 2007 17:14 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
many fine coding examples can be found at http://asktom.oracle.com
Re: Insert from cursor [message #271535 is a reply to message #271512] Mon, 01 October 2007 23:28 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why not directly
vQuery := 'insert into table2 (...) select ... from '||function1(...);
execute immediate vquery;

Regards
Michel
Previous Topic: MAX .. Group By
Next Topic: PL SQL procedure optimization
Goto Forum:
  


Current Time: Sun Dec 04 10:38:13 CST 2016

Total time taken to generate the page: 0.09022 seconds