Variable where clause

Hi,

I am new to Oracle and had been working on Ingres previously.

There is something called as a 'Qualification' clause in Ingres 4GL. I can specify a query like this.

Select emp_no, emp_name, addr
from employee
qualification

	emp_no := :empno_var 
	emp_age := :empage_var
	emp_experience := :emp_experience_var

Note that emp_no, emp_age, emp_experience are columns in the table employee, and the corresponding _var's are local program variables.

When Ingres executes this query, if any of these local variables does not have any value, then that line does not make a part of the where clause of the query executed.

I am trying to find a way of achieving the same in Oracle Stored Procedures, written in PL-SQL. I want to write stored procedures which will select values from a table, and return the columns. The parameters passed to the stored procedure will determine the where clause of the select query. I want to omit that value from the where clause, if no value is passed in the input parameter.

Please post a reply or mail to me.

Thanx in advance

-Nara

  R. Narayanan                                   Home -
  US West Technologies,                          1966, S. University Blvd.
  4001, Discovery Dr.                            Apt# 2C
  Boulder, CO.                                   Denver, CO-80210
  Ph - 303 541 7056 (Boulder)                    Ph - 303 765 0480
       303 624 1114 (Denver)