Constraints: ENABLE NOVALIDATE

Yesterday while perusing the Concepts Guide, I stumbled across the ENABLE NOVALIDATE keywords for the definition of a Foreign Key constraint. I've always known it was there, just never used it, or thought to use it.

It can be a big benefit while working on a legacy system.

Suppose you have a table, T_CHILD:

CREATE TABLE t_child
(
  child_id NUMBER(10)
    CONSTRAINT pk_childid PRIMARY KEY,
  soon_to_be_parent_id NUMBER(10)
);

INSERT INTO t_child
  ( child_id,
    soon_to_be_parent_id )
SELECT
    rownum,
    TRUNC( dbms_random.value( -9999, -1 ) )
FROM dual
  CONNECT BY LEVEL <= 10;

This table has been around for quite some time. You decide that you would like to constrain the values in the SOON_TO_BE_PARENT_ID column. First, here's the data that exists:

CJUSTICE@TESTING>SELECT * FROM t_child;

  CHILD_ID SOON_TO_BE_PARENT_ID
---------- --------------------
         1                -5560
         2                -1822
         3                -2499
         4                -7039
         5                -8718
         6                -1019
         7                -9997
         8                -9553
         9                -4477
        10                -1458

Now I'll create a table that will contain the values I want to constraint SOON_TO_BE_PARENT_ID to, call it a lookup or reference table.

CREATE TABLE t_parent
(
  parent_id NUMBER(10)
    CONSTRAINT pk_parentid PRIMARY KEY
);

I'll populate it with some data:

INSERT INTO t_parent( parent_id )
SELECT rownum
FROM dual
  CONNECT BY LEVEL <= 10;

CJUSTICE@TESTING>SELECT * FROM T_PARENT;

 PARENT_ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

Now I'll add the constraint that references the PARENT_ID column of T_PARENT

ALTER TABLE t_child
  ADD CONSTRAINT fk_parentid
  FOREIGN KEY ( soon_to_be_parent_id )
  REFERENCES t_parent( parent_id )
  ENABLE
  NOVALIDATE;

and rename the column to PARENT_ID:

ALTER TABLE t_child RENAME COLUMN soon_to_be_parent_id TO parent_id;

What will this do? I should no longer be able to enter a value into T_CHILD.PARENT_ID that does not exist in T_PARENT, but it will ignore anything that already exists.

INSERT INTO t_child
  ( child_id,
    parent_id )
VALUES
  ( 11,
    11 );

INSERT INTO t_child
*
ERROR at line 1:
ORA-02291: integrity constraint (CJUSTICE.FK_PARENTID) violated - parent key not found

Perfect! Now I'll add a value that does exist in T_PARENT.

INSERT INTO t_child
  ( child_id,
    parent_id )
VALUES
  ( 11,
    10 );

1 row created.

Win!

This is just another reminder why you must read the Concepts Guide. By the way, I found the quote I was looking for from Mr. Kyte (h/t @boneist)

"...if you simply read the Concepts Guide...and retain just 10%..., you’ll already know 90% more than most people do"

Classic: DBMS_APPLICATION_INFO

I've decided to post a few "classics" from long before anyone paid attention...as opposed to now, when 30 people pay attention. I originally posted this on 12/02/2007 at 10:04 PM which you can find here.

Instrumentation has something that I have come to rely on fairly heavily. I believe I first read about it on asktom, but the one that really spurred me on was this post on instrumentation on his personal blog.

Initially, I couldn't really wrap my head around instrumentation. I don't know why it was so difficult; I had a similar problem with sessions when I first started my career. I look back now and it just seems so obvious.

Now that I am doing datawarehouse work, nothing is fast. Fast to me is now one hour to load 30 or 40 million records. No more split second queries for me.

We currently use no tools. It's straight PL/SQL. Instrumentation of the code is ideal. Actually, it's more instrumentation to aid monitoring. The tool most easily used is provided by Oracle in the DBMS_APPLICATION_INFO package.

There are three subprograms that I use most, SET_MODULE, SET_ACTION and most importantly SET_SESSION_LONGOPS. I hadn't started using it until this year, I mainly stuck to the first two. SET_SESSION_LONGOPS is now part of my procedure/function template I've created in JDeveloper.

What it allows you to do is set a row in the v$session_longops view (I know it's not actually putting the row in the view...it's the underlying table, but I digress). You can then monitor how your job is doing.

Here's an example:

dbms_application_info.set_session_longops
  ( rindex => g_index,
    slno => g_slno,
    op_name => 'GETTING MEMBER DATA',
    sofar => 0,
    totalwork => l_table.COUNT + 1,
    target_desc => 'GETTING MEMBER DATA' );

g_index and g_slno are global variables in the package. l_table is a PL/SQL TABLE OF VARCHAR2.

Now you can monitor the progress of your job in v$session_longops!

Here's the query I use:

SELECT 
  username,
  sid,
  serial#,
  TO_CHAR( start_time, 'MM/DD/YYYY HH24:MI:SS' ) start_ti,
  time_remaining rem,
  elapsed_seconds ela,
  ROUND( ( sofar / REPLACE( totalwork, 0, 1 ) ) * 100, 2 ) per,
  sofar,
  totalwork work,
  message,
  target_desc
FROM v$session_longops
WHERE start_time >= SYSDATE - 1 
ORDER BY start_time DESC

Now you too can sit for hours and watch your job move incrementally forward!

But seriously, it does help tremendously to know where a job is at. You can further use the SET_MODULE and SET_ACTION calls to see a specific point in the processing (inside a loop).

Here's the code in context:

PROCEDURE get_member_data
IS
  l_exists INTEGER;
  TYPE table_of_lobs IS TABLE OF VARCHAR2(3);
  l_table TABLE_OF_LOBS := TABLE_OF_LOBS( 'COM', 'ORG' );
  l_count INTEGER := 0;
BEGIN
  --check to see if there is enrollment data, if not, move on
  SELECT COUNT(*)
  INTO l_exists
  FROM members
  WHERE rownum < 2;
  
  IF l_exists = 1 THEN--data exists, truncate and reload
  
    g_index := dbms_application_info.set_session_longops_nohint;
      
    EXECUTE IMMEDIATE 'TRUNCATE TABLE member_stg';

    g_audit_key := p_audit.begin_load
                    ( p_targettable => 'MEMBER_STG',
                      p_loadsource => 'MEMBER_SOURCE',
                      p_loadstatus => 'PRE',
                      p_loadprogram => 'GET_MEMBER_DATA',
                      p_commenttext => 'INSERT' );

    dbms_application_info.set_session_longops
      ( rindex => g_index,
        slno => g_slno,
        op_name => 'GETTING MEMBERS',
        sofar => 0,
        totalwork => l_table.COUNT + 1,
        target_desc => 'GETTING MEMBERS' );

  FOR i IN 1..l_table.COUNT LOOP
    l_count := l_count + 1;

    INSERT INTO member_stg
    SELECT *
    FROM members;

    g_total_rows_affected := g_total_rows_affected + sql%rowcount;

    COMMIT;

    dbms_application_info.set_session_longops
      ( rindex => g_index,
        slno => g_slno,
        op_name => 'GETTING MEMBERS',
        sofar => l_count,
        totalwork => l_table.COUNT + 1,
        target_desc => 'GETTING MEMBERS' );
  
  END LOOP;

  p_audit.end_load
    ( p_auditkey => g_audit_key,
      p_loadstatus => 'SUC',
      p_rowsuccess => g_total_rows_affected );
  
  gather_table_stats
    ( p_tablename => 'MEMBER_STG',
      p_schemaname => 'MYHOME' );

  dbms_application_info.set_session_longops
    ( rindex => g_index,
      slno => g_slno,
      op_name => 'GETTING MEMBERS',
      sofar => l_count + 1,
      totalwork => l_table.COUNT + 1,
      target_desc => 'GETTING MEMBERS' );

  END IF;

EXCEPTION
  WHEN others THEN
    p_audit.failed_load
      ( p_auditkey => g_audit_key,
        p_comments => SQLCODE || ' ' || SQLERRM );
    RAISE;

END get_member_data;

Oracle Concepts: Data Integrity Rules

I'm reading through the Concepts manual again as mentioned on last week.

I'm going to make a small effort to post some of the key concepts here over the next couple of weeks. If you've read through the Concepts Guide before, this can serve as a brief refresher. If not, good, you're exposed to something new.

Data Integrity Rules

This section describes the rules that can be applied to table columns to enforce different types of data integrity.

Null rule: A null rule is a rule defined on a single column that allows or disallows inserts or updates of rows containing a null (the absence of a value) in that column.

Unique column values: A unique value rule defined on a column (or set of columns) allows the insert or update of a row only if it contains a unique value in that column (or set of columns).

Primary key values: A primary key value rule defined on a key (a column or set of columns) specifies that each row in the table can be uniquely identified by the values in the key.

Referential integrity rules: A referential integrity rule is a rule defined on a key (a column or set of columns) in one table that guarantees that the values in that key match the values in a key in a related table (the referenced value).

Referential integrity also includes the rules that dictate what types of data manipulation are allowed on referenced values and how these actions affect dependent values. The rules associated with referential integrity are:

* Restrict: Disallows the update or deletion of referenced data.
* Set to null: When referenced data is updated or deleted, all associated dependent data is set to NULL.
* Set to default: When referenced data is updated or deleted, all associated dependent data is set to a default value.
* Cascade: When referenced data is updated, all associated dependent data is correspondingly updated. When a referenced row is deleted, all associated dependent rows are deleted.
* No action: Disallows the update or deletion of referenced data. This differs from RESTRICT in that it is checked at the end of the statement, or at the end of the transaction if the constraint is deferred. (Oracle Database uses No Action as its default action.)

Complex integrity checking: A user-defined rule for a column (or set of columns) that allows or disallows inserts, updates, or deletes of a row based on the value it contains for the column (or set of columns).

Reading on past the brief section to the Constraint States I found this nugget:

ENABLE NOVALIDATE means that the constraint is checked, but it does not have to be true for all rows. This allows existing rows to violate the constraint, while ensuring that all new or modified rows are valid.

This is a great tool for legacy systems. You have data in the column(s) that you can't really do anything with, but you want to insure that all future data that goes in that particular column(s) matches the parent key.

Of course the ideal is to somehow clean the data up, but you don't always have that option. This is a good first step towards to overall cleanup of your legacy system.

APEX: URL Syntax

This is more for my own edification than anything. I always forget which place the ClearCache inhabits.

f?p=App:Page:Session:Request:Debug:ClearCache:itemNames:PrinterFriendly

With numbers so I don't have to count every time:

f?p=App1:Page2:Sess3:Requ4:Debug5:Cache6:items7:printer8

f?p=1:2:3:4:5:6:7:8

Link to the documentation is here.

Oracle Concepts

Years and years of reading AskTom, if there's one thing that I really come away with, it's read. RTFM. Specifically though, he's telling everyone to read the Concepts Guide. Paraphrasing:

If you can get a good look at the Concepts Guide by sticking your head...

That's not the one.

If you read the Concepts Guide you'll have 10% of the knowledge that 90% don't have.

Nope.

If 90% of the people would read 10% of the Concepts Guide, they'd be able to program in C...?

Seriously, I need help.

If you do know the saying I'm talking about, please share. I can't seem to find it anywhere.

Ultimately it doesn't matter what was said. I'm reading the Concepts Guide again, browsing through it really, trying to find something new. Then I noticed this:


I know it's a "feature" and all, but does it have to be so prominently displayed? Couldn't it be buried in the Appendix of the PL/SQL Guide or something? This is especially important since Mr. Kyte is telling all the new people to read the Concepts Guide.

Perhaps the new people figure it out, the ones that have read the Concepts Guide anyway. I've run across too many "experienced" database developers that can't create apps without them. My guess is that they have barely hit any of the documentation, let alone the Concepts Guide.

A NULL Observation, III

Part I here.

Part II here.

OK, we have a winner. Coskan Gundogar suggested in the comments, that using the MODIFY clause of the ALTER TABLE statement should work. Let's see:

desc t_null

Name                    Null?    Type
----------------------- -------- ----------------
COL1                             VARCHAR2(30)

CJUSTICE@TESTING>ALTER TABLE T_NULL DROP CONSTRAINT nn_col1_tn;

Table altered.

CJUSTICE@TESTING>ALTER TABLE t_null MODIFY ( col1 CONSTRAINT nn_col1_tn NOT NULL );

Table altered.

CJUSTICE@TESTING>@DESC T_NULL
Name                    Null?    Type
----------------------- -------- ----------------
COL1                    NOT NULL VARCHAR2(30)

Voila!

Much better. The question still remains as to what's going on in the background. Until now, I had never thought that white space mattered in anything Oracle. Very strange.

Maybe I can get Miladin to dig through the internals and see what's really going on.

A NULL Observation, II

Shoot me, I couldn't let this go. Plus, I needed a reason to test drive my new sandbox.

The part about the space had me a little perplexed:

CJUSTICE@TESTING>SELECT
  2    table_name,
  3    constraint_name,
  4    constraint_type,
  5    search_condition,
  6    status
  7  FROM user_constraints
  8  ORDER BY table_name;

TABLE_NAME           CONSTRAINT_NAME      C SEARCH_CONDITION     STATUS
-------------------- -------------------- - -------------------- --------
T_NOT_NULL           NN_COL1_TNN          C "COL1" IS NOT NULL   ENABLED
T_NOT_NULL_CHECK     NN_COL1_TNNC         C  "COL1" IS NOT NULL  ENABLED
T_NULL               NN_COL1_TN           C  col1 IS NOT NULL    ENABLED

Here's the DDL that created that constraint:

ALTER TABLE t_not_null_check DROP CONSTRAINT nn_col1_tnnc;

ALTER TABLE t_not_null_check
  ADD CONSTRAINT nn_col1_tnnc
  CHECK ( "COL1" IS NOT NULL );

Note the white space there. I like putting a space after a parenthesis as I believe it makes it slightly easier to read.

With that in mind, watch this:

ALTER TABLE t_not_null_check DROP CONSTRAINT nn_col1_tnnc;

ALTER TABLE t_not_null_check
  ADD CONSTRAINT nn_col1_tnnc
  CHECK ("COL1" IS NOT NULL);

Note that I removed the space between the "(" and the """. Here's what it looks like:

SELECT 
  table_name, 
  constraint_name, 
  constraint_type, 
  search_condition
FROM user_constraints
ORDER BY table_name;

TABLE_NAME       CONSTRAINT_N C SEARCH_CONDITION
---------------- ------------ - ------------------------------
T_NOT_NULL       NN_COL1_TNN  C "COL1" IS NOT NULL
T_NOT_NULL_CHECK NN_COL1_TNNC C "COL1" IS NOT NULL
T_NULL           NN_COL1_TN   C "COL1" IS NOT NULL

CJUSTICE@TESTING>desc t_not_null_check
 Name    Null?   Type
 ----------------------- -------- ----------------
 COL1      VARCHAR2(30)

The leading space is gone now. Null? still shows up as NULL. I would assume that most GUI apps get their data from USER/ALL/DBA_TAB_COLUMNS, so let's take a look:

SELECT 
  table_name, 
  column_name, 
  nullable
FROM user_tab_columns
ORDER BY table_name;

TABLE_NAME       COLUMN_NAME                    N
---------------- ------------------------------ -
T_NOT_NULL       COL1                           N
T_NOT_NULL_CHECK COL1                           Y
T_NULL           COL1                           Y

How about USER_TAB_COLS?

SELECT 
  table_name, 
  column_name, 
  nullable
FROM user_tab_cols
ORDER BY table_name;

TABLE_NAME       COLUMN_NAME                    N
---------------- ------------------------------ -
T_NOT_NULL       COL1                           N
T_NOT_NULL_CHECK COL1                           Y
T_NULL           COL1                           Y

Nothing to see there.

So, there is a difference between defining a NOT NULL constraint using either the NOT NULL or CHECK syntax. In USER_CONSTRAINTS, there are 4 distinct constraint types:
1.  P = PRIMARY
2.  U = UNIQUE
3.  R = REFERENCE or FOREIGN KEY
4.  C = CHECK

What's the lesson here? Well, if doing analysis, you can't just depend on using the DESCRIBE command from SQL*PLus to see what is required and what is not. Of course you can't depend on that for everything. Interesting "problem" none-the-less.

Update
Part III (the final solution) is here.

A NULL Observation

As I've been doing a lot of analysis lately, I've found a slightly annoying "problem."

I typically use JDev or SQLDev to see details on a table, more than just a DESCRIBE from SQL*Plus can give me anyway.

This "problem" relates to how NULL columns are displayed, both via DESCRIBE and the previously mentioned tools.

First up, my favorite, the table definition with inline constraints.

CREATE TABLE t_not_null
(
  col1 VARCHAR2(30)
    CONSTRAINT nn_col1_tnn NOT NULL
);

Let's see how it looks in a SQL Worksheet (Columns):
Constraints:
SQL*Plus (DESCRIBE):

CJUSTICE@TESTING>desc t_not_null
 Name                    Null?    Type
 ----------------------- -------- ----------------
 COL1                    NOT NULL VARCHAR2(30)

Now I'll create a different table, this time instead of using NOT NULL, I'll use the CHECK syntax.

CREATE TABLE t_not_null_check
(
  col1 VARCHAR2(30)
    CONSTRAINT nn_col1_tnnc CHECK ( col1 IS NOT NULL )
);

Columns:
Nullable = Yes? Hmmm...

Constraints:

DESCRIBE

CJUSTICE@TESTING>desc t_not_null_check
 Name                    Null?    Type
 ----------------------- -------- ----------------
 COL1                             VARCHAR2(30)

Interesting, it doesn't show up in the "Null?" column like it did with the NOT NULL syntax used above.

Now I'll create a table with no inline constraint defined.

CREATE TABLE t_null
(
  col1 VARCHAR2(30)
);

I know (famous last words) I can't use the NOT NULL syntax in an out-of-line constraint:

ALTER TABLE t_null
  ADD CONSTRAINT nn_col1_tn NOT NULL;
  ADD CONSTRAINT nn_col1_tn NOT NULL
                            *
ERROR at line 2:
ORA-00904: : invalid identifier

So I use the CHECK syntax:

ALTER TABLE t_null
  ADD CONSTRAINT nn_col1_tn
  CHECK ( col1 IS NOT NULL );

Table altered.

Columns:
Constraints:
DESCRIBE

CJUSTICE@TESTING>desc t_null
 Name                    Null?    Type
 ----------------------- -------- ----------------
 COL1                             VARCHAR2(30)

Very odd.

Let's look at the dictionary:

SELECT
  table_name,
  constraint_name,
  constraint_type,
  search_condition,
  status
FROM user_constraints
ORDER BY table_name;

TABLE_NAME           CONSTRAINT_NAME      C SEARCH_CONDITION     STATUS
-------------------- -------------------- - -------------------- --------
T_NOT_NULL           NN_COL1_TNN          C "COL1" IS NOT NULL   ENABLED
T_NOT_NULL_CHECK     NN_COL1_TNNC         C  col1 IS NOT NULL    ENABLED
T_NULL               NN_COL1_TN           C  col1 IS NOT NULL    ENABLED

Interesting, I wonder if the fact that it's not UPPERcased and in quotes?

CJUSTICE@TESTING>DROP TABLE t_not_null_check;

Table dropped.

Elapsed: 00:00:00.04
CJUSTICE@TESTING>CREATE TABLE t_not_null_check
  2  (
  3    col1 VARCHAR2(30)
  4      CONSTRAINT nn_col1_tnnc CHECK ( "COL1" IS NOT NULL )
  5  );

Table created.

DESCRIBE

CJUSTICE@TESTING>DESC T_NOT_NULL_CHECK
 Name                    Null?    Type
 ----------------------- -------- ----------------
 COL1                             VARCHAR2(30)

OK, Null? is still...NULL.

CJUSTICE@TESTING>SELECT
  2    table_name,
  3    constraint_name,
  4    constraint_type,
  5    search_condition,
  6    status
  7  FROM user_constraints
  8  ORDER BY table_name;

TABLE_NAME           CONSTRAINT_NAME      C SEARCH_CONDITION     STATUS
-------------------- -------------------- - -------------------- --------
T_NOT_NULL           NN_COL1_TNN          C "COL1" IS NOT NULL   ENABLED
T_NOT_NULL_CHECK     NN_COL1_TNNC         C  "COL1" IS NOT NULL  ENABLED
T_NULL               NN_COL1_TN           C  col1 IS NOT NULL    ENABLED

Why is there that extra space in front of "COL1" IS NOT NULL?

I ended my investigation there as it seems to be a waste of time. I just found it interesting that there was a difference in how you defined a NOT NULL constraint and whether or not it would show up in the DESCRIBE command. Anyone out there notice this before?

Read Part II here.

Part III (the final solution) is here.

Virtual Box, OEL, 11g, ApEx

Finally.

After many months and many attempts, I have managed to install a database on a non-windows platform. I think I deserve a little credit. ;)

In January, I began installing Ubuntu Intrepid Ibex on all of my home computers. What this meant for me is that I was often without one because I screwed something up. I even got so far as to install Oracle XE on Ubuntu which took 2 blog posts.

Just before COLLABORATE 09, I installed Jaunty Jackalope on my laptop. Then I got the crazy idea to install Oracle on Ubuntu. I ended up having to wipe the hard drive and start fresh. I have no doubt that it (Oracle) can be installed on Ubuntu, it just takes someone much more experienced than I.

Armed with a fresh install, I was going the virtual route. Inspired, I believe, by this comment (Aman Sharma).
1.  Install Sun's Oracle's VirtualBox
2.  Download the latest version of Oracle Enterprise Linux (v5)

I think that's the point I realized my first installation of Jaunty was hosed, and by hosed I mean unrecoverable by me. I couldn't see the 2nd partition, the one with all the room.

3.  Create OEL virtual machine
4.  Follow this guide to get it ready to install the Oracle database (seriously? Why does OEL not come ready for the database server?)


4a.    There's no mention of opening up the firewall in that article. It's probably obvious to most...
5.  Connect to Oracle from the host machine
6.  ApEx? Which version am I running? 3.0? That won't do. Download ApEx 3.2
7.  Install ApEx
8.  Configure Embedded PL/SQL Gateway for port 80
8a.    Ports below 1024 are reserved for privileged processes...Configure Embedded PL/SQL Gateway for port 8080
9.  Connect from host machine.


9a.    Login to ApEx.


10.  WIN!

PL/SQL: Split Key-Value Pairs

In a continuation from a previous post, PL/SQL: Split URL Parameters, I give you the key/value splitting! (cheese, I know...I'm bored).

In that previous post, I was taking a URL string and splitting it based on the delimiter, which is typically the ampersand &. In ApEx it is the colon :. I'd take a wild guess and say there are others, but I'm not going to look them up. An example string looks like this:

param1=value1¶m2=value2

Etc. Etc. Etc.

The output of that split looks like this:

param1=value1
param2=value2

Now you need that string parsed. Instead of just a string being returned however, you'd like to know the name of the parameter as well, thus key/value. Desired format looks like this:

KEY         VALUE
----------- -----------
param1      value1
param2      value2

First up, I'll create the SQL objects (user defined types):

CREATE TYPE r_key_value AS OBJECT
(
  key VARCHAR2(100),
  value VARCHAR2(100)
)
/

CREATE TYPE t_key_value AS TABLE OF r_key_value
/

I could easily do this using PL/SQL tables, but I have future plans.

DECLARE
  l_string VARCHAR2(1000) := 'param1=value1¶m2=value2';
  l_table T_KEY_VALUE := T_KEY_VALUE();
  l_delimiter VARCHAR2(5) := '&';
  l_delimiter_length INTEGER := LENGTH( l_delimiter );
  l_key VARCHAR2(100);
  l_value VARCHAR2(100);
  l_keyvalue VARCHAR2(200);
  l_counter INTEGER := 0;
  l_new VARCHAR2(4000);
  l_start INTEGER := 1;
  l_end INTEGER := 0;

This will eventually turn into a Function, but I'm just doing some smoke testing now to get the logic right. More extensive testing will be performed in the future with SQLUnit.

BEGIN
  IF SUBSTR( l_string, -1, 1 ) <> l_delimiter THEN
    l_string := l_string || l_delimiter;
  END IF;

  l_new := l_string;
  p( L_NEW );

I still haven't figured out the best way to grab the last token without appending the delimiter onto the end. It feels like a kludge, but for now, it works.

  LOOP
    l_counter := l_counter + 1;
    l_end := INSTR( l_new, l_delimiter, 1 );
    l_keyvalue := SUBSTR( l_new, 1, l_end - 1 );
    EXIT WHEN l_keyvalue IS NULL;
    
    l_table.EXTEND(1);
    l_key := SUBSTR( l_keyvalue, 1, INSTR( l_keyvalue, '=' ) - 1 );
    l_value := SUBSTR( l_keyvalue, INSTR( l_keyvalue, '=' ) + 1 );
    l_table(l_counter) := R_KEY_VALUE( l_key, l_value );
    l_start := l_start + ( l_end + ( l_delimiter_length - 1 ) );
    l_new := SUBSTR( l_string, l_start );
  END LOOP;

Regular expressions would be the best fit here. Until I learn them (yes, I hear you, "Isn't this the perfect opportunity?"), I'm going to do it the hard way.

Finally, just printing out to the console so I can see the results.

  p( 'table counter:  ' || l_table.COUNT );
  FOR I IN 1..l_table.COUNT LOOP
    p( 'key:    ' || l_table(i).key );
    p( 'value:  ' || l_table(i).value );
    p( '' );
  END LOOP;

Run it and I get the following:

CJUSTICE@TESTING>@C:\TEMP\S
table counter:  2
key:    param1
value:  value1
key:    param2
value:  value2

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07

Now I just need to wrap this up into package form and I'm all done. That will be another post with the unit tests provided.

Update
So after rereading this post, I realized that I just confused it with the previous post on splitting URL strings. Probably the best solution would be to combine these 2 functions, or at least pipe out the key/value pairs from the main function (previous post). Did I catch a niner in there?

Data Grids for Database Developers

Back at COLLABORATE, I met one Patrick Peralta, member of the Coherence team (reason #109 to go to conferences).

I spent about 2 hours with Mr. Peralta that day and had quite a bit of fun. I learned a couple of new tricks (JRockit for one) and got a slightly better understanding of Coherence.

I emphasized "slightly" because I still don't quite know how to use it. Caching data, I get that part. I guess I do understand it from a high-level perspective...it's the where part that I haven't quite grasped yet.

One thing I asked Mr. Peralta to do for me was to right it up, blog it. Show me (us) how and when to use it. Through twitter, he's shared with me some links. Specifically this one on how to define a Data Grid.

Finally (he did not mention that he likes to procrastinate), he's written something up, An Introduction to Data Grids for Database Developers.

Smarter Caching

An obvious (or maybe not so obvious depending on who you ask) first step in scaling a database application is to cache as much as you can. This is fairly easy to do if you have a single app server hitting a database. It becomes more interesting however as you add more app servers to the mix. For instance:

* Is it OK if the caches on your app servers are out of sync?
* What happens if one of the app servers wants to update an item in the cache?
* How do you minimize the number of database hits to refresh the cache?
* What if you don’t have enough memory on the app server to cache everything?

It's a very interesting technology. I would highly encourage you to click through and give it a go. Leave comments here or abroad.

Oracle's New INSERT Syntax

Not really Oracle's...just mine.

I'm busy debugging

  VALUES
  *
ERROR at line 19:
ORA-00947: not enough values

I manually count the columns and they're the same. Run it again.

  VALUES
  *
ERROR at line 19:
ORA-00947: not enough values

I then cut and paste both "halves" of the INSERT statement into excel so I can get a row-by-row compare.


Everything looks good, go!

  VALUES
  *
ERROR at line 19:
ORA-00947: not enough values

WTF?

I still haven't figured it out, but that excel picture gave me an idea...

I tend to put one column on each line, for large tables, this takes up quite a bit of vertical space. I've seen others put columns (and values) on the same line. Just looks ugly to me. Here's what this table looks like:

INSERT INTO my_table
  ( id,
    create_date,
    update_date,
    col1,
    col2,
    col3,
    col4,
    col5,
    col6,
    col7,
    col8,
    col9,
    col10,
    col11,
    col12,
    col13,
    col14 ) 
VALUES 
    seq.nextval,
    SYSDATE,
    SYSDATE,
    'A',
    'SOMETHING',
    'SOMETHING',
    'SOMETHING',
    'SOMETHING',
    'SOMETHING',
    'SOMETHING',
    'SOMETHING',
    'SOMETHING',
    'SOMETHING',
    'SOMETHING',
    'SOMETHING',
    'SOMETHING',
    'SOMETHING' );

Wouldn't it be cool if you could do something like this though?

INSERT INTO my_table
  ( id => seq.nexval,
    create_date => SYSDATE,
    update_date => SYSDATE, 
    col1 => 'A',
    col2 => 'SOMETHING',
    col3 => 'SOMETHING',
    col4 => 'SOMETHING',
    col5 => 'SOMETHING',
    col6 => 'SOMETHING',
    col7 => 'SOMETHING',
    col8 => 'SOMETHING',
    col9 => 'SOMETHING',
    col10 => 'SOMETHING',
    col11 => 'SOMETHING',
    col12 => 'SOMETHING',
    col13 => 'SOMETHING',
    col14 => 'SOMETHING' );

1. You'd save space.
2. It would be easier to read and
3. It would be easier to debug

Thoughts?

update:
I did solve my problem, I was missing the opening parenthesis in the VALUES clause.

update 2:
I created an "Idea" over at Oracle Mix, check it out and vote for it here.

PL/SQL: Split URL Parameters

I've always had this phobia of anything to do with strings. Might be because I find it hard to think that way...and by "that way" I have no idea what I'm talking about.

Whenever I come across this problem, my first instinct is to go through every character and build a string, something like this:

DECLARE
  l_string VARCHAR2(100) := 'THIS_LITTLE_STRING';
  l_character VARCHAR2(1);
BEGIN
  FOR i IN 1..LENGTH( l_string ) LOOP
    l_character := l_character || SUBSTR( l_string, i, 1 );
  END LOOP;
END;
/

Of course that means I have to go through the loop at least 18 times. How could I loop less?

Well, I would need to know the delimiter first, in this case, it will be the underscore character:

DECLARE
  l_string VARCHAR2(100) := 'THIS_LITTLE_STRING';
  l_character VARCHAR2(1);
  l_delimiter VARCHAR2(1) := '_';
BEGIN
...

OK, that helps. So what can I do with that?

DECLARE
  l_string VARCHAR2(100) := 'THIS_LITTLE_STRING';
  l_new_string VARCHAR2(100) := l_string;
  l_token VARCHAR2(30);
  l_character VARCHAR2(1);
  l_delimiter VARCHAR2(1) := '_';
BEGIN
  LOOP
    EXIT WHEN l_new_string IS NULL;
    l_token := SUBSTR( l_new_string, 1, INSTR( l_new_string, '_', 1 ) );
...

OK, now I remember what I always get confused. There seems to be this endless stream of SUBSTR and INSTR involved. It's difficult to follow...for me anyway. So here's what I've come up with for now. I am aware of regular expressions and the DBMS_UTILITY.COMMA_TO_TABLE procedures, but for some reason, I like to reinvent the wheel. No, not really, I just find it challenging. More challenging would be for me to start using regular expressions...Anyway, here goes my attempt at splitting a URL string:

DECLARE
  TYPE my_table IS TABLE OF VARCHAR2(100);
  l_table MY_TABLE := MY_TABLE();
  l_string VARCHAR2(1000) := 'TESTING=YES&&BOLLOCKS=SOMETHING&&&testing=DF';
  l_keyvalue VARCHAR2(100);
  l_delimiter VARCHAR2(5) := '&&';
  l_delimiter_length INTEGER := LENGTH( l_delimiter );
  l_counter INTEGER := 0;
  l_new VARCHAR2(4000);
  l_start INTEGER := 1;
  l_end INTEGER := 0;
BEGIN
  IF SUBSTR( l_string, -1, 1 ) <> l_delimiter THEN
    l_string := l_string || l_delimiter;
  END IF;

  l_new := l_string;

  LOOP
    l_counter := l_counter + 1;
    l_end := INSTR( l_new, l_delimiter, 1 );
    l_keyvalue := SUBSTR( l_new, 1, l_end - 1 );
    EXIT WHEN l_keyvalue IS NULL;
    l_table.EXTEND(1);
    l_table(l_counter) := l_keyvalue;
    l_start := l_start + ( l_end + ( l_delimiter_length - 1 ) );
    l_new := SUBSTR( l_string, l_start );
  END LOOP;
  p( 'table counter:  ' || l_table.COUNT );
  FOR I IN 1..l_table.COUNT LOOP
    p( 'string:  ' || l_table(i) );
  END LOOP;
END;
/

CJUSTICE@TESTING>@T
table counter:  3
string:  TESTING=YES
string:  BOLLOCKS=SOMETHING
string:  &testing=DF

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

Voila! My own URL String Tokenizer.

Now I just have to write something that will split up the key/value pairs...

SQLUnit: Database Unit Testing

What feels like a thousand years ago I used SQLUnit to do create and perform unit tests on database stored procedures. Lots of them. To the point where that's almost all I was doing. I both hated it and loved it. Hated it because it was boring, tedious, mind-numbing work. Loved it because it gave me confidence in the code I was writing and, once all the tedium was done, could be done quickly. A small change could be immediately tested to see it's effect.

Something else happened too, I began to write code thinking about the tests. Every single line of code was a potential test. If I do this, how will I test it? If I do that, how will I test it? Where do the tests end? You could theoretically test forever...and then I would inevitably fall down that rabbit hole.

Like I was saying, it changed the way I wrote code. Test Driven Development without writing the tests first (which I can hardly stand). That was a good thing because I began to anticipate certain errors. On an INSERT statement with a sequence generated Primary Key, should I trap the exception in code and give it my own error number? Or just let the ORA-00001 propagate up? I just let the Oracle error come up, no need to create my own for that. There's something seriously wrong if you get an error in that case and the whole world should know about it.

Speaking of exceptions, use them, throw them, don't trap them (mostly) and do something else. Log it and then RAISE it. I think it's good for every single developer to know when something is messed up. I've spent far too much time trying to debug something because all the errors are being trapped and processing just continues. I've been lucky on some occasions to have error logging, at least...

What is SQLUnit?

SQLUnit is a regression and unit testing harness for testing database stored procedures. An SQLUnit test suite would be written as an XML file. The SQLUnit harness, which is written in Java, uses the JUnit unit testing framework to convert the XML test specifications to JDBC calls and compare the results generated from the calls with the specified results.

Unfortunately it has not been worked on in almost 3 years, but I'd say it's in pretty good shape. There's support for Procedures, Functions, Ref Cursors and User Defined Types (though I haven't tried this out yet). The current version is 5.0. The last version I worked on consistently was 1.3 I believe. A colleague (who now works for Oracle) from my early days of SQLUnit testing even contributed some Oracle specific items.

I used it to build the database for CABEZE, my first, unsuccessful, try at my own business. It was nice because I was building it from scratch, so I could build all the test data along with it (no testing with production data...though there wasn't really much of that anyway), set up the database (create test data), run the tests and then tear it down back to it's original state (empty). Unfortunately the systems I've worked on since weren't empty and testing with production, or semi-production (cleansed) data was the only viable alternative.

Which brings me to now. I'm trying to reacquaint myself with the tool and writing test cases for various procedures. I ran into an issue create test cases because all of our credit card numbers were scrambled. Everything failed with an Invalid Card Number. Barnacles.

Why not create a routine that would generate "real" credit card numbers, more specifically, numbers of the appropriate length that had a check digit? So credit cards use the Luhn formula which is supposed to prevent certain transposition errors.

The Luhn algorithm will detect any single-digit error, as well as almost all transpositions of adjacent digits. It will not, however, detect transposition of the two-digit sequence 09 to 90 (or vice versa). It will detect 7 of the 10 possible twin errors (it will not detect 22 ↔ 55, 33 ↔ 66 or 44 ↔ 77).

With CABEZE, I had written my own PL/SQL card number generator (and validator), but I didn't blog at the time and seem to have lost that code. So I tried writing it again.

Since I'm using SQLUnit, what a great way to demonstrate the power of the tool. It's a somewhat complex (to me anyway) formula, so writing up tests for it should help me to figure it out. Here's the code to create a check digit based on the Luhn formula.

CREATE OR REPLACE
FUNCTION create_check_digit( p_card_number IN NUMBER ) RETURN NUMBER
IS
  TYPE t_digits IS TABLE OF INTEGER;
  l_table T_DIGITS := T_DIGITS();
  l_count INTEGER := 0;
  l_num INTEGER;
  l_digit INTEGER;
  l_odd INTEGER := 0;
  l_even INTEGER := 0;
  l_sum INTEGER := 0;
  l_check_digit INTEGER;
BEGIN
  IF p_card_number IS NULL THEN
    raise_application_error( -20001, 'you must provide a card number' );
  END IF;

  FOR i IN REVERSE 1..LENGTH( p_card_number ) LOOP
    l_count := l_count + 1;
    l_table.EXTEND(1);
    l_table( l_count ) := SUBSTR( p_card_number, i, 1 );
  END LOOP;

  FOR i IN 1..l_table.COUNT LOOP
    l_digit := l_table(i);
    IF MOD( i, 2 ) > 0 THEN
      l_num := l_digit * 2;
      IF l_num > 9 THEN
        FOR i IN 1..LENGTH( l_num ) LOOP
          l_odd := l_odd + SUBSTR( l_num, i, 1 );
        END LOOP;
      ELSE
        l_odd := l_num;
      END IF;
      p( 'odd:  ' || l_odd );
    ELSE
      l_even := l_digit;
    END IF;
    l_sum := l_sum + l_odd + l_even;
    p( 'l_sum:  ' || l_sum );
    l_odd := 0;
    l_even := 0;
  END LOOP;
  l_check_digit := ABS( ( CEIL( MOD( l_sum / 10, 10 ) ) * 10 ) - l_sum );
  p( 'check digit:  ' || l_check_digit );
  p( 'l_sum:  ' || l_sum );
  p( p_card_number || l_check_digit );
  RETURN l_check_digit;
END create_check_digit;
/

I have no doubt it could be done easier, specifically using regular expressions. This is just my first go at it...so don't give me a hard time...any other solutions are welcome in the comments. ;)

Here's the output of my final test run:

[sqlunit] *** Running SQLUnit file: p_cc.xml
[sqlunit] Getting connection(DEFAULT)
[sqlunit] Setting up test...
[sqlunit] Running test[1]: PASSING NULL (125ms)
[sqlunit] Running test[2]: VALID CARD NUMBER (4992739871) (15ms)
[sqlunit] Running test[3]: VALID CARD NUMBER (4012888888881881) (16ms)
[sqlunit] Running test[4]: VALID CARD NUMBER (4111111111111111) (0ms)
[sqlunit] Running test[5]: VALID CARD NUMBER (4222222222222) (15ms)
[sqlunit] Running test[6]: RANDOM (1) NUMBER (5) (0ms)
[sqlunit] Running test[7]: RANDOM (2) NUMBER (55) (0ms)
[sqlunit] Running test[8]: RANDOM (3) NUMBER (557) (16ms)
[sqlunit] Running test[9]: RANDOM (4) NUMBER (5579) (0ms)
[sqlunit] Running test[10]: RANDOM (5) NUMBER (65579) (0ms)
[sqlunit] Running test[11]: RANDOM (14) NUMBER (12345678965579) (16ms)
[sqlunit] Running test[12]: RANDOM NUMBER (5498975) (0ms)
[sqlunit] Tearing down test...

I was able to run a variety of different tests in seconds. A previous test run looked like this:

[sqlunit] *** Running SQLUnit file: p_cc.xml
[sqlunit] Getting connection(DEFAULT)
[sqlunit] Setting up test...
[sqlunit] Running test[1]: PASSING NULL (109ms)
[sqlunit] Running test[2]: VALID CARD NUMBER (4992739871) (109ms)
[sqlunit] Assertion "outparams-equal" failed (6(NUMERIC) != 1(NUMERIC) at outparams[0])
[sqlunit] *** expected:
[sqlunit] 
[sqlunit]   6
[sqlunit] 
[sqlunit] *** but got:
[sqlunit] 
[sqlunit]   1
[sqlunit] 
[sqlunit]
[sqlunit] Running test[3]: VALID CARD NUMBER (4012888888881881) (0ms)
[sqlunit] Running test[4]: VALID CARD NUMBER (4111111111111111) (0ms)
[sqlunit] Running test[5]: VALID CARD NUMBER (4222222222222) (0ms)
[sqlunit] Assertion "outparams-equal" failed (2(NUMERIC) != 0(NUMERIC) at outparams[0])
[sqlunit] *** expected:
[sqlunit] 
[sqlunit]   2
[sqlunit] 
[sqlunit] *** but got:
[sqlunit] 
[sqlunit]   0
[sqlunit] 
[sqlunit]
[sqlunit] Running test[6]: RANDOM NUMBER (5498975) (0ms)
[sqlunit] Tearing down test...
[sqlunit] sqlunit-ant: SQLUnit Tests Failed: In file: p_cc.xml, tests: 6, failures: 2, errors = 0
[sqlunit] SQLUnit Tests Failed: In file: p_cc.xml, tests: 6, failures: 2, errors = 0

I could then identify the problem, fix it, and run the tests within seconds. How cool is that?

That particular run helped me realize that if I passed in a string of numbers that was even, it would give me the wrong result. If you look at Visa for instance, it's typically 16 digits long with the final digit being the check digit. You start at the rightmost digit before the check digit and go backwards. That was the impetus behind putting the digits into the PL/SQL TABLE OF INTEGERS up above.

I'll post more on creating validly formatted credit card numbers later, I just wanted to use it as an example for SQLUnit.

Try it. Use it. Let me know how it goes. Maybe we can get some Java guy to get it started up again.

UPDATEs in OLTP: A Design Flaw?

This one has been on my mind for the past year or so...

Should you do UPDATEs in an OLTP environment?

The answer is maybe, or it depends, of course.

When I ask this question I ask it in relation to INSERTs. There is a difference between creating a record and updating a record. I think it comes down to a design decision.

For instance, I once used a table to track sessions (web sessions, using Application Context in the database to "reconnect"), it was defined something like the following:


An Acceptable Time to use UPDATE
A session defaulted was created with a 30 minute window. Each page the user would hit would update the END_TIME to SYSDATE + 30 Minutes. If they had no activity for 30 minutes, the END_TIME would be less then the current time (SYSDATE) and they would be logged out. This table did exactly what I needed, UPDATEs worked perfectly here.

What if I wanted to track page hits though? Could I do that with the current table? I could possibly add PAGE_ID or something I suppose, but then I would have to write this "complicated" query to find the first START_TIME and then compare that with the last END_TIME. Kind of changes the meaning of the table right?

An Unacceptable Time to use UPDATE
You have a table of scheduled transactions or some sort of recurring billing.


You use this table by having a job that looks at the DATE_TO_POST, takes those and attempts to post them. If there is a problem, you update the DATE_TO_POST column to the next date/time (based on rules somewhere) and you increment the TIMES_POSTED counter. That doesn't sound so bad right? If you don't have any processing or billing failures, it's not. But if you do, you lose some valuable data, in my opinion.

First, get rid of the TIMES_POSTED column. You don't need it. Create a record for every single transaction you have posted. This obviously changes the meaning of the table and will force you to change your code.

Here's why it's good though (to remove the UPDATE): You now have a single record for every attempt. Your reporting off of this becomes much easier and is performed with SQL. With the UPDATE, you have to maintain some PL/SQL code ( TIMES_POSTED + 1). With the INSERT, you don't perform a calculation at all.

A slightly different example and one that may be more familiar to everyone:


This kind of structure is more suited for a data warehouse. The columns have been pivoted to make reporting easier. In an ideal environment, this wouldn't exist on the OLTP server. However we don't all work in ideal environments and this type of structure is often employed. I pretty much hate it (in OLTP, not DW). The volume and complexity of code needed to maintain this type of table is large. There are all sorts of computations that must be performed and then someone, like me, has to come in and maintain it. I always vote for scrapping it in favor of the following type structure(s):


Now you have INSERT statements as opposed to one or more UPDATE statements. Reporting tools can then handle the pivoting or whatever else you want to do to the data.

UPDATEs in an OLTP are not a flaw, but when about to write one, think of the implications. Is that what you really want to do? Wouldn't you rather just do an INSERT and be done with it?

Create Database Link - Loopback Edition

I've been tasking with making some fairly drastic changes. When I say drastic I mean that I'm changing the name of a table and updating all of the dependencies. The drastic part comes in because there are about 11 Trillion dependencies.

So not to affect the other developers while I hammer this out, I created my own instance and one-by-one brought over the necessary objects (and their dependent objects). I know there are other ways to do this, develop in your named schema in the development database just changing or repointing the objects to your own.

Since I am new however, I thought it would be very beneficial to do it on my own. There are a couple of advantages:
1. I won't interfere with any developer's progress
2. It's a great opportunity to learn how everything fits together
3. I have ApEx installed (development doesn't...yet).
4. Look what I can do

Anyway, one procedure had an INSERT into our reporting environment (another database) so it wouldn't compile. It used the INSERT INTO [table]@database_link syntax. How can I replicate that? I know I've seen or read the solution somewhere, but I decided to do without Google this time to see how long it would take me.

I'll create TEST_USER, where the procedure lives and REPORT_DATA where the table lives. Following is performed with DBA privs:

CREATE USER test_user
  IDENTIFIED BY testing
  DEFAULT TABLESPACE users
  QUOTA 5M ON users;

GRANT resource, create session TO test_user;

CREATE PUBLIC DATABASE LINK loopback_link
  CONNECT TO report_data
  IDENTIFIED BY testing
  USING 'TESTING';

Now I'll create the procedure in the TEST_USER schema:

PROD@TESTING>CREATE OR REPLACE
  2  PROCEDURE test_user.update_t
  3    ( p_x IN NUMBER,
  4      p_y IN VARCHAR2 )
  5  IS
  6  BEGIN
  7    INSERT INTO t@loopback_link
  8      ( x,
  9        y )
 10    VALUES
 11      ( p_x,
 12        p_y );
 13  END update_t;
 14  /

Warning: Procedure created with compilation errors.

Elapsed: 00:00:00.18
PROD@TESTING>show errors
Errors for PROCEDURE TEST_USER.UPDATE_T:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      ORA-04052: error occurred when looking up remote object
         REPORT_DATA.T@LOOPBACK_LINK.REGRESS.RDBMS.DEV.US.ORACLE.COM
         ORA-00604: error occurred at recursive SQL level 1
         ORA-01017: invalid username/password; logon denied
         ORA-02063: preceding line from LOOPB

Yup, no user yet.

CREATE USER report_data 
  IDENTIFIED BY testing
  DEFAULT TABLESPACE users
  QUOTA 1M ON users;

GRANT create session TO report_data;

CREATE TABLE report_data.t
(
  x NUMBER PRIMARY KEY,
  y VARCHAR2(30)
);

Try creating the procedure in TEST_USER again

PROD@TESTING>CREATE OR REPLACE
  2  PROCEDURE test_user.update_t
  3    ( p_x IN NUMBER,
  4      p_y IN VARCHAR2 )
  5  IS
  6  BEGIN
  7    INSERT INTO t@loopback_link
  8      ( x,
  9        y )
 10    VALUES
 11      ( p_x,
 12        p_y );
 13  END update_t;
 14  /

Procedure created.

WIN! Does it work though?

PROD@TESTING>EXEC TEST_USER.UPDATE_T( 1, 'BOLLOCKS' );

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
PROD@TESTING>SELECT * FROM report_data.t;

         X Y
---------- ------------------------------
         1 BOLLOCKS

1 row selected.

As LC would say, "That's not so bad."

/etc/fstab help!

Most of you know I'm a Linux n00b. I have no apologies for that...at least I'm trying right?

I unsucessfully attempted to install Oracle on Jaunty Jackalope (9.04) so I decided to use VirtualBox and Oracle Enterprise Linux. I'd successfully created a Windows Vista Ultimate virtual machine, off to try my second.




I chose fixed size storage:


Then select the folder where I wanted it to reside:


Location and size:


And the summary:


And received this:


At first, I began to search for problems with VirtualBox. I tried created a smaller disk, 2.0 GB, and it worked. So I knew I had a space issue (well, I think I knew).

So, fdisk to view the available partitions (as root):

root@cdj-laptop:/# fdisk -l
root@cdj-laptop:/#

So I did it as myself:

chet@cdj-laptop:/# fdisk -l
chet@cdj-laptop:/#

Using sudo:

chet@cdj-laptop:/# sudo fdisk -l
root@cdj-laptop:/#

So I installed GParted, and tried using that Partition Editor to see what was available:


WTF?

So I've tried search on "ubuntu fdisk nothing" and get nothing. So I'm asking you fine folks...either help me out or point me to the manual (RTFM!). Links are helpful. Answers are the most helpful. Something. I don't want to reinstall again but will if I have to.

Probably helpful to everyone is the contents of my /etc/fstab file as well:


Thank you and have a nice day.