Home » SQL & PL/SQL » SQL & PL/SQL » Execute Immediate
icon5.gif  Execute Immediate [message #248072] Thu, 28 June 2007 00:22 Go to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
Hi!

When I was working in my previous work (view in a stored example) I was thinking about the following problem:
There are some special view for example the dba_roles. If I would like to select the number of the rows of this view in a stored procedure I've got to use the execute immediate command. That's good.
But what can I do if I would like to manage this view within a cursor from row to row in a stored procedure?
I studied this problem from every side. I look into the syntax and the usage of the execute immediate command. I tried to assemble a very difficult execute immediate command to solve this problem, but this trying got nowhere.

This "difficult" and very complex command exists at all?
Re: Execute Immediate [message #248073 is a reply to message #248072] Thu, 28 June 2007 00:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
If I would like to select the number of the rows of this view in a stored procedure I've got to use the execute immediate command. That's good.

No, that's wrong you don't use "execute immediate" just directly select.

Quote:
But what can I do if I would like to manage this view within a cursor from row to row in a stored procedure?

Use a cursor and not "execute immediate".

Rule: NEVER USE "EXECUTE IMMEDIATE" BUT FOR DYNAMIC SQL (or ddl).

(Well, there are some very very specific cases).
Regards
Michel

[Updated on: Thu, 28 June 2007 00:27]

Report message to a moderator

Re: Execute Immediate [message #248080 is a reply to message #248072] Thu, 28 June 2007 00:39 Go to previous messageGo to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
I have a similar requirement. But in my case, the select query has multiple tables, so I cannot use %ROWTYPE.

Also, I have not used 'sys_refcursor' , can you tell me whats the use of this?
Re: Execute Immediate [message #248089 is a reply to message #248073] Thu, 28 June 2007 01:04 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel Cadot wrote on Thu, 28 June 2007 07:27

Rule: NEVER USE "EXECUTE IMMEDIATE" BUT FOR DYNAMIC SQL (or ddl).


May I add to that?

NEVER use execute immediate. Period. You don't need it.


Once you know what it is for and how it works, you don't need these kind of advises anyway, so as long as you're here to read about how to use execute immediate, there is only one sound advise: DON'T.
Re: Execute Immediate [message #248133 is a reply to message #248072] Thu, 28 June 2007 02:33 Go to previous messageGo to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
Hi all,

Please help me out to this questions..Miche come on..

I have a similar requirement. But in my case, the select query has multiple tables, so I cannot use %ROWTYPE.

Also, I have not used 'sys_refcursor' , can you tell me whats the use of this?
Re: Execute Immediate [message #248139 is a reply to message #248133] Thu, 28 June 2007 02:52 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't see the relation with the original question.
If it is a new one then create a new topic.

Regards
Michel
Previous Topic: iterating over columns
Next Topic: TO_CHAR(TO_DATE()) error...!!!!
Goto Forum:
  


Current Time: Sat Dec 10 20:46:31 CST 2016

Total time taken to generate the page: 0.10271 seconds