Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic sql bulk insert
Dynamic sql bulk insert [message #36754] Mon, 17 December 2001 22:23 Go to next message
Mike Verkimpe
Messages: 2
Registered: December 2001
Junior Member
I a mtrying to do a bulk insert with the same datarecord. To make it really dynamic over several table i need a way to one time insert 2 var , the other time 5 vars. and so on ...

An example :

declare
v_line varchar2(1000);
v_data_record varchar2(1000);
v_sub varchar2(1000);
begin
v_data_record := '2001120601QZETGQERGB QGRREQGEQRGQGQREGQEGQG 5456456';
v_line := 'INSERT INTO IF_D010_STORE
( DATE_CREATED,STO_IDENTIFIER,STO_NAME,STO_FORMULA_CODE,STATUS )
VALUES ( to_number(RTRIM(SUBSTR(:v_data_record,1,8)))/1,to_number(RTRIM(SUBSTR(:v_data_record,9,2)))/1,TO_CHAR(RTRIM(SUBSTR(:v_data_record,11,30))),TO_CHAR(RTRIM(SUBSTR(:v_data_record,41,2))),TO_CHAR(RTRIM(SUBSTR(:v_data_record,43,1))) )';
execute immediate v_line using v_data_record,v_data_record,v_data_record,v_data_record,v_data_record;
end;
/

when I want to insert into a next table I only have to insert 2 bind vars and in another table i have to insert 50 vars , the insert statement is no problem , but specifieing the bind vars dynamicly is the realy problem. How can i solve this , in other words how do i specify the number of bind vars dynamicly.

----------------------------------------------------------------------
Re: Dynamic sql bulk insert [message #36775 is a reply to message #36754] Wed, 19 December 2001 23:02 Go to previous message
Mike Verkimpe
Messages: 2
Registered: December 2001
Junior Member
I allready found it myself ...

by using the dbms_sql package you can dynamicly define your bind vars.

-- open cursor

c := dbms_sql.open_cursor;

-- parse the cursor

dbms_sql.parse(c,v_insert_line,dbms_sql.native);

-- supply binds

for i in 1 .. v_bind_number loop
dbms_sql.bind_variable(c, ':v_last_data'||i,v_last_data);
end loop;

-- execute cursor

rows_processed := dbms_sql.execute(c);

-- close cursor
dbms_sql.close_cursor(c);

you can easely make a table of the v_data_record variable.

----------------------------------------------------------------------
Previous Topic: Data from Web page to Oracle DB
Next Topic: Re: Ref Cursor Error
Goto Forum:
  


Current Time: Fri Mar 29 06:33:46 CDT 2024