📄 otl3_wa01.htm
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"><HTML><HEAD> <TITLE>OTL 3.1, Workaround 1 (PL/SQL tables as parameters)</TITLE> <META NAME="Author" CONTENT="Sergei Kuchin"> <META NAME="GENERATOR" CONTENT="Mozilla/3.03Gold (Win95; I) [Netscape]"> <META NAME="KeyWords" CONTENT="OTL, Oracle, ODBC, DB2, CLI, database API, C++, Template Library"></HEAD><BODY><H1 ALIGN=CENTER>OTL 3.1, Workaround 1 (PL/SQL tables as parameters)</H1><H1><A NAME="example1"></A>Workaround 1 (PL/SQL tables as parameters)</H1><P>This is an example of a workaround on how to pass data from C++ intoPL/SQL tables and back. This example works with both OCI7 and OCI8. Theidea here is to use global PL/SQL table variables.</P><H2>Source Code</H2><PRE>#include <iostream>using namespace std;#include <stdio.h></PRE><PRE>#define OTL_ORA7 // Compile OTL 3.1/OCI7#include <otlv31.h> // include the OTL 3.1 header file<A HREF="otl3_connect_class.htm">otl_connect</A> db; // connect object</PRE><PRE>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(){ <A HREF="otl3_connect_class.htm">otl_connect::otl_initialize</A>(); // 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(<A HREF="otl3_exception_class.htm">otl_exception</A>& 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;}</PRE><H2>Output</H2><PRE>V1_SIZE=4V1(1)=1100V1(2)=2100V1(3)=3100V1(4)=0<HR WIDTH="100%"></PRE><CENTER><P><A HREF="otl3_examples.htm">Examples</A> <A HREF="otl3.htm">Contents</A><A HREF="home.htm">Go Home</A> </P></CENTER><P>Copyright © 1996, 2008, Sergei Kuchin, email: <A HREF="mailto:skuchin@aceweb.com">skuchin@aceweb.com</A>,<A HREF="mailto:skuchin@gmail.com">skuchin@yahogmail</A><SCRIPT Language="JavaScript"><!-- hide from old browsers var modDate = new Date(document.lastModified) document.write("<i> Last Updated:</i> " + (modDate.getMonth()+1) + "/" + modDate.getDate() + "/" + "0"+(modDate.getYear())%100+"."); //--> </SCRIPT>.</P><P><I>Permission to use, copy, modify and redistribute this document forany purpose is hereby granted without fee, provided that the above copyrightnotice appear in all copies. </I></P><script type="text/javascript">var gaJsHost = (("https:" == document.location.protocol) ? "https://ssl." : "http://www.");document.write(unescape("%3Cscript src='" + gaJsHost + "google-analytics.com/ga.js' type='text/javascript'%3E%3C/script%3E"));</script><script type="text/javascript">var pageTracker = _gat._getTracker("UA-5456201-1");pageTracker._trackPageview();</script></BODY></HTML>⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -