Re: dynamic stored procedure

From: pdog <pmussomeli_at_gmail.com>
Date: 25 Aug 2006 05:49:11 -0700
Message-ID: <1156510150.954447.51380@i3g2000cwc.googlegroups.com>

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_at_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/cd/B19306_01/appdev.102/b14261/dynamic.htm#i14500
> http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sql.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

Received on Fri Aug 25 2006 - 07:49:11 CDT