| how to execute dynamic sql in procedure with special characters? [message #572808] |
Mon, 17 December 2012 15:47  |
gentleman777us
Messages: 96
Registered: April 2005
|
Member |
|
|
Hi,
I have the following procedure body in a package.
PROCEDURE getrecordsForinspection(i_table_name in varchar2, i_thread_id in varchar2, i_max_count in number default null, o_results out sys_refcursor)
AS
v_sql varchar2(1000):= null;
begin
v_sql := 'update '||'i_table_name||' set status = '||'''IN_PROCESS-'||i_thread_id||''''||' Where final_status = '||''''STATUS_ACCEPTED'''||' and ('||i_max_count||' is null or rownum <= '||i_max_count||');';
EXECUTE IMMEDIATE(v_sql);
commit;
end;
when I execute the above procedure it gives the following error.
ORA-00911: invalid character
cause: Identifiers may not start with any ASCII characters other than letters and numbers.$#_ are also allowed after the first character. Identifiers enclosed by double quotes may contain any character other than a double quote. Alternative quotes(q'#....#') can not use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL language reference Manual.
I think dynamic sql is not executed because of the pipe character in the sql statement.
Is there anyway to get around this problem. your prompt reply is greatly appreciated.
Thanks
|
|
|
|
|
|
| Re: how to execute dynamic sql in procedure with special characters? [message #572815 is a reply to message #572810] |
Mon, 17 December 2012 18:40  |
cookiemonster
Messages: 9170
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
The problem is you haven't got the quotes in the right places.
You also have a semi-colon that shouldn't be there - you don't need them in strings you pass to execute immediate.
And this isn't going to work:
and ('||i_max_count||' is null or rownum <= '||i_max_count||');';
If i_max_count is null then that becomes:
and is null or rownum <= ;
Which isn't valid syntax.
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
