How can I correct errors in calling this function?

[cybersean]

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

[tamilselvan]

TYPE stats_rec IS RECORD
(F_NAME student.FIRST_NAME%TYPE,
L_NAME student.LAST_NAME%TYPE,
NO_BOOKS number);


Change the first line as :
TYPE stats_rec IS OBJECT

I have not tried.

Tamil

[gamyers]

Package and spec is fine. The following script works in 10gR2, but should be fine in 8i too.
set serveroutput on format wrapped size 1000000;

DECLARE
stdata stats.stats_rec;
BEGIN
stdata:=stats.compute_statistics('341165421');
dbms_output.put_line(stdata.F_NAME);
END;
/