Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2006
    Posts
    13

    Question Unanswered: How to pass varray/table parameter to a stored procedure using OCI?

    Hi, Everybody
    I have written stored procedure which has a parameter of tabel and varray type. I'm using OCI to develop application. My question is:
    How to pass a array of c/c++ to the stored procedure's parameters? Do you have such example?
    Thanks very much!

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Here is the documentation for OCI (never used it), and for OCCI you have two ways :

    - setDataBufferArray
    - setVector

    For OCI programming I won't be of much help to you unfortunately but for OCCI programming feel free to ask .

    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  3. #3
    Join Date
    Aug 2006
    Posts
    13
    Can you give me an example of OCCI? Include stored procedure and program code piece? Thank you very much!

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Here we go .

    The SQL / PLSQL part :
    Code:
    CREATE SEQUENCE SEQ_TESTTABLE MAXVALUE 9999999999 CYCLE START WITH 1 INCREMENT BY 1 CACHE 20;
    
    CREATE TABLE Test_Table(
        Id NUMBER(10),
        Description VARCHAR2(300),
        CONSTRAINT TestTable_PK PRIMARY KEY(Id));
        
    CREATE OR REPLACE PACKAGE PKG_BULK_INSERT AS
    
        TYPE VARCHAR2_I_TABLE IS TABLE OF VARCHAR2(300) INDEX BY BINARY_INTEGER;
    
        PROCEDURE BULK_INSERT_TEST(
            strTable IN VARCHAR2_I_TABLE);
            
    END PKG_BULK_INSERT;
    /
    CREATE OR REPLACE PACKAGE BODY PKG_BULK_INSERT AS
    
        PROCEDURE BULK_INSERT_TEST(
            strTable IN VARCHAR2_I_TABLE) AS
            
        i BINARY_INTEGER;
        
        BEGIN
        
            FORALL i IN 1..strTable.COUNT
                INSERT INTO Test_Table(
                    Id,
                    Description)
                VALUES(
                    SEQ_TESTTABLE.NEXTVAL,
                    strTable(i));
        
        END BULK_INSERT_TEST;
            
    END PKG_BULK_INSERT;
    /
    And the OCCI part with setDataBuffer which performs a 100 rows bulk insert :
    Code:
    #include <occi.h>
    #include <unistd.h> // For the "sleep" command
    
    using namespace oracle::occi;
    
    #define MAX_LINES 100
    
    char GcUser[30];   // DB user
    char GcPwd[30];    // DB user's password
    char GcDBName[30]; // DB connection name present in the tnsnames.ora file
    
    Environment* Genv; // OCCI environment
    Connection* Gcon;  // OCCI connection
    
    Statement* GstmtBulkInsertTest; // OCCI statement
    // The variables for the array
    char GcDescriptions[MAX_LINES][300]; // Array of Values
    ub2  Gub2Descriptions_Size[MAX_LINES]; // Array of Lengthes
    sb2  Gsb2Descriptions_Null[MAX_LINES]; // Array of NULL indicators : -1 if NULL
    ub4 Gub4MaxLines = MAX_LINES; // Max number of lines in the Array
    ub4 Gub4CurrentLines = 0; // Current number of lines, must be <= Gub4MaxLines
    
    /*
        Function that creates the statement object.
    */
    int fctInitStatements()
    {
        try
        {
            GstmtBulkInsertTest = Gcon->createStatement();
            GstmtBulkInsertTest->setAutoCommit(FALSE);
            GstmtBulkInsertTest->setSQL("BEGIN RBARAER.PKG_BULK_INSERT.BULK_INSERT_TEST(:1); END;");        
            
            GstmtBulkInsertTest->setDataBufferArray(1, GcDescriptions, OCCI_SQLT_STR, MAX_LINES, &Gub4CurrentLines, 300, Gub2Descriptions_Size, Gsb2Descriptions_Null);
        }
        catch (SQLException &e)
        {      
             printf("Statement Creation Error : %s\n", e.getMessage().c_str());
             return -1;
        } 
        return 1;
    }
    
    /*
        Function that connects to Oracle.
    */
    int fctCreateOracleConnection()
    {  
        try
        {
            Genv = Environment::createEnvironment(Environment::DEFAULT);
            Gcon = Genv->createConnection (GcUser, GcPwd, GcDBName);
            while (fctInitStatements() < 0)
            {
                sleep(3);
            }
        }
        catch (SQLException &e)
        {      
             printf("Oracle Connect Error : %s\n", e.getMessage().c_str());
             return -1;
        } 
    
        return 1;
    }
    
    /*
        Function that loops in order to reconnect to Oracle.
    */
    void fctReconnectToOracle()
    {  
        printf("Disconnected from Oracle");
     
        while (fctCreateOracleConnection() < 0)
        {
            sleep(3);
        }
       
        printf("Reconnected to Oracle");
    }
    
    /*
        Function that performs the bulk insert.
    */
    int fctBulkInsert()
    {
        try
        {
            GstmtBulkInsertTest->executeUpdate();
            Gcon->commit();
        }
        catch (SQLException &e)
        {   
            if((e.getErrorCode() == 1012) || (e.getErrorCode() == 3113) || (e.getErrorCode() == 3114))
            {
                 // Disconnected from Oracle
                 fctReconnectToOracle();
                 return -1;
            }
            
            printf("Oracle Insert Error : %s\n", e.getMessage().c_str());
            return -2;
        } 
        
        return 1;
    }
    
    /*
        Function that frees the OCCI resources.
    */
    int fctFreeOcci()
    {
        
    	try
    	{
    		Gcon->terminateStatement(GstmtBulkInsertTest);
    		Genv->terminateConnection(Gcon);
    		Genv->terminateEnvironment(Genv);
    	}
    	catch(SQLException e)
        {
            printf("Error while freeing resources : %s", e.getMessage().c_str());
            return -1;
        }
        
        return 1;
    } 
    
    /*
        The main function.
    */
    int main (int argc, char** argv)
    {
        int i = 0;
        int iTmp = 0;
        
        if (argc != 4)
        {
            printf("use : test_bulk_insert username userpwd dbconnection\n");
            return -1;
        }
        
        memcpy(GcUser, argv[1], strlen(argv[1]));
        memcpy(GcPwd, argv[2], strlen(argv[2]));
        memcpy(GcDBName, argv[3], strlen(argv[3]));
        
        // Connect to Oracle
        if (fctCreateOracleConnection() < 0)
        {
            printf("Program exited\n");
            return -1;
        }
        
        Gub4CurrentLines = 0;
        
        // Fill the array in
        for (i=0; i<MAX_LINES; i++)
        {
            if (i%10 == 0)
            {
                Gsb2Descriptions_Null[i] = -1; // These will be NULL
            }
            else
            {
                sprintf(GcDescriptions[i], "Insert number %d", i+1);
                Gub2Descriptions_Size[i] = strlen(GcDescriptions[i]) + 1;
                Gsb2Descriptions_Null[i] = 0;
            }
            Gub4CurrentLines++;
        }
        
        // The insert
        iTmp = fctBulkInsert();
        
        while (iTmp == -1) // While insert fails due to a connection failure, loop
        {
            iTmp = fctBulkInsert();
        }
        
        if (iTmp < 0)
        {
            printf("Insert failed\n");
        }
        else
        {
            printf("Insert succeeded\n");
        }
        
        fctFreeOcci();
        
        return 1;
    }
    I've tested it and this code works, though of course it must not be bug free . setDateBuffer does not work with SQL Nested Tables or VARRAYS, it seems only to work with PLSQL Index-By Tables (which I used here). I know the setVector() method works with Nested Tables but I've never used it (a colleague of mine did) and I don't have time to look further into this for now, so I let this up to you .

    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  5. #5
    Join Date
    Aug 2006
    Posts
    13
    Hi, RBARAER,
    Thanks very much!!.
    Advanced I want to know how can I get the paremeters' type. For the procedrue BULK_INSERT_TEST, before I call it, can I get the parameter - strTable' type and find it is a table? Thus I can decide whether to call setDataBufferArray at runtime according different procedures.

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Yes, OCCI allows you to get Metadata for almost every object in the database (provided you have the rights on these objects), including procedures. So you can get the parameters from a procedure as well as their type, and then metadata related to this type if it is an Object or Collection Type.

    I haven't used this personally but the colleague I spoke of used it and it proved to be very useful to us.

    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  7. #7
    Join Date
    Aug 2006
    Posts
    13
    Yes, I found I can get data type if I create it in Oracle:
    Create VARCHAR2_I_TABLE IS TABLE OF VARCHAR2(300)
    But If I only defined this type in a package:
    CREATE OR REPLACE PACKAGE PKG_BULK_INSERT AS
    Type VARCHAR2_I_TABLE IS TABLE OF VARCHAR2(300)
    I can not get the type name, the type name I got is the package's name

  8. #8
    Join Date
    Aug 2004
    Location
    France
    Posts
    754


    Sorry I can't help you here. All I can tell you is that my colleague has used setVector / getVector (not setDataBuffer) and SQL collection types (not PLSQL collection types defined inside packages) and he was able to do what you want. Too bad setDataBuffer does not seem to work with SQL Nested Tables (my test failed, but you can check by yourself and maybe ask support).

    Do not hesitate if you have other questions.

    Good luck & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  9. #9
    Join Date
    Nov 2008
    Posts
    1

    help?? OCCI

    I have written a program in c++ to connect with oracle database.but unable to compile it..

    plz help me.. i am new to both OCCI as weel unix...

    basically i hv problem in make file..

    thanks in advance.. plz help me

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •