Good day.
Please response your opinion. Assume a function should return result set based on given parameters, but not all parameters can be given (NULL valued). Which method is better and why?

CREATE OR REPLACE FUNCTION dynamic_sql( pparameter table_name.col_name%TYPE ) RETURN SYS_REFCURSOR IS
RESULT SYS_REFCURSOR;
vSql VARCHAR2( 100 );
vCursor NUMBER;
vNumber NUMBER;
BEGIN
vSql := 'SELECT * FROM table_name';
IF pparameter IS NOT NULL THEN
vSql := vSql || ' WHERE col_name LIKE parameter';
END IF;
vCursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE( vCursor, vSql, DBMS_SQL.NATIVE );
IF pparameter IS NOT NULL THEN
DBMS_SQL.BIND_VARIABLE( vCursor, 'pparameter', pparameter );
END IF;
vNumber := DBMS_SQL.EXECUTE( vCursor );
RESULT := DBMS_SQL.TO_REFCURSOR( vCursor );
RETURN ( RESULT );
END dynamic_sql;

CREATE OR REPLACE FUNCTION static_sql( pparameter table_name.col_name%TYPE ) RETURN SYS_REFCURSOR IS
RESULT SYS_REFCURSOR;
BEGIN
OPEN RESULT FOR SELECT * FROM table_name WHERE pparameter IS NULL OR col_name LIKE pparameter;
RETURN ( RESULT );
END static_sql;

On Jul 11, 5:26*am, foredst... (AT) gmail (DOT) com wrote:
Is the function going to be run more than once? If so, the static
will be better because it won't fill up your shared pool with useless
garbage to clean up. It will leave garbage to clean up in the
buffers, but Oracle is built for that.

jg
--
@home.com is bogus.
http://blogs.computerworlduk.com/the...nees/index.htm

On Jul 11, 6:26*am, foredst... (AT) gmail (DOT) com wrote:
I agree with Joel -- the dynamic SQL will litter the shared SQL area
with code that won't likely be used again where the static sql
statements won't create as much trash. Have you thought of not using
DBMS_SQL and going with EXECUTE IMMEDIATE and bind variables? That
could create even less non-reusable SQL.


David Fitzjarrell

Do You mean that variant followed will be the best?

CREATE OR REPLACE FUNCTION bind_sql( pparameter table_name.col_name%TYPE ) RETURN SYS_REFCURSOR IS
RESULT SYS_REFCURSOR;
vSql VARCHAR2( 1000 );
BEGIN
vSql := 'SELECT * FROM table_name WHERE IS NULL OR col_name LIKE ';
OPEN RESULT FOR vSql USING pparameter, pparameter;
RETURN ( RESULT );
END bind_sql;