5 Replies Latest reply: Sep 15, 2013 11:00 PM by HimanshuP RSS

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

    854091

      Hi All,

       

      I am working on ODI 11.1.1.5.

       

      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.

       

      Regard,Sp.