DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: execute immediate insert !!

  1. #1
    Join Date
    Nov 2002
    Posts
    170

    execute immediate insert !!

    Is it possible to use execute immediate "insert into table select distinct values
    inside the loop.

    Eg:

    for c_tab_col_rec in c_tab_col loop
    execute immediate 'insert into ABC(distinct_val)
    select distinct '||c_tab_col_rec.column_name||' from '||c_tab_col_rec.table_name;
    end loop;

  2. #2
    Join Date
    Sep 2005
    Posts
    278
    Try it out, if you have any problems then post the errors

  3. #3
    Join Date
    Nov 2002
    Posts
    170
    execute immediate 'insert into ABC(distinct_val) ' ||
    ' select distinct '||c_column_name_rec.column_name||' from yyy.'||c_table_name_rec.table_name;

    ERROR

    ORA-00904: "SHORT_DISPLAY": invalid identifier
    ORA-06512: at line 22

    I'm not using SHORT_DISPLAY ANYWHERE IN THE CODE.

  4. #4
    Join Date
    Sep 2005
    Posts
    278
    Print that string, using dbms_output package, check if the column name or table name have any invalid character.

    The problem may also be other, if you can provide that stored procedure then someone can help you.
    Last edited by tabreaz; 03-22-2007 at 06:38 PM.

  5. #5
    Join Date
    Feb 2005
    Posts
    158
    Maybe your c_column_name_rec is out of step with c_table_name_rec

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width