How to pass a complex query in a variable

[weberm]

How does one pass a complex value into a SELECT statement? I have a query I wrote for a project that I'd love to make into a stored procedure but am having trouble implementing it. This is what I am attempting, based on the emp table in scott/tiger:

DECLARE
 InStr VARCHAR2(20); 
BEGIN
  SELECT COUNT(*)
  FROM emp
  WHERE deptno IN (InStr);
END;

I can set InStr to a single value easily, but how would I set InStr for multiple values, ie., "'10','20'"? I remember seeing it done once but can't seem to find it now and ORACLE doesn't seem to like embedded quotes. This is so frustrating!

 

[Thargy]

web

as usual, the illustrious Mr Kyte has done it all before

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:210612357425

should be of some assistance.

Regards

T

 

[SantaMufasa]

I agree, Tharg, that Tom Kyte stellar in providing lucid illustrations/explanations of code concepts.

I believe, however, that my code, below, represents a tighter, simpler, cleaner way to achive WebERM's objective: "Pass a string of zero, one, or more delimited values that represent the universe of valid matches for individual values in a table."

Instead of using Oracle's more functionally complex IN operator, I recommend using the INSTR function:

(Sample EMP data)
col ename format a20
select ename,deptno from emp order by 2,1;

ENAME                    DEPTNO
-------------------- ----------
Mark Quick-To-See            10
Colin Magee                  31
Midori Nagayama              31
Henry Giljum                 32
Yasmin Sedeghi               33
Mai Nguyen                   34
Radha Patel                  34
Andre Dumas                  35
Elena Maduro                 41
George Smith                 41
LaDoris Ngao                 41
Molly Urguhart               41
Akira Nozaki                 42
Roberta Menchu               42
Vikram Patel                 42
Alexander Markarian          43
Ben Biri                     43
Chad Newman                  43
Antoinette Catchpole         44
Eddie Chang                  44
Bela Dancs                   45
Marta Havel                  45
Sylvie Schwartz              45
Audry Ropeburn               50
Carmen Velasquez             50

create or replace function weberm (depts varchar2) return number is
  num_depts number;
BEGIN
  SELECT COUNT(*) into num_depts
  FROM emp
  WHERE instr(depts,deptno) > 0;
  return num_depts;
END;
/

select weberm('10,20,31') from dual;

WEBERM('10,20,31')
------------------
                 3

...or use bizarre delimiters...

select weberm('howdy 10 hello 20 hehehe 31') from dual;

WEBERM('HOWDY10HELLO20HEHEHE31')
--------------------------------
                               3

...or even an empty function call...

select weberm(null) from dual;

WEBERM(NULL)
------------
           0

The beauty of the above technique is that you can use any delimeter (e.g. comma, space, $, /, even 'Hello') to separate the values that you send in the function call.

Let us know if you find this useful or if you have questions.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Thargy]

I give up!

Will call and discuss terms of surrender at your convenience.

Regards

T

 

[karluk]

SantaMufasa's method, although clever, is not a good general solution to the problem of searching on multiple values. It works only because all of the DEPTNO values in the table are exactly two digits long. If you add another row to EMP which has a DEPTNO of 5, say, and then use the weberm function to search on '11, 47, 51', you would get a match even though none of those DEPTNOs exist in the EMP table. That's because the row with DEPTNO=5 is, indeed, a substring of '11, 47, 51' even though there is no exact match on DEPTNO.

 

[SantaMufasa]

(Sorry for the delayed response, I had a doctor's appointment [following my previous post] from which I did not return until just a few minutes ago.)

Point well taken, Karl. But with an ever-so-slight adjustment to the code (to generalise the solution for any number of digits or characters), and a slight disciplining of the specification of the function's incoming parameter values, one can deal with the issue you mention.

The disciplining of the parameter values is that once WebERM decides upon the parameter value, he sticks with that value.

To illustrate, I've modified the DEPTNO of two rows to reflect both single-digit and greater-than-two-digit searches. I have also chosen (for ease of reading the code) "/" to be the delimeter of choice (but the delimiter can become any characters, so long as delimiter in the function definition is the delimiter in the function call).:

select ename,deptno from emp order by 2,1;

ENAME                    DEPTNO
-------------------- ----------
LaDoris Ngao                  5
Mark Quick-To-See            10
Colin Magee                  31
Midori Nagayama              31
Henry Giljum                 32
Yasmin Sedeghi               33
Mai Nguyen                   34
Radha Patel                  34
Andre Dumas                  35
Elena Maduro                 41
George Smith                 41
Molly Urguhart               41
Akira Nozaki                 42
Roberta Menchu               42
Vikram Patel                 42
Alexander Markarian          43
Chad Newman                  43
Antoinette Catchpole         44
Eddie Chang                  44
Bela Dancs                   45
Marta Havel                  45
Sylvie Schwartz              45
Audry Ropeburn               50
Carmen Velasquez             50
Ben Biri                   3131

select weberm('5/20/31/43') from dual;

WEBERM('5/20/31/43')
--------------------
                   5

select weberm('5/3131') from dual;

WEBERM('5/3131')
----------------
               2

So, I believe that the code is still tighter, simpler, cleaner, and now, generalised for use with any-length values (and still, as before, able to handle either Alpha or Numeric values), provided the user follows the consistent-delimiter specifications.

(If one wished to generalise even further, to accommodate the use of any delimiter for any invocation of the function, then one could add another argument to the "weberm" function to specify the delimiter of choice.)

Let me know if this passes muster.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

Sorry...I did not re-read my post carefully enough before my hitting the [Submit] button...Above, when I said,

"...WebERM decides upon the parameter value, he sticks with that value...",

it should have, instead, read,
"...WebERM decides upon a delimeter character, he sticks with that character...".

Apologies,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

Geez...it must be late...Wouldn't it be nice if I posted the revised code, as well? Here it is:

create or replace function weberm (depts varchar2) return number is
  num_depts number;
BEGIN
  SELECT COUNT(*) into num_depts
  FROM emp
  WHERE instr('/'||depts||'/','/'||deptno||'/') > 0;
  return num_depts;
END;
/

Again, Sorry for my quick trigger finger.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Thargy]

Obviously the doctor didn't prescribe you anything for itching trigger fingers

Regards

T

 

[karluk]

SantaMufasa's revised code fixes some of the problems with spurious matches, but it doesn't really address the fundamental weakness of the approach - that 'instr' detects matches on any substring of DEPTS, not just those that are exact matches on DEPTNO. The revised code requires that the separator character never occur in the column being searched. That may be true for the DEPTNO of SCOTT.EMP, but will fail as a general solution.


Take, for example, the '5/20/31/43' string used by SantaMufasa in his example. If DEPTNO weren't restrict to numeric digits, you could get spurious matches on things like '1/4' (a commonly used fraction) or '5/20/31' (someone's birthdate) in addition to the values '5' '20' '31' and '43' which was the intended search.

 

[karluk]

Correction: In the example above there would be a spurious match on '31/43', but not on '1/4'.

 

[SantaMufasa]

...the '5/20/31/43' string used by SantaMufasa in his example. If DEPTNO weren't restrict to numeric digits, you could get spurious matches on things like '1/4'...

If one wished to generalise even further, to accommodate the use of any delimiter for any invocation of the function, then one could add another argument to the "weberm" function to specify the delimiter of choice.

...thus resolving the '1/4', '31/43', et cetera issues.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[karluk]

Unfortunately, modifying "weberm" by allowing the user to specify the separator character doesn't fix the problem. In general, the invoker of the function would have to have EXTREMELY detailed advanced knowledge of the data in the column being searched. Specifically, he would have to know that the separator character definitely never makes an appearance in potentially millions of rows of data being searched. But what's the point of writing a search function if it's only reliable if you already know what's in the column you're searching on?

You will note that Tom Kyte's proposed solution has a similar, but far less serious, coding problem. He is assuming that a comma will always work as the delimiter. But in his case, the proposal to parameterize the delimiter would actually work. He strips off the delimiter before searching the table, so all he has to be concerned about is that the delimiter not appear in any of the values he is searching on - a much more managable list.

I also have some serious concerns about the efficiency of the 'instr' approach. With all of the concatenation being done to column values, it seems pretty hopeless that Oracle will ever use any indexes that might exist on the column being searched. I haven't done any large scale tests, but I strongly suspect that the performance would be worse than just about any other alternative.

So, all in all, I would highly recommend using Tom Kyte's solution as an overall better approach to searching on a list of values.

 

[weberm]

Wow, I didn't realize I'd get so many replies to my query!

I'll have to ponder this further...