- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 62 Insurance
- 536.1K On-Premises Infrastructure
- 138.2K Analytics Software
- 38.6K Application Development Software
- 5.7K Cloud Platform
- 109.4K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.2K Integration
- 41.6K Security Software
SQL string is not a Query error in ODI procedure with different source&target and dynamic SQL
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:
SELECT replace(RTRIM(XMLAGG(XMLELEMENT(E,'!!!!!'||column_name||'!!!!!',',').EXTRACT('//text()') ORDER BY column_name).GetClobVal(),','), '!!!!!', '"')
WHERE OWNER = 'DB1' AND TABLE_NAME = '#V_OFFL_TABLE_NAME'
and COLUMN_NAME != 'SRC_DATE_FROM_DT';
EXECUTE IMMEDIATE ' SELECT '|| COL_LIST ||' FROM DB1.' ||
' WHERE SRC_DATE_FROM_DT = TO_DATE(''' || '#V_OFFL_TRANSFER_DATE' || ''', ''YYYY-MM-DD'')';
EXECUTE IMMEDIATE 'INSERT /*+ APPEND*/ INTO DB2.'||#V_OFFL_TABLE_NAME||
' ('||#COL_LIST||' )'||
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!