CODEWITHSUNDEEP
sqloracle-databaseselectplsqlprocedure
cildoz
cildoz

Reputation: 436

Select in procedure Oracle

I've got this procedure I've defined in Oracle:

CREATE OR REPLACE Procedure InsertCliente
(dni IN VARCHAR, nombre IN VARCHAR, apellidos IN VARCHAR, apellido2 IN VARCHAR, direccion IN VARCHAR, 
    CALLE IN VARCHAR, NUMERO IN NUMBER, PISO IN VARCHAR, CIUDAD IN VARCHAR, CODPOSTAL IN NUMBER, 
    telefono IN NUMBER, edad IN DATE, email IN VARCHAR)
IS
    claveDireccion number;

BEGIN
    IF apellido2 is null THEN
        INSERT INTO Cliente(DNI,Telefono,Direccion,Email,Edad,Apellidos,Nombre) 
        VALUES (dni,telefono,direccion,email,TO_DATE(edad,'YYYY-MM-DD'),apellidos,nombre);
ELSE
    claveDireccion := (SELECT ID_DIRECCION FROM DIRECCION@SCHEMA2BD2 WHERE ID_DIRECCION=Direccion);
    IF claveDireccion is not null THEN
        INSERT INTO TITULAR@SCHEMA2BD2(DNI,Nombre,Apellido1, Apellido2, Direccion,Telefono,Fecha_Nacimiento) 
            VALUES (DNI,Nombre,Apellidos, Apellido2, Direccion,Telefono,TO_DATE(Fecha_Nacimiento,'YYYY-MM-DD'));
    ELSE
        raise_application_error(-20001, 'La direccion proporcionada no existe');
    END IF;
END IF;

END;
/

But I'm getting the following error from Oracle:

LINE/COL ERROR
-------- -----------------------------------------------------------------
13/22    PLS-00103: Encountered the symbol "SELECT" when expecting one of
     the following:
     ( - + case mod not null others <an identifier>
     <a double-quoted delimited-identifier> <a bind variable> avg
     count current exists max min prior sql stddev sum variance
     execute forall merge time timestamp interval date
     <a string literal with character set specification>
     <a number> <a single-quoted SQL string> pipe

13/96    PLS-00103: Encountered the symbol ")" when expecting one of the
     following:

LINE/COL ERROR
-------- -----------------------------------------------------------------
     . ( * @ % & - + ; / at for mod rem <an exponent (**)> and or
     group having intersect minus order start union where connect
     ||

20/6     PLS-00103: Encountered the symbol "IF" when expecting one of the
     following:
     ; <an identifier> <a double-quoted delimited-identifier>
     delete exists prior <a single-quoted SQL string>

I don't know what's happening there. It's just a select sentence which is being assigned to a variable. Any help?

Upvotes: 1

Views: 143

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Use INTO:

SELECT ID_DIRECCION INTO claveDireccion
FROM DIRECCION@SCHEMA2BD2
WHERE ID_DIRECCION = Direccion;

The logic doesn't make sense. Why not just use this?

claveDireccion := Direccion;

I would suggest that you name your parameters so they are distinguished from column names:

CREATE OR REPLACE Procedure InsertCliente (
    in_dni IN VARCHAR,
    in_nombre IN VARCHAR,
    in_apellidos IN VARCHAR,
    in_apellido2 IN VARCHAR,
    in_direccion IN VARCHAR, 
    in_CALLE IN VARCHAR,
    in_NUMERO IN NUMBER,
    in_PISO IN VARCHAR,
    in_CIUDAD IN VARCHAR,
    in_CODPOSTAL IN NUMBER, 
    in_telefono IN NUMBER,
    in_edad IN DATE,
    in_email IN VARCHAR
) IS
    p_claveDireccion number;
BEGIN
    . . .
END;

This helps to prevent problems in the body of the stored procedure.

Upvotes: 2

Related Questions