How to return "Table of Number" etc from Oracle procedure into C++

Scott Fetterman
Scott Fetterman used Ask the Experts™
on
From C++ how can I capture and iterate through the output results of an Oracle PL/SQL stored procedure that returns data as "TABLE of NUMBER(7)", or "TABLE of VARCHAR2(100)" etc?  I haven't found a clear example or documentation that details the accessing data returned as tables.
   I'm using the MS VC9++ (VS2008 SP1) with the Oracle 11.2 32-bit Instant Client. At this point I need to stay at these versions to not risk breaking the compilation of a much larger project with a lot of legacy hardware and drivers.  I've tried many variations that don't compile, or give runtime errors. Running OTT has prove problematic as the TYPE definitions only exist when the procedure is run.
   I'm currently using the OCCI interface but am open to using C++ .net if it will coexist with the current C++ code.

PL/SQL package definition

create or replace PACKAGE          "CARRIERPKG" AS
    TYPE tcarrier_site     is TABLE of NUMBER(7)      INDEX BY BINARY_INTEGER;
    TYPE tpart_serial      is TABLE of VARCHAR2(100)  INDEX BY BINARY_INTEGER;
    TYPE tpart_rtn_msg     is TABLE of VARCHAR(200)   INDEX BY BINARY_INTEGER;
   
 
      PROCEDURE get_parts_v12
      (p_carrier_serial	  IN  phase2.carrier_site.carrier_serial_num%TYPE,
       p_part_num         IN  phase2.part_info.PART_NUM%TYPE,
       p_carrier_type     IN  phase2.carrier_type.id%TYPE,
       carrier_site       OUT NOCOPY tcarrier_site,
       part_serial        OUT NOCOPY tpart_serial,
       rtn_msg            OUT NOCOPY tpart_rtn_msg);

 --------------  End PL/SQL procedure definition
 
 
 //C++ Access using Oracle InstantClient 11p2 compiled with Microsoft Visual Studio 2008 SP1 as a 32-bit app
 void Carrier::CAT_CarrierPkg() 
{ 

  Statement *stmt = con->createStatement();

  try 
  {
   stmt->setSQL("begin  phase2.carrierpkg.get_parts_v12(:1, :2, :3, :4, :5, :6); end;");
   stmt->setString(1, "189134075");     
   stmt->setString(2, "001050717");
   stmt->setString(3, "CARGPA0007");

   stmt->registerOutParam(4, OCCI???);  // What OCCI type and how to iterate over returned data?
   stmt->registerOutParam(5, OCCI???);
   stmt->registerOutParam(6, OCCI???);
  }
  catch (SQLException& ex) 
  { 
    cout << ex.getMessage(); 
  }
  
  try 
  { 
   stmt->execute(); 
  } 
  catch (SQLException& ex) 
  { 
    cout << ex.getMessage(); 
  }
    con->terminateStatement(stmt); 
}

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi Scott,

Here's a link to one of the Oracle pages with some sample code.

 https://docs.oracle.com/cd/E11882_01/appdev.112/e10764/performance.htm#LNCPP20478

Basically, Oracle returns a result set and you loop through the result set to access each of the elements.


Good Luck!
Kent
oracle drivers support for PL Sql tables has been since I remember "going to be supported in the next version"and it has never been in the next version (not sure nowadays I have not used .NET to connect to oracle since we changed to SAP).

I once had to do that and the only way I managed to do it was create a SP that would act as an interface, it would read the tables (parse) convert them to a formatted string that would be returned instead of the PL/SQL table, so that I could use it in .NET, I called the SP and it returned my query in the format I needed, I just created my query in .net in an oracle understandable syntax and passed it to the sp, then the sp would return my results in the way I needed.
 
Maybe ODP now supports it but I don't think so.

Author

Commented:
Kent,
    How do I  register the three outputs as a "results set"?   Does the SP return three separate results sets, or just one? Also, I don't see "results set" as one of the oracle::occi::Type enums.  I've tried OCCIANYDATA, OCCIVECTOR, OCCIPOBJECT, and OCCICURSOR without success.  
   We had been using the MSADORA driver and that worked just fine up until windows-10, when it wouldn't connect to the database.  I'm able to pull back data using OCCI if I issue just a query, so I know the connectivity/permissions are there.
--Scott
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Hi Scott,

That example was actually for connection pooling, so it made multiple connections.  You probably want just a single connection so ignore the parts that reference connections 2 and 3.

Look at the code right after the comment:

//use 1 connection

It looks like what you want.


Kent

Author

Commented:
Hi Kent,
    That section is for a simple SQL query, which I have working.   My problem is binding the outputs from a stored procedure when the output is of type "TABLE of NUMBER(7)" etc.

My SQL statement is:    stmt->setSQL("begin  phase2.carrierpkg.get_parts_v12(:1, :2, :3, :4, :5, :6); end;");    
     Where :4 is returned TYPE is a  "TABLE of NUMBER(7)"
     Where :5 is returned TYPE is a  "TABLE of VARCHAR2(100)"
     Where :6 is returned TYPE is a  "TABLE of VARCHAR(200)"[/indent]

If I'm reading the documentation and examples correctly, I need to register the binding as for example  stmt->registerOutParam(4, OCCI???); , but I'm not sure what OCCI-Type I should be using and if there are other parameters I need to specify in the registerOutParam().

Scott
Hi Scott,

I have to admit that I've never returned a table through procedure's OUT parameter, but if there's any logic to all of this it shouldn't be too tough.

A result set is an internal table that contains the results of a query.  Oracle (Db2, SQL Server, et al) already has the management of table built into it so using the same tools to manage the results instead of developing a completely different data management system makes sense.

Have your call return the table into a ResultSet variable.  The way I read the documentation, the OCCI type should probably be a String.

https://docs.oracle.com/cd/B28359_01/appdev.111/b28390/reference030.htm#LNCPP21749
Top Expert 2016

Commented:
you may try to use

stmt->executeQuery();

Open in new window


rather than stmt->execute();

executeQuery returns a ResultSet which could be iterated.

see https://docs.oracle.com/cd/B28359_01/appdev.111/b28390/relational.htm#LNCPP00343 for samples.

Sara

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial