Home » SQL & PL/SQL » SQL & PL/SQL » PLSQL TABLE & USING FORALL
PLSQL TABLE & USING FORALL [message #285609] Wed, 05 December 2007 02:45 Go to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
I have an Associative Array, which contains a column.
array holds compiled free 10000 insert commands in it.
At the end, i want the insert commands in
plsql table get executed and insert values
into a seperate table.

For Ex:

tab1 is a ass. array.
it contains records like
'insert into t1 values (1)',
'insert into t1 values (2)',
.
.

Now i want retrieve the values and execute
each insert command. issue of performance will effect
as array has thousands of records.

--END OF EX --


Can we do it through FORALL?

Please help me in this regard.

Thanks in Advance

Nat
Re: PLSQL TABLE & USING FORALL [message #285612 is a reply to message #285609] Wed, 05 December 2007 02:50 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Yes..


Some think like

FORALL i in 1 ..Arr.COUNT SAVE EXCEPTIONS
	INSERT INTO TABLE_NAME VALUES arr(i);


Thumbs Up
Rajuvan.

[Updated on: Wed, 05 December 2007 02:51]

Report message to a moderator

Re: PLSQL TABLE & USING FORALL [message #285618 is a reply to message #285609] Wed, 05 December 2007 02:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/
for i in 1..tab1.count loop
  execute immediate tab1(i);
end loop;


2/ No

3/ It is a bad way to do things. A much better way is:
insert into t1
select 1 from dual
union all
select 2 from dual
...


Regards
Michel

Re: PLSQL TABLE & USING FORALL [message #285624 is a reply to message #285609] Wed, 05 December 2007 03:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or even better, record only values in your array (declared as an external custom datatype) and use:
insert into t1 select * from table(tab1);

Regards
Michel
Re: PLSQL TABLE & USING FORALL [message #285628 is a reply to message #285624] Wed, 05 December 2007 03:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create type tab as table of number
  2  /

Type created.

SQL> create table t1 (val number);

Table created.

SQL> declare
  2    tab1 tab := tab(1,2,3,4,5);
  3  begin
  4    insert into t1 select * from table(tab1);
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select * from t1;
       VAL
----------
         1
         2
         3
         4
         5

5 rows selected.

Regards
Michel
Re: PLSQL TABLE & USING FORALL [message #285629 is a reply to message #285618] Wed, 05 December 2007 03:18 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Another way of doing it (besides the way Michel showed) is to only save the values to be inserted in the plsql table and cast that during the insert.
Re: PLSQL TABLE & USING FORALL [message #285631 is a reply to message #285629] Wed, 05 December 2007 03:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Too late... hehehe. Twisted Evil

Michel
Re: PLSQL TABLE & USING FORALL [message #285636 is a reply to message #285631] Wed, 05 December 2007 03:23 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
grmpf...


Easy or I will remove your post...

:evil-grin:
Re: PLSQL TABLE & USING FORALL [message #285688 is a reply to message #285636] Wed, 05 December 2007 05:31 Go to previous message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Thanks Folks
Previous Topic: DBMS_CRYPTO IN ORACLE 10G
Next Topic: about sequence
Goto Forum:
  


Current Time: Mon Dec 05 20:58:23 CST 2016

Total time taken to generate the page: 0.11194 seconds