Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2007
    Posts
    1

    Unanswered: bind variables and REF CURSORs

    hi all

    well i have an interesting problem

    i need to construct a query for a refcursor besade on conditions....

    as:

    input params: in_lname and in_fname
    __________________________________________________ ____


    sql_stmt := 'Select first_name, ' || 'last_name, ' ||
    'from table1';

    IF in_lname is not null then
    -- we are selecting guests by Lastname, Lastname & Firstname,
    -- Lastname & Firstname & Zip, or Lastname & Zip

    sql_stmt := sql_stmt || 'where gc_last_name = :1 ';

    IF in_fname is not null then
    sql_stmt := sql_stmt || ' and gc_first_name = :2 ';
    END IF;

    END IF;

    open fm_lom_cv for sql_stmt using in_lname, in_fname;


    ___________________________________________


    Now here the in_lname is required BUT in_fname can be NULL

    hence we dont excute or create the sql with sql_stmt || ' and gc_first_name = :2 ';

    so :2 will not be used

    hence it gives error IF in_fname is null ie ORA-01006: bind variable does not exist

    Please suggest how to handle it....

    thanks

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

    Talking


    Try this:
    Code:
    ...etc...
    IF in_fname is not null then
        sql_stmt := sql_stmt || ' and gc_first_name = NVL(:2,gc_first_name) ';
    END IF;
    ...etc...


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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