Oracle: Is there a way to put exception handling into a function and just reference that function inside procedures

functionsoraclestored-procedures

If you were going to write 10 procedures that all have the same exceptions, you'd want to just reference the procedures defined somewhere else, right?

You can call a function inside a procedure

create or replace procedure P
    a_variable_name number;
begin
    a_variable := a_function('a parameter');
end;
/

And i imagine you'd have to pass the result of your procedure into the function. Like

if X procedure executed correctly, then do nothing
if Y error, do the Y exception handling

Is such a thing possible? If not, how do oracle users handle writing 10 different procedures that all use the same exception handling? Like, are there any tips and tricks besides just copying and pasting those exceptions and hoping you'll never have to change all 10 of them if you have to change one thing?

Best Answer

Well, you could have a procedure that checked for various values of sqlcode or sqlerrm and performed whatever actions you have in mind based on those, and called that procedure from your exception handler passing the filename and directory object name. However, I don't think that would be a neat solution.

If the problem is trying to reproduce the (quite old) Oracle Base approach for handling utl_file exceptions in multiple procedures, don't do that. It just copies every single exception from the manual and dumps them all into a monumental end-of-procedure exception handler, regardless of context.

When you open a file, you should use an exception handler to ensure that it opened OK, otherwise you can report that you couldn't open file x in folder y. For example (from www.williamrobertson.net/documents/refcursor-to-csv.shtml):

begin
    l_file :=
        utl_file.fopen
        ( filename => p_filename
        , location => p_directory
        , open_mode => 'w'
        , max_linesize => 4000 );
exception
    when utl_file.invalid_operation then
        raise_application_error(-20007, 'File '''||p_filename||''' could not be opened in directory '||p_directory||' ('||k_dir_path||')', TRUE);
    when utl_file.invalid_path then
        raise_application_error(-20008, 'File location '||p_directory||' ('||k_dir_path||') is invalid.', TRUE);
    when utl_file.invalid_filename then
        raise_application_error(-20009, 'Filename '''||p_filename||''' is invalid.', TRUE);
end;

Similarly, when you write to a file you could use exception handlers to ensure that write errors are reported clearly with appropriate details.

Or, centralise all CSV writing operations in a single package like the one I linked to, where you just pass in a refcursor and the file details and it does the rest.

Related Question