Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

GennericSearch_Routine.

Posted on 2011-09-11
8
Medium Priority
?
252 Views
Last Modified: 2012-05-12
I have a procedure that handles generic searches submitted by user.
User can enter name, hiredate or salary or stock number.


All the data can be fetched from EMP table except when user enters a "Stock Number". If he does, then query needs to lookup in EMP_ORDERS_DETAILS to search for records that match the stock number. For example, if user entered "DVD100", I would need to find all customers who placed orders with that stock number "DVD100".

Would it best to create another query string for this condition (when user enters this field) that JOINS both table EMP and EMP_ORDER_DETAILS like this

--Add this to declaration
l_query_detail varchar2(512) default 'select * from emp a, emp_order_details b where ...'

--Addd this after begin
if (p_stock_no is NOT NULL) then
l_query := l_query_detail;
end if;

if ( p_stock_no is NOT NULL )
then
  l_query := l_query ||‘ and stock_no =:p_stock_no'‘;
else
  l_query := l_query || ‘ and (1=1 or :p_stock_no is null) ‘;
end if;

....continue code below as normal



create or replace procedure my_new_procedure
( p_ename in varchar2 default NULL,
p_hiredate in date default NULL,
p_sal in number default NULL)
as
l_cursor sys_refcursor;
l_query varchar2(512) default ‘select * from emp’;

cursor l_template is select * from emp;
l_rec l_template%rowtype;
begin
if ( p_ename is NOT NULL )
then
  l_query := l_query ||‘ where ename like ‘’%’’||:p_ename||’’%’’ ‘;
  else
l_query := l_query ||‘ where (1=1 or :p_ename is null) ‘;
end if;

if ( p_hiredate is NOT NULL )
then
  l_query := l_query ||‘ and hiredate > :p_hiredate ‘;
else
  l_query := l_query || ‘ and (1=1 or :p_hiredate is null) ‘;
end if;

if ( p_sal is NOT NULL )
then
  l_query := l_query || ‘ and sal > :p_sal ‘;
else
  l_query := l_query || ‘ and (1=1 or :p_sal is null) ‘;
end if;

dbms_output.put_line ( l_query );

open l_cursor for l_query using p_ename, p_hiredate, p_sal;
loop
fetch l_cursor into l_rec;
exit when l_cursor%notfound;
dbms_output.put_line ( l_rec.ename || ‘,’ || l_rec.hiredate || ‘,’ || l_rec.sal );
end loop;
close l_cursor;

end;
/

Open in new window

0
Comment
Question by:sam15
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 15

Expert Comment

by:x77
ID: 36520037
You work with strings, then oracle always need parse your sentence.
Then two solutions are identicals on performance.

Note that you can declare cursors and execute it with "Using" Clause for parameters.

Then cursors are precompiled and Oracle do´nt need parse.
0
 

Author Comment

by:sam15
ID: 36520533
since the query is not known until compile time, you have to start using a string and then add to it based on user inputs.
But since cursor is using bind variables it should parse only once and run thousands of times later.
0
 
LVL 15

Expert Comment

by:x77
ID: 36520811
You says have 3 cases, then you can declare 3 cursors and select the apropiate cursor depending on selected parameter.

Also, when your query is not deterministic using parameters do the possibility for Oracle Cache Find Sql Sentences more probable.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 15

Expert Comment

by:x77
ID: 36521023
You can also define a more complex Sql sentence that allow omited parameter:

 
SQL> DEFINE UBTEC=NA
SQL> DEFINE SUP=JLL
SQL> DEFINE TORD=WS
SQL> Select codtra,destra,sup,tord,ubtec from r18td 
  2  where   ('&Sup'='NA' or sup='&Sup') and
  3          ('&Tord'='NA' or Tord ='&Tord') and
  4          ('&UbTec'='NA' or UbTec='&UbTec');
old   2: where   ('&Sup'='NA' or sup='&Sup') and
new   2: where   ('JLL'='NA' or sup='JLL') and
old   3:         ('&Tord'='NA' or Tord ='&Tord') and
new   3:         ('WS'='NA' or Tord ='WS') and
old   4:         ('&UbTec'='NA' or UbTec='&UbTec')
new   4:         ('NA'='NA' or UbTec='NA')

Open in new window


Note that I use SqlPlus to build Sample and it support replacement, not parameters.
I use 'NA' for omited parameters.
When you build a query with parameters, you can code like:

   (pSup is null or pSup=Sup)                       -- Where pSup is a Plsql Var or procedure parameter

or

   (:pSup is null or :pSup=Sup)                     -- Where I use Oracle.DataAccess Command with BindByName = True
0
 
LVL 15

Expert Comment

by:x77
ID: 36521110
I Built a complex QueryCtl on Vb.Net that allows build Dynamic Query for Oracle and also for memory DataTables.
When I send a query to Oracle, always use Parameters.
It is very important for DataType compatibility and also for special character support ( embebed Quote by sample ).

 QueryCtl
For this sample, the DataTable Filter or displayed query is:

       "(T.Sup = 'JLL' And (T.Est = '8' Or T.Anulado is not null))"

But Query to Oracle with Parameters is:

       "(T.Sup = :1 And (T.Est = :2 Or T.Anulado is not null))"
0
 

Author Comment

by:sam15
ID: 36523245
if I have 3 parameters. I would need 6 CURSORS (not 3).

Think of all the combinations you can have (1 only, 2 only, 3 only, 1&2, 2&3, 1&3, 1&2&#).
If you have 4 parameters then you would need 24 cursors if you want to do that way.

0
 
LVL 15

Accepted Solution

by:
x77 earned 1500 total points
ID: 36525055
Yes, you are right.
At first time I think on alternate parameter, not combinations.

Next I propose a Complex Query that can combine any number of parameters.
0
 

Author Closing Comment

by:sam15
ID: 36571280
not fully answer question
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question