Home » Developer & Programmer » Precompilers, OCI & OCCI » Retrieve table data from PL/SQL into OCCI (Oracle 10g2)
Retrieve table data from PL/SQL into OCCI [message #351700] Thu, 02 October 2008 10:12 Go to next message
ThMielke
Messages: 3
Registered: October 2008
Junior Member
I have an anonymous PL/SQL procedure executed from OCCI like:

declare
type temp is table of emp%rowtype;
vemp temp;
begin
select * bulk collect into vemp
from emp where nose = 'red';
open :1 for select * from table(vemp);
end

and a cpp code like:

string stmt = "see above";
stmt->registerOutParam (1, OCCICURSOR);
stmt->execute ();

The "execute" gives me the error (of course):
PLS-00642: local collection types not allowed in SQL statements

O.k., I cannot select from an PL/SQL table.
But how to transfer these PL/SQL table "vemp" into my OCCI program?

Note: the first select is for information only, so I can not select the data in question from that directly.
Re: Retrieve table data from PL/SQL into OCCI [message #351704 is a reply to message #351700] Thu, 02 October 2008 10:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to create a SQL type (with CREATE TYPE), you can't use a PL/SQL type.

Regards
Michel
Re: Retrieve table data from PL/SQL into OCCI [message #351747 is a reply to message #351704] Thu, 02 October 2008 16:06 Go to previous messageGo to next message
ThMielke
Messages: 3
Registered: October 2008
Junior Member
Is there no other (straight) way to return table data from PL/SQL to OCCI?
Re: Retrieve table data from PL/SQL into OCCI [message #351774 is a reply to message #351747] Fri, 03 October 2008 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Returning a cursor not a table is the most efficient way.
begin
open :1 for select * from emp;
end;

Then bulk collect in program.

Regards
Michel
Re: Retrieve table data from PL/SQL into OCCI [message #351788 is a reply to message #351774] Fri, 03 October 2008 02:39 Go to previous messageGo to next message
ThMielke
Messages: 3
Registered: October 2008
Junior Member
Yes, I know that (I'm not an absolute beginner)!

What I want to know, how to read a PL/SQL collection with OCCI calls in an efficient way.

Like "open :1 for select * from vemp".
Re: Retrieve table data from PL/SQL into OCCI [message #351789 is a reply to message #351788] Fri, 03 October 2008 02:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 02 October 2008 17:27
You have to create a SQL type (with CREATE TYPE), you can't use a PL/SQL type.

Regards
Michel

But it is NOT efficient, and will never be: you fetch twice the same data.

Regards
Michel

Re: Retrieve table data from PL/SQL into OCCI [message #398478 is a reply to message #351789] Thu, 16 April 2009 06:48 Go to previous message
jmiglia
Messages: 1
Registered: April 2009
Junior Member
you should return a REF CURSOR variable to the calling C routine
Previous Topic: Errors during Insert operation in Pro*C
Next Topic: SQLnet + c guides?
Goto Forum:
  


Current Time: Thu Mar 28 04:15:01 CDT 2024