Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2003
    Posts
    7

    Unanswered: PL/SQL Variable StoredProcedure Question

    Hi,
    I want to make a Stored Procedure for Oracle 8i that has a filter functionality.
    E.g.
    I have a table Car
    A user can make a filter function in a form bij selecting fields of the car table
    He can say : Show me all cars where name <> audi and weight > 1000.
    Or he can say : SHow me all cares where color = red.
    This functionaltity must be made in a stored proc.

    When only the = operator is allowed it would be easy :
    **********************
    param1 default '%'
    param2 default '%'

    select * form cars where name like param1 and color like param2 etc
    **********************

    but the problem are the operators that can be chosen : <, <>,>, etc.
    How can I make this variable in the stored proc ?

    Should I make for every field in the cars table an extra param for the operator and then depending on this using a different Select statement ?
    This will be very elaborated , no ?

    Thanks in advance
    Regards Rick

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: PL/SQL Variable StoredProcedure Question

    You would have to construct the desired SQL statement in a character string and use dynamic SQL like this:

    DECLARE
    v_sql VARCHAR2(4000);
    TYPE rc_type IS REF CURSOR;
    rc rc_type;
    BEGIN
    v_sql := 'select * from cars where name '||p_op1||' :v1 ' ... etc.
    OPEN rc FOR v_sql USING p_val1, p_val2, ... etc.;
    ...
    END;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •