Home » SQL & PL/SQL » SQL & PL/SQL » Executing dynamic SQL statements (10g linux)
Executing dynamic SQL statements [message #349091] Thu, 18 September 2008 19:37 Go to next message
slee72890
Messages: 3
Registered: July 2008
Junior Member
My statement below generates errors such as unexpected 'Grant', etc. Is this because EXECUTE IMMEDIATE will not run a DDL? Is there another way I can accomplish my desired outcome? TIA, slee72890

declare
usernam Varchar2(30):= NULL;
v_sql varchar2(300);
BEGIN
select user_name into usernam from dba_users where user_name like 'susan%';
IF usernam IS NULL THEN
dbms_output.put_line('There is no susan in the database.');
ELSE
v_sql := 'GRANT SELECT,INSERT,UPDATE,DELETE ON test TO '||usernam||';';
EXECUTE IMMEDIATE v_sql;
END IF;
END;
/

Re: Executing dynamic SQL statements [message #349092 is a reply to message #349091] Thu, 18 September 2008 20:00 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

ERROR? What Error? I don't see any error.

[Updated on: Thu, 18 September 2008 21:12] by Moderator

Report message to a moderator

Re: Executing dynamic SQL statements [message #349116 is a reply to message #349091] Thu, 18 September 2008 23:42 Go to previous messageGo to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
The user in which you are running the procedure should have the those previleges which you need to grant.means, if user dont have select previlege on table x,then through any procedure it can't give select grant to that proc.
IF i m not having a power,I can't delgate that power to another.as simple as it is..

Regards,
Navneet
Re: Executing dynamic SQL statements [message #349123 is a reply to message #349091] Fri, 19 September 2008 00:10 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Additionally ,

There could be issue with Semi-colon ( ; ) in your query .

The following link could help you.

Execute immediate

Thumbs Up
Rajuvan

[Updated on: Fri, 19 September 2008 00:11]

Report message to a moderator

Re: Executing dynamic SQL statements [message #349132 is a reply to message #349123] Fri, 19 September 2008 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition I doubt there are many schemas beginning with "susan" in lower case.

Regards
Michel
Re: Executing dynamic SQL statements [message #349200 is a reply to message #349091] Fri, 19 September 2008 05:05 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
user_name column does not exist in the dba_users view.
Its is : USERNAME column
SELECT username 
FROM dba_users 
WHERE username LIKE 'SUSAN%';
Re: Executing dynamic SQL statements [message #349273 is a reply to message #349091] Fri, 19 September 2008 10:35 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
You're all overlooking the fact that if there is no user matching susuan% (and like Michel said, I doubt there are any lowercase users), then it will error out with a NO_DATA_FOUND exception and never get to the ELSE logic.
Re: Executing dynamic SQL statements [message #349309 is a reply to message #349091] Fri, 19 September 2008 17:27 Go to previous messageGo to next message
slee72890
Messages: 3
Registered: July 2008
Junior Member
It was the extra semi-colon I had in the dynamic statement. Look like the variable string does not require a semi-colon at the end.

Thanks for all replies especially from rajavu1.
Re: Executing dynamic SQL statements [message #349389 is a reply to message #349309] Sun, 21 September 2008 02:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Maybe if you would have posted your real code, there would not have been so many different responses..
Re: Executing dynamic SQL statements [message #349398 is a reply to message #349309] Sun, 21 September 2008 07:10 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
And what happens if there are more than 1 'susan'?
Re: Executing dynamic SQL statements [message #349412 is a reply to message #349398] Sun, 21 September 2008 11:05 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
@pablolee

Quote:
And what happens if there are more than 1 'susan'?


It will throw TOO_MANY_ROWS exception.

In my opinion, probably not a query for which a fuzzy search
is the safe way to go; given that the intent is to grant authorities.

Noteworthy as Joy_Division pointed out is that the

Quote:
then it will error out with a NO_DATA_FOUND exception and never get to the ELSE logic.


If a BEGIN/END block were wrapped around the select and
both errors were handled in an EXCEPTION block then both
scenarios could be handled effectively, if the fuzzy search is
truly needed.

My philosophy is to always handle these potential exceptions
and not provide a work around by, as eg, turning SELECT's into a cursor and checking a row count before proceding.

A situtiation I see a lot in my shop that double's I/O. Just had to do a performance sweep on a procedure who's intent was to isolate one (and only one) row on a lookup table where a one or many relationship back to the parent could exist.

The original developer structured 15+ lookups as:
(psuedocode)
CURSORS anycursor(p_in  varchar2) IS 
SELECT Count(*) FROM TABLE WHERE 
Field = p_in;


Then if count = 1, open another(!?) cursor to select the
desired field.

If count was > 1 then try another lookup.

I replaced with a SELECT statement, and used the EXCEPTION
block for TOO_MANY_ROWS to accomplish the same.

Very Good question to ask as the basic concept merits attention in design for scenarios common in many PL/SQL apps.

I've read before philosophies that EXCEPTION handling
should be used only for "True" errors (process stopping)
and not to substitute for conditional logic. I've yet to read a 'Why?' in conjunction.

My philosophy is practical experience trumps especially when
the work-around has performance disadvantages.

Readability is always an analysis time-saver for future maintenance and I see good clarity in understanding intent
of code when I see ..WHEN TOO_MANY_ROWS then...

Best Regards
Harry




Regards
Harry
Re: Executing dynamic SQL statements [message #349438 is a reply to message #349412] Sun, 21 September 2008 16:41 Go to previous message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
@Harry
My question was intended to make the OP think a little more about his code. I am well aware of what would occur should there be more than 1 Susan.
Quote:
I replaced with a SELECT statement, and used the EXCEPTION
block for TOO_MANY_ROWS to accomplish the same.


Seems a bit of a waste to me when you could simply add a restriction on the rownum pseudocolumn.
Previous Topic: group by issue with adding another field
Next Topic: Convertion from Number to Word
Goto Forum:
  


Current Time: Thu Dec 08 20:13:46 CST 2016

Total time taken to generate the page: 0.06413 seconds