In the aftermath of the holiday season, there follows the inevitable January sales.
This year, I have been spared the inevitable trudge around the stores. Deb has hurt her knee and has therefore been restricted to browsing on-line.
I thought she “kneeded” cheering up, but to date, my attempts at lightening the mood, seem only to have given her the “kneedle”.
Sitting quietly, whilst Deb is wandering through various furniture store websites, I had cause to reflect on Oracle’s own version of Nested Tables.
These were introduced way back in Oracle 8, when Oracle confidently predicted that the Object-Relational Database was the way of the future.
Imagine if they were just bringing this feature out now. You can picture it. Larry would have spent months making disparaging remarks about IKEA’s occasional table range, before unveiling his own version, which was better, cheaper and more efficient.
Whilst you’re never going to be able to rest your pint on one, a Nested Table in Oracle may be useful on occasion.
The Application Error Log
In many Oracle database applications you will find a utility that’s used for debugging issues.
It consists of :
- a table to hold the error log information
- a procedure to write to the table
- a large amount of logging data
The structure of the table will probably be something like this :
CREATE TABLE error_logs(
username VARCHAR2(30),
datetime TIMESTAMP,
program_name VARCHAR2(30),
parameters VARCHAR2(4000),
message VARCHAR2(4000))
/
And the procedure is likely to be a variation on theme of :
CREATE OR REPLACE PROCEDURE log_error_pr (
i_prog_name VARCHAR2, i_params VARCHAR2, i_err VARCHAR2) AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO error_logs(
username, datetime, program_name,
parameters, message)
VALUES(
USER, SYSTIMESTAMP, i_prog_name,
i_params, i_err);
COMMIT;
END log_error_pr;
/
At this point, it’s worth considering the parameters column. Yes, it is a VARCHAR2(4000) and whilst this may, in exceptional circumstances, prove to be too small to hold the requisite data, on the whole it should be OK.
Unfortunately, getting the required data ( in this case the parameter names and values) into the required format to go into the table requires a fair bit of messing about.
CREATE OR REPLACE PROCEDURE trouble_pr(
i_name IN VARCHAR2, i_payday IN VARCHAR2,
i_been_to_ikea IN VARCHAR2)
AS
--
-- This procedure only exists to cause trouble
-- It starts off having just returned from a well-known
-- furniture store
--
l_msg VARCHAR2(4000);
e_bits_missing EXCEPTION;
l_params error_logs.parameters%TYPE;
BEGIN
--
-- here it comes...
--
l_msg := 'Back to the shop';
RAISE e_bits_missing;
EXCEPTION
WHEN OTHERS THEN
l_params :=
'i_name : '||i_name
||' i_payday : '||i_payday
||' i_been_to_ikea : '||i_been_to_ikea;
log_error_pr(
i_prog_name => 'TROUBLE_PR',
i_params => l_params,
i_err => l_msg);
RAISE_APPLICATION_ERROR(-20000, l_msg);
END trouble_pr;
/
Of course, you can have a peek in the data dictionary to get the parameter names by means of DBA_ARGUMENTS. You could then use this information in the error logging procedure meaning that you only had to build a string of parameter values in the order in which they appear in the program. However, looking at the parameter column, especially when there are a large number of parameters passed into the program in question, can get a bit tedious.
Let’s take a different approach which, for the want of a better gag, we’ll call the IKEA method.
Object Relational thingys
The steps for this are :
- create a database type to hold the data
- create another type – essentially an arary of the first type
- create the table with a column defined as the array type
- enter and retrieve the data using the new column
The base type essentially defines a record. The record consists of two fields – the parameter name and the parameter value :
CREATE or REPLACE TYPE paramlist_typ AS OBJECT( param_name VARCHAR2(30), param_val VARCHAR2(4000)) /
Now to create an array of the first type. This will enable us to use the base type as a Nested Table :
CREATE or REPLACE TYPE paramlist_tab_typ AS TABLE OF paramlist_typ /
Next up, the table :
CREATE TABLE error_logs(
username VARCHAR2(30),
datetime TIMESTAMP,
program_name VARCHAR2(30),
parameters paramlist_tab_typ,
message VARCHAR2(4000))
NESTED TABLE parameters STORE AS paramlist
/
Insert and Select on a Nested Table
Probably a good idea to go through a simple insert statement at this point to see how this nested table thing works :
INSERT INTO error_logs(
username, datetime, program_name,
parameters, message)
VALUES(
USER, SYSTIMESTAMP, 'TEST',
paramlist_tab_typ(
paramlist_typ('PARAM1', 'Someval'),
paramlist_typ('PARAM2', TO_CHAR(SYSDATE, 'DD-MON-RR HH24:MI')),
paramlist_typ('PARAM3', 123456)),
'Some error message')
/
If we want to select from the nested table, we can do :
SQL> select parameters
2 from error_logs
3 where program_name = 'TEST';
PARAMETERS(PARAM_NAME, PARAM_VAL)
--------------------------------------------------------------------------------
PARAMLIST_TAB_TYP(PARAMLIST_TYP('PARAM1', 'Someval'), PARAMLIST_TYP('PARAM2', '0
7-JAN-12 20:44'), PARAMLIST_TYP('PARAM3', '123456'))
Hmmm, not the most elegant output. By casting the parameters column in the error_logs table to a TABLE, we can flatten out the nested table, which looks quite a bit better :
SQL> SELECT pl.param_name, pl.param_val 2 FROM error_logs err, TABLE( err.parameters) pl 3 WHERE err.program_name = 'TEST'; PARAM_NAME PARAM_VAL -------------------- -------------------- PARAM1 Someval PARAM2 07-JAN-12 20:44 PARAM3 123456 SQL>
Now we’ve got that sorted, we can see how this translates into our error logging utility.
The procedure to log the errors first :
CREATE OR REPLACE PROCEDURE log_error_pr (
i_prog_name VARCHAR2,
i_params paramlist_tab_typ,
i_err VARCHAR2) AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO error_logs(
username, datetime, program_name,
parameters, message)
VALUES(
USER, SYSTIMESTAMP, i_prog_name,
i_params, i_err);
COMMIT;
END log_error_pr;
/
And now for a procedure to call it :
CREATE OR REPLACE PROCEDURE more_trouble_pr(
i_name VARCHAR2,
i_somedate DATE,
i_some_num NUMBER) IS
l_msg VARCHAR2(4000);
e_bits_missing EXCEPTION;
BEGIN
l_msg := 'Where did I leave that lump hammer ?';
RAISE e_bits_missing;
EXCEPTION
WHEN OTHERS THEN
log_error_pr(
i_prog_name => 'MORE_TROUBLE_PR',
--
-- Might be a good idea to do type conversions in
-- the program calling the error logging routine
-- as at this point we know, for example, what the
-- date format should be
--
i_params => paramlist_tab_typ(
paramlist_typ( 'i_name', i_name),
paramlist_typ( 'i_somedate', TO_CHAR(i_somedate, 'DD-MON-YYYY')),
paramlist_typ( 'i_some_num', TO_CHAR(i_some_num,9999))
),
i_err => l_msg);
RAISE_APPLICATION_ERROR(-20000, l_msg);
END;
/
And now to run the procedure :
SQL> exec more_trouble_pr('MIKE', TO_DATE('03012012','DDMMRRRR'), 1234)
BEGIN more_trouble_pr('MIKE', TO_DATE('03012012','DDMMRRRR'), 1234); END;
*
ERROR at line 1:
ORA-20000: Where did I leave that lump hammer ?
ORA-06512: at "MIKE.MORE_TROUBLE_PR", line 28
ORA-06512: at line 1
SQL>
Now let’s have a look at the entry in the error_logs table :
SQL> SELECT pl.param_name, pl.param_val 2 FROM error_logs err, TABLE(parameters) pl 3 WHERE err.program_name = 'MORE_TROUBLE_PR'; PARAM_NAME PARAM_VAL -------------------- -------------------- i_name MIKE i_somedate 03-JAN-2012 i_some_num 1234
I could go on, but Deb has just forbade me from making any more “kneedless” puns on pain of having to live in the shed.
Share this:
The Anti-Kyte 