5 Replies Latest reply on Sep 16, 2013 4:00 AM by HimanshuP

    ODI Procedure (passing parameter from command on source to command on target) issue.


      Hi All,


      I am working on ODI


      I am trying to run a select query on 'command on source' and tying to capture the output into command on target and execute the code captured.


      'command on source'

      select 'BEGIN ' || 'dbms_stats.gather_table_stats(ownname => ''' || pfg.table_owner || ''', tabname => ''' || pfg.table_name || '''' ||'END' ||';'  SQL_TEXT from TABLE_GROUP pfg , ALL_TABLES at

      where pfg.enabled = 'Y'     and   pfg.group_name = 'TABLE_NAME'     and   pfg.table_owner = at.owner     and   pfg.table_name  = at.table_name  (forgot abt the syntax, i have removed some part of the code to make it simple)


      the desired output will be:  (SQL_TEXT in the above code is to capture the output into it)

      BEGIN dbms_stats.gather_table_stats(ownname => 'OWNER_NAME', tabname =>  'TABLE_NAME', estimate_percent =>  DBMS_STATS.AUTO_SAMPLE_SIZE, degree =>  DBMS_STATS.DEFAULT_DEGREE); END;


      I am getting the desired result , the only problem is passing on this to 'command on target'. On "comm on target", I wanted to run this whole output (from BEGIN to END, as above) so I followed the below syntax/method of calling/executing this:


      :SQL_TEXT (did not work)

      #SQL_TEXT  (did not work)


      Any help on this is much appreciated. thanks in advance.