Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Posts
    2

    Question Unanswered: optional parameters in package

    Hi,
    is in oracle packages option how to handle optional parameters? I've tried WHERE IF clause but it looks like my oracle 8 doesn't like this logic handling. I have this code in package body>

    PACKAGE BODY KARTAKB_WEBPACK AS
    PROCEDURE GetKBTransactions(parHaus IN VARCHAR2, parDateFrom IN DATE,parDateTo IN DATE, p_cursor OUT RefCur,p_errorcode OUT INT)
    IS
    BEGIN

    OPEN p_cursor FOR SELECT HAUS, DATUM, CASBULL, KASA, TERM, KASNR, UCET, OBNOSPOS, KARTA, AUTORI FROM BELDEN
    WHERE (HAUS = parHaus) AND (DATUM BETWEEN parDateFrom AND parDateTo);
    EXCEPTION
    WHEN OTHERS THEN
    p_errorcode := SQLCODE;
    END GetKBTransactions;

    END KARTAKB_WEBPACK;

    >and webpage with eight optional parameters of varchar type which I need to filter here. Only solution which works is to make default of these parameters as "%" and change "=" to "like", but this is crap code I think.

    If this question is dumb one, sorry, I am not database developer at all and have virtually no experience with Oracle.

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

    Re: optional parameters in package

    This could be a case where dynamic SQL is the best approach:

    Code:
    ...
    IS
      v_sql VARCHAR2(4000);
    BEGIN
      v_sql := 'SELECT HAUS, DATUM, CASBULL, KASA, TERM, KASNR, UCET, OBNOSPOS, KARTA, AUTORI
      FROM BELDEN
      WHERE (HAUS = :Haus)
      AND (DATUM BETWEEN :DateFrom AND :DateTo)';
      
      IF parThing IS NOT NULL THEN
        v_sql := v_sql || ' AND (THING = :parThing)'
      ELSE
        v_sql := v_sql || ' AND (:parThing IS NULL)'
      END IF;
    
      OPEN p_cursor FOR v_sql USING parHaus, parDateFrom, parDateTo, parThing;
    ...
    In the above example there is one optional parameter parThing. I have had to create a bind variable for parThing even when it is not used, otherwise you will get "ORA-01006: bind variable does not exist" if there are more values in the USING clause than there are bind variables in v_sql.

    An alternative approach without dynamic SQL is to have WHERE clauses like this:

    AND (parThing IS NULL OR THING=parThing)

    However, this will not allow the optimizer to make use of an index on THING if there is one.

  3. #3
    Join Date
    Mar 2003
    Posts
    2

    Thumbs up Re: optional parameters in package

    Wow..it works...thanks :-)
    Originally posted by andrewst
    This could be a case where dynamic SQL is the best approach:

    Code:
    ...
    IS
      v_sql VARCHAR2(4000);
    BEGIN
      v_sql := 'SELECT HAUS, DATUM, CASBULL, KASA, TERM, KASNR, UCET, OBNOSPOS, KARTA, AUTORI
      FROM BELDEN
      WHERE (HAUS = :Haus)
      AND (DATUM BETWEEN :DateFrom AND :DateTo)';
      
      IF parThing IS NOT NULL THEN
        v_sql := v_sql || ' AND (THING = :parThing)'
      ELSE
        v_sql := v_sql || ' AND (:parThing IS NULL)'
      END IF;
    
      OPEN p_cursor FOR v_sql USING parHaus, parDateFrom, parDateTo, parThing;
    ...
    In the above example there is one optional parameter parThing. I have had to create a bind variable for parThing even when it is not used, otherwise you will get "ORA-01006: bind variable does not exist" if there are more values in the USING clause than there are bind variables in v_sql.

    An alternative approach without dynamic SQL is to have WHERE clauses like this:

    AND (parThing IS NULL OR THING=parThing)

    However, this will not allow the optimizer to make use of an index on THING if there is one.

Posting Permissions

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