Home » SQL & PL/SQL » SQL & PL/SQL » execute immediate error (oracle 10g)
execute immediate error [message #431490] Tue, 17 November 2009 12:04 Go to next message
gauravpuri2002
Messages: 24
Registered: October 2007
Location: Bangalore
Junior Member
Hi I am running the following scripts :-

DECLARE
a VARCHAR2(100);
CURSOR c1
IS
SELECT 'REVOKE '||A.PRIVILEGE ||' ON CIM_ETL_SQL FROM '||A.grantee sql_str
FROM DBA_TAB_PRIVS a
WHERE a.table_name='CIM_ETL_SQL'
AND A.PRIVILEGE <>'SELECT'
AND A.owner='CCP';
BEGIN
FOR i IN c1 LOOP
a:=''''||i.sql_str||'''';
dbms_output.put_line(a);
EXECUTE IMMEDIATE a;
END LOOP;
ROLLBACK;
END;

When I am executing it, its saying invalid sql.
Re: execute immediate error [message #431491 is a reply to message #431490] Tue, 17 November 2009 12:12 Go to previous messageGo to next message
BlackSwan
Messages: 24904
Registered: January 2009
Senior Member
>its saying invalid sql
I believe Oracle.
You offer no proof to the contrary.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed
Re: execute immediate error [message #431492 is a reply to message #431490] Tue, 17 November 2009 12:18 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
When I am executing it, its saying invalid sql.

So you tried to execute an invalid SQL.
Replace execute immeddiate by dbms_output.put_line and you will know why.

Regards
Michel
Re: execute immediate error [message #431494 is a reply to message #431490] Tue, 17 November 2009 12:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
a:=''''||i.sql_str||'''';


Why do you feel that your SQL statement needs to be wrapped in quotes?

I suspect that removal of this line will help.

Why have you got a rollback statement in this code? What, exactly, do you think it will do?
Re: execute immediate error [message #431650 is a reply to message #431490] Wed, 18 November 2009 22:09 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
I am waiting for the actual error message. I am still impressed 1000 posts later by people who say

Quote:
Oracle is giving me an error message

But then they never show the error message.

Kevin
Previous Topic: optimisation for view as it is time consuming
Next Topic: Issue while creating Materialized View
Goto Forum:
  


Current Time: Sun Sep 25 07:51:31 CDT 2016

Total time taken to generate the page: 0.04484 seconds