NT Memory fills up

From: PB <pejbaanv_at_mailserv.caiw.nl>
Date: Fri, 26 Jun 1998 12:11:05 +0200
Message-ID: <6mvrhs$hsh@ns.infotech.de>

I have created some procs. When I call them they most of the times work, but sometimes the memory usage grows until I can't work anyfurther.

As said before I am pretty new in developing in PL/SQL, so please help me: It is working under NT, Oracle 7.3.4 OWS3.0

CREATE OR REPLACE PACKAGE myPackage AS

    Recordid NUMBER(6);
    Naam VARCHAR2(64);

    FUNCTION recordSelectNext RETURN number;     FUNCTION recordSelectInit RETURN number;     FUNCTION recordSelectClose RETURN number; END myPackage ;
/

CREATE OR REPLACE PACKAGE BODY myPackage AS

    aCursor INTEGER;
    queryActive BOOLEAN;

---

---

 FUNCTION recordSelectNext RETURN number AS

    lastStatus INTEGER;
    tmp INTEGER;
    sqlStatement VARCHAR2(1024);
BEGIN
    IF queryActive THEN

        lastStatus :=DBMS_SQL.FETCH_ROWS( aCursor);
        IF( lastStatus >0 ) THEN
            DBMS_SQL.COLUMN_VALUE( aCursor, 1, RecordID);
            DBMS_SQL.COLUMN_VALUE( aCursor, 2, Naam);
        END IF;
    ELSE
        lastStatus := -1;

    END IF;
    RETURN lastStatus;
END recordSelectNext;

---

---

FUNCTION recordSelectInit RETURN number AS

    lastStatus INTEGER;
    tmp INTEGER;
    sqlStatement VARCHAR2(1024);
BEGIN
    aCursor := DBMS_SQL.OPEN_CURSOR;
    sqlStatement := 'SELECT * FROM Geachte ORDER BY Geachte';

    DBMS_SQL.PARSE( aCursor, sqlStatement, DBMS_SQL.V7);
    DBMS_SQL.DEFINE_COLUMN( aCursor, 1, RecordID);
    DBMS_SQL.DEFINE_COLUMN( aCursor, 2, Naam,64);

    tmp := DBMS_SQL.EXECUTE( aCursor);
    lastStatus:=1;
    queryActive := True;
    RETURN laststatus;
END recordSelectInit;

---

---

FUNCTION recordSelectClose RETURN number AS BEGIN
    DBMS_SQL.CLOSE_CURSOR(aCursor );
    queryActive := False;
    RETURN 1;
END recordSelectClose;

---

---

END myPackage;
/

CREATE OR REPLACE PROCEDURE myProc AS
IF (myPackage.recordSelectInit>0) THEN

    LOOP

        EXIT WHEN (myPackage.recordSelectNext <=0);
        htp.print(myPackage.Name||'<BR>');

    END LOOP;
END IF;
END myProc;
/ Received on Fri Jun 26 1998 - 05:11:05 CDT