StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Your example is out of context so it's impossible to say if there is some problems or not. The code snippet is probably reporting the error condition correctly in the context (by <code>create_log</code>-procedure) even it is this case in <code>others</code> exception handler (that might be a sign of problems and according to Tom Kyte <a href="http://asktom.oracle.com/pls/asktom/f?p=100:11:0%3a%3a%3a%3aP11_QUESTION_ID:1270688200346183091" rel="nofollow">it's for sure</a>).</p> <p>In general I see no issues returning from PL/SQL function non-<code>others</code> exception handler.</p> <p>Oracle PL/SQL documentation says nothing about the subject:</p> <ul> <li><a href="http://docs.oracle.com/cd/E11882_01/appdev.112/e10472/subprograms.htm#LNPLS653" rel="nofollow">Subprogram Parts</a></li> <li><a href="http://docs.oracle.com/cd/E11882_01/appdev.112/e10472/subprograms.htm#LNPLS99897" rel="nofollow">RETURN Statement</a></li> <li><a href="http://docs.oracle.com/cd/E11882_01/appdev.112/e10472/errors.htm#LNPLS851" rel="nofollow">Continuing Execution After Handling Exceptions</a></li> </ul> <p>I use the following pattern routinely to return default values when optional value is not found:</p> <pre><code>create or replace function f1 return dual.dummy%type is v_dummy dual.dummy%type; begin select dummy into v_dummy from dual where dummy = 'Y'; return v_dummy; exception when no_data_found then return 'Z'; end; / </code></pre> <p>(Note that I only process well known error cases where I know how the situation should be handled.)</p> <p>I find the alternative way unnecessary verbose, though it will please return-once purists:</p> <pre><code>create or replace function f2 return dual.dummy%type is v_dummy dual.dummy%type; begin begin select dummy into v_dummy from dual where dummy = 'Y'; exception when no_data_found then v_dummy := 'Z'; end; return v_dummy; end; / </code></pre> <p>Compiling the functions with all warnings turned on return only the following non-related warnings:</p> <pre><code>Warning: PLW-06015: parameter PLSQL_DEBUG is deprecated; use PLSQL_OPTIMIZE_LEVEL = 1 Warning(1,1): PLW-05018: unit F1 omitted optional AUTHID clause; default value DEFINER used </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload