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.

basically, here's what i have:

-----------------------------------------code
block----------------------------------

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

Select ACCOUNTTABLE.account, USERTABLE.NAME_U, USERTABLE.SS_NO,
CASETABLE.CASE_NUMBER FROM ACCOUNTTABLE JOIN USERTABLE ON
ACCOUNTTABLE.ACCOUNT = USERTABLE.ACCOUNT
LEFT JOIN CASETABLE ON ACCOUNTTABLE.ACCOUNT = CASETABLE.ACCOUNT
WHERE ACCOUNTTABLE.STATUS = 'STATUS1' AND ROWNUM < 1000
and .....

return c;
end;
------------------------------END CODE
BLOCK-------------------------------

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...

Please help

Thanks

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

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