Return implicit string from oracle SP

Question

Hi i have the following stored procedure...

CREATE OR REPLACE PROCEDURE DB.INSERTGOOD
(
--CapRefCursor OUT Cap_Cur_Pkg.CapCur,
p_APPLIANT_TLT IN GOODRIGHT_MANUAL.APPLICANT_TLT%TYPE,
p_APPLIANT_NME IN GOODRIGHT_MANUAL.APPLICANT_NME%TYPE,
p_APPLICANT_SURNME IN GOODRIGHT_MANUAL.APPLICANT_SURNME%TYPE,
p_COMPANY_NME IN GOODRIGHT_MANUAL.COMPANY_NME%TYPE,
p_ID_CDE IN GOODRIGHT_MANUAL.ID_CDE%TYPE,
p_ADD1 IN GOODRIGHT_MANUAL.ADD1%TYPE,
p_OCCUPATION1 IN GOODRIGHT_MANUAL.OCCUPATION1%TYPE,
p_REMARK1 IN GOODRIGHT_MANUAL.REMARK1%TYPE,
p_SOURCE IN GOODRIGHT_MANUAL.SOURCE%TYPE
) 
IS
BEGIN


INSERT
INTO GOODRIGHT_MANUAL
(
SEQ_ID,
APPLICANT_TLT,      
APPLICANT_NME,
APPLICANT_SURNME,
COMPANY_NME,   
ID_CDE,
ADD1,            
OCCUPATION1,            
REMARK1,            
GOODRIGHT_MANUAL.SOURCE            
)
VALUES
(
goodright_seq.nextval, 
p_APPLIANT_TLT,
p_APPLIANT_NME,
p_APPLICANT_SURNME,
p_COMPANY_NME,
lower(p_ID_CDE),
p_ADD1,
p_OCCUPATION1,
p_REMARK1,
p_SOURCE
);
COMMIT;
-- OPEN CapRefCursor FOR
--select 'True';
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN ROLLBACK;
--  select 'False';
END DB.INSERTGOOD;
/

Here i want to return a string TRUE if the transaction commit successfully and FALSE if transaction rollback.
An Output Variable CapRefCursor is defined but i don't know how to assign true false to that variable and return it. Thanks in advance.

score 1 · Accepted Answer · edited May 23 '17 at 11:43

You have defined a procedure with no OUT parameter, therefore it can not return anything.

You have several options to return success information:

define a function instead of a procedure. A function always returns something, you can define a string to be returned as VARCHAR2 for example in your case.
add an OUT parameter to the procedure. OUT parameters are logically equivalent to function returned values. You can have more than one such parameters.
modify your logic so that the procedure returns nothing when it works and throws an exception when it fails.

I would go with solution (3) because:

solution (1) and (2) are bug-prone: you may easily forget to check the return code in which case your program will continue as if no error had happened in case of failure. Ignoring error is the surest way to transform a benign bug into a monstrosity because it can lead to extensive data corruption. Your program may go on for months without you realising that it is intermittently failing!
Exception logic is designed to overcome this problem and makes the code cleaner and clearer. No more ugly if-then-else after every single procedure call. For this reason alone, solutions (1) and (2) are considered code-smell (anti-pattern) when used extensively to return success/error state.
Less code is involved, just remove the EXCEPTION block and let the error propagate.
procedures that fail will undo their work without rolling back the whole transaction if you let the exception propagate (and don't issue intermediate commits).

Finally, in general you should not control transaction logic in your sub-procedures. A procedure that does a single insert is probably part of a larger transaction. You should not let this procedure either commit or rollback. Your calling code, be it PL/SQL, GUI or script should decide if the transaction should move forward and complete or be rolled back.

Return implicit string from oracle SP

1 Answers1