Home » RDBMS Server » Server Administration » Re: PL/SQL Dinamic problem
Re: PL/SQL Dinamic problem [message #370526] Wed, 15 December 1999 13:58 Go to next message
hmg
Messages: 40
Registered: March 1999
Member
Hi,

I tested your procedure "proceso" (without any htp.. calls) and I found three mistakes:

1. If p_appelido is null and p_nombre is null your variable "v_select" contains a "where" but nothing after it
=> ORA-00921: unexpected end of SQL command

2. IF p_appelido is not null and p_nombre is null your variable "v_select" contains only the bind variable :ap. But you bind later all two variables
with
   DBMS_SQL.BIND_VARIABLE(v_cursorID, ':ap', p_apellido);
   DBMS_SQL.BIND_VARIABLE(v_cursorID, ':no', p_nombre);
=> ORA-01006: bind variable does not exist

3. The same as 2. with p_appelido is null and p_nombre not null

One solution is that you write an if statement before the statements:
   DBMS_SQL.BIND_VARIABLE(v_cursorID, ':ap', p_apellido);
and
   DBMS_SQL.BIND_VARIABLE(v_cursorID, ':ap', p_apellido);

or you don't use dynamic sql and write it
like this with a cursor loop:

PROCEDURE Proceso(p_apellido IN VARCHAR2 DEFAULT NULL, p_nombre IN VARCHAR2 DEFAULT NULL)
IS 
   cursor cur_indice(p1 varchar2, p2 varchar2) is
      select * from indice
         where nvl(inapel,'XXX') = nvl( p1, nvl(inapel,'XXX') )
         and   nvl(innomb,'XXX') = nvl( p2, nvl(innomb,'XXX') );
BEGIN

   -- htp.tableOpen;
   FOR rec IN cur_indice(p_apellido, p_nombre) LOOP
   -- htp.tableRowOpen;
   -- htp.tableData(..,..,cvalue=>rec.inapel);  
   -- htp.tableData(..,..,cvalue=>rec.innomb);  
   -- htp.tableRowClose;
   END LOOP;
   --htp.tableClose;

END Proceso;

Bye

Report message to a moderator

Re: PL/SQL Dinamic problem [message #370527 is a reply to message #370526] Wed, 15 December 1999 14:23 Go to previous message
MAG
Messages: 3
Registered: December 1999
Junior Member
Good analysis HMG ! I found the solution without "DBMS_SQL.BIND_VARIABLE" and the consult works.

Otherwise, if there isn't "p_apellido" and "p_nombre" it that isn't work. I have to create a "FUNCTION" OR "SCRIPT" in order to validation. You are right.

thank you

It's look like this.

--------------------
CREATE OR REPLACE PACKAGE BODY ANIBAL AS

c_OWAPath CONSTANT VARCHAR2(50) := '/ows-bin/owa/';

FUNCTION GetApellido (ap in varchar2) return indice.inapel%type is
apel indice.inapel%type;
BEGIN
SELECT DISTINCT inapel INTO apel
FROM indice
WHERE ltrim(rtrim(upper(inapel))) = ltrim(rtrim(upper(ap)));
RETURN apel;
END;

PROCEDURE ShowForm IS

BEGIN

HTP.CENTEROPEN;
HTP.LINE;
HTP.FORMOPEN(curl => c_OWAPath || 'ANIBAL.Proceso');
HTP.BR;
HTP.PARA;
HTP.P(cbuf => 'Apellido: ');
HTP.FORMTEXT(CNAME => 'p_apellido');
HTP.PARA;
HTP.P(cbuf => 'Nombre: ');
HTP.FORMTEXT(CNAME => 'p_nombre');
HTP.PARA;

HTP.FORMSUBMIT(Cvalue => 'Consultar');
HTP.CENTERCLOSE;
HTP.LINE;
HTP.HTMLCLOSE;
END ShowForm;


PROCEDURE Proceso(p_apellido IN VARCHAR2 DEFAULT NULL,
p_nombre IN VARCHAR2 DEFAULT NULL) IS

v_cursorID integer;
apellido indice.inapel%type DEFAULT NULL;
v_resultado integer;
v_apellido varchar2(20);
v_nombre varchar2(20);
v_select VARCHAR2(150) := 'SELECT inapel, innomb FROM indice WHERE ';

/*,inpiso,ininte,insoci,inedif,insect*/
BEGIN

v_cursorID := DBMS_SQL.OPEN_CURSOR;

IF p_apellido IS NOT NULL THEN
/* apellido := GetApellido (p_apellido); */
v_select := v_select||'inapel = '||''''|| RTRIM(p_apellido) ||''' AND ';
END IF;

IF p_nombre IS NOT NULL THEN
/* nombre := GetNombre (p_nombre); */
v_select := v_select||'innomb = '||''''|| RTRIM(p_nombre)|| ''' AND ';
END IF;

v_select := LTRIM(rtrim(v_select,'AND '))||'ORDER BY inapel';

DBMS_SQL.PARSE(v_cursorID, v_select , DBMS_SQL.V7);

DBMS_SQL.DEFINE_COLUMN(v_cursorID, 1 , v_apellido, 20);
DBMS_SQL.DEFINE_COLUMN(v_cursorID, 2 , v_nombre, 20);

v_resultado := DBMS_SQL.EXECUTE(v_cursorID);

htp.tableOpen(cborder => 'BORDER=1');
htp.tableRowOpen;
htp.tableheader(cvalue => 'Apellido',calign => 'center',crowspan => 1);
htp.tableheader(cvalue => 'Nombre',calign => 'center',ccolspan => 2);
htp.tablerowclose;

LOOP

IF DBMS_SQL.FETCH_ROWS(v_cursorID) = 0 THEN
EXIT;
END IF;

DBMS_SQL.COLUMN_VALUE(v_cursorID, 1 , v_apellido);
DBMS_SQL.COLUMN_VALUE(v_cursorID, 2 , v_nombre);

htp.tableRowOpen;
HTP.tableData(crowspan => 1,
calign => 'CENTER',
cvalue => v_apellido);
HTP.tableData(ccolspan => 2,
calign => 'CENTER',
cvalue => v_nombre);
htp.tableRowClose;
END LOOP;

htp.tableClose;
DBMS_SQL.CLOSE_CURSOR(v_cursorID);


END Proceso;


PROCEDURE Go IS
BEGIN
HTP.htmlOpen;
HTP.headOpen;
HTP.title('Consulta de Teléfonos e Internos');
HTP.headClose;
HTP.CENTEROPEN;
HTP.header(2, 'Consulta de Teléfonos e Internos');
HTP.CENTERCLOSE;
HTP.bodyOpen;
ShowForm;
HTP.bodyClose;
HTP.htmlClose;
END Go;

END ANIBAL;

Report message to a moderator

Previous Topic: Is the incomin field contains date
Next Topic: Re: Procedure Source
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 19 16:35:23 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.