Home » SQL & PL/SQL » SQL & PL/SQL » Build SQL Statement then Execute
Build SQL Statement then Execute [message #276220] Wed, 24 October 2007 07:05 Go to next message
sjordan
Messages: 19
Registered: October 2007
Junior Member
Hello All, I'm trying to build a cursor. The issue i'm having is that it does not interpret the variable correctly that I am passing to it. For instance, I have something like this:

CURSOR testc (wherePref VARCHAR2, whereExemp VARCHAR2, whereType VARCHAR2) RETURN testing%ROWTYPE
IS
SELECT * FROM tableX where abc in (wherePref) AND def like '%whereExemp%' AND ghi in (whereType);

Then, the procedure is making the call something like:

FOR testResults IN testc('''Y'', ''N''', 'NNNYNNN', '''First Check'', ''Second Check''')
LOOP
execute some code
END LOOP;

The problem i'm having is that the in clause is not working where I pass it multiple items, I really do not want to have to pass each item as a separate parameter (this could create a lot of work, especially since I may not always know how many parameters would be passed).

I am new to Oracle, and most of the programming I have done, you can build a SQL statement, and then execute it, for instance:

sqlStmt = "SELECT * FROM tableX where abc in (" & wherePref & ") AND def like '%" & whereExemp & "%' AND ghi in (" & whereType & ")";
cursorResult = connectionToDB.Execute(sqlStmt);

Is there a way in Oracle to build the cursor using something like that? If so, then that may work for me.

Thank you,
Shane
Re: Build SQL Statement then Execute [message #276231 is a reply to message #276220] Wed, 24 October 2007 07:54 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
You need to build your sql statement in a variable and then perform an execute immediate.

Look through the documentation for dynamic sql or execute immediate.

Re: Build SQL Statement then Execute [message #276268 is a reply to message #276220] Wed, 24 October 2007 10:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But of course this will have poor performances and will not be scalable.

Regards
Michel
Re: Build SQL Statement then Execute [message #276299 is a reply to message #276268] Wed, 24 October 2007 13:06 Go to previous messageGo to next message
sjordan
Messages: 19
Registered: October 2007
Junior Member
So, are there any suggestions as to how I should accomplish this, without degrading performance?
Re: Build SQL Statement then Execute [message #276301 is a reply to message #276299] Wed, 24 October 2007 13:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not a matter of an answer in a couple of lines.
It is a matter of rethinking the application to avoid this kind of situation.

Regards
Michel
Re: Build SQL Statement then Execute [message #276374 is a reply to message #276301] Thu, 25 October 2007 01:08 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
It appears to me that you are looking for some kind of "dynamic IN list".

I believe Tom Kyte has handled this in one of his Oracle Magazine articles. Search for it. But this is what I can come up with right now:
Option 1: A pipelined function: link
Option 2: string decomposition in SQL: Use a row generator and break a comma-separated list into elements.
Option 3: Use a global temporary table to hold the different options for your in list.

And now I'm off for the first cup of coffee of the day.

Edit: found the article. It's right here
MHE

[Updated on: Thu, 25 October 2007 01:09]

Report message to a moderator

Previous Topic: PL/SQL table Searching logic
Next Topic: Merge performance
Goto Forum:
  


Current Time: Sun Dec 04 16:45:46 CST 2016

Total time taken to generate the page: 0.21342 seconds