This is an example of a workaround on how to pass data from C++ into PL/SQL tables and back. This example works with both OCI7 and OCI8. The idea here is to use global PL/SQL table variables.
#include <iostream> using namespace std; #include <stdio.h>
#define OTL_ORA7 // Compile OTL 3.1/OCI7 #include <otlv31.h> // include the OTL 3.1 header file otl_connect db; // connect object
void plsql(void) { otl_stream s1(100, // buffer size "begin " " pkg_test.glob_v1(:ndx<int,in>) := :A<int,in>; " "end;", // PL/SQL block: initialize the global PL/SQL table variable db // connect object ); s1.set_commit(0); // set stream auto-commit off since // the block does not have any transactions // to commit s1<<1<<1000; // v1(1) := 1000; s1<<2<<2000; // v1(2) := 2000; s1<<3<<3000; // v1(3) := 3000; s1.flush(); // executing PL/SQL block 3 times otl_stream s2(1, // buffer size "begin " " pkg_test.prc_test(pkg_test.glob_v1); " " :v1_size<int,out> := pkg_test.glob_v1.last; " "end;", // PL/SQL block: execute the stored procedure and get // the size of the v1 PL/SQL table db // connect object ); s2.set_commit(0); int v1_size=0; s2>>v1_size; // execute the stored procedure and get the PL/SQL table // size cout<<"V1_SIZE="<<v1_size<<endl; otl_stream s3(100, // buffer size "begin " " :v1<int,out> := pkg_test.glob_v1(:ndx<int,in>); " "end;", // PL/SQL block: initialize the global PL/SQL table variable db // connect object ); s3.set_commit(0); // set stream auto-commit off since // the block does not have any transactions // to commit // get the elements of the PL/SQL table for(int i=1;i<=v1_size;++i)s3<<i; s3.flush(); int v,j=0; while(!s3.eof()){ // not end-of-data ++j; s3>>v; cout<<"V1("<<j<<")="<<v<<endl; } } int main() { otl_connect::otl_initialize(); // initialize OCI environment try{ db.rlogon("scott/tiger"); // connect to Oracle otl_cursor::direct_exec (db, "CREATE OR REPLACE PACKAGE pkg_test IS " " TYPE my_plsql_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; " " " " PROCEDURE prc_test(v1 IN OUT my_plsql_table); " " " " glob_v1 my_plsql_table; " " " "END; " ); otl_cursor::direct_exec (db, "CREATE OR REPLACE PACKAGE BODY pkg_test IS " " " " PROCEDURE prc_test(v1 IN OUT my_plsql_table) " " IS " " BEGIN " " FOR i IN 1..v1.last LOOP " " v1(i) := v1(i)+100; " " END LOOP; " " v1(v1.last+1) := 0; " " END; " " " "END; " ); plsql(); } catch(otl_exception& p){ // intercept OTL exceptions cerr<<p.msg<<endl; // print out error message cerr<<p.stm_text<<endl; // print out SQL that caused the error cerr<<p.var_info<<endl; // print out the variable that caused the error } db.logoff(); // disconnect from Oracle return 0; }
V1_SIZE=4 V1(1)=1100 V1(2)=2100 V1(3)=3100 V1(4)=0
Copyright © 1996-2023, Sergei Kuchin, email: skuchin@gmail.com, skuchin@gmail.com .
Permission to use, copy, modify and redistribute this document for any purpose is hereby granted without fee, provided that the above copyright notice appear in all copies. THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.