Path: news.cambrium.nl!textnews.cambrium.nl!feeder2.cambriumusenet.nl!feed.tweaknews.nl!209.197.12.242.MISMATCH!nx01.iad01.newshosting.com!newshosting.com!news.glorb.com!news-in-01.newsfeed.easynews.com!easynews!core-easynews-01!easynews.com!en-nntp-09.am2.easynews.com.POSTED!not-for-mail From: Sybrand Bakker Newsgroups: comp.databases.oracle.server Subject: Re: Dynamic query Message-ID: <9uc1r89sei2kjfievqqoc8knikrucn7p16@4ax.com> References: <8650c747-6f35-430e-ab01-5d1108ec9dde@googlegroups.com> X-Newsreader: Forte Agent 6.00/32.1186 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Lines: 57 X-Complaints-To: abuse@easynews.com Organization: Forte Inc. http://www.forteinc.com/apn/ X-Complaints-Info: Please be sure to forward a copy of ALL headers otherwise we will be unable to process your complaint properly. Date: Thu, 06 Jun 2013 18:10:38 +0200 X-Received-Bytes: 2971 Xref: news.cambrium.nl On Mon, 3 Jun 2013 18:42:50 -0700 (PDT), Javier Montero 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