Home » SQL & PL/SQL » SQL & PL/SQL » HELP !! How to execute an input parameter sql
HELP !! How to execute an input parameter sql [message #36283] Wed, 14 November 2001 08:05 Go to next message
Poonamb
Messages: 16
Registered: October 2001
Junior Member
Hello,

I have a procedure to which, I pass the actual SQL statement string as IN parameter.

I need to execute this in the query. But don't know how.

Eg.

CREATE OR REPLACE MYProc(MySelectStatement IN varchar2(500) AS

BEGIN

EXEC SQL EXECUTE IMMEDIATE &MySelectStatement; -- OR SOMETHING LIKE THIS. I am getting errors no matter what I try to do.

END;

Please suggest!
Thanks in advance,
PB

----------------------------------------------------------------------
Re: HELP !! How to execute an input parameter sql [message #36284 is a reply to message #36283] Wed, 14 November 2001 09:02 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
CREATE OR REPLACE MYProc(MySelectStatement IN varchar2(4000)) AS
BEGIN
EXECUTE IMMEDIATE MySelectStatement;
END;

----------------------------------------------------------------------
Re: HELP !! How to execute an input parameter sql [message #36288 is a reply to message #36284] Wed, 14 November 2001 09:28 Go to previous messageGo to next message
Poonamb
Messages: 16
Registered: October 2001
Junior Member
Thanks Todd!

I tried this and it compiled but when I try to test it :

package_name.procedure_name('Select * from MyTable');

I get ORA-01036 error. How do you do this?

Also, if I have single quotes in the select statement, how to handle that?

Thanks in advance.
PB

----------------------------------------------------------------------
Re: HELP !! How to execute an input parameter sql [message #36299 is a reply to message #36288] Wed, 14 November 2001 23:03 Go to previous message
Rob Baillie
Messages: 33
Registered: November 2001
Member
As for the single quotes in the statement, it shouldn't be an issue within the procedure since you are using a variable.

However, to call the procedure with the single quotes within the string you need to replace the quotes with two single quotes:

package_name.procedure_name('Select * from MyTable Where MyVarcharColumn = ''ABC''');

Rob

----------------------------------------------------------------------
Previous Topic: HELP!!! How to preserve data order in table when fetching data in there?
Next Topic: Re: msagent and oracle plz help
Goto Forum:
  


Current Time: Fri Mar 29 01:01:51 CDT 2024