Creating package body

Question

I created a package and there everything is ok. I think the package is well made.

SQL> CREATE OR REPLACE PACKAGE utl_zadaci AS
  2  PROCEDURE insertZadatak (
  3     p_naziv varchar2,
  4     p_kategorija varchar2,
  5     p_opis varchar2,
  6     p_komanda varchar2
  7  );
  8  
  9  PROCEDURE deleteZadatak (
 10         p_id number
 11    );
 12  
 13   PROCEDURE displayZadatak (
 14         p_id number,
 15     p_naziv varchar2,
 16         p_kategorija varchar2,
 17     p_opis varchar2,
 18     p_komanda varchar2
 19    );
 20  
 21  PROCEDURE modifyZadatak (
 22     p_id number,
 23     p_naziv varchar2,
 24     p_kategorija varchar2,
 25     p_opis varchar2,
 26     p_komanda varchar2
 27   );
 28  END utl_zadaci;
 29  /

 Package created.

The problem arises when I create the body of the package.

There is an error that I can not find here

In this I'm a beginner, I create a package for the first time. I need to write more for update and select.

If you know, I would be very grateful to help you here too: D

SQL> CREATE OR REPLACE PACKAGE BODY utl_zadaci AS
 2  PROCEDURE insertZadatak(
 3  p_naziv VARCHAR2,
 4  p_kategorija VARCHAR2,
 5  p_opis VARCHAR2,
 6  p_komanda VARCHAR2)
 7  IS
 8  BEGIN
 9  INSERT INTO zadaci(id, naziv, kategorija, opis, komanda)
 10  VALUES(SEQ_ZADACI_ID.NEXTVAL, p_naziv, p_kategorija, p_opis, p_komanda);
 11  COMMIT;
 12  EXCEPTION
 13   WHEN OTHERS THEN
 14      ROLLBACK;
 15      RAISE;
 16  END;
 17  
 18  PROCEDURE deleteZadatak(
 19  p_id NUMBER)
 20  IS
 21  BEGIN
 22  DELETE FROM zadaci
 23  WHERE id = p_id;
 24  COMMIT;
 25  EXCEPTION
 26   WHEN OTHERS THEN
 27      ROLLBACK;
 28      RAISE;
 29  END;
 30  END utl_zadaci;
 31  /

Warning: Package Body created with compilation errors.

Answer 1

1. create the package without procedures
2. create the package body
3. put the procedures on the package and then in the package body.

Answer 2

There is only one small error here in the select procedure

 SQL> CREATE OR REPLACE PACKAGE BODY utl_zadaci AS 

  2  PROCEDURE insertZadatak(
  3     p_naziv VARCHAR2,
  4     p_kategorija VARCHAR2,
  5     p_opis VARCHAR2,
  6     p_komanda VARCHAR2)
  7  IS
  8  BEGIN
  9     INSERT INTO zadaci(id, naziv, kategorija, opis, komanda)
 10     VALUES(SEQ_ZADACI_ID.NEXTVAL, p_naziv, p_kategorija, p_opis, p_komanda);
 11  COMMIT;
 12  EXCEPTION
 13    WHEN OTHERS THEN
 14       ROLLBACK;
 15       RAISE;
 16  END;
 17  PROCEDURE deleteZadatak(
 18    p_id NUMBER)
 19  IS
 20  BEGIN
 21     DELETE FROM zadaci
 22     WHERE id = p_id;
 23   COMMIT;
 24  EXCEPTION
 25     WHEN OTHERS THEN
 26       ROLLBACK;
 27      RAISE;
 28  END;
 29  PROCEDURE modifyZadatak(
 30     p_id NUMBER,
 31     p_naziv VARCHAR2,
 32     p_kategorija VARCHAR2,
 33     p_opis VARCHAR2,
 34     p_komanda VARCHAR2)
 35  IS
 36  BEGIN
 37     UPDATE zadaci SET naziv = p_naziv, kategorija = p_kategorija, opis = p_opis,
 38     komanda = p_komanda WHERE id = p_id;
 39   COMMIT;
 40    EXCEPTION
 41    WHEN OTHERS THEN
 42       ROLLBACK;
 43       RAISE;
 44   END;
 45   PROCEDURE displayZadatak(p_id NUMBER,
 46   p_naziv VARCHAR2,
 47   p_kategorija VARCHAR2,
 48   p_opis VARCHAR2,
 49   p_komanda VARCHAR2)
 50   IS
 51   BEGIN
 52   SELECT naziv, kategorija, opis, komanda
 53  INTO p_naziv, p_kategorija, p_opis, p_komanda
 54  FROM zadaci WHERE id = p_id;
 55  COMMIT;
 56  EXCEPTION
 57  WHEN OTHERS THEN
 58     ROLLBACK;
 59     RAISE;
 60  END;
 61  END utl_zadaci;
 62  /

Answer 3

Without knowing the exact error here is what I can infer from the code you posted. You have 4 procedures in your spec but only two in the body. You need to define the displayZadatak and modifyZadatak procedures.

EDIT: After the original question was edited here are the issues seen.

The insertZadatak procedure takes 4 arguments but in the body only 3 are present.

In the displayZadatak procedure, Select needs to be clubbed with an INTO in PLSQL to store the selected values. It can be a single variable, rowtype or collection.

Answer 4

I need to write more for update and select.

This is the issue. The package expects all the procedures in the specification to also be in the body; they aren't so there is an exception.

To list the exceptions you can run:

SHOW ERRORS;

or you can use:

SELECT * FROM USER_ERRORS;

So for your code:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE zadaci(
  id VARCHAR2(20),
  naziv VARCHAR2(20),
  kategorija VARCHAR2(20),
  opis VARCHAR2(20),
  komanda VARCHAR2(20)
)
/
CREATE SEQUENCE SEQ_ZADACI_ID
/

/* Your code */

Query 1:

SELECT * FROM USER_ERRORS

Results:

|       NAME |         TYPE | SEQUENCE | LINE | POSITION |                                                                                                                            TEXT | ATTRIBUTE | MESSAGE_NUMBER |
|------------|--------------|----------|------|----------|---------------------------------------------------------------------------------------------------------------------------------|-----------|----------------|
| UTL_ZADACI | PACKAGE BODY |        1 |   11 |       13 | PLS-00323: subprogram or cursor 'DISPLAYZADATAK' is declared in a package specification and must be defined in the package body |     ERROR |            323 |
| UTL_ZADACI | PACKAGE BODY |        2 |   18 |       13 |  PLS-00323: subprogram or cursor 'MODIFYZADATAK' is declared in a package specification and must be defined in the package body |     ERROR |            323 |

As an aside - do not use COMMIT in procedures. If you do then you cannot use multiple procedures in a transaction and rollback the entire transaction if later procedures raise an error as the earlier procedures will already have been committed. Instead issue the COMMIT statement in the block that calls the procedure(s).