# OTL 3.1, Workaround 1 (PL/SQL tables as parameters)

# Workaround 1 (PL/SQL tables as parameters)

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.

## Source Code

    #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;

    }

## Output

    V1_SIZE=4
    V1(1)=1100
    V1(2)=2100
    V1(3)=3100
    V1(4)=0

Examples Contents Go Home

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.