0

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?

10
  • A function is not the proper object. While you can jump through hoops and make a function do a lot of things, just because you can does not mean you should. The purpose of a function is to return a value. Think to_date. What you want to do can be accomplished, but is properly done with another procedure, not a function.
    – EdStevens
    Aug 19, 2020 at 11:53
  • @EdStevens ok, is there another thing I can use to refer to a set of exception handling statements? Aug 19, 2020 at 17:57
  • Sure. Just like I said .."What you want to do can be accomplished, but is properly done with another procedure, not a function". Write a stored procedure and call that. You just need to be careful that when you start trying to handle and report exceptions yourself (instead of letting Oracle do what it does) that you don't end up swallowing critical debugging information that oracle would have reported if you had let it handle things. Nothing worse than a user getting error message "Severe error. Contact systems administrator". with no additional info to report to said admin.
    – EdStevens
    Aug 19, 2020 at 19:02
  • @EdStevens ohh i see. I misunderstood. That's helpful, thank you! Aug 19, 2020 at 19:05
  • Can you give an example of the exception handler you want to reuse? Aug 20, 2020 at 7:50

1 Answer 1

0

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.

5
  • Yeah, I think your package executes the general idea I was looking for. I haven't worked with packages yet, but I'm trying out your package and I'm learning so many things right now! I'm so glad i asked this question on SE instead of just writing a bunch of this code: dba.stackexchange.com/questions/274063/… I really appreciate your help! Aug 20, 2020 at 14:36
  • One final question. I want to make a procedure of nested procedures from your package for all the CSVs I want to generate and have the one procedure be performed automatically at set periods of time. Is there a problem with doing this? It seems that some people on the internet are not fans of nested procedures, but I don't understand why. Aug 20, 2020 at 14:39
  • I'm not sure what you mean. If you want to structure your code so that a procedure calls other procedures, that sounds fine, unless the whole calling tree becomes overcomplicated. Aug 20, 2020 at 14:52
  • Yes, a procedure would call other procedures. Cool, I just wanted to see if my code that I was excited about was a terrible idea for some reason that I'm not aware of. Thanks for all your help! Aug 20, 2020 at 17:04
  • Hello again! I tried using your package, but it stopped working. Is it compatible with CAST statements in procedures? Would you mind taking a look at my question if you have time? dba.stackexchange.com/q/274535/213511 Aug 28, 2020 at 10:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.