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;

Do you actually read from a table other than `TEMPOUTCOME`? It seems like you dont need that temp table at all. — Pavel Smirnov, Jan 27 '20 at 14:26
The part that says "Determine V_OUTCOME" queries a bunch of different table to determine what the OUTCOME is for each ITEMID. I need to send the set of ITEMDID-OUTCOME-DATE records to the calling API. If I don't use a temp table to store these intermediate result, how would I do it? — NoBullMan, Jan 27 '20 at 14:50
It's difficult to say without seeing the queries, but I'd look into rewriting those "determination" queries into a single select containing required values, so that select can be returned as a cursor. — Pavel Smirnov, Jan 27 '20 at 14:54
Those queries I omitted are a bunch of select statements and IF ... THEN ... ELSE depending on the select results in order to set some internal variables. At the end of it all, variable V_OUCOME is determined. My question is after I set he cursor to "SELECT ... from TEMPTOUTCOME" and open it for LVSQUERY variable, would it be safe then to delete the content of temp table? — NoBullMan, Jan 27 '20 at 15:04
The cursor will contain those rows. However, this does not seem like the right approach to solve the problem. Take a look at this post, for example. https://stackoverflow.com/questions/987013/bulk-insert-into-oracle-database-which-is-better-for-cursor-loop-or-a-simple-s — Pavel Smirnov, Jan 27 '20 at 15:18
Thank you Pavel. But that question doesn't help. As I said, the logic to get the outcome is not as simple as selecting some rows from one table. Multiple tables have to be checked and depending on the result of each SELECT (e.g. get count of rows in some table where ...) other actions have to be taken. A bunch of IF statements, that's why I omitted the gory detail. — NoBullMan, Jan 27 '20 at 15:28
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 — Jury Golubev, Jan 27 '20 at 16:56
@JuryGolubev thank you for the suggestion. To be on the safe side, it makes sense to delete (relatively) older rows based on SYSDATE (stored into a var) and leave the current ones intact. Just for my own understading though, would deleting all rows in temp table after cursor is created mess up the cursor and the data I am passing back to calling API? — NoBullMan, Jan 28 '20 at 14:18
@NoBullMan, 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 — Jury Golubev, Jan 28 '20 at 19:02
@JuryGolubev thank you. Can you change your comment(s) into an answer so I can mark it as one? — NoBullMan, Jan 29 '20 at 13:27
@NoBullMan, sure, thanks. Please pay attention to the last line of it — Jury Golubev, Jan 30 '20 at 08:31

Jury Golubev · Accepted Answer · 2020-01-30T08:30:36.473

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.

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

1 Answers1