Dynamic sql bulk insert [message #36754] |
Mon, 17 December 2001 22:23 |
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 |
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.
----------------------------------------------------------------------
|
|
|