Home » SQL & PL/SQL » SQL & PL/SQL » Error in Execute Immediate (Oracle 10G)
Error in Execute Immediate [message #584235] Fri, 10 May 2013 06:36 Go to next message
gurusuryas
Messages: 49
Registered: March 2009
Location: Chennai
Member

Hi,
I have attached my Code and log records, here while calling Execute Immediate the program throws an error.



Rgds.
Guru
  • Attachment: p1.sql
    (Size: 1.24KB, Downloaded 51 times)
Re: Error in Execute Immediate [message #584241 is a reply to message #584235] Fri, 10 May 2013 07:58 Go to previous messageGo to next message
joy_division
Messages: 4504
Registered: February 2005
Location: East Coast USA
Senior Member
"Throws an error"

How unhelpful.
Re: Error in Execute Immediate [message #584242 is a reply to message #584241] Fri, 10 May 2013 08:02 Go to previous messageGo to next message
Littlefoot
Messages: 19523
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
ORA-00900: Invalid SQL statement. Obviously, you'll have to fix it so that it becomes valid.

As you use dynamic SQL, create a local variable, put the whole statement into it and display it with DBMS_OUTPUT.PUT_LINE so that you'd see what you are trying to execute. It will be invalid, I suppose. Once you fix the syntax, just EXECUTE IMMEDIATE contents of that local variable.

By the way, you'd be much happier without that useless WHEN OTHERS you currently have.
Re: Error in Execute Immediate [message #584247 is a reply to message #584235] Fri, 10 May 2013 09:04 Go to previous messageGo to next message
Michel Cadot
Messages: 58934
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:

Michel Cadot wrote on Fri, 26 April 2013 16:14
From your previous topics:

Michel Cadot wrote on Wed, 06 January 2010 16:04
1/
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
...


Michel Cadot wrote on Thu, 21 May 2009 09:56
1/
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
...


Do follow and apply the rules.

Quote:
exception
when others then
TEMP_TRAN('Err Occured :'||Sqlerrm);
RAISE;
end pkg1;
/

...
Regards
Michel

[Updated on: Fri, 10 May 2013 09:04]

Report message to a moderator

Re: Error in Execute Immediate [message #584390 is a reply to message #584247] Mon, 13 May 2013 10:38 Go to previous message
pointers
Messages: 350
Registered: May 2008
Senior Member
It is always advisable to encode the dynamic sql in a block and handle the exception, print (capture in log table in the production environment) the sql statement and most importantly RAISE it.

something like,

BEGIN
    ...........
    ...........

    V_SQL := 'SOME SQL';
     BEGIN
       EXECUTE IMMEDIATE v_sql;
     EXCEPTION
       WHEN OTHRS THEN
          dbms_output.put_line('v_sql: '|| v_sql);
          RAISE;  
    END;

    ............
    ............
END;
/


Regards,
Pointers
Previous Topic: Help required for .dat file generated
Next Topic: SQL Logic
Goto Forum:
  


Current Time: Fri Aug 29 03:28:14 CDT 2014

Total time taken to generate the page: 0.13742 seconds