Blekko /oracledb Needs You !

Despite popular opinion, there is more than one search engine. And I'm not talking Bing either. I do not use the MS word :) I mean blekko.

I do admit to being somewhat wedded to Google. I use GMail, Google Reader, Blogger, Sites, Docs. Oh, and the search engine. I'll also admit to once believing that Google Search (or rather, AltaVista, which was the big one before Google came into the picture) would be doomed to failure. Quite simply, I couldn't see how one site could hold a copy of the entire Internet. I still can't, but they come close enough to managing it.

Back then, Yahoo was in the picture with a human moderated directory of sites but without the ability to text search within those sites. It wasn't search as it is understood today. Blekko have merged the concept of the directory with the text search aspect, under its concept of /slashtags.

Part of the idea, as I understand it, is to keep out content farms and scrapers.

So a week or so ago, they talked to the folks at StackOverflow (my favorite Q&A forum) to get some of their experts in to organize some programming related slashtags. With a bit of debate, /oracledb has been set up as "our" slashtag.

I've put myself forward (and been accepted) as one editor for the slashtag. I've listed a bunch of useful sites in there which previously lived as my bookmarks or feed sources.

Please, try some searches on the /oracledb tag. If you find important sites missing, I can add them in if you throw me a comment. Here is the current site list. And some more reputable editors would be good too. ACEs or Oak Tablers have that extra credibility.

When does a foreign key not reference a primary key ?

Generally your foreign key will point to the primary key in the parent table. However it is actually possible to point a foreign key to any column, or set of columns, where uniqueness is enforced through a constraint.

One situation where this may be applicable is a type / subtype on your entities. I'm using the familiar employee, department and orders model as it was used in the Stackoverflow question that prompted this. Incidentally, the "Subscriber Content" (ie questions, answers and comments) on StackOverflow are all under licensed as Creative Commons, so there are no legal issues with copyright as might be experienced elsewhere.

The problem was that of ensuring orders could only be owned by certain types of employees, the salesmen. By the way, is anyone else worried that the abbreviation for Sales and Marketing is S&M. Now if you are a data modeling fundamentalist, you would normalize out 'SALESMAN' as a separate entity from 'EMPLOYEE', with an optional one-to-one relationship. I'm not saying that is the wrong solution, but simply offering an alternative which may offer practical benefits in some circumstances (eg reduced storage and de-duplication, and maybe tighter consistency in column statistics).

Firstly, I create the employee table. I add an extra field which is always populated for salesmen, and never for another type of employee. I enforce the value of this to be the same as the employee id, although this is not really essential as long as it is unique. And that uniqueness MUST be enforced by a constraint and not just by an index.


CREATE TABLE t_emp
  (emp_id number PRIMARY KEY, emp_name varchar2(20),
  dept_name varchar2(10),
  sales_emp_id number,
  CONSTRAINT sales_emp_id_sales_ck CHECK
      ((sales_emp_id IS NULL AND dept_name != 'SALES') OR
       (dept_name = 'SALES' AND sales_emp_id = emp_id
                            AND sales_emp_id IS NOT NULL)),
  CONSTRAINT sales_emp_id_uk UNIQUE (sales_emp_id));

I insert some employees, with the last three expected to fail due to the constraint violation.

     
INSERT INTO t_emp VALUES (1,'Alan','SALES',1);
INSERT INTO t_emp VALUES (2,'Bill','ACCOUNTS',NULL);
INSERT INTO t_emp VALUES (3,'Chuck','ACCOUNTS',3);
INSERT INTO t_emp VALUES (4,'Dan','SALES',NULL);
INSERT INTO t_emp VALUES (5,'Ellen','SALES',6);

SELECT * FROM t_emp;

Now I create the ORDERS table. Rather than use the primary key of the EMPLOYEES table, I use the unique salesman's identifier. This ensures that orders can only belong to a salesman. 


CREATE TABLE t_orders
  (ord_id number PRIMARY KEY,
  salesman number,
  CONSTRAINT salesman_fk FOREIGN KEY (salesman)
       REFERENCES t_emp(sales_emp_id));

Finally, a simple test to show that only the sales employees can own orders.


INSERT INTO t_orders VALUES (1,1);
INSERT INTO t_orders VALUES (2,2);
 

A game of Truth and Falsehood ?

Here's a quick quiz for you. I create the following table:
CREATE TABLE t_truth
  (truth_id   NUMBER NOT NULL,
  dare_id     NUMBER,
  CONSTRAINT truth_dare_id CHECK (truth_id = dare_id));

Which of the following statements will successfully insert a row into the table.

INSERT INTO t_truth VALUES (1,null);
INSERT INTO t_truth VALUES (2,2);
INSERT INTO t_truth VALUES (3,0);


.
.
.
.
.
.

Actually both the first and second statements succeed.

The check constraint condition "truth_id = date_id" will evaluate to unknown when dare_id is null. The constraint is only violated if the condition fails.

Because 1.25 isn't actually a number

Prompted by a stackoverflow post, here's a little demo of the role of NLS_NUMERIC_CHARACTERS in a TO_CHAR.


SQL> alter session set nls_numeric_characters=',.';


Session altered.


SQL> select to_char(1.25) from dual;


TO_C
----
1,25

So what happens ?

Firstly 1.25 might be a number to us, it isn't actually a NUMBER. It is a numeric literal representing the value "1 and a quarter". Oracle's first step is to convert it into its internal representation.

SQL> select dump(1.25) from dual;

DUMP(1.25)

---------------------

Typ=2 Len=3: 193,2,26

To paraphrase Crocodile Dundee, "Now, that's a NUMBER". Okay, it's actually the dump of the NUMBER but it is a condition of Australian citizenship that you work a Mick Dundee reference in at least once a year.

The same concept applied to date literals, such as  date '2011-02-13' but the 'date' keyword makes it more obvious to us that we are telling Oracle that it is something special.

The NLS values don't play any part in this conversion to the internal format. The internal value itself is NLS neutral so it doesn't have the concept of a period or a comma as a decimal separator.

The NLS settings do play a part in the TO_CHAR conversion from the internal format. That's where the comma is being formatted into the string. If you really want to be sure about the formatting, you can add this to the TO_CHAR, and the matter will be settled once and for all.

select to_char(1.25,'999.99','NLS_NUMERIC_CHARACTERS=.,') from dual;

Fluffy white Oracle clouds by the hour

Pay-by-the-hour options are becoming more common, with Amazon and Oracle are getting even more intimate in the next few months. Yes, you too will be able to pay for a quickie with the king of databases (or queen if you prefer that as a mental image).

No prices have been announced, so it will be interesting to see what they come up with. Anyone with their own licenses can still use them. Anyone who is already running Oracle on Amazon would find it easy (and legal) to throw up another one for testing (maybe Real Application Testing, if you've got the money) or perhaps year-end reporting.

It would also be handy for demos and development outside the scope of the OTN licenses.With the facility to push your RMAN backups to the S3 cloud, you may even have scope for using Amazon as a DR.

Of course if you're in that group who are chucking terabytes of data around on a daily basis, then your hardware is near and dear to your heart and all this cloud stuff is a load of vapour. And then there's whatever restrictions your application, industry or country has on storing data as bits and bytes floating rounds the US.

If nothing else, the hourly rates will be interesting to factor in to any future negotiations about licensing for those database servers you don't need running 24x7.

You can sign up to be pestered by marketing kept informed of developments through the Amazon Oracle page

Using Oracle's SERVERERROR to catch unlogged application errors

In an ideal world, when an application hits an exception it will write a log message indicating what it was doing and what error was reported.

In the real world, that doesn't always happen. There are reasons for not showing these details to the user, both from a user-interaction point of view (who wants to see an Oracle error message on a web site) and a security aspect (why tell them what database you use and, potentially, details about table and column structures). They should end up in a log file though.

But if the application developer's haven't done their job, you can do it for them with a SERVERERROR trigger.

If the server process returns an error to the client process the trigger is fired. It doesn't fire for "No Rows Returned" or if the error is caught and handled in PL/SQL. If you have a habit of using PL/SQL procedures which return a success/failure parameter with an error message, then stop. It is a bad idea.

My standard setup includes a table to which I can write debug or log messages.

I use a sequence generated id as a "key", but also have TIMESTAMP column . I also use a whole bunch of SYS_CONTEXT values as column defaults. I love SYS_CONTEXT defaults. Very handy for tracking batch jobs too.

REM Create a default sequence for ordering of data
CREATE SEQUENCE track_seq;

REM Create a table to which log text can be written
CREATE TABLE track_detail
  (ID                       NUMBER,     
  TRACK_TIME                TIMESTAMP,  
  DETAIL                    VARCHAR2(4000),
  ACTION                    VARCHAR2(256) DEFAULT SYS_CONTEXT('USERENV','ACTION'),
  AUTHENTICATED_IDENTITY    VARCHAR2(256) DEFAULT SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY'),
  AUTHENTICATION_METHOD     VARCHAR2(256) DEFAULT SYS_CONTEXT('USERENV','AUTHENTICATION_METHOD'),
  JOB_ID                    VARCHAR2(256) DEFAULT
                              NVL(SYS_CONTEXT('USERENV','BG_JOB_ID'),SYS_CONTEXT('USERENV','FG_JOB_ID')),
  CLIENT_IDENTIFIER         VARCHAR2(256) DEFAULT SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER'),
  CLIENT_INFO               VARCHAR2(256) DEFAULT SYS_CONTEXT('USERENV','CLIENT_INFO'),
  /* 11gR2 CURRENT_EDITION_ID        VARCHAR2(256) DEFAULT SYS_CONTEXT('USERENV','CURRENT_EDITION_ID'), */
  CURRENT_SCHEMA            VARCHAR2(256) DEFAULT SYS_CONTEXT('USERENV','CURRENT_SCHEMA'),
  CURRENT_SQL               VARCHAR2(256) DEFAULT SYS_CONTEXT('USERENV','CURRENT_SQL'),
  CURRENT_USER              VARCHAR2(256) DEFAULT SYS_CONTEXT('USERENV','CURRENT_USER'),
  DB_NAME                   VARCHAR2(256) DEFAULT SYS_CONTEXT('USERENV','DB_NAME'),
  HOST                      VARCHAR2(256) DEFAULT SYS_CONTEXT('USERENV','HOST'),
  IP_ADDRESS                VARCHAR2(256) DEFAULT SYS_CONTEXT('USERENV','IP_ADDRESS'),
  LANGUAGE                  VARCHAR2(256) DEFAULT SYS_CONTEXT('USERENV','LANGUAGE'),
  MODULE                    VARCHAR2(256) DEFAULT SYS_CONTEXT('USERENV','MODULE'),
  NLS_DATE_FORMAT           VARCHAR2(256) DEFAULT SYS_CONTEXT('USERENV','NLS_DATE_FORMAT'),
  NLS_DATE_LANGUAGE         VARCHAR2(256) DEFAULT SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE'),
  NLS_SORT                  VARCHAR2(256) DEFAULT SYS_CONTEXT('USERENV','NLS_SORT'),
  NLS_TERRITORY             VARCHAR2(256) DEFAULT SYS_CONTEXT('USERENV','NLS_TERRITORY'),
  OS_USER                   VARCHAR2(256) DEFAULT SYS_CONTEXT('USERENV','OS_USER'),
  PROXY_USER                VARCHAR2(256) DEFAULT SYS_CONTEXT('USERENV','PROXY_USER'),
  SERVER_HOST               VARCHAR2(256) DEFAULT SYS_CONTEXT('USERENV','SERVER_HOST'),
  SESSION_USER              VARCHAR2(256) DEFAULT SYS_CONTEXT('USERENV','SESSION_USER'),
  SID                       VARCHAR2(256) DEFAULT SYS_CONTEXT('USERENV','SID'),
  TERMINAL                  VARCHAR2(256) DEFAULT SYS_CONTEXT('USERENV','TERMINAL'));
GRANT SELECT ON track_detail TO PUBLIC;



I use an autonomous procedure to do the writing. I'm content with one that only does logging through a simple INSERT...VALUES without querying the database.


REM Create a procedure to write to the log table without interrupting or interfering with the current transaction
CREATE OR REPLACE PROCEDURE track (p_text IN VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO track_detail   (id, track_time, detail)
  VALUES (track_seq.NEXTVAL, SYSTIMESTAMP, p_text);
  COMMIT;
END;
/

REM Grant to all so it can be universally used. You may not want this.
GRANT EXECUTE ON track TO PUBLIC;


The trigger is pretty simple. I use a schema level one because I don't want it firing all over the database. I leave it disabled most of the time. If you have any automated processes for re-enabling disabled triggers, it will be safer to drop it when you don't need it.


It firstly grabs the entire error stack (not just the SQLERRM) but also logs the SQL statement that has failed.

REM -- Create trigger to log any errors from this user's sessions to table
REM -- Only errors raised to the client are picked up.
REM -- Doesn't work in Apex where errors are caught to generate HTML
CREATE OR REPLACE TRIGGER log_err AFTER servererror ON SCHEMA
DECLARE
  v_stack    VARCHAR2(2000) := SUBSTR(DBMS_UTILITY.FORMAT_ERROR_STACK,1,2000);
  v_num      NUMBER;
  v_sql_text ora_name_list_t;
BEGIN
  v_stack := TRANSLATE(v_stack,'''','"');
  track(v_stack);
  v_num  := ora_sql_txt(v_sql_text);
  BEGIN
    FOR i IN 1..v_num LOOP
      track(TO_CHAR(i,'0000')||':'||v_sql_text(i));
    END LOOP;
  EXCEPTION
    WHEN VALUE_ERROR THEN NULL;
  END;
END;
/

Finally a quick test:

TRUNCATE TABLE track_detail;
SELECT * FROM sdsd;
SELECT detail FROM track_detail;

All ready to go. But don't forget to drop or disable that trigger or your log table will get very big.