Re: forms and package-cursor / package-pl-sql-table

"Thomas Stuefer" <stuefer_at_halli-data.at> wrote in message news:<Ejc18.32$b82.394254_at_news.salzburg-online.at>...
> I have created a package with a cursor and a pl-sql-table (see sql-script
> below). Now I will use this package-objects in forms.
>
> By using the cursor in a trigger i can compile the form without an error,
> but get an error in the forms-runtime: "FRM-40734: Internal error: PL/SQL
> error occurred". My forms-trigger looks like:
>
> FOR recJobs IN pkg_test.curJobs ('SALESMAN') LOOP
> MESSAGE(recJobs.empno);
> END LOOP;
>
>
> By using the pl-sql-table in a trigger i get an compiler error:
> "PKG_TEST.TABSUMSAL: Cannot directly access remote package variable or
> cursor". My forms-trigger:
>
> DECLARE
> nIndexMerk dept.deptno%TYPE := 0;
>
> BEGIN
> LOOP
> nIndexMerk := pkg_test.tabSumSal.NEXT(nIndexMerk);
>
> EXIT WHEN nIndexMerk IS NULL;
>
> MESSAGE('Abteilung ' || nIndexMerk || ' = ' || 'Gehaltssumme: ' ||
> pkg_test.tabSumSal(nIndexMerk));
> END LOOP;
> END;
>
>
> What's wrong in my forms-module ? I use Oracle 8.1.6 and Forms 6.0.5.35.3 on
> Windows 98.
>
>
> Many thanks for your help,
> Tom
>
> --------------
>
> CREATE OR REPLACE PACKAGE pkg_test IS
>
> TYPE tabSumSalTyp IS TABLE OF emp.sal%TYPE INDEX BY BINARY_INTEGER;
> tabSumSal tabSumSalTyp;
>
> CURSOR curJobs (cParJob IN emp.job%TYPE) RETURN emp%ROWTYPE;
>
> END pkg_test;
>
>
> CREATE OR REPLACE PACKAGE BODY pkg_test IS
>
> nSumSal emp.sal%TYPE;
>
> CURSOR curJobs (cParJob IN emp.job%TYPE) RETURN emp%ROWTYPE IS
> SELECT * FROM emp WHERE job = cParJob;
>
> BEGIN
> FOR recDept IN (SELECT deptno FROM dept ORDER BY deptno) LOOP
> SELECT SUM(sal) INTO nSumSal FROM emp WHERE deptno = recDept.deptno;
>
> tabSumSal(recDept.deptno) := NVL(nSumSal, 0);
> END LOOP;
>
> END pkg_test;

Hi Tom,

long time ago and using Forms 4.5 we got a similar problem. Maybe this clue will help you, but better ask Oracle Support.

I tried to define global constants within a global package, but i didn't retrieve the correct constant values during runtime - compiling was no problem.
The only solution (after calling Oracle support) was to declare functions for each constant, which gave the assigned value. It worked, but you should better not think about that kind o solution - it sucks.

You may use a ref cursor or build a function, also. If you find a work-around, please post it (or send a mail).

Good luck... Received on Thu Jan 17 2002 - 09:24:50 CET