Re: Dynamic or Static

From: ddf <oratune_at_msn.com>
Date: Wed, 11 Jul 2012 20:59:08 -0700 (PDT)
Message-ID: <397b7b4d-9f21-4cd9-8119-b38d7c1c6a0e_at_re8g2000pbc.googlegroups.com>



On Jul 11, 6: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;

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 Received on Wed Jul 11 2012 - 22:59:08 CDT

Original text of this message