-
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.
-
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
-
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
-
marist89 wrote to u:
mysql := 'insert into '||table_in||'(col1) values(''HELLO'')'
'' not " (two single quotes not equal one double quote)
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|