15 February 2013

Views, Datatypes and Nulls

Had an interesting little case come up today when helping a client re-write a view. This legacy application stored a date value as January 1, 1970 when they didn't have a date (basically using it as a de facto NULL). I did my best to lecture the client on why this was a horrible idea, but there was nothing we could do in the short term.

Anyway, he had a view that would use a DECODE function to convert a date of 1970/01/01 to NULL, otherwise just pass the date through. However, we noticed that the view schema said the field was defined as varchar2(18) instead of date. Here is my example using a view on DBA_OBJECTS:

SQL> create or replace view test_objects1 (object_name, created) as
  2  select object_name, decode (created
  3                          , to_date('1970/01/01','yyyy/mm/dd')
  4                          , null, created)
  5  from dba_objects;

View created.

SQL> desc test_objects1;
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 OBJECT_NAME                                        VARCHAR2(128)
 CREATED                                            VARCHAR2(18)

02 February 2013

Learning to Love the ADR - Part 4: Incident Packaging Service

This post is the fourth (and final) in a short series about Oracle 11g ADR. These posts are based on the presentations I gave at the NoCOUG Summer Conference in August 2011 and at the RMOUG Training Days in February 2012.


Any DBA that has had to file a support request with Oracle Support will be violently nodding their head in agreement when I ask if they've ever had Oracle Support ask for log file after trace file after log file after trace file. It's hard to not be cynical, especially when they sometimes request a file that was already provided! In 11g, Oracle has made some of that work easier by providing the Incident Packaging Service, or IPS, with the ADR. IPS lets you quickly and easily gather up diagnostic files related to a given incident, problem, or timeframe. You can also create empty packages and add whatever files you see fit.

It all starts with the IPS CREATE PACKAGE command. This creates a logical package (no package files are created at this point) in the ADR. Various options include:

  • adrci> ips create package incident <incident_id>;
  • adrci> ips create package problem <problem_id>;
  • adrci> ips create package problemkey "problem_key_string";
  • adrci> ips create package seconds <seconds_before_now>;
  • adrci> ips create package time 'start_timestamp' to 'end_timestamp';
  • adrci> ips create package;

It is definitely worth your while to review the output of help ips create package to get details on these, as I won't be touching most of them any further. The last command listed creates an empty package, after which you can use the ips add incident or ips add file commands to add data to the package before generating it.