Home » SQL & PL/SQL » SQL & PL/SQL » how to execute dynamic sql in procedure with special characters? (oracle 11g)
how to execute dynamic sql in procedure with special characters? [message #572808] Mon, 17 December 2012 15:47 Go to next message
gentleman777us
Messages: 117
Registered: April 2005
Senior 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 #572810 is a reply to message #572808] Mon, 17 December 2012 15:56 Go to previous messageGo to next message
Littlefoot
Messages: 18826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Don't guess and don't EXECUTE the statement - display it on the screen first (use DBMS_OUTPUT.PUT_LINE to do that). Then you'll see how the statement looks like. Fix it if necessary; then run it.
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 Go to previous message
cookiemonster
Messages: 10573
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.
Previous Topic: Sql Query with Mutiple columns
Next Topic: Redo log generated in Materalized view
Goto Forum:
  


Current Time: Fri Apr 18 21:13:31 CDT 2014

Total time taken to generate the page: 0.13241 seconds