Return REF CURSOR to procedure generated data

I need to write a sproc which performs 开发者_开发技巧some INSERTs on a table, and compile a list of "statuses" for each row based on how well the INSERT went. Each row will be inserted within a loop, the loop iterates over a cursor that supplies some values for the INSERT statement. What I need to return is a resultset which looks like this:

FIELDS_FROM_ROW_BEING_INSERTED.., STATUS VARCHAR2

The STATUS is determined by how the INSERT went. For instance, if the INSERT caused a DUP_VAL_ON_INDEX exception indicating there was a duplicate row, I'd set the STATUS to "Dupe". If all went well, I'd set it to "SUCCESS" and proceed to the next row.

By the end of it all, I'd have a resultset of N rows, where N is the number of insert statements performed and each row contains some identifying info for the row being inserted, along with the "STATUS" of the insertion

Since there is no table in my DB to store the values I'd like to pass back to the user, I'm wondering how I can return the info back? Temporary table? Seems in Oracle temporary tables are "global", not sure I would want a global table, are there any temporary tables that get dropped after a session is done?

If you are using Oracle 10gR2 or later then you should check out DML error logging. This basically does what you want to achieve, that is, it allows us to execute all the DML in a batch process by recording any errors and pressing on with the statements.

The principle is that we create an ERROR LOG table for each table we need to work with, using a PL/SQL built-in package DBMS_ERRLOG. Find out more. There is a simple extension to the DML syntax to log messages to the error log table. See an example here. This approach doesn't create any more objects than your proposal, and has the merit of using some standard Oracle functionality.

When working with bulk processing (that is, when using the FORALL syntax) we can trap exceptions using the built-in SQL%BULK_EXCEPTIONS collection. Check it out. It is possible to combine Bulk Exceptions with DML Error Logging but that may create problems in 11g. Find out more.

"Global" in the case of temporary tables just means they are permanent, it's the data which is temporary.

I would define a record type that matches your cursor, plus the status field. Then define a table of that type.

TYPE t_record IS
(
    field_1,
    ...
    field_n,
    status VARCHAR2(30)
);

TYPE t_table IS TABLE OF t_record;

FUNCTION insert_records
(
    p_rows_to_insert IN SYS_REFCURSOR
)
    RETURN t_table;

Even better would be to also define the inputs as a table type instead of a cursor.