Home » SQL & PL/SQL » SQL & PL/SQL » Generic SELECT procedure. (Oracle 10g)
Generic SELECT procedure. [message #330407] Mon, 30 June 2008 02:08 Go to next message
Messages: 10
Registered: June 2008
Junior Member
Hello. I'm trying to create a generic select procedure. let me explain what I mean:

suppose I have a table Products with the following fields:
ProductId, Price, Name, Quantity, Category.

now I want to create a stored procedure that will accept a ProductId as a parameter (p_Id) and return the products with the ID. if p_Id isn't supplied (DEFAULT NULL), then all products should be returned.

what I have come up with is this:

FROM Products
WHERE (p_Id IS NULL) OR (ProductId = p_ID)

now, this works. the problem is, what happens when I'm doing this with more than just one field? let's say 20-30 fields (most of which will be null parameters, by the way).

I then tried using NDS (with concatination):
sql_str := 'SELECT * FROM Products WHERE 1=1';
sql_str := sql_str || ' AND ProductId = || p_Id;

the problem with this approach is that it's vulnerable to SQL code injection and also, I have to write special code for different types - comparing string is difference from comparing dates which is different form comparing integers and so on...

I then tried binding variables, but as it seems to me, you can't have a variable number of bind variables.

the last option I thought of was DBMS_SQL.
I don't know much about it, so I thought I'd try asking here before persuing this option.

to make things more clear, I'd like to ask this:

1) will the first approach (p_Id is null) or (productId = p_Id), with a lot of variables (20 - 30) be a great performance hit?

2) is it possible to bind a variable number of parameters in NDS? this is my preferred approach.

3) will using DBMS_SQL be a great performance hit? is it suitable for working with REF CURSORs?

obviously it won't be as fast as a hard coded procedure, but that's not the kind of performance I'm looking for. I'm asking if it'll be something like an order of magnitude slower...

4) and most importantly - do you have any other approachs you could share with me?

thanks ahead.
Re: Generic SELECT procedure. [message #330417 is a reply to message #330407] Mon, 30 June 2008 02:27 Go to previous messageGo to next message
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Use ref cursor's and a simple if statement
for example
create or replace procedure (p_product_id in products.product_id%TYPE , p_ref out sys_refcursor)

if product_id is null then
open p_ref for select * from products;
open p_ref for select * from products where product_id=p_product_id;
end if;


Just to give you an idea of what you could do
Re: Generic SELECT procedure. [message #330422 is a reply to message #330407] Mon, 30 June 2008 02:44 Go to previous messageGo to next message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I've solved the problem of a large number of paramters, sme of which may be null, by building up a SQL statement in a Varchar2, from lots of conditional blocks like this:
IF p_parameter7 IS NOT NULL THEN
  v_sql := v_sql||' AND table_name.column_name = :p_parameter7 ';
  v_sql := v_sql||' AND :p_parameter7 IS NULL ';

That way, when you open a cursor usingthis SQL statement, you can just pass in a standard list of parameters.
Re: Generic SELECT procedure. [message #330457 is a reply to message #330422] Mon, 30 June 2008 03:56 Go to previous message
Messages: 7880
Registered: March 2000
Senior Member
I solved the problem of a dynamic cursor with an undetermined number of bind-variables by using sys_context.
v_sql := 'select *
          from   my_table
          where  1 = 1';
if v_param1 is not null
  v_sql := v_sql || ' and    my_col1 = ||sys_context(''my_context'', '':my_first_param'')';

  ( 'my_context'
  , 'my_first_param'
  , v_param1
end if;

I find it results in very readable and maintainable code.
Previous Topic: creating a excel file data from the sql prompt
Next Topic: I am unable to create a nested table (merged)
Goto Forum:

Current Time: Sat Aug 19 12:02:59 CDT 2017

Total time taken to generate the page: 0.01356 seconds