Forum Stats

  • 3,827,587 Users
  • 2,260,795 Discussions
  • 7,897,309 Comments

Discussions

SQL string is not a Query error in ODI procedure with different source&target and dynamic SQL

juno_2020
juno_2020 Member Posts: 10 Green Ribbon

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!

Tagged:

Best Answer

  • juno_2020
    juno_2020 Member Posts: 10 Green Ribbon
    Answer ✓

    Solution found. I'm sharing it here as I found from my own experience it's not that easy to find support on such quite narrow topic questions.

    I made two ODI variables - one for the source column names and one for calling the rows returned from the source tab.

    The source command (executed on the source remote DB) is

    select #V_OFFL_COL_LIST

    from <%=odiRef.getObjectName("L", "#V_OFFL_TABLE_NAME", "DBSOURCE_SCHEMA", "DWH_LCL", "D") %>  

    where src_date_from_dt = to_date('#V_OFFL_TRANSFER_DATE','YYYY-MM-DD')

    The target command is:

    insert /*+ append*/ into <%=odiRef.getObjectName("L", "#V_OFFL_TABLE_NAME", "DBTARGET_SCHEMA", "DWH_LCL", "D") %>

    (#V_OFFL_COL_LIST)

     values (#V_OFFL_COL_LIST_TGT)


    So this #V_OFFL_COL_LIST_TGT contains the same column list as in #V_OFFL_COL_LIST, but with ":" in front of each column name in the list.

      

Answers

  • Rodrigo Radtke Souza
    Rodrigo Radtke Souza Member Posts: 212 Gold Badge

    For you do use dynamic SQL in ODI, you cannot use a PL/SQL "BEGIN/END" kind of structure in the source. It needs to be a pure SQL statement that returns columns. Those colums you may pass as bind variables to the target.

    The post below shows one example of looping scenarios using command on source/target tab. Its not exactly what you need here, but the concept is the same. The source tab contains only a SQL that returns columns and those columns are the ones that you will use to pass it to the target.

    https://devepm.com/2012/11/12/10-important-things-to-improve-odi-integrations-with-hyperion-planning-part-2-procedure-loop/

    juno_2020
  • juno_2020
    juno_2020 Member Posts: 10 Green Ribbon

    Thanks for the input, I'm afraid the source tab is also needed not only to pass column names, but also values to the target. You see, the source tab gets rows from DB1 which must be inserted in DB2. I need to replace the existing stored procedure which uses db link with something through ODI which must use the JDBC connection to the source DB1. ODI procedure seemed a good option, but I can't get through the part which selects the data from the source - dynamically.. depending on the table name. I've also tried before what you said - without the BEGIN/END, but it's again using execute immediate, the version was:

    execute immediate 'SELECT (SELECT replace(RTRIM(XMLAGG(XMLELEMENT(E,'!!!!!'||column_name||'!!!!!',',').EXTRACT('//text()') ORDER BY column_name).GetClobVal(),','), '!!!!!')

                FROM ALL_TAB_COLUMNS

         WHERE OWNER = 'DB1' AND TABLE_NAME = ||''#V_OFFL_TABLE_NAME''

         ||' and COLUMN_NAME != 'SRC_DATE_FROM_DT' ) FROM 

                  DB1.TABLE_1

                  WHERE SRC_DATE_FROM_DT = TO_DATE('||'#BG_GDWH_LCL.V_OFFL_TRANSFER_DATE', 'YYYY-MM-DD')'

    and again I get the error. I'm sure that if I used a simple SELECT query, it would work, but I don't know neither the table name, nor the date, nor the column names before the start of execution. Just in case I removed some more dynamic strings to test if the issue is with them - here I select only one column name, but I get the same result - ERROR.

    execute immediate 'SELECT cnum FROM 

                  DB1.TABLE_1

                  WHERE SRC_DATE_FROM_DT = TO_DATE('||'#V_OFFL_TRANSFER_DATE', 'YYYY-MM-DD')'


    I've also considered using the odiRef substitution APIs, but some of them, at least what I checked.. are not supposed to work in Procedures. E.g. getCollList.

    Another option I'm trying is using BULK COLLECT INTO and dynamic cursors, but again.. if I can't declare them in the source, and I can't use dynamic SQL in the source at all, then I would reach the conclusion that I have no workable options using PL/SQL code for the job I need to to. Am I right or there's still hope left? Problem is - there's too scarce information on dynamic SQL in ODI procedures with source&target tab split.

  • Rodrigo Radtke Souza
    Rodrigo Radtke Souza Member Posts: 212 Gold Badge

    So, as I told you in the beggining, only pure SQL statements will work on the source tab. Anything else, like PL/SQL, will not work. So just forget about anything with begin/end/execute. Those won't work. However, ODI is the best ETL tool around because its pretty flexible. You can do whatever you want to do with ODI, but it will require something more than one proc with source/target tabs. The reason for this, as you already figured it out, is that you need some prior info to build things dynamic, like table name, column name, etc.

    I wrote about this in this post here:

    Basically you will need to create some prior ODI scenario that will call a child scenario passing some information, like the table name. From there, you may query the Oracle dictionary and build the column names. There is a limitation of 4000 characters on this approach, so if your table has too many columns, you won't be able to use. I'm currently using this approact to load 500+ tables using only two ODI scenarios and one control table, so I know it works pretty well. From 500+, maybe 10 tables are too big and I needed to create some specific mappings for them in a traditional way.

    I hope it helps! It will give you some work upfront to setup the best architecture for your project, but I guarantee to you that the dynamic load is worth the time investiment.

    juno_2020
  • juno_2020
    juno_2020 Member Posts: 10 Green Ribbon
    edited Apr 7, 2022 6:11AM

    Hi, Rodrigo!

    Thanks for the second link, it's much more helpful. Actually, I already found the same code on another site

    How to use ODI to generate Dynamic code for you | PeerSpot

    I tried modifying it for my needs, as in my case I'm not interested in moving deltas from the source to the target (historized data), but the opposite - moving a full historized date back to the tables which are normally the input to the historized tables. I tried the SOURCE tab just by removing the primary key part because in my case the tables don't have primary keys. It had some errors though, I was tuning the target, but I left it out because I thought that in this was I wouldn't be able to filter only for the date I need. Because the filter is based on a date column which is only present in the source and in the code I'm omitting it in the selected column list. And I guess the filter should be made in the source tab anyway?

    So in the meanwhile I came up with maybe a more "wooden" solution, but as of now it is working. I moved to using a "dummy" mapping with source and target - my log table, but using a custom IKM which contains the actual dynamic code for select and insert. This time I don't have the limitations of odiRef APIs as in procedures and I applied the select using those. It's final outlook will be like in your first link - a loop in a package. Currently the unfinished dynamic code in the SOURCE tab of the IKM looks like that:

    select <%=odiRef.getColList("(", "[COL_NAME]", ", ", ")", "")%>

    from <%=odiRef.getObjectName("L", "#V_OFFL_TABLE_NAME", "DB1", "BG_GDWH_LCL", "D")%>

    where src_date_from_dt = to_date('V_OFFL_TRANSFER_DATE','YYYY-MM-DD')

    It worked perfectly when I listed the exact column names instead of the getColList API (TARGET tab still doesn't have dynamic code, I'm gradually removing it, started from the source tab first as more problematic), but now I'm checking how to remove from the returned list the column name 'src_date_from_dt', which has to be omitted because it's not present in the respective target table.

  • Rodrigo Radtke Souza
    Rodrigo Radtke Souza Member Posts: 212 Gold Badge

    Awesome! You approach of using a custom KM is totally valid because, in the end of the day, KMs and procs in ODI are very simmilar. They are even stored in the same metadata table SNP_TRT (if I'm not mistaken). When you use a custom KM approach, you get some more API options, which may be easier in some cases.

    You saw the same post because Ricardo and I worked togheter on this code. We both run DEVEPM blog, so he got one of the posts to publish it also on PeerSpot.

    Good luck on your coding!

  • juno_2020
    juno_2020 Member Posts: 10 Green Ribbon

    Thanks, Rodrigo!

    I'm afraid I'm stuck again though. It seems like the odiRef.getCollList doesn't get the column list of the object I'm "forcing" with the IKM, but of the target table in the interface (which, as I said, I'm using only as means to have a valid physical architecture and do everything actually inside the IKM)... I checked topics about it, still checking options but I can't figure this one out either. Tried a few versions but no use. So I opened another thread for that case in particular:

    GetCollist for other datastore not in interface — oracle-tech

    I'm continuing to research this option as it was too good to miss, but I think I'll give your and your colleague's idea a second try too. I just need to figure out where and how to add the filter for on the date column from the source tables.

  • juno_2020
    juno_2020 Member Posts: 10 Green Ribbon
    Answer ✓

    Solution found. I'm sharing it here as I found from my own experience it's not that easy to find support on such quite narrow topic questions.

    I made two ODI variables - one for the source column names and one for calling the rows returned from the source tab.

    The source command (executed on the source remote DB) is

    select #V_OFFL_COL_LIST

    from <%=odiRef.getObjectName("L", "#V_OFFL_TABLE_NAME", "DBSOURCE_SCHEMA", "DWH_LCL", "D") %>  

    where src_date_from_dt = to_date('#V_OFFL_TRANSFER_DATE','YYYY-MM-DD')

    The target command is:

    insert /*+ append*/ into <%=odiRef.getObjectName("L", "#V_OFFL_TABLE_NAME", "DBTARGET_SCHEMA", "DWH_LCL", "D") %>

    (#V_OFFL_COL_LIST)

     values (#V_OFFL_COL_LIST_TGT)


    So this #V_OFFL_COL_LIST_TGT contains the same column list as in #V_OFFL_COL_LIST, but with ":" in front of each column name in the list.