Parsing Table Name as Variable
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Parsing Table Name as Variable

  1. #1
    Join Date
    Dec 2000
    Posts
    37
    I am trying to parse table name as a variable for an insert operation (ex: insert into table1(col1) values('HELLO')). But could not get it to work:

    create or replace procedure insert_table (table_in in varchar2)
    as
    mysql varchar2(200);
    begin
    mysql := 'insert into '||table_in||'(col1) values('HELLO')'
    execute immediate mysql;
    end;
    /

    Errors for PROCEDURE INSERT_TABLE:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    5/52 PLS-00103: Encountered the symbol "TOM" when expecting one of the
    following:
    . ( * @ % & = - + ; < / > at in mod not rem
    <> or != or ~= >= <= <> and or like
    between is null is not || is dangling

    Please help! Thanks.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    You have to use two single tiks for your string...
    Code:
    create or replace procedure insert_table (table_in in varchar2) 
    as 
    mysql varchar2(200); 
    begin 
    mysql := 'insert into '||table_in||'(col1) values(''HELLO'')' 
    execute immediate mysql; 
    end;
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Dec 2000
    Posts
    37
    Thanks, Jeff. I revised the line per your correction:

    mysql := 'insert into '||table_in||'(name) values("TOM")';

    It compiles with no error now. However, when I execute it by parsing a table named 'TEST', it gives error:

    SQL> exec insert_table('TEST');
    BEGIN insert_table('TEST'); END;

    *
    ERROR at line 1:
    ORA-00984: column not allowed here
    ORA-06512: at "USER.INSERT_TABLE", line 6
    ORA-06512: at line 1

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    marist89 wrote to u:
    mysql := 'insert into '||table_in||'(col1) values(''HELLO'')'

    '' not " (two single quotes not equal one double quote)


  5. #5
    Join Date
    Dec 2000
    Posts
    37
    Thank you all. It works fine now.

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