CODEWITHSUNDEEP
sqloracle-databaseplsql-package
BOWIEKNIFE
BOWIEKNIFE

Reputation: 11

PL/SQL Oracle Package error when executing functions

The package compiles well, but when executing the functions, it gives the following error "'SEARCH FOR_EMP_NO' is not a procedure or has not been defined" what is the problem? I think I am committing some problem with the ver_emple procedure. I have to pass the record to it to display an output text as in the example and I don't know how to do it.

/* Cabecera o especificación del paquete */
CREATE OR REPLACE PACKAGE buscar_emple2
  AS
  TYPE t_reg_emple IS RECORD(emp_no emple.emp_no%TYPE, 
                              apellido emple.apellido%TYPE, 
                              oficio emple.oficio%TYPE, 
                              salario emple.salario%TYPE, 
                              dept_no emple.dept_no%TYPE
                              );
                              
  FUNCTION busca_por_emp_no(v_num_emple emple.emp_no%TYPE)
  RETURN t_reg_emple;

  FUNCTION busca_por_ape(v_apellido emple.apellido%TYPE)
  RETURN t_reg_emple;

  PROCEDURE ver_emple;
END buscar_emple2;


/* Cuerpo del paquete */
CREATE OR REPLACE PACKAGE BODY buscar_emple2
AS
vg_emple t_reg_emple;

FUNCTION busca_por_emp_no(v_num_emple emple.emp_no%TYPE)
RETURN t_reg_emple
  IS
  BEGIN
    SELECT emp_no, apellido, oficio, salario, dept_no
    INTO vg_emple
    FROM emple
    WHERE emp_no = v_num_emple;
    RETURN vg_emple;
    ver_emple;
END busca_por_emp_no;

FUNCTION busca_por_ape(v_apellido emple.apellido%TYPE)
RETURN t_reg_emple
  IS
  BEGIN
    SELECT emp_no, apellido, oficio, salario, dept_no
    INTO vg_emple
    FROM emple
    WHERE apellido = v_apellido;
    RETURN vg_emple;
    ver_emple;
END busca_por_ape;
  
  PROCEDURE ver_emple
    IS
    BEGIN
    DBMS_OUTPUT.PUT_LINE(vg_emple.emp_no|| '=======' ||vg_emple.apellido|| '=======' ||
                         vg_emple.oficio|| '=======' ||vg_emple.salario|| '=======' ||vg_emple.dept_no);
    END ver_emple;
  
END buscar_emple2;


EXECUTE buscar_emple2.busca_por_emp_no(7839);

EXECUTE buscar_emple2.busca_por_ape('SALA');

enter image description here

Upvotes: 0

Views: 433

Answers (1)

Andrew Sayer
Andrew Sayer

Reputation: 2336

The output you have shared is the desired output, not the actual one you got. Running error messages through a translator is fine but note that some of it won't make any sense if you translate names of thing that haven't been translated in your actual code.

Edit- Second attempt at guessing from your translated error. You have defined busca_por_emp_no as a FUNCTION not a procedure. In order to execute it you have to return it into something. You've defined it so that it returns a custom type. You would either change it's definition to be a procedure (with no return clause) or call it like you would a function:

declare
  myVar buscar_emple2.t_reg_emple;
begin
  myVar := buscar_emple2.busca_por_emp_no(7839);
end;
/

(This was my first guess as this was most striking about your code)

Your problem is you didn't actually send the PL/SQL packages to the databases to compile, you must end both the package specification and the package body definitions with a / e.g

CREATE OR REPLACE PACKAGE buscar_emple2
  AS
  TYPE t_reg_emple IS RECORD(emp_no emple.emp_no%TYPE, 
                              apellido emple.apellido%TYPE, 
                              oficio emple.oficio%TYPE, 
                              salario emple.salario%TYPE, 
                              dept_no emple.dept_no%TYPE
                              );
                              
  FUNCTION busca_por_emp_no(v_num_emple emple.emp_no%TYPE)
  RETURN t_reg_emple;

  FUNCTION busca_por_ape(v_apellido emple.apellido%TYPE)
  RETURN t_reg_emple;

  PROCEDURE ver_emple;
END buscar_emple2;
/


/* Cuerpo del paquete */
CREATE OR REPLACE PACKAGE BODY buscar_emple2
AS
vg_emple t_reg_emple;

FUNCTION busca_por_emp_no(v_num_emple emple.emp_no%TYPE)
RETURN t_reg_emple
  IS
  BEGIN
    SELECT emp_no, apellido, oficio, salario, dept_no
    INTO vg_emple
    FROM emple
    WHERE emp_no = v_num_emple;
    RETURN vg_emple;
    ver_emple;
END busca_por_emp_no;

FUNCTION busca_por_ape(v_apellido emple.apellido%TYPE)
RETURN t_reg_emple
  IS
  BEGIN
    SELECT emp_no, apellido, oficio, salario, dept_no
    INTO vg_emple
    FROM emple
    WHERE apellido = v_apellido;
    RETURN vg_emple;
    ver_emple;
END busca_por_ape;
  
  PROCEDURE ver_emple
    IS
    BEGIN
    DBMS_OUTPUT.PUT_LINE(vg_emple.emp_no|| '=======' ||vg_emple.apellido|| '=======' ||
                         vg_emple.oficio|| '=======' ||vg_emple.salario|| '=======' ||vg_emple.dept_no);
    END ver_emple;
  
END buscar_emple2;
/

You should receive a message if they were able to be compiled successfully, if it errored then check the compilation error and fix that.

show errors

Is the command to get errors if you receive a compilation error. I'm not going to create my own tables just to check if you don't have other typos in your code.

Upvotes: 1

Related Questions