Join between a collection and a table

Question

I have a problem with procedures in PL/SQL. I have a public procedure declared in a package and I want to call another procedure(private) inside the first one.

PROCEDURE show_notesforstudent (number_id IN number) as
    ...
    DBMS_OUTPUT.PUT_LINE('The notes for the student with number_id X are: '|| Y);

    DBMS_OUTPUT.PUT_LINE('here is the call for the private procedure');
    erase_student(number_id);
END;

This code is a general example, my code is bigger and I can't put it all here. Here is only the main idea. For this call I face this error: "Error(31,5): PLS-00313: 'erase_student' not declared in this scope".

The implementation of erase_student procedure is:

PROCEDURE erase_student(n_id students.number_id%type) AS
  student_inexistent EXCEPTION;
  PRAGMA EXCEPTION_INIT(student_inexistent, -20002);
  counter integer;
BEGIN
  SELECT COUNT(nmber_id) INTO counter FROM studens where number_id = n_id;
  IF counter = 0 THEN
      raise student_inexistent;
  END IF;
  DELETE FROM students WHERE number_id = n_id;
  EXCEPTION
  WHEN student_inexistent THEN
      raise_application_error (-20002, 'Student with number_id' || n_id ||        '     doesn't exists in database');
END stergere_student;`

Declare the erase_student procedure before the show_notesforstudent or use a forward declaration of the erase_student's header at the start of the procedure. — MT0
Perhaps erase_student doesn't exist because of the syntax errors in erase_students. It appears that the table name in the SELECT COUNT(*)... should be students rather than studens. Also, the name of the procedure (erase_student) doesn't match the name on the END stergere_student statement. Possibly an issue when translating (from Romanian?). Also, in the RAISE_APPLICATION_ERROR call, the apostrophe in doesn't should be doubled (doesn''t) so you get one apostrophe in the string literal. Best of luck. — Bob Jarvis - Reinstate Monica
I'm also confused about how the title and the question relate. In your title, you're talking about wanting to join to a collection. I don't see anything in the body of your question that references a collection. — Justin Cave
I made a mistake..I wanted to post another question and the title remains..sorry about that — Caroso

MT0 MT0 · Accepted Answer · 2016-03-29T23:57:14

Lets simplify your code and make it syntactically correct:

CREATE OR REPLACE PACKAGE package_name
AS
  PROCEDURE show_notesforstudent (number_id IN number);
END;
/

CREATE OR REPLACE PACKAGE BODY package_name
AS
  PROCEDURE show_notesforstudent (number_id IN number)
  AS
  BEGIN
      erase_student(number_id);
  END;

  PROCEDURE erase_student(n_id NUMBER) AS BEGIN NULL; END; 
END;
/

However, it still won't compile as we get PLS-00313: 'ERASE_STUDENT' not declared in this scope. It can be fixed by re-organising the package to ensure that the private procedure is declared before it is called; like this:

CREATE OR REPLACE PACKAGE BODY package_name
AS
  PROCEDURE erase_student(n_id NUMBER) AS BEGIN NULL; END; -- Header and body

  PROCEDURE show_notesforstudent (number_id IN number)
  AS
  BEGIN
      erase_student(number_id);
  END;
END;
/

However, if you want to keep the order of the procedures in the package then you could just use a forward declaration to declare the header for the private procedure before it is called and declare the body afterwards; like this:

CREATE OR REPLACE PACKAGE BODY package_name
AS
  PROCEDURE erase_student(n_id NUMBER); -- Header only

  PROCEDURE show_notesforstudent (number_id IN number)
  AS
  BEGIN
      erase_student(number_id);
  END;

  PROCEDURE erase_student(n_id NUMBER) AS BEGIN NULL; END; -- Header and body
END;
/

Join between a collection and a table

1 Answers