Home » SQL & PL/SQL » SQL & PL/SQL » Bulk inserts
Bulk inserts [message #260488] Mon, 20 August 2007 02:28 Go to next message
subbu_tce
Messages: 98
Registered: July 2007
Location: pune
Member
How do we bulk insert into table on which list of values as in parameter?

Ex

Consider that am having the following things.

CREATE OR REPLACE TYPE Numtype_obj AS object
(
cont_id NUMBER,
policy_no VARCHAR2(20)
);

CREATE OR REPLACE TYPE Numtype_lst AS TABLE OF Numtype_obj;


create or replace procedure bulk_ins
(
p_data_list in Numtype_lst
)
as
begin
-- here i wants to bulk insert into a table ocp_policy
end;

consider that ocp_policy having two columns as cont_id and policy_no.


Re: Bulk inserts [message #260503 is a reply to message #260488] Mon, 20 August 2007 03:11 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Since you are passing in a nested table, you should be able to cast it with a Table function.

Using an object-relational table
CREATE TABLE numtype OF NUMTYPE_OBJ;

create or replace
procedure bulk_ins 
(
p_data_list in Numtype_lst
)
as
begin
    INSERT INTO NUMTYPE
    SELECT object_value
    FROM   table(p_data_list);
end;

declare
   d Numtype_lst;
   r Numtype_obj;
   i integer;
begin
   d := Numtype_lst();
   
   FOR i IN 1 .. 10 LOOP
       d.extend;
       r := Numtype_obj(i, 'whatever');
       d(d.last) := r;
   END LOOP;
   bulk_ins(d);
END;


If you create a regular relational table:
CREATE TABLE NUMTYPE (
cont_id NUMBER,
policy_no VARCHAR2(20)
);

create or replace
procedure bulk_ins 
(
p_data_list in Numtype_lst
)
as
begin
    INSERT INTO NUMTYPE (cont_id, policy_no)
    SELECT cont_id, policy_no
    FROM   table(p_data_list);
end;



Ross Leishman
Re: Bulk inserts [message #260504 is a reply to message #260503] Mon, 20 August 2007 03:13 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Oops. Missed the obvious: you could also use FORALL - look it up in the doco.
Re: Bulk inserts [message #260766 is a reply to message #260503] Mon, 20 August 2007 20:50 Go to previous messageGo to next message
subbu_tce
Messages: 98
Registered: July 2007
Location: pune
Member
How to use FORALL statement here ?
Re: Bulk inserts [message #260773 is a reply to message #260766] Mon, 20 August 2007 22:04 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Did you look it up? If so, explain why the examples in the Oracle manual do not help.

Ross Leishman
Re: Bulk inserts [message #260774 is a reply to message #260773] Mon, 20 August 2007 22:09 Go to previous messageGo to next message
subbu_tce
Messages: 98
Registered: July 2007
Location: pune
Member
I have gone thru the docs on which the values are coming in a cursor,but i want the things which i have mentioned above.

Re: Bulk inserts [message #260778 is a reply to message #260774] Mon, 20 August 2007 22:24 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
When you read the documentation to see how FORALL could be used to perform bulk inserts from a Nested Table collection, did you find any of these examples helpful?

Ross Leishman
Re: Bulk inserts [message #261326 is a reply to message #260488] Wed, 22 August 2007 05:50 Go to previous messageGo to next message
darshanmeel
Messages: 44
Registered: June 2007
Location: India
Member
Please use forall rather than for loop.Please see the pl/sql user guide on how to use forall.
Re: Bulk inserts [message #261330 is a reply to message #261326] Wed, 22 August 2007 06:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please stop repeating the others' answers.
Your posts are useless.

Regards
Michel
Re: Bulk inserts [message #261333 is a reply to message #261330] Wed, 22 August 2007 06:06 Go to previous messageGo to next message
darshanmeel
Messages: 44
Registered: June 2007
Location: India
Member
This is not others answers.Ok

These are my own answer and this is everyone knows that forall is best if you want to bulk insert,update,select.

The reason being that the switching between sql and pl/sql will be reduced.

I think you have some problem with others.Please accept there are 1000s other database expert in this world not only you.

So please accept others answers as well. There might be something wrong with thier posts but you cant say that they dont know anything.

Please do not get angry for very samll samll things.Life is very big.
Re: Bulk inserts [message #261344 is a reply to message #261333] Wed, 22 August 2007 06:25 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
This is not others answers.Ok

Your post:
Quote:
Please use forall. Please see the pl/sql user guide on how to use forall

Ross's post 2 days before:
Quote:
you could also use FORALL - look it up in the doco

Then he added links to examples.

Maybe you should read the other posts before answering.

Quote:
Please do not get angry for very samll samll things.Life is very big.

I'm not angry, I don't want this site to be pollute by useless answers (even if this leads me to pollute the site to say this).
And the many posts you made today are mostly useless and do not follow the guidelines.

Regards
Michel


[Updated on: Wed, 22 August 2007 06:25]

Report message to a moderator

Previous Topic: Lowest Cache value
Next Topic: how to execute this procedure
Goto Forum:
  


Current Time: Fri Dec 09 02:01:10 CST 2016

Total time taken to generate the page: 0.20373 seconds