Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: design question - stored procedures - best practice

RE: design question - stored procedures - best practice

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US