| Problem with return value of a stored function [message #81993] |
Tue, 08 April 2003 08:24  |
Raj
Messages: 411
Registered: November 1998
|
Senior Member |
|
|
Hi ,
I have a function at the database side. It returns a table of records.
When I tried to call this function from forms I'm not getting the value.
Here is the example
I have a package which has a function which returns table of ename from emp
CREATE OR REPLACE PACKAGE pkg_test AS
TYPE typ_ename_tab IS TABLE OF emp.ename%TYPE;
FUNCTION fn_GetEname RETURN typ_ename_tab;
END pkg_test;
/
CREATE OR REPLACE PACKAGE BODY pkg_test AS
FUNCTION fn_GetEname RETURN typ_ename_tab IS
op_ename typ_ename_tab := NULL;
BEGIN
SELECT ename
BULK COLLECT INTO op_ename
FROM emp;
RETURN op_ename;
END fn_GetEname;
END pkg_test;
/
I tested this package at the back end with the foll. code
DECLARE
a pkg_test.typ_ename_tab;
BEGIN
a := pkg_test.fn_GetEname;
FOR j IN a.FIRST..a.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(a(j));
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END;
and the out put was
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
So far so good.
But when I tried to get this in a When_button_pressed Trigger I was able to compile it without error but I got the output as NULL
The Code in when_button_pressed is
DECLARE
a pkg_test.typ_ename_tab;
BEGIN
a := pkg_test.fn_GetEname;
IF a IS NOT NULL THEN
FOR j IN a.FIRST..a.LAST
LOOP
MESSAGE(a(j));
MESSAGE(' ');
END LOOP;
ELSE
MESSAGE('A IS NULL');
END IF;
END;
The Out put is a message A IS NULL at the status Bar.
Pls tell me if it is possible to get the table of records in forms.
Thanks a Lot in advance.
Regards
Raj
|
|
|
|
| Re: Problem with return value of a stored function [message #81999 is a reply to message #81993] |
Tue, 08 April 2003 10:09  |
shadow
Messages: 15
Registered: April 2003
|
Junior Member |
|
|
PACKAGE EmpP1 AS
TYPE empList IS table OF emp%ROWTYPE index by binary_integer;
procedure query;
procedure pquery(result in out empList,dno in number );
procedure pqueryrun;
resultset emplist;
cursor mycursor is select empno,deptno from emp;
END;
********************************************
PACKAGE BODY EmpP1 AS
procedure query is
temp number;
eno number;
dno number;
begin
temp :=0;
open mycursor;
loop
temp :=temp+1;
fetch mycursor into eno, dno;
exit when mycursor%notfound;
resultset(temp).empno := eno;
resultset(temp).deptno := dno;
-- message(emplist(temp).deptno);
end loop;
for i in 1 .. resultset.count loop
--if resultset(i).deptno =10 then
:kontrol.empno := resultset(i).empno;
:kontrol.deptno := resultset(i).deptno;
Next_Record;
-- end if;
end loop;
end;
procedure pquery(result in out emplist,dno in number ) is
resultempty emplist;
begin
MESSAGE(result.count);
CLEAR_BLOCK;
IF DNO = -1 THEN
result:= resultempty;
GOTO ext; END IF;
for i in 1 .. result.count loop
if result(i).deptno = dno then
:kontrol.empno := result(i).empno;
:kontrol.deptno := result(i).deptno;
Next_Record;
end if;
end loop;
<<ext>>
null;
end;
procedure pqueryrun is
begin
pquery(resultset,:kontrol.dno);
end;
end;
****************************************
when-button-pressed
empp1.query;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
