Home » SQL & PL/SQL » SQL & PL/SQL » Oracle PL/SQL Collection (Oracle 9i)
Oracle PL/SQL Collection [message #322919] Mon, 26 May 2008 19:42 Go to next message
majsaxar
Messages: 4
Registered: May 2008
Location: Atlanta
Junior Member
I am new to this topic and I need some help on this. Here is what I have. I have an array of rows passed to a stored proc. which is not show here. I am working on a proof of concept.
Foe exmaple this row '1,1,54,1,1,1' is actually the values of six columns to be inserted and I want the whole thing to a bulk binding. Instead of reading one raw at a time and hitting the database I want to pass as a one dimensional array as shown below. But so far with no luck. I created a table called my_test_Table with six columns. Any input


DECLARE

TYPE my_test_tbl
IS TABLE OF VARCHAR2(32767);


my_test_tbl _list my_test_tbl ;



BEGIN

my_test_tbl _list (1):='1,1,54,1,1,1';
my_test_tbl _list (2):='2,1,12,1,1,1';
my_test_tbl _list (3):='3,1,15,1,1,1';
my_test_tbl _list (4):='4,1,19,1,1,1';
my_test_tbl _list (5):='5,1,20,1,1,1';
my_test_tbl _list (6):='6,1,23,1,1,1';
my_test_tbl _list (7):='7,1,24,1,1,1';

FORALL indx IN my_test_tbl _list .First..my_test_tbl_list .Last -- line 36, column 4 just before the FORALL


INSERT INTO my_test_Table
VALUES my_test_tbl _list (indx); --line 37 column 12 is begining of my_test_tbl _list

END;

I am getting the following error messages

ORA-06550: line 37, column 12:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 36, column 4:
PL/SQL: SQL Statement ignored

Report message to a moderator

Re: Oracle PL/SQL Collection [message #322920 is a reply to message #322919] Mon, 26 May 2008 19:48 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#i2111652

Please provide from SQL Reference Manual above the syntax which supports what you are trying to accomplish.

Report message to a moderator

Re: Oracle PL/SQL Collection [message #322925 is a reply to message #322920] Mon, 26 May 2008 20:58 Go to previous messageGo to next message
majsaxar
Messages: 4
Registered: May 2008
Location: Atlanta
Junior Member
I did not quite get what your question is referring, but what I presented here is out there in the literature, but simply it did not work for me.

Normally one will write an Insert statement in plain SQL as follows:

INSERT INTO <table_name> VALUES(a,b,c,..,k)

however in Oracle collection (Associative arrays, nested tables and Varrays) together with the FORALL key word which is used instead of the For loop, what I have written in my earlier posting is what is out there. Thanks for your response.

Jama A

Report message to a moderator

Re: Oracle PL/SQL Collection [message #322933 is a reply to message #322919] Mon, 26 May 2008 21:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I did not quite get what your question is referring
Where does the INSERT syntax state a collection can be used?

Report message to a moderator

Re: Oracle PL/SQL Collection [message #322947 is a reply to message #322933] Mon, 26 May 2008 22:51 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
Senior Member
Perhaps something like this will do:

SCOTT@orcl_11g> CREATE TABLE my_test_table
  2    (col1 NUMBER,
  3  	col2 NUMBER,
  4  	col3 NUMBER,
  5  	col4 NUMBER,
  6  	col5 NUMBER,
  7  	col6 NUMBER)
  8  /

Table created.

SCOTT@orcl_11g> DECLARE
  2    TYPE my_test_tbl IS TABLE OF my_test_table%ROWTYPE INDEX BY BINARY_INTEGER;
  3    my_test_tbl_list    my_test_tbl ;
  4  BEGIN
  5    FOR i IN 1 .. 7 LOOP
  6  	 my_test_tbl_list(i).col1 := i;
  7  	 my_test_tbl_list(i).col2 := 1;
  8  	 my_test_tbl_list(i).col4 := 1;
  9  	 my_test_tbl_list(i).col5 := 1;
 10  	 my_test_tbl_list(i).col6 := 1;
 11    END LOOP;
 12    my_test_tbl_list(1).col3 := 54;
 13    my_test_tbl_list(2).col3 := 12;
 14    my_test_tbl_list(3).col3 := 15;
 15    my_test_tbl_list(4).col3 := 19;
 16    my_test_tbl_list(5).col3 := 20;
 17    my_test_tbl_list(6).col3 := 23;
 18    my_test_tbl_list(7).col3 := 24;
 19    FORALL indx IN my_test_tbl_list.FIRST .. my_test_tbl_list.LAST
 20  	 INSERT INTO my_test_table VALUES my_test_tbl_list (indx);
 21  END;
 22  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT * FROM my_test_table
  2  /

      COL1       COL2       COL3       COL4       COL5       COL6
---------- ---------- ---------- ---------- ---------- ----------
         1          1         54          1          1          1
         2          1         12          1          1          1
         3          1         15          1          1          1
         4          1         19          1          1          1
         5          1         20          1          1          1
         6          1         23          1          1          1
         7          1         24          1          1          1

7 rows selected.

SCOTT@orcl_11g>

Report message to a moderator

Re: Oracle PL/SQL Collection [message #322982 is a reply to message #322947] Tue, 27 May 2008 00:14 Go to previous messageGo to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Can't we insert all column values at a time into the individual record at a time.
something like
my_test_tbl _list (1):='1,1,54,1,1,1'; --but this is not working

Regards,
Pointers

Report message to a moderator

Re: Oracle PL/SQL Collection [message #323134 is a reply to message #322919] Tue, 27 May 2008 07:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The problem is that you are assuming that a comma separated list of fields:
INSERT INTO demo_table VALUES (1,2,3,4,5)
and a string containing some values that happen to have commas between them
DECLARE
  v_str varchar2(100) := '1,2,3,4,5';
BEGIN
  INSERT INTO demo_table VALUES v_str;
END;
should be treated the same.

Report message to a moderator

Re: Oracle PL/SQL Collection [message #323179 is a reply to message #322982] Tue, 27 May 2008 10:26 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
Senior Member
 
SCOTT@orcl_11g> CREATE TABLE my_test_table
  2    (col1 NUMBER,
  3  	col2 NUMBER,
  4  	col3 NUMBER,
  5  	col4 NUMBER,
  6  	col5 NUMBER,
  7  	col6 NUMBER)
  8  /

Table created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE my_test_rec AS OBJECT
  2    (col1 NUMBER,
  3  	col2 NUMBER,
  4  	col3 NUMBER,
  5  	col4 NUMBER,
  6  	col5 NUMBER,
  7  	col6 NUMBER);
  8  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE my_test_tbl AS TABLE OF my_test_rec;
  2  /

Type created.

SCOTT@orcl_11g> DECLARE
  2    my_test_tbl_list  my_test_tbl := my_test_tbl ();
  3  BEGIN
  4    my_test_tbl_list.EXTEND (7);
  5    my_test_tbl_list (1) := my_test_rec (1, 1, 54, 1, 1, 1);
  6    my_test_tbl_list (2) := my_test_rec (2, 1, 12, 1, 1, 1);
  7    my_test_tbl_list (3) := my_test_rec (3, 1, 15, 1, 1, 1);
  8    my_test_tbl_list (4) := my_test_rec (4, 1, 19, 1, 1, 1);
  9    my_test_tbl_list (5) := my_test_rec (5, 1, 20, 1, 1, 1);
 10    my_test_tbl_list (6) := my_test_rec (6, 1, 23, 1, 1, 1);
 11    my_test_tbl_list (7) := my_test_rec (7, 1, 24, 1, 1, 1);
 12    INSERT INTO my_test_table
 13    SELECT * FROM TABLE (my_test_tbl_list);
 14  END;
 15  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT * FROM my_test_table
  2  /

      COL1       COL2       COL3       COL4       COL5       COL6
---------- ---------- ---------- ---------- ---------- ----------
         1          1         54          1          1          1
         2          1         12          1          1          1
         3          1         15          1          1          1
         4          1         19          1          1          1
         5          1         20          1          1          1
         6          1         23          1          1          1
         7          1         24          1          1          1

7 rows selected.

SCOTT@orcl_11g>

Report message to a moderator

Re: Oracle PL/SQL Collection [message #323319 is a reply to message #323179] Wed, 28 May 2008 03:16 Go to previous messageGo to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Thanks

Report message to a moderator

Re: Oracle PL/SQL Collection [message #323574 is a reply to message #323134] Wed, 28 May 2008 21:17 Go to previous messageGo to next message
majsaxar
Messages: 4
Registered: May 2008
Location: Atlanta
Junior Member
Thank you all for your help.

Barbara Boehmer's solution is close to what I wanted as proof of concept. However, what I will be using is a stored procedure and I will be passing an ARRAY like this. I want bulk insert.

my_test_tbl _list my_test_tbl := my_test_tbl (
'1,1,54,1,1,1',
'2,1,12,1,1,1';
'3,1,15,1,1,1',
'4,1,19,1,1,1',
'5,1,20,1,1,1',
'6,1,23,1,1,1',
'7,1,24,1,1,1'
)

The number of rows is not fixed it can be between 1 to 15. So before any INSERT takes place one has to loop first and remove the single qoutes and then insert one row at a time. Is it possible to use the FORALL constract instead of FOR loop? thanks in advance.

A. Jama

Report message to a moderator

Re: Oracle PL/SQL Collection [message #323584 is a reply to message #322919] Wed, 28 May 2008 22:48 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
First let me say, I am not a real fan of bulk operations. I say this not because they don't work as advertised, but rather because I feel they are an excuse for not coding a SQL only solution. Straight SQL without use of plsql is by definition a bulk operation and you can't get any faster.

That said, there are people who just can't give up procedural mindsets, and I suppose living with a NORMAN defeating liar's paradox that there is always an exception to every rule, your forall operation is just a matter of syntax.

Here is one solution. I have no issue giving you the code because you have it all done anyway. The only real contribution I have made here are two:

1) I use the IN INDICES OF syntax of FORALL
2) I use string operations to parse array elements into column values

SQL> 
SQL> drop table my_test_tbl;

Table dropped.

SQL> 
SQL> create table my_test_tbl (a number,b number,c number,d number,e number,f number);

Table created.

SQL> 
SQL> DECLARE
  2     TYPE my_test_tbl IS TABLE OF VARCHAR2(32767);
  3     my_test_tbl_list my_test_tbl := my_test_tbl(); 
  4     procedure add_row_to_array (string_p in varchar2) is
  5     begin
  6        my_test_tbl_list.extend;
  7        my_test_tbl_list(my_test_tbl_list.last) := string_p;
  8     end;
  9  BEGIN
 10     add_row_to_array ('1,1,54,1,1,1');
 11     add_row_to_array ('2,1,12,1,1,1');
 12     add_row_to_array ('3,1,15,1,1,1');
 13     add_row_to_array ('4,1,19,1,1,1');
 14     add_row_to_array ('5,1,20,1,1,1');
 15     add_row_to_array ('6,1,23,1,1,1');
 16     add_row_to_array ('7,1,24,1,1,1');
 17  
 18     FORALL indx IN INDICES OF my_test_tbl_list
 19  
 20     INSERT INTO my_test_tbl VALUES (
 21        substr(
 22                      ','||my_test_tbl_list(indx)||','
 23               ,instr(','||my_test_tbl_list(indx)||',',',',1,1)+1
 24               ,instr(','||my_test_tbl_list(indx)||',',',',1,2)-instr(','||my_test_tbl_list(indx)||',',',',1,1)-1
 25              )
 26       ,substr(
 27                      ','||my_test_tbl_list(indx)||','
 28               ,instr(','||my_test_tbl_list(indx)||',',',',1,2)+1
 29               ,instr(','||my_test_tbl_list(indx)||',',',',1,3)-instr(','||my_test_tbl_list(indx)||',',',',1,2)-1
 30              )
 31       ,substr(
 32                      ','||my_test_tbl_list(indx)||','
 33               ,instr(','||my_test_tbl_list(indx)||',',',',1,3)+1
 34               ,instr(','||my_test_tbl_list(indx)||',',',',1,4)-instr(','||my_test_tbl_list(indx)||',',',',1,3)-1
 35              )
 36       ,substr(
 37                      ','||my_test_tbl_list(indx)||','
 38               ,instr(','||my_test_tbl_list(indx)||',',',',1,4)+1
 39               ,instr(','||my_test_tbl_list(indx)||',',',',1,5)-instr(','||my_test_tbl_list(indx)||',',',',1,4)-1
 40              )
 41       ,substr(
 42                      ','||my_test_tbl_list(indx)||','
 43               ,instr(','||my_test_tbl_list(indx)||',',',',1,5)+1
 44               ,instr(','||my_test_tbl_list(indx)||',',',',1,6)-instr(','||my_test_tbl_list(indx)||',',',',1,5)-1
 45              )
 46       ,substr(
 47                      ','||my_test_tbl_list(indx)||','
 48               ,instr(','||my_test_tbl_list(indx)||',',',',1,6)+1
 49               ,instr(','||my_test_tbl_list(indx)||',',',',1,7)-instr(','||my_test_tbl_list(indx)||',',',',1,6)-1
 50              )
 51                                     );
 52  END;
 53  /

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from my_test_tbl;

         A          B          C          D          E          F
---------- ---------- ---------- ---------- ---------- ----------
         1          1         54          1          1          1
         2          1         12          1          1          1
         3          1         15          1          1          1
         4          1         19          1          1          1
         5          1         20          1          1          1
         6          1         23          1          1          1
         7          1         24          1          1          1

7 rows selected.

SQL> 
SQL>


Does this work for you?

Kevin

drop table my_test_tbl;

create table my_test_tbl (a number,b number,c number,d number,e number,f number);

DECLARE
   TYPE my_test_tbl IS TABLE OF VARCHAR2(32767);
   my_test_tbl_list my_test_tbl := my_test_tbl(); 
   procedure add_row_to_array (string_p in varchar2) is
   begin
      my_test_tbl_list.extend;
      my_test_tbl_list(my_test_tbl_list.last) := string_p;
   end;
BEGIN
   add_row_to_array ('1,1,54,1,1,1');
   add_row_to_array ('2,1,12,1,1,1');
   add_row_to_array ('3,1,15,1,1,1');
   add_row_to_array ('4,1,19,1,1,1');
   add_row_to_array ('5,1,20,1,1,1');
   add_row_to_array ('6,1,23,1,1,1');
   add_row_to_array ('7,1,24,1,1,1');

   FORALL indx IN INDICES OF my_test_tbl_list

   INSERT INTO my_test_tbl VALUES (
      substr(
                    ','||my_test_tbl_list(indx)||','
             ,instr(','||my_test_tbl_list(indx)||',',',',1,1)+1
             ,instr(','||my_test_tbl_list(indx)||',',',',1,2)-instr(','||my_test_tbl_list(indx)||',',',',1,1)-1
            )
     ,substr(
                    ','||my_test_tbl_list(indx)||','
             ,instr(','||my_test_tbl_list(indx)||',',',',1,2)+1
             ,instr(','||my_test_tbl_list(indx)||',',',',1,3)-instr(','||my_test_tbl_list(indx)||',',',',1,2)-1
            )
     ,substr(
                    ','||my_test_tbl_list(indx)||','
             ,instr(','||my_test_tbl_list(indx)||',',',',1,3)+1
             ,instr(','||my_test_tbl_list(indx)||',',',',1,4)-instr(','||my_test_tbl_list(indx)||',',',',1,3)-1
            )
     ,substr(
                    ','||my_test_tbl_list(indx)||','
             ,instr(','||my_test_tbl_list(indx)||',',',',1,4)+1
             ,instr(','||my_test_tbl_list(indx)||',',',',1,5)-instr(','||my_test_tbl_list(indx)||',',',',1,4)-1
            )
     ,substr(
                    ','||my_test_tbl_list(indx)||','
             ,instr(','||my_test_tbl_list(indx)||',',',',1,5)+1
             ,instr(','||my_test_tbl_list(indx)||',',',',1,6)-instr(','||my_test_tbl_list(indx)||',',',',1,5)-1
            )
     ,substr(
                    ','||my_test_tbl_list(indx)||','
             ,instr(','||my_test_tbl_list(indx)||',',',',1,6)+1
             ,instr(','||my_test_tbl_list(indx)||',',',',1,7)-instr(','||my_test_tbl_list(indx)||',',',',1,6)-1
            )
                                   );
END;
/

select * from my_test_tbl;

Report message to a moderator

Re: Oracle PL/SQL Collection [message #323797 is a reply to message #322919] Thu, 29 May 2008 08:56 Go to previous messageGo to next message
majsaxar
Messages: 4
Registered: May 2008
Location: Atlanta
Junior Member
Thanks Kevin for your help. I agree bulk insert is not some thing to rush. However, if one considers my case of having to loop through rows of data in a web page and reading one row at a time and making multi trips to the database, I decided to store the rows into an string array and passed to the stored proc.

This Kevins solution

procedure add_row_to_array (string_p in varchar2)

in my case
string_p =(
'1,1,54,1,1,1',
'2,1,12,1,1,1',
'3,1,15,1,1,1',
'4,1,19,1,1,1',
'5,1,20,1,1,1',
'6,1,23,1,1,1',
'7,1,24,1,1,1'
)

That is what I want to pass from mu application to the stored procedure.

Kevin could you please elobrate why you have the following in the BEGIN .. END block?

BEGIN
add_row_to_array ('1,1,54,1,1,1');
add_row_to_array ('2,1,12,1,1,1');
add_row_to_array ('3,1,15,1,1,1');
add_row_to_array ('4,1,19,1,1,1');
add_row_to_array ('5,1,20,1,1,1');
add_row_to_array ('6,1,23,1,1,1');
add_row_to_array ('7,1,24,1,1,1');

Because these data is passed to the stored proc ( as string_p params shows). Thank you all for your help.

Report message to a moderator

Re: Oracle PL/SQL Collection [message #324083 is a reply to message #322919] Fri, 30 May 2008 13:23 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Sure, I was hoping it was pretty obvious, but I was loading a string array with string values. The string_p = ... is not a valid pl/sql expression.

SQL> declare
  2     string_p varchar2(4000);
  3  begin
  4     string_p := ('1,2,3','4,5,6','7,8,9');
  5  end;
  6  /
   string_p := ('1,2,3','4,5,6','7,8,9');
               *
ERROR at line 4:
ORA-06550: line 4, column 16:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 4, column 4:
PL/SQL: Statement ignored


I assume you were using string_p = ... as a way to load a string array in java and that you are going to pass the string array to plsql. This should work just fine, but you would need to define a database object type to accept it. Something like this:

create or replace type c_string_4000 is table of varchar2(4000);

procedure take_it (string_p in c_string_4000) is...

Then you can call the plsql procedure from java.

Does this answer your question?

Kevin

Report message to a moderator

Previous Topic: Best way of long to long column insertion in sql
Next Topic: External table creation
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Dec 02 08:56:22 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.