CODEWITHSUNDEEP
oracle-databaseplsql
noobie
noobie

Reputation: 35

Encountered the symbol PLSQL

I'm trying to create a procedure that after finding all the necessary data prints on screen each one of them. This procedure searches for members, and it receives two parameters, P_CRITERIO if you are looking for a memeber by ID, Last name or national id, and P_SOCIO which has the id, last name or national id.

I tried deleting the Exception part, I didn't get the same error but I still got one that I can't fix

CREATE OR REPLACE PROCEDURE P_CONSULTAR_SOCIOS (P_CRITERIO IN VARCHAR2,P_SOCIO IN VARCHAR2)
IS  

    V_SENT_SOCIO SOC_SOCIO%rowtype;
    V_DEUDA NUMBER;--total dept
    V_SALDO NUMBER;--available money
    capital_pagado NUMBER;--how much he has paid
    capital_a_pagar NUMBER;--how much he has to pay
    V_APORTES NUMBER;--total contributions

BEGIN

    IF UPPER(P_CRITERIO) = 'ID' THEN    
        SELECT * INTO V_SENT_SOCIO FROM SOC_SOCIO WHERE ID_SOCIO=P_SOCIO;
    ELSE IF UPPER(P_CRITERIO) = 'CEDULA' THEN       
        SELECT * INTO V_SENT_SOCIO FROM SOC_SOCIO WHERE CEDULA=P_SOCIO; 
    ELSE IF UPPER(P_CRITERIO) = 'APELLIDO' THEN 
        SELECT * INTO V_SENT_SOCIO FROM SOC_SOCIO WHERE APELLIDO=P_SOCIO;
    END IF;

    --available money           
    SELECT NVL(SALDO_DISPONIBLE,0) INTO V_SALDO     
    FROM AHO_CUENTA_AHORRO      
    WHERE ID_SOCIO = V_SENT_SOCIO.ID_SOCIO; 

    --calculates if he has any loan active and the amount is stored in V_DEUDA      
    select sum(capital_pagado) into capital_pagado from cre_prestamos where id_sol_cred=V_SENT_SOCIO.ID_SOCIO;          
    select sum(capital_a_pagar) into capital_a_pagar from cre_prestamos where id_sol_cred=V_SENT_SOCIO.ID_SOCIO and UPPER(estado)='A';          
    V_DEUDA := (capital_pagado - capital_a_pagar);


    --sum of total contributions
    select sum(nvl(sdo.monto,0)) into V_APORTES from soc_detalle_obligaciones sdo
    join soc_obligaciones o on sdo.id_obligacion = o.id_obligacion
    where o.ID_SOCIO=V_SENT_SOCIO.ID_SOCIO and o.tipo_obligacion = 'A';

    --prints result
    DBMS_OUTPUT.PUT_LINE('| '||V_SENT_SOCIO.ID_SOCIO||' | '||V_SENT_SOCIO.CEDULA||
    ' | '||V_SENT_SOCIO.NOMBRE_APELLIDO||' | '||' | '||V_SALDO||' | '||V_DEUDA||' | '||
    V_APORTES||' |');   

    EXCEPTION           
        WHEN NO_DATA_FOUND THEN         
            DBMS_OUTPUT.PUT_LINE(-20032,'El socio no existe');      
        WHEN OTHERS THEN        
            DBMS_OUTPUT.PUT_LINE('Ha ocurrido un error');
END P_CONSULTAR_SOCIOS;
/

the errors I'm getting says

42/1     PLS-00103: Encountered the symbol "EXCEPTION" when expecting one
         of the following:
         ( begin case declare end exit for goto if loop mod null
         pragma raise return select update while with <an identifier>
         <a double-quoted delimited-identifier> <a bind variable> <<
         continue close current delete fetch lock insert open rollback
         savepoint set sql execute commit forall merge pipe purge

47/23    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

Upvotes: 2

Views: 158

Answers (2)

Javier Heisecke
Javier Heisecke

Reputation: 1232

There are somethings wrong with your code.

To fix the error with the if, you can replace that part of the code with this

IF UPPER(P_CRITERIO) = 'ID' THEN    
    SELECT * INTO V_SENT_SOCIO FROM SOC_SOCIO WHERE ID_SOCIO=P_SOCIO;
ELSIF UPPER(P_CRITERIO) = 'CEDULA' THEN       
    SELECT * INTO V_SENT_SOCIO FROM SOC_SOCIO WHERE CEDULA=P_SOCIO; 
ELSIF UPPER(P_CRITERIO) = 'APELLIDO' THEN 
    SELECT * INTO V_SENT_SOCIO FROM SOC_SOCIO WHERE APELLIDO=P_SOCIO;
END IF;

There is a mistake with your Exceptions.

PUT_LINE Can't take more than one parameter

EXCEPTION           
WHEN NO_DATA_FOUND THEN         
    DBMS_OUTPUT.PUT_LINE('El socio no existe');

That's what I can see so far

Upvotes: 4

APC
APC

Reputation: 146349

PLS-00103: Encountered the symbol exception always indicates a syntax error. So it is easy to solve by looking at the code above the given line number - 42 in this case - and comparing it with the syntax given in the Oracle PL/SQL documentation.

The problem in your case is that your code could be valid but it's not. You have written 

IF ... 
ELSE IF

Now that is valid syntax when the second IF is nested:

IF ...
ELSE IF ... END IF;
END IF;

In this scenario every standalone IF is matched with an END IF. But you are actually trying to implement a switch. So you have only one END IF. In this case you must use ELSIF instead:

IF ...
ELSIF ...
END IF;

There is another syntax error in the EXCEPTION block. DBMS_OUTPUT.PUT_LINE() takes one parameter, a string. To return a message with user-defined number use RAISE_APPLICATION_ERROR() function.

The WHEN OTHERS branch is just bad practice. There are literally thousands of Oracle error messages, many of which might trip up your procedure. To condense all those to one generic message is unhelpful to anybody trying to diagnose why the program failed. Initially that will be you, so help yourself as well as the people who will maintain it after you. Also it is also better to raise exceptions rather than use DBMS_OUTPUT: messages can be ignored or go un-noticed, exceptions must be handled. 

EXCEPTION           
    WHEN NO_DATA_FOUND THEN         
        raise_application_error(-20032,'El socio no existe');      
    WHEN OTHERS THEN        
        DBMS_OUTPUT.PUT_LINE('Ha ocurrido un error');
        raise;
END P_CONSULTAR_SOCIOS;

Upvotes: 2

Related Questions