| HELP !! How to execute an input parameter sql [message #36283] |
Wed, 14 November 2001 08:05  |
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 #36288 is a reply to message #36284] |
Wed, 14 November 2001 09:28  |
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 |
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
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
