Hi Tom,
I have the following procedure which takes in a table name (v_tab_name) at run-time, fetches data from another table (tab_data) and inserts the data into the relevant columns of the Input Table through an Object Type.
create type src_obj_type
as object
(
Request_ID VARCHAR2(15),
Submitter VARCHAR2(254),
Short_Description VARCHAR2(254),
CustomerID VARCHAR2(255),
Customer_InstID VARCHAR2(255),
ContractInstID VARCHAR2(255),
Statement_Number VARCHAR2(10),
ThirdParty VARCHAR2(10),
TP_Comp_Code VARCHAR2(4),
TP_Sales_Org VARCHAR2(4)
);
/
create type src_obj_tab is table of src_obj_type;
/
create or replace
procedure proc_test(v_table_name in varchar2) as
obj_tab src_obj_tab;
begin
--populating the Object Type
select src_obj_type (
lpad(seq_test.nextval,15,'0'),
'abc',
'test for dynamic bulk update',
null,
null,
null,
statement_number,
third_party ,
comp_code,
sales_org
)
bulk collect into obj_tab
from tab_data;
--Dynamic Execution of the Insert Statement.
forall i in 1..obj_tab.count
execute immediate 'begin
insert into '||v_table_name||' select * from table(cast(src_obj_tab(:1) as src_obj_tab) ) ;
end;'
using obj_tab(i);
commit;
exception
when others then
dbms_output.put_line(dbms_utility.format_error_backtrace()||'error'||sqlerrm||sqlcode);
end;Here I am inserting data into the input Table Name from the source table TAB_DATA. Here all the columns of the target table do not have values as they are not present in the Source Table (tab_data). Hence I am fetching a NULL. This Insert works fine.
Now I need to update the columns having NULL values i.e:- CustomerID VARCHAR2(255),Customer_InstID VARCHAR2(255) and ContractInstID VARCHAR2(255) present in the target table (v_table_name). The
target table name and hence the columns therein are un-known at run-time and hence I am using the the EXECUTE IMMEDIATE.
Also to note, The values in the 3 columns CustomerID VARCHAR2(255),Customer_InstID VARCHAR2(255)and ContractInstID VARCHAR2(255) are coming from a
third table which is again un-known. I need to join TAB_DATA with this Third Table (let me call it as TAB_THIRD) and get the values to be updated into the 3 columns.
The columns(giving values) coming from this join query (between TAB_DATA and TAB_THIRD) are also
un-known at run-time. (Actually the table names and columns change with a change in the Environments as they are getting generated from a third party tool.)
Please help giving an idea as to how to make this dynamic engine work in the case of updates. The data will be in the scales of lakhs of rows. Also please let me know if any more information is required.
Thank You in Advance!
I hate your code:
https://www.google.com/search?q=site%3Aasktom.oracle.com+"i+hate+your+code" exception
when others then
dbms_output.put_line(dbms_utility.format_error_backtrace()||'error'||sqlerrm||sq
lcode);
end;
please don't say "this is a demo, I don't do that in real life" (if that is true, WHY DO IT IN A DEMO)
please don't say "just ignore that, answer my question anyway". I cannot ignore that, it is the most offensive piece of code possible.
and dynamic sql??? ugh - trying to be super generic, i have a big problem with that too. SQL injection being first and foremost! You are being very trusting of whomever calls you here - very very trusting.
and the commit - the commit is probably wrong too - how I wish plsql didn't have commit and rollback sometimes!!!
and unfortunately, I cannot run your code as I have no tables to run it against and you didn't give me anything to test with!!!!
you would need to write code that took the source table name, the target tablename, the lists of columns to be joined on and the lists of columns to be updated - and then generate merge statement
merge into target_table
using (select some_cols from source_table) s
on (join-columns)
when matched then update set c1 = s.c1, c2 = s.c2, ....;
making sure you do not suffer from sql injection of course!!! please read *a lot* about sql injection and then read about the dbms_assert package which maybe useful to you.
So, write a routine that takes
p_target_table in varchar2,
p_source_table in varchar2,
p_target_table_key_list in sys.odciVarchar2List,
p_source_table_key_list in sys.odciVarchar2List,
p_target_table_update_cols in sys.odciVarchar2List,
p_source_table_update_cols in sys.odciVarchar2List
and builds such an update statement. making sure your commit is actually in the right place, getting rid of the when others than null and giving serious consideration to
writing a code generator that generates STATIC routines and compiles them into your database ONCE and then calls the proper routine. Rather than always dynamically generating sql (subjecting yourself to poor performance that is hard to tune, since there is no sql to be fixed in the code - sql injection and so on) - generate the procedure once - today, install it and call it over and over. If the tables change over time, just run your code generator, verify the generated code, install it and call it over and over...