3 Replies Latest reply: Jan 2, 2013 5:29 AM by user11081688 RSS

    Execute immdiate out variable

    user11081688
      Hi ,

      here is my code... now i want to take two values (c1.id,id) and insert into another table.

      could you please tell how to collect variables data in execute immdiate statment assing to variable ?

      <<CODE >>


      DECLARE
      culprit                    varchar2(30) := 'test';
      marker                    varchar2(4) := '0000';
      perf_ref               number;
      sql_statement          varchar2(2000);
      rowcount               integer := 0;
      n_insert_count          integer := 0;
      product_group_id ivr_product_group.id%type;
      product_id product.id%type;
      begin
      marker := '0010';
      for c1 in (select * from ivr_product_group where id >1 order by id)
      loop
           marker := '0025';
           rowcount := 0;
           sql_statement:='select '||c1.id||', id from product '||
                               'where ('||c1.where_clause||') '||
                               'and id not in '||
                               '(select product_id from ivr_product_group_member '||
                               'where prdct_group_id = '||c1.id||')';                                             
           marker := '0035';
           execute immediate sql_statement;
           dbms_output.put_line(lpad(c1.id,10)||' / '||rpad(c1.name,40)||' / Inserted: '||lpad(rowcount,10));     
      end loop;
      marker := '0060';
      exception
      when others then
      dbms_output.put_line(substr(sqlerrm, 1, 70));
      --dbms_output.put_line(proc_name||': check code after marker '||marker||' in '||proc_name);
      rollback;
      end;