Error executing oracle procedure

1

I have this procedure:

CREATE OR REPLACE PROCEDURE buscaturma(cod in INTEGER) AS
  nomeT varchar2(20);
BEGIN

  dbms_output.put_line('inicio');

  IF (cod=0)then
    dbms_output.put_line('ZERO');
  ELSE
    SELECT DS_TURMA 
     into nomeT
    FROM TURMA
    WHERE CD_TURMA = cod;
  END IF;

EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('Não foi possível verificar');

END;

And I execute with this call:

EXECUTE  buscaturma(2);

Error message:

  

Error from line: 22 in command - EXECUTE buscaturma (2)   Error Reporting - ORA-06550: line 1, column 8: PLS-00905: object   DBAMV.BUSCATURMA is invalid ORA-06550: line 1, column 8: PL / SQL:   Statement ignored   06550. 00000 - "line% s, column% s: \ n% s"   * Cause: Usually a PL / SQL compilation error.   * Action:

    
asked by anonymous 23.01.2018 / 14:30

1 answer

1

I may be wrong but what you need is a function and not a procedure, see if the function below meets your need, if you want to return a value using procedure, you must declare a parameter of type OUT: / p>

CREATE OR REPLACE FUNCTION F_BUSCARTURMA(P_CODIGO INTEGER) RETURN VARCHAR2 IS
  RESULT TURMA.DS_TURMA%TYPE;
BEGIN

BEGIN
SELECT DS_TURMA 
     INTO RESULT
    FROM TURMA
    WHERE CD_TURMA = P_CODIGO;
    
EXCEPTION
  WHEN NO_DATA_FOUND THEN
     DBMS_OUTPUT.PUT_LINE('NÃO FOI POSSÍVEL ENCONTRAR A TURMA');
  WHEN OTHERS THEN 
     DBMS_OUTPUT.PUT_LINE('OCORREU ALGUM ERRO AO TENTAR BUSCAR A TURMA');
END;

  RETURN(RESULT);
END F_BUSCARTURMA;

Below is an example of using the function:

select F_BUSCARTURMA (1) from DUAL

    
09.10.2018 / 05:14