Dynamic SQL - referencing table name dynamically
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Dynamic SQL - referencing table name dynamically

  1. #1
    Join Date
    Aug 2006
    Posts
    5

    Dynamic SQL - referencing table name dynamically

    Can someone suggest a change to the code below or an alternative method? I want to be able to execute an insert statement , selecting from a table whose name is variable.

    many thanks

    declare
    l_table varchar(50) := 'PRICE SOURCE';

    begin

    execute immediate 'INSERT INTO DVD.TMP_DVD_PRICE select * from :1'
    using l_table;

    commit;
    end;

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Code:
    declare
    l_table varchar(50) := 'PRICE SOURCE';
    
    begin
    
    execute immediate 'INSERT INTO DVD.TMP_DVD_PRICE select * from ' || l_table;
    
    commit;
    end;
    You can not use table names or column names as bind variables, you have to concatenate them into the dynamic string.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Sep 2005
    Posts
    278
    execute immediate 'INSERT INTO DVD.TMP_DVD_PRICE select * from ' || l_table

  4. #4
    Join Date
    Aug 2006
    Posts
    5
    many thanks

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