Re: Dynamic or Static

From: joel garry <joel-garry_at_home.com>
Date: Wed, 11 Jul 2012 08:38:27 -0700 (PDT)
Message-ID: <dd99579c-2276-410d-97da-0e4fab6fed86_at_q5g2000pba.googlegroups.com>

On Jul 11, 5:26 am, foredst..._at_gmail.com wrote:
> 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 :pparameter';
>     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;

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

--
_at_home.com is bogus.
http://blogs.computerworlduk.com/the-oracle-oracle/2012/07/oracle-on-its-knees/index.htm

Received on Wed Jul 11 2012 - 10:38:27 CDT