# OTL 4.0, Example 62 (Insert and Select via referenced cursor for Oracle 8i)

# Example 62 (Insert and Select via referenced cursor for Oracle 8i)

This example demonstrates a simple INSERT, SELECT (via refernced cursor), and constant SQL statements. Referenced cursors in Oracle 8i are slightly different from referenced cursors in Oracle 8, so the code generation of OCI8 function calls for OCI8 in Oracle 8i is different from OCI8 in Oracle 8. Therefore, a new #define was introduced: OTL_ORA8I.

## Source Code

    #include <iostream>
    using namespace std;

    #include <stdio.h>

    #define OTL_ORA8I // Compile OTL 4.0/OCI8 in the Oracle 8i environment.
    #include <otlv4.h> // include the OTL 4.0 header file

    otl_connect db; // connect object

    void insert()
    // insert rows into table
    {
     otl_stream o(50, // buffer size
                  "insert into test_tab values(:f1<float>,:f2<char[31]>)",
                     // SQL statement
                  db // connect object
                 );
     char tmp[32];

     for(int i=1;i<=100;++i){
      sprintf(tmp,"Name%d",i);
      o<<(float)i<<tmp;
     }
    }

    void select()
    {
     otl_stream i(50, // buffer size
                  "begin "
                  " open :cur for "
                  "  select * "
                  "  from test_tab "
                  "  where f1>=:f<int> and f1<=:f*2; "
                  "end;", // PL/SQL block returns a referenced cursor
                  db, // connect object
                  ":cur" // referenced cursor placeholder name
                 );
       // create select stream with referenced cursor

     float f1;
     char f2[31];

     i<<8; // assigning :f = 8
       // SELECT automatically executes when all input variables are
       // assigned. First portion of output rows is fetched to the buffer

     while(!i.eof()){ // while not end-of-data
      i>>f1>>f2;
      cout<<"f1="<<f1<<", f2="<<f2<<endl;
     }

     i<<4; // assigning :f = 4
       // SELECT automatically executes when all input variables are
       // assigned. First portion of output rows is fetched to the buffer

     while(!i.eof()){ // while not end-of-data
      i>>f1>>f2;
      cout<<"f1="<<f1<<", f2="<<f2<<endl;
     }

    }

    int main()
    {
     otl_connect::otl_initialize(); // initialize OCI environment
     try{

      db.rlogon("scott/tiger"); // connect to Oracle

      otl_cursor::direct_exec
       (
        db,
        "drop table test_tab",
        otl_exception::disabled // disable OTL exceptions
       ); // drop table

      otl_cursor::direct_exec
       (
        db,
        "create table test_tab(f1 number, f2 varchar2(30))"
        );  // create table

      insert(); // insert records into table
      select(); // select records from table

     }

     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

    f1=8, f2=Name8
    f1=9, f2=Name9
    f1=10, f2=Name10
    f1=11, f2=Name11
    f1=12, f2=Name12
    f1=13, f2=Name13
    f1=14, f2=Name14
    f1=15, f2=Name15
    f1=16, f2=Name16
    f1=4, f2=Name4
    f1=5, f2=Name5
    f1=6, f2=Name6
    f1=7, f2=Name7
    f1=8, f2=Name8

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.