Error in PL / SQL with a procedure with cursors

Question

Error in PL / SQL with a procedure with cursors

Navigation

#1 by (1 votes)

1

I try to solve the following question:

Ask the User for nationality, and show the clients with that nationality, for this I used an anonymous block and a procedure that is as follows:

Procedure:

CREATE OR REPLACE PROCEDURE mostrarNacionalidad(nacionalidad IN String2(30)) AS
CURSOS registros is SELECT Nacionalidad FROM clientes WHERE Nacionalidad = nacionalidad;

BEGIN

FOR registro IN registros LOOP
dbms_output.put_line('* * * * * * * * * * * * * * * * * * ');
dbms_output.put_line('Nombre Cliente: ' || registro.NOMBRECLIENTE);
dbms_output.put_line('Nacionalidad: ' || registro.Nacionalidad );
dbms_output.put_line('* * * * * * * * * * * * * * * * * * ');
END LOOP;

END;

This block gives me the following error:

Error at line 13: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   end not pragma final instantiable order overriding static
   member constructor map

1. CREATE OR REPLACE PROCEDURE mostrarNacionalidad(nacionalidad IN String2(30)) AS
2. CURSOS registros is SELECT Nacionalidad FROM clientes WHERE Nacionalidad = nacionalidad;
3. BEGIN

I would appreciate if someone could lend me a hand to reshoot it. Thank you very much.

oracle oracle-sqldeveloper plsql

asked by Javier Avila Fernandez 30.05.2018 в 20:50

source

1 answer

Warning "Undefined variable: row in ..." Can you relate 2 tables of different databases by means of a foreign key?

score 1 · Accepted Answer

I do not know if my version of the oracle is different but I tried your code and another error occurred.

PLS-00103: The symbol "(" was found when you expected one of the following:: =. ), @% default character The symbol ":=" has been replaced by "(" to continue.

Which I solved by removing parentheses from the variable that is in the procedure statement.

String2 (30)

Then another error came out which did not recognize me that kind of data

1/47 PLS-00201: identifier 'STRING2' must be declared 0/0
PL / SQL: Compilation unit analysis terminated

I changed the data type and tried it again and it works!

The code was as follows:

CREATE OR REPLACE PROCEDURE mostrarNacionalidad(nacionalidad IN VARCHAR2) AS
CURSOR registros is SELECT NOMBRECLIENTE, Nacionalidad FROM clientes WHERE Nacionalidad = nacionalidad;

BEGIN

FOR registro IN registros LOOP
dbms_output.put_line('* * * * * * * * * * * * * * * * * * ');
dbms_output.put_line('Nombre Cliente: ' || registro.NOMBRECLIENTE);
dbms_output.put_line('Nacionalidad: ' || registro.Nacionalidad );
dbms_output.put_line('* * * * * * * * * * * * * * * * * * ');
END LOOP;

END;

/

The results:

declare 
  -- Local variables here
  i integer;
begin
  -- Test statements here
  mostrarNacionalidad('PY');
end;

/

* * * * * * * * * * * * * * * * * * 
Nombre Cliente: PEPITO
Nacionalidad: PY
* * * * * * * * * * * * * * * * * * 
* * * * * * * * * * * * * * * * * * 
Nombre Cliente: ARIELITO
Nacionalidad: PY
* * * * * * * * * * * * * * * * * *