CODEWITHSUNDEEP
oracle-databaseoracle-apex
Миша Попов
Миша Попов

Reputation: 137

why does the function call produce no results

I created a package with a function and a procedure.

CREATE OR REPLACE PACKAGE BODY test_action AS
    FUNCTION count_positive (sub NVARCHAR2)
        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 = sub
        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 package_table 
                set    callCount = callCount + 1,
                     counts = count_student
                where  subject = subj;
    COMMIt;
    END number_requests;   
END test_action;

then, I call the function and get the result like this: SELECT test_action.count_positive('ИНФОРМАТИКА') FROM DUAL; [![enter image description here][1]][1] If you try to do the same operation just on a query, then the result is [![enter image description here][2]][2]

The query I used:

SELECT COUNT(*) 
                FROM D8_EXAMS E JOIN D8_SUBJECT S ON E.subj_id = S.subj_id
            WHERE E.mark > 3 
                AND S.subj_name = 'ИНФОРМАТИКА'
        GROUP BY S.subj_name;

What's wrong? [1]: https://i.sstatic.net/tSPpr.png? [2]: https://i.sstatic.net/GuMCT.png?

Upvotes: 0

Views: 101

Answers (1)

Roberto Hernandez
Roberto Hernandez

Reputation: 8518

Try to run the function in sqlplus. If the query gives you results, the function will do too.

A function is an object that returns a result. In your case it returns a number, so you can use it in PLSQL or in SQL:

PLSQL

set serveroutput on  
declare
x pls_integer; 
begin
x := test_action.count_positive(sub => 'ИНФОРМАТИКА');
dbms_output.put_line(x);
exception 
when others then raise;
end;
/

SQL

select test_action.count_positive(sub => 'ИНФОРМАТИКА') from dual ;

Upvotes: 1

Related Questions