Oracle Stored Procedure - Can I empty temp table after cursor is created

Question

I have a web service API that uses a list of item ID as input parameter and a data table as output parameter (among other parameters irrelevant to this question). This API calls an Oracle stored procedure within a package to get the content of the output data table.

The stored procedure loops through each item ID and determines an outcome for it. It then uses a temp table to store the results for each item ID (Item ID, outcome, sysdate). A the end, a cursor is used to query this temp table and get the result.

My question is that as time goes by content of this data table gets too big (millions of records). I know I can have a clean up process but was wondering if it acceptable to delete the content after cursor is created.

This is a watered version of web service API and stored procedure:

public static EnumGlobal.Errorcode GetOutcomeByItem(string itemIDs, out DataTable dtOutcome, ...)
{
    OracleDbContext dbContext = new OracleDbContext();
    List<OracleParameter> spParams = new List<OracleParameter>();
    DataSet dsOutcome = new DataSet();
    ...
    try
    {
        spParams.Add(new OracleParameter("IPSITEMIDS", OracleDbType.Varchar2, itemIDs, ParameterDirection.Input));
        ...
        spParams.Add(new OracleParameter("CUR_OUT", OracleDbType.RefCursor, ParameterDirection.Output));

        try
        {
            dbContext.Open();
            dbContext.ExecuteStoredProcedure("PKGSOMEQUERY.USPGETOUTCOMEBYITEM", spParams, ref dsOutcome);
        }
    }
}

PROCEDURE USPGETOUTCOMEBYITEM
(
IPSITEMIDS                VARCHAR2,
...
CUR_OUT                   OUT GETDATACURSOR
)
IS
LVSQUERY                VARCHAR2(4000):='';
V_OUTCOME VARCHAR2(5);
V_NEWITEMSLIST VARCHAR2(4000) := REPLACE(IPSITEMIDS, '''', '');

CURSOR cur IS
SELECT  REGEXP_SUBSTR(V_NEWITEMSLIST, '[^,]+', 1, LEVEL) V_NEWITEM2 FROM DUAL CONNECT BY instr(V_NEWITEMSLIST, ',',1, LEVEL -1) > 0;

BEGIN
-- Loop thorugh each ITEM ID and determine outcome, add ITEM ID and OUTCOME to temp table
FOR rec IN cur LOOP   
    V_NEWITEM := rec.V_NEWITEM2;
    ...
    -- Determine V_OUTCOME
    ...
    INSERT INTO TEMPOUTCOME
    (
      ITEMID,
      OUTCOME,
      ORIGINDATE
    )
    VALUES
    (
        V_NEWITEM,
        V_OUTCOME,
        SYSDATE
    );
    COMMIT;
END LOOP;

LVSQUERY:='SELECT ITEMID, OUTCOME, ORIGINDATE FROM TEMPOUTCOME WHERE ITEMID IN (' || IPSITEMIDS || ')';

OPEN CUR_OUT FOR LVSQUERY;
COMMIT;

-- Can I do this?
-- Delete from temp table all item IDs used in this session, in one shot
--    DELETE FROM TEMPOUTCOME WHERE ITEMID IN (select REGEXP_SUBSTR(IPSITEMIDS, '\''(.*?)\''(?:\,)?', 1, LEVEL, NULL, 1) FROM dual CONNECT BY LEVEL <= REGEXP_COUNT(IPSITEMIDS, '''(?: +)?(\,)(?: +)?''', 1) + 1);        

EXCEPTION WHEN OTHERS THEN
    PKGHANDLEERROR.USPHANDLEERROR('USPGETOUTCOMEBYITEM', LVIERRORCODE);
    OPIERRORCODE:=LVIERRORCODE;
END USPGETOUTCOMEBYITEM;

Answer 1

I haven't really tested that, but from general ORACLE knowledge perspective, as soon as you open a cursor, you are no longer dealing with stored data. Instead you are iterating an in-memory snapshot. So I believe it should work. Unless there's a huge amount of data and oracle tries to page the results (not sure if it actually happens though)...

As a simple/safe option you can delete the records that are a day/hour/minute old (depending on the utilization).

Also as a suggestion, if you get sysdate once into a variable and use it in your insert, it may be much easier to deal with the dataset. as you may just query by origindate. It will also make it a bit faster to insert

One more thing to take a look at (maybe even the best one) is Oracle Temporary tables.