# OTL 4.0, Example 153 (create_stored_proc_call)

# Example 153 (create_stored_proc_call)

This example demonstrates the use of otl_stream::create_stored_proc_call() in OTL/OCI8/9.

## Source Code

    #include <iostream>
    using namespace std;

    #include <stdio.h>

    #define OTL_ORA8 // Compile OTL 4.0/OCI8
    //#define OTL_ORA8I // Compile OTL 4.0/OCI8i
    //#define OTL_ORA9I // Compile OTL 4.0/OCI9i
    #include <otlv4.h> // include the OTL 4.0 header file

    otl_connect db; // connect object

    void print_proc_type(const int stm_type)
    {
      switch(stm_type){
      case otl_inout_stream_type:
        cout<<"OTL IN/OUT stream";
        break;
      case otl_refcur_stream_type:
        cout<<"OTL reference cursor stream";
        break;
      case otl_constant_sql_type:
        cout<<"OTL constant statement without any parameters";
        break;
      }
    }

    void stored_proc()
    {
     otl_stream s; // stream variable, external to the create_stored_proc_call()

     char sql_stm[4096];
     int stm_type;
     char refcur_placeholder[128];

      otl_stream::create_stored_proc_call
       (db, // connect object
        s, // an external stream variable is needed here
        sql_stm, // output buffer for generating a stored procedure call
        stm_type, // output paremeter, indicating what type of stored procedure
        refcur_placeholder, // output parameter, which gets populated
                            // in the case of a stored procedure that returns
                            // a reference cursor.
        "my_proc1", // stored procedure name
        "my_pkg" // PL/SQL package name
       );
      cout<<"SQL_STM1="<<sql_stm<<endl;
      cout<<"STM_TYPE1=";
      print_proc_type(stm_type);
      cout<<endl;
      cout<<"REF.CUR.NAME1="<<refcur_placeholder<<endl;
      cout<<endl;

      otl_stream::create_stored_proc_call
       (db, // connect object
        s, // an external stream variable is needed here
        sql_stm, // output buffer for generating a stored procedure call
        stm_type, // output paremeter, indicating what type of stored procedure
        refcur_placeholder, // output parameter, which gets populated
                            // in the case of a stored procedure that returns
                            // a reference cursor.
        "my_proc2", // stored procedure name
        "my_pkg" // PL/SQL package name
       );
      cout<<"SQL_STM2="<<sql_stm<<endl;
      cout<<"STM_TYPE2=";
      print_proc_type(stm_type);
      cout<<endl;
      cout<<"REF.CUR.NAME2="<<refcur_placeholder<<endl;
      cout<<endl;

      otl_stream::create_stored_proc_call
       (db, // connect object
        s, // an external stream variable is needed here
        sql_stm, // output buffer for generating a stored procedure call
        stm_type, // output paremeter, indicating what type of stored procedure
        refcur_placeholder, // output parameter, which gets populated
                            // in the case of a stored procedure that returns
                            // a reference cursor.
        "my_func1", // stored function name
        "my_pkg" // PL/SQL package name
       );
      cout<<"SQL_STM3="<<sql_stm<<endl;
      cout<<"STM_TYPE3=";
      print_proc_type(stm_type);
      cout<<endl;
      cout<<"REF.CUR.NAME3="<<refcur_placeholder<<endl;
      cout<<endl;

      otl_stream::create_stored_proc_call
       (db, // connect object
        s, // an external stream variable is needed here
        sql_stm, // output buffer for generating a stored procedure call
        stm_type, // output paremeter, indicating what type of stored procedure
        refcur_placeholder, // output parameter, which gets populated
                            // in the case of a stored procedure that returns
                            // a reference cursor.
        "my_proc3", // stored procedure name
        "my_pkg" // PL/SQL package name
       );
      cout<<"SQL_STM4="<<sql_stm<<endl;
      cout<<"STM_TYPE4=";
      print_proc_type(stm_type);
      cout<<endl;
      cout<<"REF.CUR.NAME4="<<refcur_placeholder<<endl;
      cout<<endl;

      otl_stream::create_stored_proc_call
       (db, // connect object
        s, // an external stream variable is needed here
        sql_stm, // output buffer for generating a stored procedure call
        stm_type, // output paremeter, indicating what type of stored procedure
        refcur_placeholder, // output parameter, which gets populated
                            // in the case of a stored procedure that returns
                            // a reference cursor.
        "my_func2", // stored function name
        "my_pkg" // PL/SQL package name
       );
      cout<<"SQL_STM5="<<sql_stm<<endl;
      cout<<"STM_TYPE5=";
      print_proc_type(stm_type);
      cout<<endl;
      cout<<"REF.CUR.NAME5="<<refcur_placeholder<<endl;
      cout<<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

      // create a PL/SQL package
      otl_cursor::direct_exec
       (db,
        "CREATE OR REPLACE PACKAGE my_pkg IS "
        " "
        "  TYPE my_cursor IS REF CURSOR; "
        " "
        "  PROCEDURE my_proc1; "
        "  PROCEDURE my_proc2 "
        "   (f1 IN NUMBER, "
        "    f2 IN OUT VARCHAR2, "
        "    f3 OUT DATE); "
        " "
        "  FUNCTION my_func1 "
        "   (f1 IN NUMBER, "
        "    f2 IN VARCHAR2) "
        "  RETURN DATE; "
        " "
        "  PROCEDURE my_proc3 "
        "   (f1 IN NUMBER, "
        "    f2 IN VARCHAR2, "
        "    f3 OUT my_cursor); "
        " "
        "  FUNCTION my_func2 "
        "   (f1 IN NUMBER, "
        "    f2 IN VARCHAR2) "
        "  RETURN my_cursor; "
        " "
        "END; "
       );

      otl_cursor::direct_exec
       (db,
        "CREATE OR REPLACE PACKAGE BODY my_pkg IS "
        " "
        "  PROCEDURE my_proc1 "
        "  IS "
        "  BEGIN "
        "    NULL; "
        "  END; "
        "   "
        "  PROCEDURE my_proc2 "
        "   (f1 IN NUMBER, "
        "    f2 IN OUT VARCHAR2, "
        "    f3 OUT DATE) "
        "  IS "
        "  BEGIN "
        "    NULL; "
        "  END; "
        " "
        "  FUNCTION my_func1 "
        "   (f1 IN NUMBER, "
        "    f2 IN VARCHAR2) "
        "  RETURN DATE "
        "  IS "
        "  BEGIN "
        "    RETURN SYSDATE; "
        "  END; "
        " "
        "  PROCEDURE my_proc3 "
        "   (f1 IN NUMBER, "
        "    f2 IN VARCHAR2, "
        "    f3 OUT my_cursor) "
        "  IS "
        "  BEGIN "
        "    NULL; "
        "  END; "
        " "
        "  FUNCTION my_func2 "
        "   (f1 IN NUMBER, "
        "    f2 IN VARCHAR2) "
        "  RETURN my_cursor "
        "  IS "
        "   lv_cur my_cursor; "
        "  BEGIN "
        "    OPEN lv_cur FOR "
        "    SELECT * FROM test_tab; "
        "    RETURN lv_cur; "
        "  END; "
        " "
        "END; "
       );

      stored_proc(); // generate a stored procedure call

     }

     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

    SQL_STM1=BEGIN my_pkg.my_proc1; END;
    STM_TYPE1=OTL constant statement without any parameters
    REF.CUR.NAME1=

    SQL_STM2=BEGIN my_pkg.my_proc2(:f1<double,in> ,:f2<char[2001],inout> ,:f3<timestamp,out> );  END;
    STM_TYPE2=OTL IN/OUT stream
    REF.CUR.NAME2=

    SQL_STM3=BEGIN :rc__<timestamp,out> := my_pkg.my_func1(:f1<double,in> ,:f2<char[2001],in> );  END;
    STM_TYPE3=OTL IN/OUT stream
    REF.CUR.NAME3=

    SQL_STM4=BEGIN my_pkg.my_proc3(:f1<double,in> ,:f2<char[2001],in> ,:f3  );  END;
    STM_TYPE4=OTL reference cursor stream
    REF.CUR.NAME4=:f3

    SQL_STM5=BEGIN :rc__  := my_pkg.my_func2(:f1<double,in> ,:f2<char[2001],in> );  END;
    STM_TYPE5=OTL reference cursor stream
    REF.CUR.NAME5=:rc__

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.