I am creating a package and I don't understand why the error occurs. My package:

create or replace PACKAGE test_action AS
    subj CHAR default '';
    count_student INTEGER default 0;
    FUNCTION count_positive(subject CHAR) RETURN INTEGER;
    PROCEDURE number_requests;
END test_action;

Package body:

CREATE OR REPLACE PACKAGE BODY test_action AS
    FUNCTION count_positive(sub CHAR) RETURN INTEGER
    AS
    BEGIN 
        count_student := 0;
        subj := sub;
        SELECT COUNT(*) INTO count_student 
        FROM D8_EXAMS E JOIN D8_SUBJECT S 
        ON E.subj_id = S.subj_id
        WHERE E.mark > 3 AND S.subj_name = subject
        GROUP BY S.subj_name;
        number_requests;          
        return count_student;
    END count_positive; 
    PROCEDURE number_requests AS
    BEGIN
        INSERT INTO package_table (subject,counts,callCount)
                VALUES (subj,count_student,1);
        exception
            when dup_val_on_index then
                update t 
                set    callCount := callCount   1,
                set     counts := count_student
                where  subject = subj;
    END number_requests;   
END test_action;

and then I get an error

if I try to add assignment to variables before the function description, then a new error occurs

CodePudding user response：

Because - as error says - you're missing equal sign (it is just =, not :=), and there's only one set keyword per update:

update t 
   set    callCount = callCount   1,
          counts    = count_student
   where  subject   = subj;

Apart from that:

• function declaration in package body must match the one in package specification
• in exception, you're updating table t; shouldn't that be package_table?
• select count(*) has group by clause; to me, it smells like possible point to raise too_many_rows error you didn't handle

This, at least, compiles: package spec:

SQL> CREATE OR REPLACE PACKAGE test_action
  2  AS
  3     subj           CHAR DEFAULT '';
  4     count_student  INTEGER DEFAULT 0;
  5
  6     FUNCTION count_positive (sub CHAR)
  7        RETURN INTEGER;
  8
  9     PROCEDURE number_requests;
 10  END test_action;
 11  /

Package created.

Package body:

SQL> CREATE OR REPLACE PACKAGE BODY test_action
  2  AS
  3     FUNCTION count_positive (sub CHAR)
  4        RETURN INTEGER
  5     AS
  6     BEGIN
  7        count_student := 0;
  8        subj := sub;
  9
 10          SELECT COUNT (*)
 11            INTO count_student
 12            FROM D8_EXAMS E JOIN D8_SUBJECT S ON E.subj_id = S.subj_id
 13           WHERE     E.mark > 3
 14                 AND S.subj_name = sub
 15        GROUP BY S.subj_name;
 16
 17        number_requests;
 18        RETURN count_student;
 19     END count_positive;
 20
 21     PROCEDURE number_requests
 22     AS
 23     BEGIN
 24        INSERT INTO package_table (subject, counts, callCount)
 25             VALUES (subj, count_student, 1);
 26     EXCEPTION
 27        WHEN DUP_VAL_ON_INDEX
 28        THEN
 29           UPDATE package_table
 30              SET callCount = callCount   1, counts = count_student
 31            WHERE subject = subj;
 32     END number_requests;
 33  END test_action;
 34  /

Package body created.

SQL>