Hi,
I have a relatively simple task: to move a procedure, working as a stored in the DB, into an ODI procedure (ODI12c), which must insert rows from tables in one DB into tables with almost the same structure, but in another DB. I'm using an ODI procedure for this, with the code spread between the source and target commands. Source is let's say DB1, Target is let's say DB2. Since there are many tables with their own structure, I'm using a dynamic sql for the select in the Source as well as the Insert in the Target. I've tried many alternative ways to write the code, but I always get the error "SQL String is not a Query".
Here's the basic version I started from. SOURCE command:
DECLARE
COL_LIST clob;
BEGIN
SELECT replace(RTRIM(XMLAGG(XMLELEMENT(E,'!!!!!'||column_name||'!!!!!',',').EXTRACT('//text()') ORDER BY column_name).GetClobVal(),','), '!!!!!', '"')
INTO COL_LIST
FROM ALL_TAB_COLUMNS
WHERE OWNER = 'DB1' AND TABLE_NAME = '#V_OFFL_TABLE_NAME'
and COLUMN_NAME != 'SRC_DATE_FROM_DT';
EXECUTE IMMEDIATE ' SELECT '|| COL_LIST ||' FROM DB1.' ||
'#V_OFFL_TABLE_NAME' ||
' WHERE SRC_DATE_FROM_DT = TO_DATE(''' || '#V_OFFL_TRANSFER_DATE' || ''', ''YYYY-MM-DD'')';
END;
The TARGET:
BEGIN
EXECUTE IMMEDIATE 'INSERT /*+ APPEND*/ INTO DB2.'||#V_OFFL_TABLE_NAME||
' ('||#COL_LIST||' )'||
values (:COLL_LIST);
END;
I've copied the code in the PL/SQL Developer and there the dbms_output.put_line returns exactly what I expect from the execute immediates. So what I've reached as a conclusion for my problem is that the target expects selected rows with column names - the list returned by the COL_LIST variable, but when I call them from the target, I get the string itself. I tried many different versions for a solution, but none have worked since.
I'm ready to give more details about the options I used, but I'd rather have first an objective opinion without taking in regards the alternatives I tried in case I lead you to a wrong direction for your advise.
Thanks in advance!