i have a issue in body package pl/sql when I select

Question

I currently have a procedure stored inside a package. I want to get all the data from the client table but I have an error. the problem is in procedure stored p_listar_cliente.

the code is:

create tables and insert

create table cliente (id NUMBER, name VARCHAR(50), address VARCHAR(50), salary NUMBER);

INSERT INTO cliente VALUES (1, 'camilo chaparro', 'popayan', 563321);
INSERT INTO cliente VALUES (2, 'ernesto lopez', 'ibague', 768954);
INSERT INTO cliente VALUES (3, 'camila españa', 'medellin', 113456);
INSERT INTO cliente VALUES (4, 'john jimenez', 'pasta', 789456);
INSERT INTO cliente VALUES (5, 'silvio jimenez', 'cali', 456123);

create package:

CREATE OR REPLACE PACKAGE paquete_cliente_listar IS
--add clientes
  PROCEDURE p_agregar_clientes (
                                    c_id IN cliente.id%TYPE,
                                    c_name IN cliente.name%TYPE,
                                    c_address IN cliente.address%TYPE,
                                    c_salary IN cliente.salary%TYPE
                                    );

--delete clientes
  PROCEDURE p_quitar_clientes (c_id IN cliente.id%TYPE);

--Lists all customers 
   PROCEDURE p_listar_cliente;

--listar todos los clientes
END paquete_cliente_listar;

body package:

CREATE OR REPLACE PACKAGE BODY paquete_cliente_listar AS 
--agregar clientes
  PROCEDURE p_agregar_clientes (
                                    c_id  cliente.id%TYPE,
                                    c_name  cliente.name%TYPE,
                                    c_address  cliente.address%TYPE,
                                    c_salary cliente.salary%TYPE
                                    ) IS
    BEGIN 

        INSERT INTO cliente (id, name, address, salary) VALUES (c_id, c_name, c_address, c_salary);

    END p_agregar_clientes;

--quitar clientes
  PROCEDURE p_quitar_clientes (c_id IN cliente.id%TYPE) IS

  BEGIN 

    DELETE FROM cliente WHERE id = c_id;


  END p_quitar_clientes;

--listar todos los clientes

 PROCEDURE p_listar_cliente AS
    BEGIN

    clientes_cursor cliente%rowtype;

     CURSOR clientes_cursor IS 
        SELECT id, name, address, salary FROM cliente;




    OPEN clientes_cursor;
    LOOP
      FETCH clientes_cursor INTO clientes_cursor;
      EXIT WHEN clientes_cursor%notfound;
      DBMS_OUTPUT.put_line(clientes_cursor.id || ' ' || clientes.name);
     END LOOP;

 END p_listar_cliente;


END paquete_cliente_listar;

I have this issue:

Errors: PACKAGE BODY PAQUETE_CLIENTE_LISTAR Line/Col: 30/21 PLS-00103: Encountered the symbol "CLIENTE" when expecting one of the following:

:= . ( @ % ; The symbol ":=" was substituted for "CLIENTE" to continue.

Line/Col: 32/13 PLS-00103: Encountered the symbol "CLIENTES_CURSOR" when expecting one of the following:

:= . ( @ % ;

PLS-00103 is a syntax error. Syntax errors are usually easy to resolve by comparing our code to [the examples in the PL/SQL Documentation](https://docs.oracle.com/database/121/LNPLS/subprograms.htm#GUID-855AA11F-32CB-4CAD-9255-BE92A6821487). — APC, Aug 16 '19 at 06:34

score 2 · Accepted Answer · answered Aug 15 '19 at 22:50

The first issue is that variables must be declared before the BEGIN. But instead of trying to figure out how the variables work, your code will be simpler and faster if you use a cursor for loop, like this:

...
 PROCEDURE p_listar_cliente AS

 BEGIN

    FOR clientes IN
    (
      SELECT id, name, address, salary FROM cliente
    ) LOOP
      DBMS_OUTPUT.put_line(clientes.id || ' ' || clientes.name);
    END LOOP;

 END p_listar_cliente;
...

Popeye · Answer 2 · 2019-08-16T07:14:44.430

There is an issue with the declaration of variables. It must be before BEGIN.

PROCEDURE p_listar_cliente AS
clientes_cursor cliente%rowtype; -- moved it before BEGIN
 CURSOR clientes_cursor IS 
    SELECT id, name, address, salary FROM cliente;   -- moved it before BEGIN 

BEGIN
OPEN clientes_cursor;
LOOP
  FETCH clientes_cursor INTO clientes_cursor;
  EXIT WHEN clientes_cursor%notfound;
  DBMS_OUTPUT.put_line(clientes_cursor.id || ' ' || clientes.name);
 END LOOP;

 END p_listar_cliente;

Closing the cursor after using it is also good practice.

Cheers!!

i have a issue in body package pl/sql when I select

2 Answers2