Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2006
    Posts
    2

    Unanswered: 9i, OCCI, passing an array to a PL/SQL procedure

    Hello-
    I am using Oracle 9i on AIX 5.2. I am also using OCCI to run our program.

    We have a great need to do bulk inserts. I would like to have a stored procedure that takes in an array and then uses a forall loop to insert.

    I have seen posts and examples all throughout many boards (and recommended sites) on the PL/SQL side.

    I would like to get help/understand the OCCI side and the call that needs to be made.

    Right now in my c++ code I have this to insert a couple of records into a table with 3 columns:

    **
    vector<string> myArray;
    myArray.push_back("r1:first");
    myArray.push_back("r1:row");
    myArray.push_back("r1:insert");
    myArray.push_back("r2:second");
    myArray.push_back("r2:row");
    myArray.push_back("r2:data");


    stmt = conn->createStatement("execute proc_foo(:1)");
    setVector(stmt,1,myArray,"FOO_OBJECT");
    stmt->executeQuery();

    **
    Is that the correct thought process behind this? Our DBA wrote a procedure that accepts an array as a FOO_ARRAY, and the FOO_ARRAY is defined as a FOO_OBJECT which looks like the table that we are trying to insert into. I'm trying to get the procedure from her- will post it when I get it.

    A lot of my confusion from all of the examples I've seen lies in the insert part of the PL/SQL- everything I have seen does a BULK COLLECT from a table that is already populated in the database, and then uses that data to bulk insert into the new table. I need to take what is in my c++ code (users enter info) and bulk insert into a table.

    If anyone can help, I would really appreciate it. I am not an Oracle buff, so hopefully I explained correctly what I'm looking for. I did look through all the recommended sites first before posting.

    Thanks!

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Firstly, think of your PLSQL procedure as if it did only one insert. What would it need as parameters ?

    The answer is one parameter per column you want to insert. For example :

    If you had a table like :

    Code:
    CREATE TABLE Sample_Table(
        title VARCHAR2(50) CONSTRAINT sampletable_pk PRIMARY KEY, 
        content VARCHAR2(500));
    and a nested table array type like :

    Code:
    CREATE OR REPLACE TYPE VARCHAR2_TABLE AS TABLE OF VARCHAR2(4000);
    /
    you would have a procedure like :

    Code:
    CREATE OR REPLACE PROCEDURE Sample_proc(strTitle VARCHAR2, strContent VARCHAR2) AS
    
    BEGIN
    
        INSERT INTO Sample_Table(
            title,
            content)
        VALUES(
            strTitle,
            strContent);
    
         -- if you want to commit here
         COMMIT;
    
    END Sample_proc;
    /
    Now you want to do bulk inserts, so what would you change ? Just pass array parameters instead of simple variables :

    Code:
    CREATE OR REPLACE PROCEDURE Sample_proc(
        strTblTitle VARCHAR2_TABLE, 
        strTblContent VARCHAR2_TABLE) AS
    
    BEGIN
    
        FOR i IN strTblTitle.FIRST..strTblTitle.LAST LOOP
    
            INSERT INTO Sample_Table(
                title,
                content)
            VALUES(
                strTblTitle(i),
                strTblContent(i));
    
        END LOOP;
    
         -- if you want to commit here
         COMMIT;
    
    END Sample_proc;
    /
    This is not bad, but a standard FOR loop like this makes a switch between PLSQL and SQL contexts on each iteration. PLSQL has something better : FORALL, which is a special loop structure that switches only once to the SQL context, executes all the queries within the loop and then returns to the PLSQL context. FORALL has a big restriction : one can only put one insert, update or delete statement within the loop, no PLSQL.

    Code:
    CREATE OR REPLACE PROCEDURE Sample_proc(
        strTblTitle VARCHAR2_TABLE, 
        strTblContent VARCHAR2_TABLE) AS
    
    BEGIN
    
        FORALL i IN strTblTitle.FIRST..strTblTitle.LAST
            INSERT INTO Sample_Table(
                title,
                content)
            VALUES(
                strTblTitle(i),
                strTblContent(i));
    
        -- No "LOOP" or "END LOOP"
    
        -- if you want to commit here
         COMMIT;
    
    END Sample_proc;
    /
    With the code above, an exception will stop the loop. You can use SAVE EXCEPTIONS to avoid this, and then the special structure SQL%BULK_EXCEPTIONS to deal with these exceptions.

    Code:
    CREATE OR REPLACE PROCEDURE Sample_proc(
        strTblTitle VARCHAR2_TABLE, 
        strTblContent VARCHAR2_TABLE) AS
    
    BEGIN
    
        FORALL i IN strTblTitle.FIRST..strTblTitle.LAST SAVE EXCEPTIONS
            INSERT INTO Sample_Table(
                title,
                content)
            VALUES(
                strTblTitle(i),
                strTblContent(i));
    
        -- No "LOOP" or "END LOOP"
    
        -- if you want to commit here
         COMMIT;
    
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Number of errors : '||SQL%BULK_EXCEPTIONS.COUNT);
            FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
                DBMS_OUTPUT.PUT_LINE('Error ' || i || ' occurred during '|| 'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
                DBMS_OUTPUT.PUT_LINE('Oracle error is ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
            END LOOP;
    
    END Sample_proc;
    /
    For the C++ side, you would do something like :

    Code:
    vector<string> strArrTitle;
    strArrTitle.push_back("1st title");
    strArrTitle.push_back("2nd title");
    strArrTitle.push_back("3rd title");
    vector<string> strArrContent;
    strArrContent.push_back("1st content");
    strArrContent.push_back("2nd content");
    strArrContent.push_back("3rd content");
    
    stmt = conn->createStatement("BEGIN Sample_proc(:1, :2); END;");
    setVector(stmt,1,strArrTitle,"VARCHAR2_TABLE");
    setVector(stmt,2,strArrContent,"VARCHAR2_TABLE");
    // PLSQL procedures must be called via an executeUpdate()
    stmt->executeUpdate();
    HTH & Regards,

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

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Beautiful!

  4. #4
    Join Date
    Oct 2006
    Posts
    2

    Thank you!

    Wow- thank you so much! That really helped out and my DBA and I were able to get something working.

    And thanks for the info on the SAVE EXCEPTIONS- we will definitely implement that.

    I really appreciate all of the information.

  5. #5
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by Littlefoot
    Beautiful!
    Thanks .

    Quote Originally Posted by tmlomb
    Wow- thank you so much! That really helped out and my DBA and I were able to get something working.

    And thanks for the info on the SAVE EXCEPTIONS- we will definitely implement that.

    I really appreciate all of the information.
    You're welcome. I'm glad I could help .

    Regards,

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

  6. #6
    Join Date
    Nov 2010
    Posts
    1

    desde argentina agradecimientos

    les agradezco de corazon la explicacion ya que me fue de gran ayuda los felicito muchas gracias... tanksssssssssssssssssssss

Posting Permissions

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