Home » SQL & PL/SQL » SQL & PL/SQL » Run string as SQL statement
Run string as SQL statement [message #295164] Mon, 21 January 2008 09:44 Go to next message
amardilo
Messages: 37
Registered: February 2007
Member
Hi there,

I have a stored procedure that I need to allow users to pass in select statements into. I need those select statements to output a cursor.

Is there a way to do this?

Some users have little SQL skills so I will need to fix up the statement (i.e. some people won't put the word SELECT at the start), this I can fix but is there anyway I can execute a select statement as a string and return it as a CURSOR from a stored procedure?
Re: Run string as SQL statement [message #295165 is a reply to message #295164] Mon, 21 January 2008 09:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
open my_curs for my_string;
return my_curs;

Search for "ref cursor" or "sys_refcursor" in the documentation:
Application Developer's Guide - Fundamentals
PL/SQL User's Guide and Reference


Regards
Michel
Re: Run string as SQL statement [message #295167 is a reply to message #295165] Mon, 21 January 2008 09:56 Go to previous messageGo to next message
amardilo
Messages: 37
Registered: February 2007
Member
You Sir are a legend!

I am already using a ref cursor.

This means I have little work to do Laughing
Re: Run string as SQL statement [message #295186 is a reply to message #295164] Mon, 21 January 2008 11:37 Go to previous messageGo to next message
amardilo
Messages: 37
Registered: February 2007
Member
Sorry to double post but I have another question what happens if I need to do a search on a string/VARCHAR2

i.e. how do I turn this into a VARCHAR2

vMyString := 'SELECT * FROM my_table WHERE column_name LIKE '%col%' AND field = 'abc' OR field2 = ' || myString2 || ';'

This string uses several ' in there would that cause an issue? do I need to write in a different character (or is there cleaver override for this)?

[Updated on: Mon, 21 January 2008 11:53]

Report message to a moderator

Re: Run string as SQL statement [message #295187 is a reply to message #295186] Mon, 21 January 2008 11:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Double the ' inside a string literal.

Regards
Michel
Re: Run string as SQL statement [message #295192 is a reply to message #295187] Mon, 21 January 2008 11:55 Go to previous messageGo to next message
amardilo
Messages: 37
Registered: February 2007
Member
Thanks so would this be acceptable?


vMyString := 'SELECT * FROM my_table WHERE column_name LIKE ''%col%'' AND field = ''abc'' OR field2 = '' || myString2 || '';'
Re: Run string as SQL statement [message #295196 is a reply to message #295192] Mon, 21 January 2008 12:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Beware of SQL injection.
The last part should be: 'OR field2 = :1' (don't put the ; in the query) then use
open my_curs for my_string using mystring2;

Regards
Michel
Re: Run string as SQL statement [message #295394 is a reply to message #295196] Tue, 22 January 2008 04:31 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
also change col to a bind variable.
Re: Run string as SQL statement [message #295408 is a reply to message #295394] Tue, 22 January 2008 05:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why if it is static?

Regards
Michel
Re: Run string as SQL statement [message #295477 is a reply to message #295408] Tue, 22 January 2008 07:24 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Sorry, misread the code.
My bad.

I read it as another concatenated string.
Previous Topic: Update
Next Topic: update telephone type
Goto Forum:
  


Current Time: Sat Dec 10 05:30:15 CST 2016

Total time taken to generate the page: 0.20316 seconds