Dynamic query

From: Javier Montero <jmontero19_at_gmail.com>
Date: Mon, 3 Jun 2013 18:42:50 -0700 (PDT)
Message-ID: <8650c747-6f35-430e-ab01-5d1108ec9dde_at_googlegroups.com>



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; Received on Tue Jun 04 2013 - 03:42:50 CEST

Original text of this message