# OTL 4.0, Example 88 (STL vector based PL/SQL table of DATEs as parameter, Oracle 8)

# Example 88 (STL vector based PL/SQL table of DATEs as parameter, Oracle 8)

This example demonstrates how to use OTL STL vector based PL/SQL table containers for reading/writing PL/SQL tables of DATEs from/to the otl_stream.

## Source Code

    #define OTL_ORA8 // Compile OTL 4.0/OCI7
    #define OTL_STL // Turn on STL
    #include <otlv4.h> // include the OTL 4.0 header file

    #include <cstdio>
    #include <string>
    #include <iostream>

    using namespace std;

    otl_connect db; // connect object

    void plsql(void)
    {
     otl_stream s(1, // buffer size needs to be set to 1 in this case
                  "begin "
                  "  pkg_test.prc_test(:v<int,in>, :t2<timestamp,out[200]>); "
                  "end;",
                  db // connect object
                 );

     s.set_commit(0); // Since there is no transactions, unset the stream auto-commit

     otl_datetime_vec t2; // PL/SQL table of otl_datetime in the vector format

     s<<7; // assign :v = 7
     s>>t2; // read :t2 from the stream.

     cout<<endl<<endl<<"T2_Len="<<t2.len()<<endl;
     for(int j=0;j<t2.len();++j)
      if(t2.is_null(j))
       cout<<"T2["<<j<<"]=NULL"<<endl;
      else
       cout<<"T2["<<j<<"]="<<t2.v[j].month
           <<"/"<<t2.v[j].day
           <<"/"<<t2.v[j].year
           <<" "<<t2.v[j].hour
           <<":"<<t2.v[j].minute
           <<":"<<t2.v[j].second
           <<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_date_table IS TABLE OF DATE INDEX BY BINARY_INTEGER; "
        " "
        "  PROCEDURE prc_test(v IN NUMBER, v2 OUT my_date_table); "
        " "
        "END; "
      );

      otl_cursor::direct_exec
       (db,
        "CREATE OR REPLACE PACKAGE BODY pkg_test IS "
        " "
        "  PROCEDURE prc_test(v IN NUMBER, v2 OUT my_date_table) "
        "  IS "
        "  BEGIN "
        "    FOR i IN 1..v LOOP "
        "      v2(i) := SYSDATE-i; "
        "    END LOOP; "
        "  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

    T2_Len=7
    T2[0]=2/29/2000 15:40:33
    T2[1]=2/28/2000 15:40:33
    T2[2]=2/27/2000 15:40:33
    T2[3]=2/26/2000 15:40:33
    T2[4]=2/25/2000 15:40:33
    T2[5]=2/24/2000 15:40:33
    T2[6]=2/23/2000 15:40:33

Examples Contents Go Home

Copyright © 1996, 2007, Sergei Kuchin, email: skuchin@ispwest.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.