Member Avatar for karunakar reddy

create or replace

PACKAGE BODY employee_details AS

      PROCEDURE INSERT_ROW
( 
    P_EMPNO                IN NUMBER ,
    P_ENAME              IN VARCHAR2 ,
    P_MGR                IN VARCHAR2 ,
    P_SAL                  IN  NUMBER,
    P_DEPTNO                IN NUMBER,
    P_COMM                  IN NUMBER,
    P_HIREDATE           IN  VARCHAR2,
    P_JOB               IN   VARCHAR2,
    P_error_message      OUT VARCHAR2 
  )
  is
  l_EMPNO  NUMBER;
  l_object_name VARCHAR2(60) := 'employee_details.INSERT_ROW';
BEGIN
  te_trace('Entered into Insert employee_details',FALSE,NULL,l_object_name,10);
   SELECT empno.NEXTVAL INTO l_empno FROM DUAL;
  INSERT INTO xxeis.employee
    (
      NO,
      NAME,
      MGR,
      SAL,
      DEPTNO,
      COMM,
      HIREDATE,
      JOB
      )
    VALUES
    (
      l_EMPNO,
      P_ENAME,
      P_HIREDATE,
      P_SAL,
      P_MGR,
      P_COMM,
      P_DEPTNO,
      P_JOB
    );
  te_trace('Insertion of Data into employee Table Successfully ',FALSE,NULL,l_object_name,20);
 EXCEPTION
WHEN OTHERS THEN
    p_error_message :=SUBSTR(SQLERRM, 1, 200);
  te_trace('Failed to Insert Data into employee Table'||p_error_message ,FALSE,NULL,l_object_name,30);
  raise_application_error(-20001, p_error_message);
  END  INSERT_ROW ;
  
END  employee_details ;

Error(4,11): PLS-00323: subprogram or cursor 'INSERT_ROW' is declared in a package specification and must be defined in the package body

Edited by Narue because: added code tags & merged with next post
  • 3 Contributors
  • 2 Replies
  • 666 Views
  • 18 Hours Discussion Span
  • Latest Post Latest Post by Member #647493

Dani AI

Generated

Short answer: the compiler is telling you a public subprogram declared in the package specification is missing or does not match the implementation in the package body. That is exactly what is seeing. As suggested, make sure the package specification is present and compiled first; as pointed out, the signatures must match.

Checklist to resolve it:

  • Verify there actually is a package specification that declares the procedure. Inspect the source for both spec and body (example queries below).
  • Confirm the procedure in the body matches the spec in number of parameters, parameter modes (IN/OUT/IN OUT) and datatypes. Parameter names may differ, but modes and types must be identical.
  • Check for overloads: if the spec declares multiple signatures, the body must implement each one or the matching signature.
  • Ensure you are compiling the specification (CREATE OR REPLACE PACKAGE ...) before compiling the body and that you are in the correct schema (or referencing schema-qualified objects).
  • Look for duplicate objects or synonyms in other schemas that might confuse compilation.

Quick commands to inspect/diagnose:

-- view package spec source
SELECT text FROM all_source
 WHERE name = 'EMPLOYEE_DETAILS' AND type = 'PACKAGE'
 ORDER BY line;

-- view package body source
SELECT text FROM all_source
 WHERE name = 'EMPLOYEE_DETAILS' AND type = 'PACKAGE BODY'
 ORDER BY line;

-- see compile errors (SQL*Plus)
SHOW ERRORS PACKAGE employee_details;

Minimal example pattern (keeps spec and body signatures identical):

CREATE OR REPLACE PACKAGE employee_details IS
  PROCEDURE insert_row(p_name IN VARCHAR2, p_sal IN NUMBER, p_err OUT VARCHAR2);
END employee_details;

CREATE OR REPLACE PACKAGE BODY employee_details IS
  PROCEDURE insert_row(p_name IN VARCHAR2, p_sal IN NUMBER, p_err OUT VARCHAR2) IS
  BEGIN
    NULL;  -- implementation here
  EXCEPTION
    WHEN OTHERS THEN p_err := SUBSTR(SQLERRM,1,200);
  END insert_row;
END employee_details;

If after these checks the error persists, recompile the spec and then the body, and confirm the object shown by the data-dictionary queries is the one you are editing (same OWNER).

Recommended Answers

All 2 Replies

Member Avatar for debasisdas

Have you compiled the package specifications before trying to compile the package body ?

Member Avatar for Member #647493

IF you do have a package spec...
Do the Parameter Names and Data Types in the package body match the package spec?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.