Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic query with different where parameters
Dynamic query with different where parameters [message #44406] Thu, 04 December 2003 11:06 Go to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
What's the best approach to use when a where clause could have multiple values.

Example:
A web page has a drop-down for year, class and input fields for start time, end time and instuctor. The select will always return a fixed number of columns, such as, year, title of class, start-end time and instructor.

I'm just not sure about the where clause if they only select year, year and instructor or any combination.

Since I need to display all the results do I just do:

var_clause := 'where ';

if year then var_clause := var_clause||'year='||web_year;

if class then var_clause := var_clause||'class='||web_class
...etc

I need to display all the results returned from the query:
loop
execute immediate 'select x, y, x'||var_clause
end loop
Re: Dynamic query with different where parameters [message #44407 is a reply to message #44406] Thu, 04 December 2003 12:21 Go to previous messageGo to next message
sverch
Messages: 582
Registered: December 2000
Senior Member
Try something like this:

CREATE OR REPLACE PACKAGE blah_blahP
AS
TYPE blah1_type IS TABLE OF blah.blah1%TYPE INDEX BY BINARY_UNTEGER;
TYPE blah2_type IS TABLE OF blah.blah2%TYPE INDEX BY BINARY_INTEGER;
TYPE blah3_type IS TABLE OF blah.blah3%TYPE INDEX BY BINARY_INTEGER;

PROCEDURE get_data (p_year IN VARCHAR2, p_title IN VARCHAR2,
p_start IN DATE, P_end IN DATE,
p_instructor IN VARCHAR2,
p_blah1 OUT blah1_type, p_blah2
OUT blah2_type, p_blah3 OUT blah3_type);

END blah_blahP;

CREATE OR REPLACE PACKAGE BODY blah_blahP
AS
PROCEDURE get_data (p_year IN VARCHAR2, p_title IN VARCHAR2,
p_start IN DATE, P_end IN DATE, p_instructor IN VARCHAR2,
p_blah1 OUT blah1_type, p_blah2 OUT blah2_type,
p_blah3 OUT blah3_type)

AS

TYPE cur_type IS REF CURSOR;
L_cursor cur_type;

strSQL VARCHAR2(2000) DEFAULT 'SELECT a.blah1, a.blah2, a.blah3 FROM blah a, blah_blah b WHERE
a.key=b.key ';

i BINARY_INTEGER:=0;

BEGIN

IF p_year<>'0000' --In your drop-down list have some default values that will be there if the
--user will not select any particular value
THEN
strSQL:= strSQL:||' AND a.year= '||''''||p_year||''''||' ';
END IF;

IF p_title<>'0000'
THEN
strSQL:= strSQL:||' AND a.title= '||''''||p_title||''''||' ';
END IF;

IF p_start<>'01-JAN-1900' --Again, some dummy value
THEN
strSQL:= strSQL:||' AND a.start= '||''''||p_start||''''||' ';
END IF;

IF p_end<>'01-JAN-1900' --Again, some dummy value
THEN
strSQL:= strSQL:||' AND a.end= '||''''||p_end||''''||' ';
END IF;

IF p_instructor<> '0000'
THEN
strSQL:= strSQL:||' AND a.instructor= '||''''||p_instructor||''''||' ';
END IF;

OPEN l_cursor FOR strSQL;
LOOP
FETCH l_cursor INTO p_blah1(i), p_blah2(i), p_blah3(i);
EXIT WHEN l_cursor%NOTFOUND;
i:=i+1;
END LOOP;
CLOSE l_cursor;

END get_data;

END blah_blah_P;

and then call this package.procedure from wherever you want, declare variables, supply parameters ,LOOP through PL/SQL table and display results...
Re: Dynamic query with different where parameters [message #44408 is a reply to message #44407] Thu, 04 December 2003 12:41 Go to previous messageGo to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
Wow, thanks.

Is the OPEN l_cursor FOR strSQL faster than doing some type of execute immediate and FORALL type processing. The web page should be fast and display the results quickly. I only say this because I read somewhere that OPEN cursor processing was slower than other type of cursor processing. I'm also looking for speed.
Re: Dynamic query with different where parameters [message #44409 is a reply to message #44408] Thu, 04 December 2003 13:02 Go to previous messageGo to next message
Art Metzer
Messages: 2478
Registered: December 2002
Senior Member
Bind variables?
Re: Dynamic query with different where parameters [message #44410 is a reply to message #44408] Thu, 04 December 2003 13:06 Go to previous messageGo to next message
sverch
Messages: 582
Registered: December 2000
Senior Member
Implicit cursors are faster because Oracle does not need to "translate" them, but you cannot build implicit cursor with REF cursor.
By the way, did you look at

http://asktom.oracle.com/pls/ask/f?p=4950:8:383290::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1288401763279,

Tom's ideas are always most brilliant
Re: Dynamic query with different where parameters [message #44411 is a reply to message #44409] Thu, 04 December 2003 13:12 Go to previous messageGo to next message
sverch
Messages: 582
Registered: December 2000
Senior Member
You are right, but this would work fine for modest number of users and this is simple (surely does not mean it's the best solution).
Re: Dynamic query with different where parameters [message #44412 is a reply to message #44410] Thu, 04 December 2003 13:40 Go to previous messageGo to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
No I didn't.

I just did a quick view and I like what I'm reading.

I need something that is fast and I can understand to code.

Any other suggestions or code someone wants to share with me. I would think someone has done something like this before. Users select parameters from a web page and then another web page displays the results based on their input. This code should be out there like a million times.

Thanks again.
Re: Dynamic query with different where parameters [message #44413 is a reply to message #44412] Thu, 04 December 2003 14:02 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Duane, I was just about to highly recommend the use of application contexts to simulate bind variables, but sverch beat me to it. We use this approach all the time for searches that have anywhere from 2 to 50 possible parameters, and it work great.
Re: Dynamic query with different where parameters [message #44414 is a reply to message #44413] Thu, 04 December 2003 14:33 Go to previous messageGo to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
Todd,

Do you have any code that you would be willing to share with me? At least something to see how you are doing things. That would be very helpful in writing mine. I'm sure it would help in seeing something that someone uses in production. It sounds like you have what I want to do.
Re: Dynamic query with different where parameters [message #44416 is a reply to message #44414] Thu, 04 December 2003 17:19 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
This is not production code, but a simple example I put together recently:

sql>create or replace context my_context using pkg_nds;
 
Context created.


Package code:

create or replace package pkg_nds
is
  procedure p_search(
    p_1  in  user_objects.object_id%type, 
    p_2  in  user_objects.object_name%type,
    p_rc out sys_refcursor);
end;
/
 
create or replace package body pkg_nds
is
  procedure p_search(
    p_1  in  user_objects.object_id%type, 
    p_2  in  user_objects.object_name%type,
    p_rc out sys_refcursor)
  is
    v_sql  varchar2(4000);
  begin
    v_sql := 'select object_id, object_name from user_objects where 1=1';
    if p_1 is not null then
      v_sql := v_sql || ' and object_id = sys_context(''MY_CONTEXT'', ''SLOT_1'')';
      dbms_session.set_context('MY_CONTEXT', 'SLOT_1', p_1);
    end if;
    if p_2 is not null then
      v_sql := v_sql || ' and object_name like sys_context(''MY_CONTEXT'', ''SLOT_2'')';
      dbms_session.set_context('MY_CONTEXT', 'SLOT_2', p_2);
    end if;
    open p_rc for v_sql;
  end;
end;
/


SQL*Plus example:

sql>var rc refcursor
sql>exec pkg_nds.p_search(null, 'EMP%', :rc)
 
PL/SQL procedure successfully completed.
 
sql>print rc
 
OBJECT_ID OBJECT_NAME
--------- ------------------------------
    29989 EMP
    34780 EMP_DEL
    31639 EMP_PK
 
3 rows selected.
 
sql>exec pkg_nds.p_search(29989, null, :rc)
 
PL/SQL procedure successfully completed.
 
sql>print rc
 
OBJECT_ID OBJECT_NAME
--------- ------------------------------
    29989 EMP
 
1 row selected.
 
sql>exec pkg_nds.p_search(29989, 'EMP%', :rc)
 
PL/SQL procedure successfully completed.
 
sql>print rc
 
OBJECT_ID OBJECT_NAME
--------- ------------------------------
    29989 EMP
 
1 row selected.


Let me know if you need any help putting your version together.
Re: Dynamic query with different where parameters [message #44444 is a reply to message #44416] Mon, 08 December 2003 07:57 Go to previous messageGo to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
Todd,

Thanks. I'm still reading and trying to comprehend your example and what I've read on asktom.

Can I ask you how you would do an IN ('apples', 'oranges', 'Pears') statement? I'll for sure have an IN statement so I'm not sure how to do it.
Re: Dynamic query with different where parameters [message #44445 is a reply to message #44444] Mon, 08 December 2003 11:04 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Object types - works great for variable IN lists.

Re: Dynamic query with different where parameters [message #44451 is a reply to message #44444] Tue, 09 December 2003 04:33 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Quick example:
<pre style="color: navy;">CREATE OR REPLACE TYPE VARCHAR2_TT AS TABLE OF VARCHAR2(4000)
/

DECLARE
some_array_parameter VARCHAR2_TT := VARCHAR2_TT('X','Y','Z');
v_result INTEGER;
BEGIN
SELECT COUNT(*) INTO v_result
FROM dual
WHERE dummy IN
( SELECT column_value
FROM TABLE(some_array_parameter) );

DBMS_OUTPUT.PUT_LINE('Retrieved ' || v_result || ' record(s)');
END;
/

Retrieved 1 record(s)</pre>I have a set of standard collection types and use them all the time (VARCHAR2_TT, NUMBER_TT, DATE_TT). I don't know how I ever got along without them.
Re: Dynamic query with different where parameters [message #218984 is a reply to message #44416] Mon, 12 February 2007 06:37 Go to previous messageGo to next message
flowera
Messages: 3
Registered: February 2007
Junior Member
i am novice to oracle/PLSQL.
I need complete syntax for selecting all rows/columns from a table(including type declaration,cursor declaration,ref cusor declaration)for this one..




This is not production code, but a simple example I put together recently:


sql>create or replace context my_context using pkg_nds;

Context created.

Package code:


create or replace package pkg_nds
is
procedure p_search(
p_1 in user_objects.object_id%type,
p_2 in user_objects.object_name%type,
p_rc out sys_refcursor);
end;
/

create or replace package body pkg_nds
is
procedure p_search(
p_1 in user_objects.object_id%type,
p_2 in user_objects.object_name%type,
p_rc out sys_refcursor)
is
v_sql varchar2(4000);
begin
v_sql := 'select object_id, object_name from user_objects where 1=1';
if p_1 is not null then
v_sql := v_sql || ' and object_id = sys_context(''MY_CONTEXT'', ''SLOT_1'')';
dbms_session.set_context('MY_CONTEXT', 'SLOT_1', p_1);
end if;
if p_2 is not null then
v_sql := v_sql || ' and object_name like sys_context(''MY_CONTEXT'', ''SLOT_2'')';
dbms_session.set_context('MY_CONTEXT', 'SLOT_2', p_2);
end if;
open p_rc for v_sql;
end;
end;
/

SQL*Plus example:


sql>var rc refcursor
sql>exec pkg_nds.p_search(null, 'EMP%', :rc)

PL/SQL procedure successfully completed.

sql>print rc

OBJECT_ID OBJECT_NAME
--------- ------------------------------
29989 EMP
34780 EMP_DEL
31639 EMP_PK

3 rows selected.

sql>exec pkg_nds.p_search(29989, null, :rc)

PL/SQL procedure successfully completed.

sql>print rc

OBJECT_ID OBJECT_NAME
--------- ------------------------------
29989 EMP

1 row selected.

sql>exec pkg_nds.p_search(29989, 'EMP%', :rc)

PL/SQL procedure successfully completed.

sql>print rc

OBJECT_ID OBJECT_NAME
--------- ------------------------------
29989 EMP

1 row selected.

Let me know if you need any help putting your version together.
Re: Dynamic query with different where parameters [message #219189 is a reply to message #218984] Tue, 13 February 2007 09:28 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
We had a situation very similar to this, and took a totally different approach. I added some code to the existing screen to log what combinations of search criteria were used when a search was processed.
After a month (we have a monthly business cycle) I analysed the results, and saw that there were only a relatively small set of searches that were performed.
I rewrote the search system to give the users a dropdown list from which they could select one of these search types, populate the one or two values required, and the back end pl/sql would then return one of s small number of distinct ref cursors. As the parameters are tightly defined for each Ref Cursor, they can all use bind variables.

I guess what I'm saying is - have you considered redefining the problem, rather than trying to provide a more general solution.
Previous Topic: Dynamic SQL and Loop question
Next Topic: status of Oracle service automatically sets to null
Goto Forum:
  


Current Time: Thu Dec 08 18:47:12 CST 2016

Total time taken to generate the page: 0.05781 seconds