Hi Tom,
In our application (.Net & Oracle) we have an address table with 20+ fields, and I was wondering if it's a good idea to pass address to SP using REF CURSOR insted of multiple params (SP below will be called from other SPs as well as from C#).
Specifically, with approach below please help with the following:
(a) is there any risk of memory leak etc., using REF CURSOR (esp. if such procedure is called frequently);
(b) is it efficient to use CURSOR, or there's a better approach, say, using Oracle Type instead of cursor;
(c) how to properly dispose/close the cursor when insert is successful or when error happens;
(d) are there any additional upsides/downsides to this approach;
Sample
simplified code:
PROCEDURE SP_INSERT_ADDRESS (v_ADDRESS_CUR IN SYS_REFCURSOR)
IS
l_ADDRESS_REC ADDRESS%ROWTYPE;
BEGIN
LOOP
FETCH v_ADDRESS_CUR INTO l_ADDRESS_REC;
WHEN v_ADDRESS_CUR%NOTFOUND;
INTO ADDRESS (ADDRESS_ID, BUILDING_NO, ...) VALUES (l_ADDRESS_REC.ADDRESS_ID, l_ADDRESS_REC.BUILDING_NO, ...);
END LOOP;
CLOSE v_ADDRESS_CUR;
COMMIT COMMENT 'ADDRESS INSERTED';
EXCEPTION
WHEN OTHERS THEN
CLOSE v_ADDRESS_CUR;
ROLLBACK;
RAISE;
END;Thank you
20 parameters isn't a big deal. For example, here's one from Apex
SQL> desc apex_collection
PROCEDURE ADD_MEMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_COLLECTION_NAME VARCHAR2 IN
P_C001 VARCHAR2 IN DEFAULT
P_C002 VARCHAR2 IN DEFAULT
P_C003 VARCHAR2 IN DEFAULT
P_C004 VARCHAR2 IN DEFAULT
P_C005 VARCHAR2 IN DEFAULT
P_C006 VARCHAR2 IN DEFAULT
P_C007 VARCHAR2 IN DEFAULT
P_C008 VARCHAR2 IN DEFAULT
P_C009 VARCHAR2 IN DEFAULT
P_C010 VARCHAR2 IN DEFAULT
P_C011 VARCHAR2 IN DEFAULT
P_C012 VARCHAR2 IN DEFAULT
P_C013 VARCHAR2 IN DEFAULT
P_C014 VARCHAR2 IN DEFAULT
P_C015 VARCHAR2 IN DEFAULT
P_C016 VARCHAR2 IN DEFAULT
P_C017 VARCHAR2 IN DEFAULT
P_C018 VARCHAR2 IN DEFAULT
P_C019 VARCHAR2 IN DEFAULT
P_C020 VARCHAR2 IN DEFAULT
P_C021 VARCHAR2 IN DEFAULT
P_C022 VARCHAR2 IN DEFAULT
P_C023 VARCHAR2 IN DEFAULT
P_C024 VARCHAR2 IN DEFAULT
P_C025 VARCHAR2 IN DEFAULT
P_C026 VARCHAR2 IN DEFAULT
P_C027 VARCHAR2 IN DEFAULT
P_C028 VARCHAR2 IN DEFAULT
P_C029 VARCHAR2 IN DEFAULT
P_C030 VARCHAR2 IN DEFAULT
P_C031 VARCHAR2 IN DEFAULT
P_C032 VARCHAR2 IN DEFAULT
P_C033 VARCHAR2 IN DEFAULT
P_C034 VARCHAR2 IN DEFAULT
P_C035 VARCHAR2 IN DEFAULT
P_C036 VARCHAR2 IN DEFAULT
P_C037 VARCHAR2 IN DEFAULT
P_C038 VARCHAR2 IN DEFAULT
P_C039 VARCHAR2 IN DEFAULT
P_C040 VARCHAR2 IN DEFAULT
P_C041 VARCHAR2 IN DEFAULT
P_C042 VARCHAR2 IN DEFAULT
P_C043 VARCHAR2 IN DEFAULT
P_C044 VARCHAR2 IN DEFAULT
P_C045 VARCHAR2 IN DEFAULT
P_C046 VARCHAR2 IN DEFAULT
P_C047 VARCHAR2 IN DEFAULT
P_C048 VARCHAR2 IN DEFAULT
P_C049 VARCHAR2 IN DEFAULT
P_C050 VARCHAR2 IN DEFAULT
P_N001 NUMBER IN DEFAULT
P_N002 NUMBER IN DEFAULT
P_N003 NUMBER IN DEFAULT
P_N004 NUMBER IN DEFAULT
P_N005 NUMBER IN DEFAULT
P_D001 DATE IN DEFAULT
P_D002 DATE IN DEFAULT
P_D003 DATE IN DEFAULT
P_D004 DATE IN DEFAULT
P_D005 DATE IN DEFAULT
P_CLOB001 CLOB IN DEFAULT
P_BLOB001 BLOB IN DEFAULT
P_XMLTYPE001 XMLTYPE IN DEFAULT
P_GENERATE_MD5 VARCHAR2 IN DEFAULT
...
I would go with the parameters because you'll get better dependency tracking and maintainability.
Because if the parameter is "sys_refcursor" I could pass *any* SQL into it.