Results 1 to 2 of 2

Thread: PL/SQL Help!!

  1. #1
    Join Date
    Feb 2003
    Posts
    15

    Unanswered: PL/SQL Help!!

    [Oracle 8.1.7][PL/SQL question...]
    In a stored procedure, I want to open a cursor to return, for which the query will join a few tables, like so:
    PROCEDURE ppp (
    crsr IN OUT crsr_type, value1_in IN INT, value2_in IN INT
    op_in IN varchar2) IS
    BEGIN
    OPEN crsr FOR
    SELECT c1, c2, c3
    FROM p, q, r
    WHERE ...(join-conditions)...
    AND (question below);
    RETURN;
    END;

    There is one condition in my WHERE clause that will depend on an input parameter of the procedure, namely 'op_in' (operator), and depending on what op_in is, my AND part could be:

    if (op_in = 'EQ')
    AND p.col = value1_in
    else if (op_in = 'GT')
    AND p.col > value1_in
    else if (op_in = 'BTW')
    AND p.col BETWEEN value1_in AND value2_in

    As you see, it is the operator of the where clause changes.

    I tried using DECODE, but I couldn't get it to decode on an operator.
    I tried to make the crsr query a dynamic sql string, but I think it's a 9i feature (OPEN crsr FOR dyn_sql_string).

    The procedure only does this and returns out the cursor.

    I am a newbie to Oracle myself, and wonder if there are ways I can get around with this?

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    You can use dynamic SQL:

Posting Permissions

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