Re: Dynamic query
From: <nasimhaiderpk_at_gmail.com>
Date: Sun, 28 Jul 2013 13:14:53 -0700 (PDT)
Message-ID: <fa684004-3339-4905-bc00-440e35b6c8ab_at_googlegroups.com>
On Tuesday, 4 June 2013 06:42:50 UTC+5, 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;
Dear
Date: Sun, 28 Jul 2013 13:14:53 -0700 (PDT)
Message-ID: <fa684004-3339-4905-bc00-440e35b6c8ab_at_googlegroups.com>
On Tuesday, 4 June 2013 06:42:50 UTC+5, 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;
Dear
I am also agree with Sybrand Bakker, but you still want to continue with this code , I feel u miss the apostrophe before and after your variables.
sql statement looks like
like ||'''||<variable>||'''||....
Received on Sun Jul 28 2013 - 22:14:53 CEST