Home » SQL & PL/SQL » SQL & PL/SQL » EXECUTE IMMEDIATE (ORacle 10g,XP)
EXECUTE IMMEDIATE [message #389967] Wed, 04 March 2009 06:38 Go to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

I am trying to execute a package named PACK from an user TESTUSER with dba role .
In that package a procedure named A contains this piece of code

EXECUTE IMMEDIATE 'grant all on DBA_TAB_COLS to TESTUSER'


I am getting this error 'ORA-01929: no privileges to GRANT'
how to solve it.I am using 'AUTHID CURRENT_USER' keyword in Package PACK .Please try to solve this error_code

Thanks in advance
Re: EXECUTE IMMEDIATE [message #389969 is a reply to message #389967] Wed, 04 March 2009 06:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read again the answers in one of your previous topic:
ORA-01031: insufficient privileges.

Regards
Michel
Re: EXECUTE IMMEDIATE [message #389970 is a reply to message #389967] Wed, 04 March 2009 06:48 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Is there 'GRANT ALL ON ... TO ...'? What is 'ALL'?

[EDIT: Shocked forget about it ... I'd rather not do several jobs at the time. Sorry.]

[Updated on: Wed, 04 March 2009 06:59]

Report message to a moderator

Re: EXECUTE IMMEDIATE [message #389974 is a reply to message #389970] Wed, 04 March 2009 06:56 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

But my old problem was overcame by using this keyword in package specification
AUTHID CURRENT_USER
Even though using this keyword when accessing the SYS objects only it resulting errors ,else there is no problem


Thanks
Re: EXECUTE IMMEDIATE [message #389976 is a reply to message #389974] Wed, 04 March 2009 07:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Conclusion?

Regards
Michel
Re: EXECUTE IMMEDIATE [message #389979 is a reply to message #389976] Wed, 04 March 2009 07:26 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

SOLVED IT Laughing

USING
EXECUTE IMMEDIATE 

[Updated on: Wed, 04 March 2009 07:26]

Report message to a moderator

Re: EXECUTE IMMEDIATE [message #389982 is a reply to message #389967] Wed, 04 March 2009 07:31 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
???
According to your first post uou already were using execute immediate?
Re: EXECUTE IMMEDIATE [message #389985 is a reply to message #389982] Wed, 04 March 2009 07:38 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

FIRST MY QUERY WAS

SELECT COUNT(*) INTO CNT FROM DBA_TAB_COLS H WHERE H.OWNER=TEST USER 


AT THE TIME IT WAS GIVING ME ERROR THEN TRIED IT AS

SELECT COUNT(*) INTO CNT FROM DBA_TAB_COLS H WHERE H.OWNER=TEST USER 
  EXECUTE IMMEDIATE 'grant all on DBA_TAB_COLS to TESTUSER'


AT THIS TIME IT GAVE ERROR 'ORA-01929: no privileges to GRANT' AND THIS
WAS SOLVED BY


SQLCNT:='SELECT COUNT(*) INTO CNT FROM DBA_TAB_COLS H WHERE H.OWNER=TEST USER'
  
  EXECUTE IMMEDIATE SQLCNT INTO CNT


THANKS Cool

[Updated on: Wed, 04 March 2009 07:39]

Report message to a moderator

Re: EXECUTE IMMEDIATE [message #389986 is a reply to message #389985] Wed, 04 March 2009 07:50 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Sssshhh..

Keep your voice down please.

I am pretty sure that this change did not have the desired effect..
One guess: You have a WHEN OTHERS exception clause and now, with the faulty dynamic sql it jumps there (and the error goes unnoticed)

[Updated on: Wed, 04 March 2009 07:52]

Report message to a moderator

Re: EXECUTE IMMEDIATE [message #390004 is a reply to message #389986] Wed, 04 March 2009 09:10 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member


SIR I AM NOT KEEPING MY VOICE UP ; I JUST EXPLAINED HOW I DONE IT AND I AM NOT USING EXCEPTION CLAUSE AND I AM GETTING THE OUT OF THE SELECT QUERY IN MY VARIABLE CNT .
Re: EXECUTE IMMEDIATE [message #390007 is a reply to message #389967] Wed, 04 March 2009 09:29 Go to previous message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
*Sigh*

Internet etiquette in general, and the orafaq forum guide in particular state that posting in all capitals LIKE THIS, is considered shouting. So please stop it.

As for the code you posted:
a) it's all got syntax errors
b) I can't imagine any way a select statement that gave an error will start working just because you wrapped it in an execute immediate.
Previous Topic: NLS Language Problem
Next Topic: Package inquiry...
Goto Forum:
  


Current Time: Fri Dec 09 06:02:33 CST 2016

Total time taken to generate the page: 0.10461 seconds