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, 2007, Sergei Kuchin, email: skuchin@ispwest.com, skuchin@yahogmail .
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.