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

From: Thomas Stuefer <stuefer_at_halli-data.at>
Date: Wed, 16 Jan 2002 14:31:26 +0100
Message-ID: <Ejc18.32$b82.394254_at_news.salzburg-online.at>

[Quoted] 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; Received on Wed Jan 16 2002 - 14:31:26 CET