| Dynamic SQL memory problems [message #370363] |
Fri, 03 September 1999 11:03 |
thomas furrer
Messages: 1
Registered: September 1999
|
Junior Member |
|
|
Can anyone help? We've written an calculator (consists of 3 packages), which does extensive use of dynamic sql (millions per run). Now, we have memory problems with the PGA (not SGA), which raises dramatically and will never be released until disconnect of the session.
In the code we use also package-wide PL/SQL-Tables, but they don't seem to be the problem (all will be empty upon end of run). We use it since RDBMS7.2.2 and the problem persists with RDBMS8.0.5, 8.1.5...
Following is the extract, which does the dynamic sql-call (the most used in the program).
--=============================================================================
--== Excerpt from package PACK_USR with dynamic sql ==
--=============================================================================
FUNCTION Int_ExecCalculation
--
-- Result of the Operation is something similar like
-- BEGIN; Y := 24 * 5; END;
--
(aParam IN PACK_SYS.T_Param, -- TABLE OF VARCHAR2
aStart IN BINARY_INTEGER,
aEnd IN BINARY_INTEGER)
RETURN NUMBER
IS
lString VARCHAR2(2000);
lCurs INTEGER;
lRows INTEGER;
lRet NUMBER;
BEGIN
lString := 'BEGIN :y := ';
FOR i IN aStart .. aEnd LOOP
lString := lString||' '||aParam(i);
END LOOP;
lString := lString||'; END;';
lCurs := dbms_sql.open_cursor;
dbms_sql.parse(lCurs,lString,DBMS_SQL.V7);
dbms_sql.bind_variable( lCurs,':y',TO_NUMBER(NULL) );
lRows := dbms_sql.execute(lCurs);
dbms_sql.variable_value(lCurs,':y',lRet);
PACK_SYS.SmoothCloseCursor(lCurs);
RETURN(lRet);
EXCEPTION
WHEN OTHERS THEN
lRet := SQLCODE;
PACK_SYS.SmoothCloseCursor(lCurs);
RETURN PACK_SYS.F_Sqlcode(lRet);
END Int_ExecCalculation;
--=============================================================================
--== Excerpt's from package PACK_SYS ==
--=============================================================================
--
-- Generate an RETURN-Status, which is identified by the caller as ERROR
-- (ACK_SYS.sSQLCODE is a CONSTANT NUMBER)
--
FUNCTION F_Sqlcode
(aSqlcode IN NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN ( PACK_SYS.sSQLCODE + ABS(aSqlcode) );
END F_Sqlcode;
-------------------------------------------------------------------------------
--
-- Systemwide CLOSE-Function to close Cursors, if they're left open
--
PROCEDURE SmoothCloseCursor
(aCursor IN OUT INTEGER)
IS
BEGIN
IF dbms_sql.is_open(aCursor) THEN
dbms_sql.close_cursor(aCursor);
END IF;
EXCEPTION
WHEN OTHERS THEN NULL;
END SmoothCloseCursor;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
