2 Replies Latest reply: Jun 12, 2012 7:23 AM by Billy~Verreynne RSS

    Wanted - Production Strength Function Example - Best Practices

    943106
      Hi All,
      I'm just starting PL/SQL (transitioning from other languages)

      It's recommended that you always use FUNCTIONS + RESULT_CACHE - for performance / abstraction - but the examples shows don't seem to be 'production ready'

      For example;

      -- ---------------------------------------------------------------------------------------------------------------------

      create or replace FUNCTION GET_ACCOUNT
      (i1_account_no IN T_ACCOUNT.ACCOUNT_NO%TYPE)

      RETURN T_ACCOUNT%ROWTYPE
      RESULT_CACHE RELIES_ON (T_ACCOUNT)
      IS
      account_row T_ACCOUNT%ROWTYPE;

      BEGIN
      SELECT *
      INTO account_row
      FROM MDR.T_ACCOUNT
      WHERE ACCOUNT_NO = i1_account_no;

      END;

      RETURN account_row;

      END GET_ACCOUNT;
      -- -------------------------------------------------------------------------------------------------------

      so I presume;

      1) I need exception handling / error logging

      2) I'd like to return 'more' details to the caller, something like

      DECLARE
      TYPE io_reply_type IS RECORD
      (rcode INTEGER := -1, -- return code
      rmsg VARCHAR(2000) := '', -- error message etc
      account T_ACCOUNT%ROWTYPE
      );

      io_reply io_reply_type;


      But there doesn't appear to be a way to define a 'global' record structure - or will using 'packages' allow me to do that.

      TIA

      Bill

      Edited by: 940103 on Jun 12, 2012 3:30 AM
        • 1. Re: Wanted - Production Strength Function Example - Best Practices
          indra budiantho
          Hi, welcome to this forum. But, before posting, just take time to read this:

          SQL and PL/SQL FAQ
          • 2. Re: Wanted - Production Strength Function Example - Best Practices
            Billy~Verreynne
            940103 wrote:

            1) I need exception handling / error logging
            Why? What are you going to do with which exception? There need to be valid reasons for code to trap an exception. Exception handlers are exceptions in themselves. Most code units do not need it - as most code units cannot fix the exception and has no reason to react to an exception.
            2) I'd like to return 'more' details to the caller, something like

            DECLARE
            TYPE io_reply_type IS RECORD
            (rcode INTEGER := -1, -- return code
            rmsg VARCHAR(2000) := '', -- error message etc
            account T_ACCOUNT%ROWTYPE
            );
            Wrong. This is NOT how error handling works in ANY language.

            You either catch the exception and fix it (no error occurred from caller's perspective). Or you propagate the exception up the call stack for the caller to deal with.

            Returning an exception as an output variable/record of some type? Wrong in Java. Wrong in .Net. Wrong in C++. Wrong in PL/SQL. This violates the basic principle of coding by contract - by not using the default and robust exception management runtime system that exists, but hijacking that, implementing your own, and creating a gross inconsistent behaviour that the caller does not expect, using data structures that the caller does not know about.