Re: Dynamic query

From: Sybrand Bakker <sybrandb_at_hccnet.nl>
Date: Thu, 06 Jun 2013 18:10:38 +0200
Message-ID: <9uc1r89sei2kjfievqqoc8knikrucn7p16_at_4ax.com>

On Mon, 3 Jun 2013 18:42:50 -0700 (PDT), Javier Montero <jmontero19_at_gmail.com> wrote:

>Hello People
>
>I new in this forum and I have a question about a problem for build a dynamic statement. My problem is when try to run my code from ASP or any app I have a error, I don't know that is bad in code, please below my PL*SQL. I'm not expertise in oracle but I try.
>Basically my problem is in the LIKE statement maybe I declared bad this sentence.
>The common code errors when try to load my query from asp are:
>ERROR-ORA-01756
>ERROR-ORA-00911
>I appreciate your comments and recommendations
>
>CREATE OR REPLACE PACKAGE SAME.TESTPGK2 AS
>TYPE out_cursor IS REF CURSOR;
>PROCEDURE T01_PRUEBA2(pp IN VARCHAR2, status IN VARCHAR2, outputCursor OUT out_cursor);
>END TESTPGK2;
>
>CREATE OR REPLACE PACKAGE BODY TESTPGK2 AS
>PROCEDURE T01_PRUEBA2(pp IN VARCHAR2, status IN VARCHAR2, outputCursor OUT out_cursor)
>AS
>
>v_query varchar2(8000);
>
>BEGIN
> v_query := 'SELECT ID_TYP_ACTOR AS CODIGO, DESCRIPCION AS DESCRIPCION, ESTATUS AS ESTATUS FROM T01_TIPOS_ACTORES';
> v_query := v_query || ' WHERE 1 = 1';
> IF pp IS NOT NULL THEN
> v_query := v_query || ' AND DESCRIPCION LIKE %||pp||% ';
>
> END IF;
> IF status IS NOT NULL THEN
> v_query := v_query || ' AND ESTATUS LIKE %||status||%' ;
>
> END IF;
> v_query := v_query || 'ORDER BY ID_TYP_ACTOR';
>
>OPEN outputCursor for v_query;
>
>
>EXCEPTION
>WHEN OTHERS THEN
> raise_application_error(-20001, 'An Error was Encountred - ' ||SQLCODE||' -ERROR-'||SQLERRM);
>END T01_PRUEBA2;
>END TESTPGK2;
There is NO need at all to use dynamic sql, as you have a static table name, and you can use bind variables.
Also your code contains an EXCEPTION WHEN OTHERS section, which must considered to be a bug.

Just to be sure: a dynamic query is *always* parsed. Parsing is a serialization mechanism as part of the shared pool will be locked during parsing. This means your code will be unscalable.

---

Sybrand Bakker
Senior Oracle DBA Received on Thu Jun 06 2013 - 18:10:38 CEST