Reputation: 35
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
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
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