RE: design question - stored procedures

From: Looney, Jason <Jason.Looney_at_echostar.com>
Date: Mon, 30 Aug 2004 11:40:38 -0600
Message-ID: <B8C9CF34B3F7164282C8610C93BB94AF04C632F4@riv-exchb1.echostar.com>

Paula,

Here is the table definition is use for a log file: CREATE TABLE cardex.cdx_processing_log

    (log_trace_level                VARCHAR2(20) NOT NULL,
    log_sid                        NUMBER NOT NULL,
    log_serial#                    NUMBER NOT NULL,
    log_username                   VARCHAR2(30) NOT NULL,
    log_osuser                     VARCHAR2(30) NOT NULL,
    log_source                     VARCHAR2(61) NOT NULL,
    log_date_time_1                DATE NOT NULL,
    log_date_time_2                DATE,
    log_action                     VARCHAR2(30),
    log_code                       NUMBER,
    log_field                      VARCHAR2(200),
    log_location                   VARCHAR2(200),
    log_message                    VARCHAR2(2000) NOT NULL)
  PCTFREE     10
  PCTUSED     40

  INITRANS 5
  MAXTRANS 255
  TABLESPACE cardex_med_d
  STORAGE (

    INITIAL     983040
    NEXT        983040

    PCTINCREASE 0
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 5
  )
/

Here is the source that performs the writes:

PROCEDURE write_processing_log (

      pi_log_trace_level   IN   cdx_processing_log.log_trace_level%TYPE


     ,pi_log_source        IN   cdx_processing_log.log_source%TYPE

     ,pi_log_date_time_1   IN   cdx_processing_log.log_date_time_1%TYPE

     ,pi_log_action        IN   cdx_processing_log.log_action%TYPE

     ,pi_log_code          IN   cdx_processing_log.log_code%TYPE

     ,pi_log_field         IN   cdx_processing_log.log_field%TYPE

     ,pi_log_location      IN   cdx_processing_log.log_location%TYPE

     ,pi_log_message       IN   cdx_processing_log.log_message%TYPE

   ) IS
      PRAGMA AUTONOMOUS_TRANSACTION;

      CURSOR v_session_info_cur IS
         SELECT SID, serial#, username, osuser
           FROM v$session
          WHERE SID = (SELECT SID
                         FROM v$mystat
                        WHERE ROWNUM = 1);

      v_session_info_row   v_session_info_cur%ROWTYPE;
   BEGIN
      OPEN v_session_info_cur;

      FETCH v_session_info_cur
       INTO v_session_info_row;

      CLOSE v_session_info_cur;

-- log event into processing_log table;
      INSERT INTO cdx_processing_log
                  (log_trace_level, log_sid, log_serial#, log_username
                  ,log_osuser, log_source, log_date_time_1, log_date_time_2,
log_action, log_code
                  ,log_field, log_location, log_message
                  )
           VALUES (pi_log_trace_level, v_session_info_row.SID,
v_session_info_row.serial#, v_session_info_row.username
                  ,v_session_info_row.osuser, pi_log_source,
pi_log_date_time_1, SYSDATE, pi_log_action, pi_log_code
                  ,pi_log_field, pi_log_location, pi_log_message
                  );

      COMMIT;
   EXCEPTION
      WHEN OTHERS THEN
         ROLLBACK;

END write_processing_log;

Since the log is written via an AUTONOMOUS TRANSACTION the commit will happen regardless of the rest of the procedure rollback or commit.

Hope this helps.
Jason.

Guys,

I have a stored procedure that loads data and writes to an error log a = specific field/message when there are issues.

I know that a better design would be to modularize this code as I have = repetition/redundancy - yuck.

I don't return anything when I write to the log so I am thinking I = should have the parent procedure do the integrity check then a child = procedure (versus a function) write to the log.

Help - can someone provide advice on this????

here is a snippet. I check values in a field then write to a log table:

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Aug 30 2004 - 12:36:28 CDT

RE: design question - stored procedures - best practice