This discussion is archived
11 Replies Latest reply: Jan 23, 2013 12:25 AM by BillyVerreynne RSS

How to  insert  300 data from associative array to backend table in PL/SQL

986043 Newbie
Currently Being Moderated
HI ALL,
I'm posting my code here:

Creating back end table:

Create table orlando
( id number(20),
calltype number(12),
     gateway_name varchar2(25),
     accounting_id varchar2(18),
     start_time_system_ticks number(11),
     node_time_zone      varchar2(25),
     start_date varchar2(10),     
     start_time varchar2(10),
     softswitch_response number(11),
     alerting number(11)     
);

Creating package:


CREATE OR REPLACE PACKAGE r IS

type apollo_rec is record(

id number(20),
calltype number(12),
     gateway_name varchar2(25),
     accounting_id varchar2(18),
     start_time_system_ticks number(11),
     node_time_zone      varchar2(25),
     start_date varchar2(10),     
     start_time varchar2(10),
     softswitch_response number(11),
     alerting number(11)
);
TYPE bin_array IS TABLE OF apollo_rec INDEX BY BINARY_INTEGER;
PROCEDURE rr (state_array bin_array);
END ;

SET SERVEROUT ON

CREATE OR REPLACE PACKAGE BODY r IS

PROCEDURE rr (state_array bin_array) IS
BEGIN

FOR i IN 1 .. state_array.COUNT LOOP
INSERT INTO orlando(id,calltype,gateway_name,accounting_id,start_time_system_ticks)VALUES(state_array(i).id,state_array(i).calltype,state_array(i).gateway_name,
state_array(i).accounting_id,state_array(i).start_time_system_ticks);
COMMIT;

END LOOP;

END ;

END ;
/

I've run this code in i*SQL PLUS.But when I run this code for 5 entries there is no error but when I modify the insert statement for 300 entries(300 identifiers in the insert statement)
it gives me error:
Warning: Package Body created with compilation errors.
Errors for PACKAGE BODY R:

LINE/COL      ERROR
7/2      PL/SQL: SQL Statement ignored
7/14      PL/SQL: ORA-00913: too many values

Is there any feature in PL/SQL to decrease the entries in insert statement and make the insert statement along with the program small and increase the program performance.

Edited by: 983040 on Jan 20, 2013 11:11 PM
  • 1. Re: How to  insert  300 data from associative array to backend table in PL/SQL
    KPR Journeyer
    Currently Being Moderated
    Hi

    Try like this...
    create or replace package body R
    
         procedure rr
         (
              state_array in bin_array,
              
         )
         is
         begin
              for i in 1..state_array.count
              loop
                   Insert into table_name
                   ( 
                        column names..
                   ) 
                   values 
                   (
                        state_array(i).id , 
                        state_array(i).calltype ....
                   );
              end loop;
         end rr;
    end r;
    Regards
    KPR
  • 2. Re: How to  insert  300 data from associative array to backend table in PL/SQL
    Karthick_Arp Guru
    Currently Being Moderated
    Welcome to the forum!!

    When ever you get an error please post the Entire error message with Line number. Also always mention your 4 digit oracle version.

    I would suggest you read {message:id=9360002} to get a better understanding of what are the things you need to consider while posting a question to get quick answers.
  • 3. Re: How to  insert  300 data from associative array to backend table in PL/SQL
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    You neglected to mention your 4 digit Oracle version.

    Your code should be using a bulk bind via a FORALL structure. Committing inside a loop is wrong, was wrong, and will be wrong in the future. Incremental commits in Oracle code is just plain bloody silly.

    With 10g versions - you cannot reference members in a bulk bind array when using a bulk bind operation.

    With 11g versions - you can.

    With 10g you thus need an array per member/field. E.g. to insert rows with 10 columns, you need 10 arrays - one per column. Or forego bulk binding and instead use a slower regular FOR loop.

    With 11g, you can use a single array structure with 10 member fields - and bind it in this fashion.


     
    As for this type declaration:
    TYPE bin_array IS TABLE OF apollo_rec INDEX BY BINARY_INTEGER;

    Junk.

    No need for an associative array. Associative arrays should NOT be used for bulk binding as the name of the name-value pair of an array cell needs to be a sequential index number, providing the cell's offset from the start of the array. Which is EXACTLY how a plain normal array works.

    So use a standard array definition:
    TYPE bin_array IS TABLE OF apollo_rec;

    Sample code showing otherwise is wrong. Including that in Oracle's reference manuals.
  • 4. Re: How to  insert  300 data from associative array to backend table in PL/SQL
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Basic example (ran on 11.2.0.3):
    SQL> create table testtab( id number, day date, val varchar2(30) );
    
    Table created.
    
    SQL> 
    SQL> create or replace package TestTabLib as
      2  
      3          type TTestTab is table of testtab%rowtype;
      4  
      5          procedure InsertRows( rowArray TTestTab );
      6  
      7  end;
      8  /
    
    Package created.
    
    SQL> 
    SQL> create or replace package body TestTabLib as
      2  
      3          procedure InsertRows( rowArray TTestTab ) is
      4          begin
      5                  forall i in 1..rowArray.Count
      6                          insert into testtab values rowArray(i);
      7          end;
      8  
      9  end;
     10  /
    
    Package body created.
    
    SQL> 
    SQL> declare
      2          rowArray        TestTabLib.TTestTab;
      3  begin
      4          --// populating the array - using a bulk fetch as
      5          --// an example
      6          select
      7                  object_id, created, object_name
      8                          bulk collect into
      9                  rowArray
     10          from    all_objects
     11          where   rownum < 11;
     12  
     13          --// bulk insert array
     14          TestTabLib.InsertRows( rowArray );
     15  end;
     16  /
    
    PL/SQL procedure successfully completed.
    
    SQL> 
    SQL> select * from testtab;
    
            ID DAY                 VAL
    ---------- ------------------- -------------------------
           100 2011/12/05 09:16:03 ORA$BASE
           116 2011/12/05 09:16:04 DUAL
           117 2011/12/05 09:16:04 DUAL
           280 2011/12/05 09:19:09 MAP_OBJECT
           365 2011/12/05 09:19:10 SYSTEM_PRIVILEGE_MAP
           367 2011/12/05 09:19:10 SYSTEM_PRIVILEGE_MAP
           368 2011/12/05 09:19:10 TABLE_PRIVILEGE_MAP
           370 2011/12/05 09:19:11 TABLE_PRIVILEGE_MAP
           371 2011/12/05 09:19:11 STMT_AUDIT_OPTION_MAP
           373 2011/12/05 09:19:11 STMT_AUDIT_OPTION_MAP
    
    10 rows selected.
    
    SQL> 
    SQL> declare
      2          rowArray        TestTabLib.TTestTab;
      3  begin
      4          --// populating the array - using a custom build
      5          --// loop example such as a Java front-end will
      6          --// use reading data from user input form
      7          rowArray := new TestTabLib.TTestTab();
      8          rowArray.Extend(2);     --// user entered 2 values
      9          for i in 1..rowArray.Count loop
     10                  rowArray(i).id := i;
     11                  rowArray(i).day := trunc(sysdate);
     12                  rowArray(i).val := 'value '||to_char(i,'000');
     13          end loop;
     14  
     15          --// bulk insert array
     16          TestTabLib.InsertRows( rowArray );
     17  end;
     18  /
    
    PL/SQL procedure successfully completed.
    
    SQL> 
    SQL> select * from testtab where val like 'value%';
    
            ID DAY                 VAL
    ---------- ------------------- -------------------------
             1 2013/01/21 00:00:00 value  001
             2 2013/01/21 00:00:00 value  002
    
    SQL> 
  • 5. Re: How to  insert  300 data from associative array to backend table in PL/SQL
    986043 Newbie
    Currently Being Moderated
    Thnx.

    My oracle version is:
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
    PL/SQL Release 10.2.0.3.0 - Production
    CORE 10.2.0.3.0 Production
    TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
    NLSRTL Version 10.2.0.3.0 - Production

    But here I've to use the associative array cuz I'm getting values from associative array.I'm doing for real time work.

    So, I need ur help how I can do the bulk binding.I'm getting associative array and after that my part starts and I 've develop this program to insert those values to the backend table.
    My consideration here to reduce the program size and time.
  • 6. Re: How to  insert  300 data from associative array to backend table in PL/SQL
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    There is no need for using an associative array. Associative arrays are designed to be used as name-value pairs. Your code is NOT using name-value pairs. The SQL engine does NOT support associative arrays - only standard arrays/collections. So if you use an associative array in the PL/SQL engine for SQL, it MUST conform to a standard array.

    In which case using an associative array makes absolutely no sense. Spend a few minutes on googling the differences between a normal/standard array and an associative array. And when to use the latter. There are very seldom any need for using associative arrays in PL/SQL. Especially when using the array as a buffer for passing data to, and receiving data from, the SQL engine.

    And my example above works for 10g too (tested on 10.2.0.1).

    The 10g issue is when attempting to reference a member field in the array via a bulk bind reference. So this is not possible in 10g, but supported in 11g:
    forall i in 1..array.Count
      insert into testtab(col1,col2) values( array(i).col1, array(i).col2 );
    However, as the array structure matches the table structure, my example above shows how the complete array cell can be bound as the value, without having to refer to the individual elements in that cell structure. E.g.
    forall i in 1..array.Count
      insert into testtab(col1,col2) values array(i);
  • 7. Re: How to  insert  300 data from associative array to backend table in PL/SQL
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    983040 wrote:

    My consideration here to reduce the program size and time.
    What program size? Code segment? Data segment?

    Which program? The client program calling the server program (with the array) to perform the insert? The server program receiving the array and doing the insert?
  • 8. Re: How to  insert  300 data from associative array to backend table in PL/SQL
    986043 Newbie
    Currently Being Moderated
    thx....it helped me.

    My program here is from server side; The code u given made the whole program shorter and for 300 entries it will take less time.

    Plz suggest me the way how quickly I can insert 300s entries into a table(simple insert)....like insert into [tablename] values[1,2,3......];

    With regards

    Sonia.
  • 9. Re: How to  insert  300 data from associative array to backend table in PL/SQL
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    983040 wrote:

    My program here is from server side; The code u given made the whole program shorter and for 300 entries it will take less time.
    Plz suggest me the way how quickly I can insert 300s entries into a table(simple insert)....like insert into [tablename] values[1,2,3......];
    You need to be clear what program does the insert, and where the program gets the data to insert from.

    If the program that does the insert, is external (e.g. C or Java, etc), reads the data from a file or the network, then the following approach should be use:
    create and initialise OCI  (Oracle Call Interface)
    parse SQL statement as cursor: 'insert into testtab( col1, col2 ) value( :1, :2 )'
    bind array variables v1 and v2 to bind positions 1 and 2 in SQL cursor
    while read-from-file loop
      read values from file into array variables v1 and v2
      execute insert cursor for array.Count iterations
    end loop
    ..cleanup..
    The insert cursor can also be to a PL/SQL procedure that has 2 parameters and does the insert. (no bulk processing in the PL/SQL procedure - the procedure itself is "bulk" executed)

    This method is documented, with sample code, in the Oracle® Call Interface Programmer's Guide.


     
    If the data comes from an Oracle table and needs to be inserted into another table, the best solution is a PL/SQL procedure that looks something as follows (to optimise this, parallel DML can be considered, and direct path inserts used):
    create or replace procedure Foo( someParam number ) is
    begin
      insert into newtable( col1, col2 )
      select dat1, dat2 from source_table where category = someParam;
    end;
    So the method to use depends entirely on what client-server architecture is used, what the data source is, and what the environment/programming language is that is reading from the data source and inserting that data into an Oracle table.
  • 10. Re: How to  insert  300 data from associative array to backend table in PL/SQL
    986043 Newbie
    Currently Being Moderated
    Hi,

    I just created a table for 350 entries. I myself want to insert data manually in PL/SQL.

    No program does the insert, and where the program gets the data to insert from me like raw data..

    Think ...that I'm assigning each and every value to that table.

    like, a number:=12;
    b varchar2(13):='ABC';


    So, for 300 entries it will be huge insert;
  • 11. Re: How to  insert  300 data from associative array to backend table in PL/SQL
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    If you are doing this manually (typing in the data), you need to type 300 values. This is going to be "large" from the start.

    The easiest way to do this would be to type the data in a file in CSV format. Then use SQL*Loader or the external table feature to load that data into Oracle.

    This will also be the most optimal approach. However, it requires the CSV file to be available and readable by the Oracle database.

    Any other method using SQL*Plus/SQL-Developer for example, will be less optimal.

    SQL*Plus allows you to define bind variables. Which is what is needed for Oracle to create shareable cursors, re-use cursors, and eliminate CPU intensive hard parsing.

    However, SQL*Plus does not provide you with a means to assign values to local variables directly. You need to run PL/SQL code to get a value into the variable. And only then can you use that variable as a bind variable in a shareable SQL. This means the same data (variable value) is send twice to the server. Which is a silly thing to do.

    If you want to do it as a SQL*Plus script then hardcode the insert SQL statements - and alter the session to force cursor sharing before running the script.

    If you are thinking of another method, please explain in detail what you want to do.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points