How can I correct errors in calling this function?
I'm using Oracle 8i sqlplus and
I've used pacakage to define the function. This part complied without errors: (Notice that it is not allowed to use cursors for this program)
CREATE OR REPLACE PACKAGE stats AS
TYPE stats_rec IS RECORD
(F_NAME student.FIRST_NAME%TYPE,
L_NAME student.LAST_NAME%TYPE,
NO_BOOKS number);
FUNCTION compute_statistics (ssn_in IN student.ssn%type)
RETURN stats_rec;
PRAGMA RESTRICT_REFERENCES(compute_statistics, WNDS);
END stats;
/
show errors
CREATE OR REPLACE PACKAGE BODY stats AS
FUNCTION compute_statistics (ssn_in IN student.ssn%type)
RETURN stats_rec
IS
statsrecord stats_rec;
BEGIN
SELECT FIRST_NAME, LAST_NAME
INTO statsrecord.F_NAME, statsrecord.L_NAME
FROM student
WHERE student.ssn=ssn_in;
SELECT COUNT(*)
INTO statsrecord.NO_BOOKS
FROM transaction
WHERE transaction.ssn=ssn_in;
RETURN statsrecord;
END compute_statistics;
END stats;
/
show errors
Now, problem in in calling the function from next script:
I have tried hundreds combinations of callings, but error seems to be either with data type mismatch in assigning the function call (since the return data type is record, which I don't have a good idea how to declare the variable, or it is with whole object thing, I had error object compute_statistics is in valid before in different combination fo function calling). :confused:
set serveroutput on format wrapped size 1000000;
DECLARE
stdata stats.stats_rec%rowtype; (<---I know it doesn't work: Just tried last)
BEGIN
stdata:=stats.compute_statistics('341165421');
dbms_output.put_line(stdata.F_NAME);
END;
/
show errors
Question is:
1. how can I declare variable so that it can match the data type of the function return?
2. if not, what the hell am I doing wrong?
Spent 12 hours straight with this problem. Help please......
Thanks :D
