PLVlog: Logging Facility

The PLVlog (PL/Vision LOGging) package provides a powerful, generic logging facility for PL/SQL packages. See PLVlog and PLVtrc: Logging and Tracing for details.

Package constants

• c_file CONSTANT VARCHAR2(100) := 'PLV.log';
• The default name of the file contains the PL/Vision log when writing to an operating system file. This can be -- and usually would be -- overridden with your own file name. It is only applicable if you are using PL/SQL Release 2.3 or above.
• c_noaction CONSTANT PLV.plsql_identifier%TYPE := '*NO ROLLBACK*';
• Name for rollback activity to not perform any rollback.
• c_none CONSTANT PLV.plsql_identifier%TYPE := '*FULL*';
• Name to indicate that a full rollback should occur.
• c_default CONSTANT PLV.plsql_identifier%TYPE := '*DEFAULT*';
• Name to indicate that a rollback should occur to the default savepoint.
• c_last CONSTANT PLV.plsql_identifier%TYPE := '*PLVRB-LAST*';
• Name to indicate that a rollback should occur to the last savepoint recorded by PLVrb.
• c_PLVlogsp CONSTANT PLV.plsql_identifier%TYPE :=
• 'PLVlog_savepoint';
• The default savepoint issued after a write to the log.

Controlling logging activity

• PROCEDURE turn_on;
• Turns on the logging activity; calls to put_line write information to the log (default).
• PROCEDURE turn_off;
• Turns off the log mechanism.
• FUNCTION tracing RETURN BOOLEAN;
• Returns TRUE if the log is active.

Selecting the log type

• PROCEDURE sendto
• (type_in IN VARCHAR2, file_in IN VARCHAR2 := NULL);
• Generic program to indicate the type of log (the repository to which information will be sent). The valid types are stored in the PLV package. If you choose PLV.file, you need to also provide a file name. You can also set the log type by calling one of the following procedures.
• PROCEDURE to_pstab;
• Requests that the information be sent to a PL/SQL table.
• PROCEDURE to_dbtab;
• Requests that the information be sent to a database table.
• PROCEDURE to_file (file_in IN VARCHAR2);
• Requests that the information be sent to an operating system file.
• PROCEDURE to_stdout;
• Requests that the information be sent to standard output.
• FUNCTION logtype RETURN VARCHAR2;
• Returns the current log target type. This type will be one of the repository constants defined in the PLV package.

Writing to the log

• PROCEDURE put_line
• (context_in IN VARCHAR2,
• code_in IN INTEGER,
• string_in IN VARCHAR2 := NULL,
• create_by_in IN VARCHAR2 := USER,
• rb_to_in IN VARCHAR2 := c_default,
• override_in IN BOOLEAN := FALSE);
• Writes a line to the PLVlog repository. This version of put_line allows you to specify a full set of values for the log record.
• PROCEDURE put_line
• (string_in IN VARCHAR2,
• rb_to_in IN VARCHAR2 := c_default,
• override_in IN BOOLEAN := FALSE);
• This version of put_line keeps to an absolute minimum what you have to/want to provide to write a line to the log.

Reading the log

• PROCEDURE get_line
• (row_in IN INTEGER,
• context_out OUT VARCHAR2,
• code_out OUT INTEGER,
• string_out OUT VARCHAR2,
• create_by_out OUT VARCHAR2,
• create_ts_out OUT DATE);
• Reads a row from the PL/SQL table log, parses the contents, and returns the individual values in the separate OUT arguments of the parameter list.
• PROCEDURE display (header_in IN VARCHAR2 := 'PL/Vision Log');
• Displays the contents of the current PLVlog log (either in the database table or in the PL/SQL table).

Managing the log

• PROCEDURE clear_pstab;
• Empties the PL/SQL table associated with the PLVlog mechanism.
• FUNCTION pstab_count RETURN INTEGER;
• Returns the number of rows filled in the PLVlog PL/SQL table.
• PROCEDURE set_dbtab
• (table_in IN VARCHAR2 := 'PLV_log',
• context_col_in IN VARCHAR2 := 'context',
• code_col_in IN VARCHAR2 := 'code',
• text_col_in IN VARCHAR2 := 'text',
• create_ts_col_in IN VARCHAR2 := 'create_ts',
• create_by_col_in IN VARCHAR2 := 'create_by');
• Determines which database table is to be used by PLVlog for logging. PLVlog relies on dynamic SQL, so you can at runtime specify the table and column names for this table.
• PROCEDURE fclose;
• Closes the operating system file if you have chosen a file for the log repository.
• PROCEDURE ps2db;
• Transfers contents of PL/SQL log to the PLVlog database table.

Rolling back in PLVlog

• PROCEDURE do_rollback;
• Turns on the issuing of a ROLLBACK before an INSERT into the log.
• PROCEDURE nodo_rollback;
• Turns off the issuing of a ROLLBACK before an INSERT into the log.
• FUNCTION rolling_back RETURN BOOLEAN;
• Returns TRUE if PLVlog is issuing a ROLLBACK before an INSERT.
• PROCEDURE rb_to (savepoint_in IN VARCHAR2 := c_none);
• Sets the default savepoint used both for the ROLLBACK command before a log insert and the SAVEPOINT command after the log insert.
• PROCEDURE rb_to_last;
• Sets the savepoint used both for the ROLLBACK command before a log insert to the last savepoint known to PLVrb.
• PROCEDURE rb_to_default;
• Sets the default savepoint used both for the ROLLBACK command before a log insert to the PLVlog default savepoint.
• PROCEDURE set_sp (savepoint_in IN VARCHAR2);
• Sets the name of the savepoint to be set after the call to put_line to write a line to the log.