Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2004

    Unanswered: Passing a SQL statement parameter values


    This could be really simple, but I can't seem to get it working.

    I have a sql statement say:

    Select id, fname, lname from Info

    I need to be able to pass this statement parameter values. I can pass one, multiple or none at all.

    So something like this:

    Select id, fname, lname from Info where id = PARAMETER VALUE for just a single value passed.

    But I am not sure how to pass it multiple values at the same time (like Select id, fname, lname from Info where id = PARAMETER VALUE or PARAMETER VALUE 2..... ), or to pass it nothing (so it would be just Select id, fname, lname from Info) and get all the data back with no restrictions. (Optional parameter)

    All these different scenarios need to be coded with the same statement and there is no set number on home many can be passed at one time.

    Any pointers?


  2. #2
    Join Date
    Jan 2004
    try this:
    select id, fname, lname
      from table
    where id = nvl(:p_id, id) and
             fname = nvl(:p_fname, fname) and
             lname = nvl(:p_lname, lname)
    It works b/c a column is always equal to itself, so it would return "all" rows if the parameter is not passed in.
    Oracle OCPI (Certified Practicing Idiot)

Posting Permissions

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