Error running Procedure on oracle

Asked

Viewed 1,277 times

1

I got this trial:

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 run with that call:

EXECUTE  buscaturma(2);

Error message:

Error from line : 22 in command - EXECUTE lookup(2) Bug report - 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:

java query oracle pl-sql procedure

  • I think there’s a few extra semicolons in there.

  • On which line ?

  • Can fix the error. but only get the message: anonymized block completed.

1 answer

1


I may be wrong but what you need is a function and not a precedent, see if the function below meets your need, if you still want to return a value using Precedent, you will have to declare a parameter of type OUT:

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 example of using the function:

select F_BUSCARTURMA(1) from dual

Browser other questions tagged java query oracle pl-sql procedure

You are not signed in. Login or sign up in order to post.