Thanks Andy,

Here's what i ended up doing, please comment if you can:

-------------------------------------code-------------------------------------------

CREATE OR REPLACE PROCEDURE SP_BKDPSEARCHTest
(param_account in ACCOUNT.ACCOUNT%TYPE,
param_name in USER.NAME%TYPE,
param_case in BKRP.CASE_NUMBER%TYPE,
param_ssn in USER.SS_NO%TYPE)
as

SQLQuery varchar2(4000) := 'Select ACCOUNT.account, USER.NAME,
USER.SS_NO, BKRP.CASE_NUMBER' ||chr(10)
||' FROM ACCOUNT JOIN USER ON ACCOUNT.ACCOUNT = USER.ACCOUNT LEFT
JOIN BKRP ON' ||chr(10)
||' ACCOUNT.ACCOUNT = BKRP.ACCOUNT where ACCOUNT.ACCOUNT IS NOT NULL
';
BEGIN
if (param_account IS NOT NULL) then
SQLQuery := SQLQuery || ' AND ACCOUNT.ACCOUNT = ''' || param_account
|| '''';
end if;
if (param_name is not null) then
sqlquery := Sqlquery || ' and USER.NAME_U like ''' || param_name ||
'%''';
end if;
if (param_case is not null) then
SQLQuery := SQLQuery || ' AND BKRP.CASE_NUMBER LIKE ''' || param_case
|| '%''';
end if;
if (param_ssn is not null) then
SQLQuery := SQLQuery || ' AND USER.SS_NO LIKE ''' || param_ssn ||
'%''';
end if;

EXECUTE IMMEDIATE (SQLQuery);

END SP_BKDPSEARCHTest;
/

-------------------------------------------code-----------------------------------------------------
Andy Hassall wrote:
> On 23 Aug 2006 12:38:02 -0700, pmussomeli@gmail.com wrote:
>
> >I am new to oracle. i'm trying to write a function/stored procedure
> >that passes variables to be used in the where clause and i'm lost.
> >
> >create function testFunction (param_name in USERTABLE.NAME%TYPE,
> >param_account in ACCOUNTTABLE.ACCOUNT%TYPE, param_case in
> >CASETABLE.CASE_NUMBER%TYPE, param_ssn in USERTABLE.SS_NO%TYPE)
> >
> >return cursor
> >as
> >begin
> > cursor c is
> >
> [snip some SQL]
> >and .....
> >
> >return c;
> >end;
> >
> >and i want to add:
> >
> >if (param_account is not null) then
> >"and where ACCOUNTTABLE.ACCOUNT = param_account"
> >end if
> >if (param_name is not null) then
> >"and where USERTABLE.NAME = param_name"
> >end if
> >etc...
>
> There are two ways of executing "dynamic SQL" within PL/SQL.
>
> The new way is using EXECUTE IMMEDIATE - also known as "Native Dynamic SQL".
> This has several advantages, but the problem is that it does not accept a
> variable number of bind variables. You will of course be using bind variables
> in your statement, but it depends on whether the passed parameters are null or
> not null. You may be able to have multiple EXECUTE IMMEDIATE statements in
> if-else branches depending on the combination of parameters, but this may
> suffer from combinatorial explosion as the number of parameters increases.
>
> The old way is using DBMS_SQL. As each parameter is bound with a separate
> call, you can have a variable number of bind variables. It's less 'integrated'
> with PL/SQL though; it's some time since I've used DBMS_SQL, but IIRC you may
> not be able to return a cursor generated by DBMS_SQL in the same way as EXECUTE
> IMMEDIATE (or the closely related OPEN-FOR syntax).
>
> The docs say:
>
> "
> Native Dynamic SQL is an alternative to DBMS_SQL that lets you place dynamic
> SQL statements directly into PL/SQL blocks. In most situations, Native Dynamic
> SQL is easier to use and performs better than DBMS_SQL. However, Native Dynamic
> SQL itself has certain limitations:
>
> * There is no support for so-called Method 4 (for dynamic SQL statements
> with an unknown number of inputs or outputs)
> * There is no support for SQL statements larger than 32K bytes
>
> Also, there are some tasks that can only be performed using DBMS_SQL.
> "
>
> See the docs for more info.
>
> http://download-uk.oracle.com/docs/c...mic.htm#i14500
> http://download-uk.oracle.com/docs/c...htm#sthref7370
>
> --
> Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
> http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool