The log_message PL/SQL procedure is an example of the benefits of using packaged procedures. You can write a single piece of code to perform a task that needs to be carried out by many other programs. By avoiding duplication of code you save time and effort. Maintenance is simplified too. If you want to change the format of the messages, you just need to amened a single packaged procedure.
The log_message program records messages in the APPLOG table, referencing APPSEVERITY
for the severity codes, typically Information, Error, and Warning. The Data Definition Language script for creating the schema objects is available
here.
Download schema DDL
The severity codes are maintained in the table APPSEVERITY. The table has the following codes loaded during installation, but you can add your own:
| Severity Codes (APPSEVERITY) | |
|---|---|
| Severity | Severity_Desc |
| I | Information |
| E | Error |
| W | Warning |
| Table: APPSEVERITY | ||
|---|---|---|
| Column Name | Data Type | Description |
| SEVERITY | VARCHAR2(1) | Primary Key |
| SEVERITY_DESC | VARCHAR2(30) | Description of severity code |
CREATE TABLE APPSEVERITY
(
SEVERITY VARCHAR2(1)
SEVERITY_DESC VARCHAR2(30)
) ;
CREATE UNIQUE INDEX APPSEVERITY_PK ON APPSEVERITY (SEVERITY) ;
ALTER TABLE APPSEVERITY ADD CONSTRAINT APPSEVERITY_PK PRIMARY KEY (SEVERITY) ENABLE;
Next we can create the APPLOG table that will store the application messages generated by our programs.
| Table: APPLOG | ||
|---|---|---|
| Column Name | Data Type | Description |
| REC_ID | NUMBER | GENERATED BY DEFAULT AS IDENTITY |
| MESSAGE | VARCHAR2(4000) | Free text message field |
| LOGGED AT | TIMESTAMP | Date and Time message recorded |
| USER_NAME | VARCHAR2(128) | Database User |
| APPLOG_SQLERRM | VARCHAR2(1000) | SQLERRM generated by calling program |
| PROGRAM_NAME | VARCHAR2(100) | Name of program logging the message |
| SEVERITY | VARCHAR2(1) | Message type lookup on APPSEVERITY |
CREATE TABLE APPLOG
(
RECID NUMBER GENERATED ALWAYS AS IDENTITY,
MESSAGE VARCHAR2(4000),
LOGGED_AT TIMESTAMP,
USER_NAME VARCHAR2(128),
APPLOG_SQLERRM VARCHAR2(1000),
PROGRAM_NAME VARCHAR2(100),
SEVERITY VARCHAR2(1)
) ;
CREATE UNIQUE INDEX APPLOG_PK ON APPLOG (RECID) ;
ALTER TABLE APPLOG ADD CONSTRAINT APPLOG_PK PRIMARY KEY (RECID) ENABLE;
ALTER TABLE APPLOG ADD CONSTRAINT APPLOG_APPSEVERITY_FK FOREIGN KEY (SEVERITY) REFERENCES APPSEVERITY (SEVERITY) ENABLE;
The instructions for how to call the procedure, and the parameters to pass, are in the UTIL_ADMIN Package specification.
| util_admin.log_message | |
|---|---|
| IN Parameters | Usage |
| p_message | Free text message to display / write to log |
| p_sqlerrm | SQLERRM default (optional) |
| p_program_name | Name of program generating the message (optional) |
| p_log_mode | Write message to one of: F=Log table, S=Screen, B=Both (default S) |
| p_severity | Severity: I=Information, E=Error, W=Warning (default I) |
When you execute the following code, a message will be displayed on the screen, and written to the APPLOG table.
SET SERVEROUTPUT ON
DECLARE
BEGIN
util_admin.log_message ('This is a test message',NULL,NULL,'B');
END;
Here's an example of how the CSV import program uses log_message to record errors.
WHEN E_INVALID_DATA THEN
util_admin.log_message('Invalid data importing file ' || p_filename,SQLERRM,'IMPORT.ORD_IMP','B',gc_error);
util_file.rename_file(gc_import_directory, p_filename, gc_import_error_dir, p_filename);
RETURN FALSE;
The demo application has an Application Log report (applog.sql) which lists the messages in the log, as shown below.
