Home » SQL & PL/SQL » SQL & PL/SQL » bulk inserts
bulk inserts [message #258627] Mon, 13 August 2007 03:53 Go to next message
subbu_tce
Messages: 98
Registered: July 2007
Location: pune
Member
I would like to bulk insert the data which is comming as a list of values as an in parameter.
But the below code doesnot work.
Plz suggest how to implement.

create or replace procedure (p_data weo_rec_list)
as
begin
forall rec in p_data.first.. p_data.last
insert into weo_led values(p_data(rec));
end;


weo_rec_list is nothing but

create or replace type weo_rec_obj as object
(
p_var_1 number,
p_var_2 varchar2
);

create or replace type weo_rec_list as table of weo_rec_obj;

[Updated on: Mon, 13 August 2007 03:55]

Report message to a moderator

Re: bulk inserts [message #258635 is a reply to message #258627] Mon, 13 August 2007 03:59 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

What is output? What is error. Format Posts. Your creating procedure syntax seems to me not correct.
Re: bulk inserts [message #258636 is a reply to message #258627] Mon, 13 August 2007 04:04 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First error:
SQL> create or replace type weo_rec_obj as object
  2  (
  3  p_var_1 number,
  4  p_var_2 varchar2
  5  );
  6  /

Warning: Type created with compilation errors.

SQL> sho err
Errors for TYPE WEO_REC_OBJ:
LINE/COL
--------------------------------------------------------------------
ERROR
--------------------------------------------------------------------
4/9
PLS-00215: String length constraints must be in range (1 .. 32767)

Second error:
SQL> create or replace procedure (p_data weo_rec_list)
  2  as
  3  begin
  4  forall rec in p_data.first.. p_data.last
  5  insert into weo_led values(p_data(rec));
  6  end;
  7  /
create or replace procedure (p_data weo_rec_list)
                            *
ERROR at line 1:
ORA-04050: invalid or missing procedure, function, or package name

Third error:
SQL> create or replace procedure p (p_data weo_rec_list)
  2  as
  3  begin
  4  forall rec in p_data.first.. p_data.last
  5  insert into weo_led values(p_data(rec));
  6  end;
  7  /

Warning: Procedure created with compilation errors.

SQL> sho err
Errors for PROCEDURE P:
LINE/COL
------------------------------------------------------------------
ERROR
------------------------------------------------------------------
0/0
PL/SQL: Compilation unit analysis terminated
1/21
PLS-00201: identifier 'WEO_REC_LIST' must be declared

Please start to fix these ones and come back with a correct post:
Read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Always post your Oracle version (4 decimals).


Regards
Michel
Previous Topic: use Flashback
Next Topic: data filtering
Goto Forum:
  


Current Time: Sun Dec 11 02:39:44 CST 2016

Total time taken to generate the page: 0.10310 seconds