Re: dynamic stored procedure

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