Oracle Tips and Tricks — David Fitzjarrell

June 30, 2008

What’s In A Name?

Filed under: General — dfitzjarrell @ 12:40

I read this post: http://arjudba.blogspot.com/2008/06/ora-12541-tnsno-listener.html (that has since been removed) describing the ORA-12541 error and what, exactly, to do to correct the problem. The one GLARING omission to his text involves running named listeners (those NOT named ‘LISTENER’). Let’s look at how someone following his advice without looking further into the listener configuration can, somewhat inexplicably, not fix the problem.

The listener.ora file contains configuration information for, you guessed it, the listener process essential for networked access to a database from a remote client. [A common ‘complaint’, which I’ll list here for want of a better place, is that the Net Configuration Assistant doesn’t ‘create’ the listener. Really, there’s nothing to create on a UNIX or Linux system with the listener as one can have a fully functioning listener without a listener.ora file. [This is also true of Windows but a service is required before the listener can be started.] The Net Configuration Assistant records the configuration of your network in the listener.ora file (server name/IP Address, SID, port number), and on Windows systems creates the required service. The Net Configuration Assistant will start the listener after the listener.ora file is created. The only time you need to run the Net Configuration assistant is on Windows systems, where a service needs to be created. On UNIX and Linux systems you can simply use lsnrctl to start a listener with the default name and port even if you do not have a listener.ora file configured.] One of the configuration parameters available is a listener name, which defaults to LISTENER in many installations, but may also be set to just about any name one could desire in place of the default. A plain-vanilla, standard listener.ora might look like this (example is for a shared server mode listener):

 LISTENER =
  (ADDRESS_LIST=
       (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
       (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))    -- configures listener for
  )                                            -- multithreaded control agent

and use the default name of LISTENER for the listener process. Then, again, it might look like this:


 APP_LISTNR =
  (ADDRESS_LIST=
       (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
       (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

Notice it does NOT use the default name of LISTENER, instead using the name APP_LISTNR as possibly a more descriptive name for the process. This is where the problems in his post here begin. Let’s follow his advice and see where we’re led.

The author of the problematic blog entry assumes a listener configuration using the default listener name, and in the first case his listed advice of running ‘lsnrctl start’ would work quite well:

LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 24-JUN-2008 06:53:50

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Starting /oracle/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

and you’ll also see the current status of the listener and a summary of the available services. But, for the second case (with the non-default-named listener) simply running ‘lsnrctl start’ WILL start a listener named LISTENER, not the configured listener named APP_LISTNR:

$ lsnrctl start

LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 24-JUN-2008 06:53:50

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Starting /oracle/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

And, in that status report one sees the following tale-telling line:

The listener supports no services

indicating that, even though you’ve successfully started a listener named LISTENER, it doesn’t support anything. [With 10g and later releases the listener.ora file may not have any hard-coded entries opting instead to take advantage of database auto-registration. In such cases when the listener is started the above message will appear. Wait for a minute or two and execute a ‘lsnrctl status’ request and you’ll likely see auto-registered databases displayed and the message regarding the listener not supporting services will have disappeared. Notice also that the entries for auto-registered databases have a status of READY and the status of hard-coded listener entries is UNKNOWN. Why UNKNOWN? Oracle is being told to listen for these databases regardless and, as such, no ‘polling’ is taking place; there is no problem with such services. If, after five minutes of waiting, you still see that the listener supports no services you’re likely running into the named listener issue described here.] So you may have ‘fixed’ the ORA-12541 error (as you DO have a listener running at the specified port and address), but you still cannot connect because you’ve traded the ORA-12541 error for an ORA-12514:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

and the advice from the same soul for an ORA-12514 (which he lists as mutually exclusive from an ORA-12541) won’t work as your listener, named LISTENER, doesn’t support any services because someone decided to name the listener for this particular system as APP_LISTNR. And, gee whiz, that particular listener hasn’t been started yet.

Starting the named listener properly fixes the entire situation:

 $ lsnrctl start app_listnr

LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 24-JUN-2008 06:53:50

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Starting /oracle/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

Reading the status report for this listener after startup has completed reveals that now the requested services are available:

Services Summary...
  MY_DB           has 2 service handler(s)
  APPDB           has 1 service handler(s)
The command completed successfully

So the listener, named APP_LISTNR, has been started and is available to field connections to the databases listed. And you’ve cured the ORA-12541 and ORA-12514 errors (which, in this case, are not mutually exclusive).

It’s funny (in a not-so-humorous way) how non-standard listener configurations can make generic advice, well, less than useful. One should always check the listener.ora file (if one exists) before blindly marching into oblivion, as a non-default name for a listener may be set thus making the ubiquitous ‘lsnrctl start’ advice meaningless.

What’s in a name? Plenty if you’ve decided to use a non-default naming convention for your listener.

So if your listener IS named LISTENER what else could cause an ORA-12514? Several things, actually, starting with a misconfigured or missing sqlnet.ora file. Let’s say you have tnsnames.ora entries that look like this:

APU.world =
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(Host = SNGSBBD.SMORT.NET)(Port = 1529))
    )
    (CONNECT_DATA = (SID = APU))
  )

Noting that all of the aliases have ‘.world’ appended to them does make for consistency, however if you’ve been used to a sqlnet.ora file configured like this:

NAMES.DEFAULT_DOMAIN = world
NAME.DEFAULT_ZONE = world  # currently unsupported parameter, ignored
                           #
                           # legacy setting from Oracle version 8 SQL*Net
                           # configuration

and it now has none of those entries listed above attempting to tnsping or access APU will generate an ORA-12514 since APU isn’t in the tnsnames.ora file, APU.world is. A properly configured sqlnet.ora file will “automagically” append the NAMES.DEFAULT_DOMAIN value to the supplied alias and create APU.world from APU. Of course if NAMES.DEFAULT_DOMAIN is listed as .net then you have other issues as none of the tnsnames.ora aliases are defined as .net and you’ll not rid yourself of the ORA-12514 errors until that file is corrected.

Another cause of an ORA-12514 is trying to use an alias which is not defined in the tnsnames.ora file. Trying to connect to, say, ABU instead of APU would throw an ORA-12514 error, and rightly so, because there is no ABU.world entry defined (presuming a configuration like our prior example above). So it’s imperative to inform the user community of the proper database TNS aliases to use.

Yet another cause is defining your tnsnames.ora alias with the wrong port number. If your listener happens to be configured for port 1532, and you let the Net Configuration Assistant have its defaults for your latest entry the ORA-12514 errors appear; you’re confused by this error because, gee, the assistant finished successfully so what could POSSIBLY be wrong? As you can see, plenty, because you must configure your tnsnames.ora entries correctly for the listener you’re trying to access, in this case by using port number 1532 instead of the default, which is 1521.

[Another error, ORA-12154, can rear its ugly head in networked environments when the remote location of the tnsnames.ora file is unavailable. It can also appear if the TNS_ADMIN environment variable is set to the wrong location (which, again, makes the tnsnames.ora file unavailable). It’s easy to misread these error numbers (since they all have a 1, 4 and a 5) so care is necessary when doing so such that a proper resolution can be effected, because the solution to an ORA-12154 error (point the environment to the proper location of the tnsnames.ora file or have the network folks fix the errant nfs mount) is different than the solution to an ORA-12514 or an ORA-12541 error.]

Yes, there are some ‘standard’ items to look for when ORA-12541 and ORA-12514 errors occur, but there are also some non-standard items as well, and it’s the responsible blogger who informs you of both, as half an answer is sometimes worse than no answer at all.

A rose by any other name would certainly smell as sweet, but that new name may keep you from getting to your Oracle database if the listener is involved.

Advertisements

June 24, 2008

Consistency Is Good

Filed under: General — dfitzjarrell @ 16:10

On-line transaction processing (OLTP) systems are designed so that multiple users can do multiple things without severely impacting each other (for the most part). Fifty users can process data at essentially the same time and not ‘step’ on each other’s toes. This doesn’t mean, however, that a user (or a developer) can’t step on his or her own toes when given the opportunity. Misunderstanding the read-consistency mechanism Oracle employs is one reason (and it’s a pretty big reason) users (and developers) shoot themselves in the foot. Let’s find out how this works.

Oracle, in its infinite wisdom, operates on the basic premise that the end user wants to see repeatable, reliable query results. Because of this Oracle uses areas of the database the garden-variety user may not expect when returning query results. Why? Because, for the most part, these databases are built to process transactions and to ensure Oracle meets the ACID test [Atomicity, Consistency, Isolation, and Durability] it uses UNDO segments to preserve the initial state of the data prior to any changes (this is not the entire picture, but it’s the portion we need to explain read-consistency). Oracle can use these UNDO segments to satisfy queries, too, so that an image of the data as of the time the query began can be returned, regardless of the number of uncommitted changes which may have occurred since the query started. Of course, as expected, this behaviour can also create problems when it is not understood. Let’s look at a small two session example.

Magdeline Whiffenpoof, manager of HR, is working on the EMP table and she’s added a new employee with an EMPNO of 8010:

SQL> INSERT
  2  INTO EMP VALUES
  3          (8010, 'SMITH',  'CLERK',     7902,
  4          TO_DATE('17-DEC-1999', 'DD-MON-YYYY'),  900, NULL, 20);

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>

Everything looks good, but, wait! Euphemia Snertle, assistant HR manager, has just been informed that this new employee changed her mind and took a job with a rival company so she’s been told to delete this person from the database:

SQL> select max(empno)
  2  from emp;

MAX(EMPNO)
----------
      8010

SQL>
SQL> delete
  2  from emp
  3  where empno = 8010;

1 row deleted.

SQL>

Of course Euphemia hasn’t committed the change yet as she’s been called into a meeting. Meanwhile Magdeline decides to check her work and, lo and behold, it still looks like that new employee is in the database:

SQL> select max(empno) from emp;

MAX(EMPNO)
----------
      8010

SQL>

This is because Euphemia’s change hasn’t been committed, and Oracle provides an image of the data as it was when Magdeline’s query started, ignoring any and all uncommitted transactions. Oracle visits the UNDO segments and ‘reconstructs’ the data as it was when Magdeline asked to ‘see’ it, so she’s not able to view what Euphemia just started and has yet to finish. Of course at Euphemia’s terminal she’s seeing a different MAX(EMPNO):

SQL> select max(empno)
  2  from emp;

MAX(EMPNO)
----------
      7934

SQL>

Euphemia returns, and commits the change she made:

SQL> commit;

Commit complete.

SQL>

And now Magdeline, again checking her work, receives a different result:

SQL> select max(empno) from emp;

MAX(EMPNO)
----------
      7934

SQL>

Hot potato pancakes, what happened?!?!? Magdeline calls Euphemia to report this ‘missing’ employee, only to be told that she took another offer and is no longer on the payroll. Since both transactions have been committed everyone will return the same maximum EMPNO from the table. And all is, again, right with the world.

Since Oracle provides a consistent view of data as of the moment a query starts it can be confusing when the exact same query is run more than once and returns differing results because of a delay in committing changes as the end user believes the data to be exactly as he or she sees it, blissfully unaware that other users could be changing the picture in the same moment their results are displayed. And, with long running queries sometimes the UNDO images are overwritten by commits, causing the query to end unsuccessfully with an ORA-01555 error. Re-running the query usually succeeds and returns the now-committed changes.

When all is said and done, I’d rather have Oracle do what it does (meaning return a consistent set of data) rather than try to return an image of uncommitted changes. Sure, a commit will set those changes in stone (so to speak) but a rollback will restore the data to the state it was before the changes were enacted. And that could be confusing.

Consistency. It’s a virtue.

June 17, 2008

Row, Row, Row

Filed under: General — dfitzjarrell @ 13:41

Many times I’ve seen questions similar to the following:

“I want the 17th row from the table. How do I do that?”

which says much as to the level of misunderstanding people have regarding the standard Oracle table type, a heap table. Such tables have no order, thus it’s really impossible to return the 17th row of a given table and be able to do it repeatably with large data sets. What confuses people about this is the fact that Oracle provides a pseudo-column named ROWNUM which sequentially numbers a result set. Let’s look at this interesting element of Oracle and see how it works, what it will work for and what it won’t do.

ROWNUM is, as mentioned previously, a sequential number assigned to elements of a result set, which means you have to start returning data before you can assign a ROWNUM of 1. Queries which will work are:

SQL> select rownum, object_name
  2  from user_objects
  3  where rownum < 16
  4  /

    ROWNUM OBJECT_NAME
---------- -----------------------
         1 ADMIN_EXT_EMPLOYEES
         2 ATTAINMENTS
         3 ATTAINMENTS_PK
         4 BONUS
         5 CHECK_SAL
         6 CHGCOLTEST
         7 CPU_APPLY_VERSION
         8 CPU_APPLY_VERSION_HOLD
         9 CPU_APPLY_VERSION_PK
        10 CPU_APPLY_VERSION_SAVE
        11 CUSTOMERS_LOOKUP

    ROWNUM OBJECT_NAME
---------- -----------------------
        12 CUSTOMERS_LOOKUP_PRIM
        13 CUSTOMERS_LOOKUP_ZE
        14 DATES_PKG
        15 DATES_PKG

15 rows selected.

SQL>

Notice we stopped returning rows when ROWNUM reached 15; since we used an inequality in our WHERE condition we were able to start with 1 and count up to our limit. Of course you can simply return the ROWNUM for every row in a result set:

SQL> select rownum, ename
  2  from emp;

    ROWNUM ENAME
---------- ----------
         1 SMITH
         2 ALLEN
         3 WARD
         4 JONES
         5 MARTIN
         6 BLAKE
         7 CLARK
         8 SCOTT
         9 KING
        10 TURNER
        11 ADAMS

    ROWNUM ENAME
---------- ----------
        12 JAMES
        13 FORD
        14 MILLER

14 rows selected.

SQL>

Since we’re dealing with heap tables there is no order to the data, and if we want the data above ordered by ename something interesting happens to ROWNUM:

SQL> select rownum, ename
  2  from emp
  3  order by ename;

    ROWNUM ENAME
---------- ----------
        11 ADAMS
         2 ALLEN
         6 BLAKE
         7 CLARK
        13 FORD
        12 JAMES
         4 JONES
         9 KING
         5 MARTIN
        14 MILLER
         8 SCOTT

    ROWNUM ENAME
---------- ----------
         1 SMITH
        10 TURNER
         3 WARD

14 rows selected.

SQL>

ROWNUM is assigned as the records are retrieved, before they are ordered by the query, so ordering the data by ename caused the ROWNUM values to display out of sequence. We can produce a result set where both the ename values and the ROWNUM values are ‘properly’ ordered, but it takes a trick — selecting from an ordered subquery then assigning the ROWNUM values to the ordered names:

SQL> select rownum, x.ename
  2  from
  3  (select ename
  4  from emp
  5  order by ename) x
  6  /

    ROWNUM ENAME
---------- ----------
         1 ADAMS
         2 ALLEN
         3 BLAKE
         4 CLARK
         5 FORD
         6 JAMES
         7 JONES
         8 KING
         9 MARTIN
        10 MILLER
        11 SCOTT

    ROWNUM ENAME
---------- ----------
        12 SMITH
        13 TURNER
        14 WARD

14 rows selected.

SQL>

I expect that query answers the question ‘how to serial rows in oracle’, unless the questioner is really asking in regard to some other topic with that rather ambiguous interrogatory.

Okay, so I can see the ROWNUM value for each row returned, can I restrict the results to only one row of my choosing? Well, sort of … but it takes yet another ‘trick’:

SQL> select *
  2  from (select rownum rn, x.ename
  3  from
  4  (select ename
  5  from emp
  6  order by ename) x)
  7  where rn = 13;

        RN ENAME
---------- ----------
        13 TURNER

SQL>

We used the ordered list query to do this, but ordered data isn’t a requirement. The two requirements are:

1) select the ROWNUM along with the rest of the data we want using a subquery, and alias the ROWNUM pseudo-column so we can reference its value later

2) use the above select as a table for our final select, and restrict the results to a given ROWNUM value (in this case aliased as rn)

As if by magic we return only the 13th record of the result set. Remember that this does NOT necessarily return the 13th row of the table (and with a heap table there is no order to the data within, so there is no ’13th’ row in the actual data). The subquery construction is necessary so we can return ROWNUM values for each row of the table data and then use that assignment to restrict the output to the desired element. Had we tried to access, directly, the ROWNUM of 13 without the trick I just ilustrated we would return … nothing:

SQL> select rownum, ename
  2  from emp
  3  where rownum = 13;

no rows selected

SQL>

Since no row met the criteria of ROWNUM = 13 (because we never were able to assign a ROWNUM of 1 so the counter never was able to increase) nothing was returned. ROWNUM is assigned to every record that meets the query criteria; the criteria we provided, ROWNUM = 13, was impossible to meet because we were asking for a value we could never reach. In pseudo-code the decision tree looks like this:

  ---------------------------> Does this row meet our criteria?
 ^                                 
 |                                         / \
 |                                        /   \
 ^                                      Yes    No
 |                                   
 |                      Return data and ctr    Leave ctr untouched
 |                      Increment ctr          Return no data
 ^                                   
 |                                       \    /
 |                                        \  /
 |                                 
 <----------------------------------------

Since the criteria could not be met the counter could not increase and no data was displayed.

Can a range of rows be returned that doesn’t start with ROWNUM=1? Yes, simply modify the above working query to use BETWEEN instead of ‘=’:

SQL> select *
  2  from (select rownum rn, x.ename
  3  from
  4  (select ename
  5  from emp
  6  order by ename) x)
  7  where rn between 2 and 11;

        RN ENAME
---------- ----------
         2 ALLEN
         3 BLAKE
         4 CLARK
         5 FORD
         6 JAMES
         7 JONES
         8 KING
         9 MARTIN
        10 MILLER
        11 SCOTT

10 rows selected.

SQL>

Knowing how ROWNUM operates is key to using it properly. Hopefully these examples will clear the confusion ROWNUM has generated over the years and help you return the results you want.

June 6, 2008

Out Of Sequence

Filed under: General — dfitzjarrell @ 12:51

Confusion over the read-consistency mechanism Oracle employs creates situations which really shouldn’t happen but often do. A common issue is experiencing an ORA-00001 error upon attempting to insert an application-generated key value. Let’s look at why that happens and what needs to be done to correct it.

Oracle’s read-consistency mechanism generates a read-consistent image of table data at the time the query starts execution. If Oracle needs to visit rollback/undo records to construct that image it does so without ‘warning’ the user. Because of this, uncommitted changes to data cannot be ‘seen’ by any session other than the one performing those changes, which means that any number of sessions can only ‘see’ the last committed key value (even though 16 other sessions are attempting to add new data to the table). If, say, 17 sessions are trying desperately to add the same key value only one will succeed (again, providing some OTHER session before that hasn’t added the same value but stopped short of a commit statement) and the rest will experience

ERROR at line %d:
ORA-00001: unique constraint (%s) violated

Let’s look at two simultaneous sessions attempting to use that same methodology. Here’s Session 1:

SQL> --
SQL> -- Create test table
SQL> --
SQL> create table ins_test(keycol number, keyval varchar2(20));

Table created.

SQL>
SQL> --
SQL> -- Add primary key constraint
SQL> --
SQL> alter table ins_test add constraint ins_test_pk primary key (keycol);

Table altered.

SQL>
SQL> --
SQL> -- Insert data
SQL> --
SQL> insert all
  2  into ins_test
  3  (keycol, keyval)
  4  values
  5  (1, 'First value')
  6  into ins_test
  7  (keycol, keyval)
  8  values
  9  (2, 'Second value')
 10  into ins_test
 11  (keycol, keyval)
 12  values
 13  (3, 'Third value')
 14  into ins_test
 15  (keycol, keyval)
 16  values
 17  (4, 'Fourth value')
 18  into ins_test
 19  (keycol, keyval)
 20  values
 21  (5, 'Fifth value')
 22  into ins_test
 23  (keycol, keyval)
 24  values
 25  (6, 'Sixth value')
 26  select * from dual;

6 rows created.

SQL>
SQL> --
SQL> -- Commit changes
SQL> --
SQL> commit;

Commit complete.

SQL>
SQL> --
SQL> -- Start questionable process to
SQL> -- insert more data based upon
SQL> -- current max(keycol) value
SQL> --
SQL>
SQL> --
SQL> -- Set a variable to reuse the returned
SQL> -- max value
SQL> --
SQL> --
SQL> -- Rather dumb idea as the max() from
SQL> -- any query will miss any new values
SQL> -- inserted by other sessions and not
SQL> -- yet committed
SQL> --
SQL> column maxval new_value next_key
SQL>
SQL> --
SQL> -- Return current max(keycol)
SQL> --
SQL> -- Not reliable in the least
SQL> -- unless there is only one user
SQL> -- ever on the system, and it's
SQL> -- you
SQL> --
SQL> select max(keycol) maxval from ins_test;

    MAXVAL
----------
         6

SQL>
SQL> --
SQL> -- Use the saved value for the next insert
SQL> --
SQL> -- Not a good idea
SQL> --
SQL> -- Using a sequence and a trigger is far
SQL> -- more reliable and recommended
SQL> --
SQL> insert into ins_test (keycol, keyval)
  2  values (&next_key + 1, 'Next value in line');
old   2: values (&next_key + 1, 'Next value in line')
new   2: values (         6 + 1, 'Next value in line')

1 row created.

SQL>
SQL> --
SQL> -- Wait for a minute so a second insert of this
SQL> -- same type can occur from a different session
SQL> --
SQL>
SQL> exec dbms_lock.sleep(60);

PL/SQL procedure successfully completed.

Now, while the first session waits we try the same thing from Session 2:

SQL> --
SQL> -- Start questionable process to
SQL> -- insert more data based upon
SQL> -- current max(keycol) value
SQL> --
SQL>
SQL> --
SQL> -- Set a variable to reuse the returned
SQL> -- max value
SQL> --
SQL> --
SQL> -- Rather dumb idea as the max() from
SQL> -- any query will miss any new values
SQL> -- inserted by other sessions and not
SQL> -- yet committed
SQL> --
SQL> column maxval new_value next_key
SQL>
SQL> --
SQL> -- Return current max(keycol)
SQL> --
SQL> -- Not reliable in the least
SQL> -- unless there is only one user
SQL> -- ever on the system, and it's
SQL> -- you
SQL> --
SQL> select max(keycol) maxval from ins_test;

    MAXVAL
----------
         6

SQL>
SQL> --
SQL> -- Use the saved value for the next insert
SQL> --
SQL> -- Not a good idea
SQL> --
SQL> -- Using a sequence and a trigger is far
SQL> -- more reliable and recommended
SQL> --
SQL> insert into ins_test (keycol, keyval)
  2  values (&next_key + 1, 'Next value in line');
old   2: values (&next_key + 1, 'Next value in line')
new   2: values (         6 + 1, 'Next value in line')
insert into ins_test (keycol, keyval)
*
ERROR at line 1:
ORA-00001: unique constraint (BING.INS_TEST_PK) violated


SQL>
SQL> --
SQL> -- Wait for a minute so a second insert of this
SQL> -- same type can occur from a different session
SQL> --
SQL>
SQL> exec dbms_lock.sleep(60);

PL/SQL procedure successfully completed.

Revisiting Session 1 we see the commit has taken place and the changes enacted have been saved:

SQL>
SQL> --
SQL> -- Commit the changes, or try to
SQL> --
SQL> commit;

Commit complete.

SQL>

In Session 2 we also issue a commit, but as we saw earlier there are no changes to save:

SQL>
SQL> --
SQL> -- Commit the changes, or try to
SQL> --
SQL> commit;

Commit complete.

SQL>

And this sort of occurrence can continue because the application was written absent the knowledge of how Oracle constructs its read-consistent images.

Is there a solution? Yes, and it’s a solution Oracle has had available for many years: use a sequence. Let’s now look at how this same set of events change when using a sequence and a trigger to generate the key value for an insert. Again, we start from Session 1:

SQL> --
SQL> -- Create test table
SQL> --
SQL> create table ins_test(keycol number, keyval varchar2(20));

Table created.

SQL>
SQL> --
SQL> -- Add primary key constraint
SQL> --
SQL> alter table ins_test add constraint ins_test_pk primary key (keycol);

Table altered.

SQL>
SQL> --
SQL> -- Add a sequence, and a trigger, to populate the key column
SQL> --
SQL> create sequence ins_test_seq
  2  start with 1
  3  increment by 1
  4  nomaxvalue
  5  nocycle
  6  order;

Sequence created.

SQL>
SQL> create or replace trigger pop_ins_test_pk
  2  before insert on ins_test
  3  for each row
  4  begin
  5        select ins_test_seq.nextval
  6        into :new.keycol
  7        from dual;
  8  end;
  9  /

Trigger created.

SQL>
SQL> --
SQL> -- Insert data
SQL> --
SQL> insert all
  2  into ins_test
  3  (keyval)
  4  values
  5  ('First value')
  6  into ins_test
  7  (keyval)
  8  values
  9  ('Second value')
 10  into ins_test
 11  (keyval)
 12  values
 13  ('Third value')
 14  into ins_test
 15  (keyval)
 16  values
 17  ('Fourth value')
 18  into ins_test
 19  (keyval)
 20  values
 21  ('Fifth value')
 22  into ins_test
 23  (keyval)
 24  values
 25  ('Sixth value')
 26  select * from dual;

6 rows created.

SQL>
SQL> --
SQL> -- Commit changes
SQL> --
SQL> commit;

Commit complete.

SQL>
SQL> insert into ins_test (keyval)
  2  values ('Next value in line');

1 row created.

SQL>
SQL> --
SQL> -- Wait for a minute so a second insert of this
SQL> -- same type can occur from a different session
SQL> --
SQL>
SQL> exec dbms_lock.sleep(60);

PL/SQL procedure successfully completed.

SQL>

Now we visit Session 2 and try our insert:

SQL> --
SQL> -- Using a sequence and a trigger is far
SQL> -- more reliable and recommended
SQL> --
SQL> insert into ins_test (keyval)
  2  values ('Next value in line');

1 row created.

SQL>
SQL> --
SQL> -- Wait for a minute so a second insert of this
SQL> -- same type can occur from a different session
SQL> --
SQL>
SQL> exec dbms_lock.sleep(60);

PL/SQL procedure successfully completed.

SQL>

Allowing both sessions to commit their changes and querying the table from both we see: Session 1 —

SQL> --
SQL> -- Commit the changes, or try to
SQL> --
SQL> commit;

Commit complete.

SQL>
SQL> select * from ins_test;

    KEYCOL KEYVAL    
---------- --------------------
         1 First value
         2 Second value
         3 Third value
         4 Fourth value
         5 Fifth value
         6 Sixth value
         7 Next value in line

7 rows selected.

SQL>

Session 2 —

SQL> --
SQL> -- Commit the changes, or try to
SQL> --
SQL> commit;

Commit complete.

SQL>
SQL> select * from ins_test;

    KEYCOL KEYVAL     
---------- --------------------
         8 Next value in line
         1 First value
         2 Second value
         3 Third value
         4 Fourth value
         5 Fifth value
         6 Sixth value
         7 Next value in line

8 rows selected.

SQL>

Notice no ORA-00001 error was generated, since a sequence doesn’t rely upon existing table values to generate the next number in line. And also notice that the trigger allowed the users to avoid having to enter the key value as it was generated and populated from the trigger by a call to the relevant sequence. AND all users get to call the same trigger so everyone gets the same treatment because each call to NEXTVAL generates a unique value, regardless of which session originates the request.

So, now we know that Oracle won’t let any session outside of the one creating data changes ‘see’ what has yet to be committed. And, knowing that, it should be fairly obvious that a ‘select max()’ query is useless in generating sequential key values in a multi-user system. This is exactly why Oracle provides sequences, to allow multiple users to insert data into the same table at essentially the same time without generating any constraint violation errors. Thus, using a sequence and a trigger is the accepted and expected method for generating numeric key values.

Using a trigger is not the only way to utilize a sequence as the sequence can be used directly in a VALUES clause of an INSERT statement, and since one can access either the next value or the current value of a sequence it’s fairly easy to populate more than one column with the exact same value:

SQL> create sequence myseq
  2  start with 1 increment by 1 nomaxvalue nocycle nocache;

Sequence created.

SQL> create table seqtest(myval number, myotherval number);

Table created.

SQL> insert into seqtest
  2  values (myseq.nextval, myseq.currval)
  3  /

1 row created.

SQL> select * from seqtest;

     MYVAL MYOTHERVAL
---------- ----------
         1          1

SQL>

The first call to a sequence in a session must be to the NEXTVAL operator as a call to CURRVAL will fail:

SQL> select myseq.currval from dual;
select myseq.currval from dual
       *
ERROR at line 1:
ORA-08002: sequence MYSEQ.CURRVAL is not yet defined in this session


SQL>

After the first call to NEXTVAL for a given sequence has been made, however, you can call CURRVAL for that same sequence as often as you wish:

SQL> select myseq.nextval from dual;

   NEXTVAL
----------
         2

SQL> select myseq.currval from dual;

   CURRVAL
----------
         2

SQL> select myseq.currval from dual;

   CURRVAL
----------
         2

SQL> select myseq.currval from dual;

   CURRVAL
----------
         2

SQL> select myseq.currval from dual;

   CURRVAL
----------
         2

SQL> select myseq.currval from dual;

   CURRVAL
----------
         2

SQL> select myseq.currval from dual;

   CURRVAL
----------
         2

SQL>

As you can see it’s possible to populate any number of columns with the same value once CURRVAL for the given sequence has been defined. CURRVAL is assigned the value from the most recent NEXTVAL call; it’s a way to preserve the currently generated sequence value so you can use it multiple times. Trying to find the current and next value of a sequence CANNOT be done using the NEXTVAL and CURRVAL calls as CURRVAL will ALWAYS be equal to the value generated by NEXTVAL. Use the query posted toward the bottom of this post to see where your sequence values are currently and what the next generated value will be. And, if this is the desired behaviour for every insert the trigger code listed above can be modified to populate multiple columns at the same time with the exact same sequence number. Pretty slick, I’d say.

And you can create a sequence starting with the next available value in a column:

SQL> column nxt new_value snum
SQL>
SQL> select max(empno)+1 nxt from emp;

       NXT
----------
      7935

SQL>
SQL> create sequence emp_seq
  2  start with &snum
  3  increment by 1
  4  nomaxvalue
  5  nocycle;
old   2: start with &snum
new   2: start with       7935

Sequence created.

SQL>

This, of course, presumes no one else is trying to add any records to this table, as any uncommitted inserts will not be seen by this process. You could end up with key violations using your brand new sequence because what you thought was the highest value in the table really wasn’t:

SQL> INSERT
  2  INTO EMP VALUES
  3          (7935, 'SMITH',  'CLERK',     7902,
  4          TO_DATE('17-DEC-1999', 'DD-MON-YYYY'),  900, NULL, 20);

1 row created.

SQL>

Creating sequence in the midst of this insert:

SQL> column nxt new_value snum
SQL>
SQL> select max(empno)+1 nxt from emp;

NXT
----------
7935

SQL>
SQL> create sequence emp_seq
2 start with &snum
3 increment by 1
4 nomaxvalue
5 nocycle;
old 2: start with &snum
new 2: start with 7935

Sequence created.

SQL>

Now we commit the prior insert:

SQL> commit;

Commit complete.

SQL>

And we fail on insert in session #2:

SQL> insert into emp
  2  values
  3  (emp_seq.nextval, 'LORPO', 'CLERK', 7902, to_date('27-JUN-2003','DD-MON-YYYY'), 975, null, 20);
insert into emp
*
ERROR at line 1:
ORA-00001: unique constraint (BING.EMP_PK) violated


SQL>

So creating a sequence based upon a maximum column value can be done, but it also can backfire on you. I’d make certain no one is doing any work should you try this.

You can also create a sequence which cycles (or ‘wraps’, but since Oracle provides a wrap utility I’d prefer to not use that terminology for such a sequence):

SQL> create sequence my_cycle_seq
  2  start with 1
  3  increment by 1
  4  cycle
  5  cache 2
  6  maxvalue 5;

Sequence created.

SQL>
SQL> select my_cycle_seq.nextval
  2  from dual;

   NEXTVAL
----------
         1

SQL>
SQL> select my_cycle_seq.nextval
  2  from dual;

   NEXTVAL
----------
         2

SQL>
SQL> select my_cycle_seq.nextval
  2  from dual;

   NEXTVAL
----------
         3

SQL>
SQL> select my_cycle_seq.nextval
  2  from dual;

   NEXTVAL
----------
         4

SQL>
SQL> select my_cycle_seq.nextval
  2  from dual;

   NEXTVAL
----------
         5

SQL>
SQL> select my_cycle_seq.nextval
  2  from dual;

   NEXTVAL
----------
         1

SQL>

Such sequences could be used for printer queues, for example, to provide a fixed number of printer IDs since, as illustrated, they generate values from the minimum to the maximum then start over again at the minimum value. In Oracle the sequence audses$, which sets the sessionid a new session is assigned, is set to cycle and has a rather large maxvalue; in some releases it’s set to 2000000000 so it could be a long time before you’ll see a sessionid value repeat.

Sequence values are returned in the order in which they are requested, regardless of which session is making the sequence call. Caching sequence values can speed things up a bit, since the cache is populated with the number of values specified in the CACHE parameter; if, for example, the cache was set for 20 entries then 20 values, starting with the next available value, would be immediately available, regardless of which session is making the sequence call. If you use all of them it’s faster than generating them ‘on the fly’, but you can, and will, lose sequence values if not all are used and the session terminates; another way to lose sequence values is to rollback a transaction where a sequence was used to populate a value which means that using sequences will NOT produce a gap-less series of sequential numbers so you’ll need to get used to that. ┬áIf, on the other hand, you want all of the sequence numbers to fall ‘in order’ regardless of the session generating the value a sequence can be created with the keyword ‘ORDER’. Yes, that’s confusing on the face of it because the sequence values each user ‘sees’ can be in order. The ‘ORDER’ option is to preserve the order of sequence values across nodes in a RAC environment, to more closely coincide with, say, a process date if one is stored in the row. And,yes, you can use both caching and cross-node ordering when creating a sequence:

SQL> create sequence empseq
  2  start with 1 increment by 1 cache 20 order nomaxvalue  nocycle;

Sequence created.

SQL>

but Oracle will let the ORDER option take precendence over caching, which makes sense given that you’ve asked for all of your sequence values to be in order, regardless which RAC node generates them.

A sequence can be created with a MAXVALUE declared and NOCYCLE specified which can create a problem:

SQL> create sequence mynewseq
  2  start with 1 increment by 1 nocycle maxvalue 6 nocache;

Sequence created.

SQL>
SQL> select mynewseq.nextval from dual;

   NEXTVAL
----------
         1

SQL> select mynewseq.nextval from dual;

   NEXTVAL
----------
         2

SQL> select mynewseq.nextval from dual;

   NEXTVAL
----------
         3

SQL> select mynewseq.nextval from dual;

   NEXTVAL
----------
         4

SQL> select mynewseq.nextval from dual;

   NEXTVAL
----------
         5

SQL> select mynewseq.nextval from dual;

   NEXTVAL
----------
         6

SQL> select mynewseq.nextval from dual;
select mynewseq.nextval from dual
*
ERROR at line 1:
ORA-08004: sequence MYNEWSEQ.NEXTVAL exceeds MAXVALUE and cannot be
instantiated


SQL>

One way to fix this is to alter the sequence to increase the MAXVALUE:

SQL> alter sequence mynewseq maxvalue 10;

Sequence altered.

SQL>
SQL> select mynewseq.nextval from dual;

   NEXTVAL
----------
         7

SQL> select mynewseq.nextval from dual;

   NEXTVAL
----------
         8

SQL> select mynewseq.nextval from dual;

   NEXTVAL
----------
         9

SQL> select mynewseq.nextval from dual;

   NEXTVAL
----------
        10

SQL> select mynewseq.nextval from dual;
select mynewseq.nextval from dual
*
ERROR at line 1:
ORA-08004: sequence MYNEWSEQ.NEXTVAL exceeds MAXVALUE and cannot be
instantiated


SQL>

Obviously the MAXVALUE wasn’t adjusted enough; the process can be repeated ad infinitum/ad nauseum every time the above error is received. Or the sequence can be altered in this manner, which corrects the problem:

SQL> alter sequence mynewseq nomaxvalue;

Sequence altered.

SQL>
SQL> select mynewseq.nextval from dual;

   NEXTVAL
----------
        21

SQL> select mynewseq.nextval from dual;

   NEXTVAL
----------
        22

SQL> select mynewseq.nextval from dual;

   NEXTVAL
----------
        23

SQL> select mynewseq.nextval from dual;

   NEXTVAL
----------
        24

SQL>

Now the sequence will generate values forever, producing no ugly ORA-08004 errors.

Tracking your sequence values is an easy task, as a simple query of USER_SEQUENCES will prove:

SQL> SELECT sequence_name, last_number curr_val, increment_by,
  2  last_number + Increment_by next_val
  3  FROM user_sequences;

SEQUENCE_NAME                    CURR_VAL INCREMENT_BY   NEXT_VAL
------------------------------ ---------- ------------ ----------
UGROUP_SEQUENCE                         1            1          2
OBJECT_GRANT                       579936            1     579937
SYSTEM_GRANT                        60949            1      60950
PROFNUM$                                1            1          2
AUDSES$                          18184437            1   18184438
SNAPSHOT_ID$                            1            1          2
SNAPSITE_ID$                            1            1          2
JOBSEQ                                  1            1          2
RGROUPSEQ                               1            1          2
ORA_TQ_BASE$                       308209            1     308210
PARTITION_NAME$                     12861            1      12862

SEQUENCE_NAME                    CURR_VAL INCREMENT_BY   NEXT_VAL
------------------------------ ---------- ------------ ----------
CDC_SUBSCRIBE_SEQ$                      1            1          2
CDC_RSID_SEQ$                           1            1          2
LOG$SEQUENCE                            1            1          2
PSINDEX_SEQ$                          100            1        101
AWSEQ$                               1000            1       1001
STREAMS$_CAPTURE_INST                   1            1          2
APPLY$_SOURCE_OBJ_ID                    1            1          2
APPLY$_DEST_OBJ_ID                      1            1          2
APPLY$_ERROR_HANDLER_SEQUENCE           1            1          2
IDGEN1$                           9509051           50    9509101
AQ$_TRANS_SEQUENCE                     21            1         22

SEQUENCE_NAME                    CURR_VAL INCREMENT_BY   NEXT_VAL
------------------------------ ---------- ------------ ----------
AQ$_PROPAGATION_SEQUENCE                1            1          2
AQ$_CHAINSEQ                            1            1          2
AQ$_IOTENQTXID                          1            1          2
AQ$_RULE_SET_SEQUENCE                   1            1          2
AQ$_RULE_SEQUENCE                       1            1          2
AQ$_PUBLISHER_SEQUENCE                  1            1          2
GENERATOR$_S                            1            1          2
STREAMS$_RULE_NAME_S                    1            1          2
HS$_FDS_CLASS_S                        21            1         22
HS$_FDS_INST_S                          1            1          2
HS$_CLASS_CAPS_S                        1            1          2

SEQUENCE_NAME                    CURR_VAL INCREMENT_BY   NEXT_VAL
------------------------------ ---------- ------------ ----------
HS$_INST_CAPS_S                         1            1          2
HS$_BASE_DD_S                         121            1        122
HS$_CLASS_DD_S                          1            1          2
HS$_INST_DD_S                           1            1          2
HS$_CLASS_INIT_S                        1            1          2
HS$_INST_INIT_S                         1            1          2
JAVA$POLICY$SEQUENCE$                  91            1         92
INVALIDATION_REG_ID$                    1            1          2
WRI$_ADV_SEQ_TASK                   17608            1      17609
WRI$_ADV_SEQ_DIR                        1            1          2
WRI$_ADV_SEQ_JOURNAL                    1            1          2

SEQUENCE_NAME                    CURR_VAL INCREMENT_BY   NEXT_VAL
------------------------------ ---------- ------------ ----------
WRI$_ADV_SEQ_MSGGROUP              131551            1     131552
WRI$_ADV_SEQ_SQLW_QUERY                 1            1          2
DBMS_LOCK_ID                   1073741824            1 1073741825
AQ$_SCHEDULER$_JOBQTAB_N               21            1         22
SCHEDULER$_INSTANCE_S               11357            1      11358
SCHEDULER$_EVTSEQ                       1            1          2
AQ$_SCHEDULER$_EVENT_QTAB_N             1            1          2
SCHEDULER$_OLDOIDS_S                    1            1          2
SCHEDULER$_JOBSUFFIX_S                  1            1          2
STREAMS$_APPLY_SPILL_TXNKEY_S           1            1          2
FGR$_NAMES_S                            1            1          2

SEQUENCE_NAME                    CURR_VAL INCREMENT_BY   NEXT_VAL
------------------------------ ---------- ------------ ----------
AQ$_KUPC$DATAPUMP_QUETAB_N              1            1          2
IDX_RB$JOBSEQ                           1            1          2
ORA_PLAN_ID_SEQ$                       11            1         12
MV_RF$JOBSEQ                            1            1          2
AQ$_AQ$_MEM_MC_N                       21            1         22
WRI$_ALERT_SEQUENCE                   303            1        304
AQ$_ALERT_QT_N                         41            1         42
WRI$_ALERT_THRSLOG_SEQUENCE             1            1          2
WRI$_SQLSET_ID_SEQ                      1            1          2
WRI$_SQLSET_REF_ID_SEQ                  1            1          2
WRI$_SQLSET_STMT_ID_SEQ                 1            1          2

SEQUENCE_NAME                    CURR_VAL INCREMENT_BY   NEXT_VAL
------------------------------ ---------- ------------ ----------
WRR$_CAPTURE_ID                         1            1          2
AQ$_SYS$SERVICE_METRICS_TAB_N          21            1         22
JAVA$PREFS$SEQ$                        21            1         22
REDEF_SEQ$                              1            1          2
CACHE_STATS_SEQ_1                       1            1          2
CACHE_STATS_SEQ_0                       1            1          2
TSM_MIG_SEQ$                            1            1          2
UTL_RECOMP_SEQ                          1            1          2

74 rows selected.

SQL>

Using sequences is the most reliable way to populate sequentially numbered columns, as the sequence values are generated on a per-session basis and no two sessions can have duplicate sequence numbers. It makes for less coding later and more reliable (and scalable) inserts in the long run.

Sequences. They can be good for what ‘ails’ you.

June 3, 2008

How About A Date?

Filed under: dates,General — dfitzjarrell @ 12:28

It seems that DATE fields in an Oracle database can be rather confusing, especially when attempting to extract various portions of the date string. Enter the TO_CHAR() function:

TO_CHAR(, [format])

where is usually a database column. The various pieces you can use to build your [format] can be found here. DATE columns contain both the date and time in a proprietary Oracle structure, and either or both can be extracted and displayed in various formats. To extract just the date from a DATE field and display it as a string you can write this:

TO_CHAR(mydate, 'DD-MON-YYYY')

or this:

TO_CHAR(mydate, 'Month DDth, YYYY')

or this:

TO_CHAR(mydate, 'MM/DD/YY')

or any of a number of other combinations. As expected the time portion can also be extracted and displayed by itself:

TO_CHAR(mydate, 'HH24:MI:SS')

to return time in a 24-hour, military format or in a ‘standard’ 12-hour format:

TO_CHAR(mydate, 'HH:MI:SS AM')

which will append either AM or PM to the time string. You can also use these same format masks with the TO_DATE function, converting character strings into valid Oracle date values:

TO_DATE('12:36:54 PM', 'HH:MI:SS AM')

although just supplying a time to the TO_DATE function will, by default, set the actual date to January 1, 1900 (probably not what you had intended). TO_DATE works best with either date strings or date/time strings; in the absence of a time value TO_DATE will set that portion of the date value to midnight (00:00:00 in 24-hour format):

SQL> create table datetst(mydate date);

Table created.

SQL> --
SQL> -- Inserts current date and time
SQL> --
SQL> insert into datetst values (sysdate);

1 row created.

SQL> --
SQL> -- Sets the time portion to midnight
SQL> --
SQL> insert into datetst values(to_date('05/30/08','MM/DD/YY'));

1 row created.

SQL> select to_char(mydate, 'DD-MON-YYYY HH24:MI:SS') from datetst;

TO_CHAR(MYDATE,'DD-M
--------------------
03-JUN-2008 07:49:12
30-MAY-2008 00:00:00

SQL>

You may have noticed the ‘shorthand’ Oracle uses for the current date and time: SYSDATE This allows you to select the current date and time (as set on the database server) whenever you want it. SYSDATE is called a pseudo-column, one that exists in the absence of any table, meaning you can select SYSDATE from any table:

SQL> select sysdate from datetst;

SYSDATE
---------
03-JUN-08
03-JUN-08

SQL> select sysdate from dual;

SYSDATE
---------
03-JUN-08

SQL>

Pay close attention to the example above, as selecting SYSDATE from a table having more than one row can produce a LOT of output you probably hadn’t planned to see. Pseudo-columns return their value once for each row in the source table or in the result set (when restrictions are in place). Selecting SYSDATE from, say, the USER_OBJECTS table can generate a long list of values:

SQL> select sysdate from user_objects

SYSDATE
---------
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08

SYSDATE
---------
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08

SYSDATE
---------
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08

SYSDATE
---------
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08

SYSDATE
---------
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08

SYSDATE
---------
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08

SYSDATE
---------
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08

SYSDATE
---------
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08

SYSDATE
---------
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08

SYSDATE
---------
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08

SYSDATE
---------
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08

SYSDATE
---------
03-JUN-08
03-JUN-08

123 rows selected.

SQL>

And I guess now is as good of a time as any to introduce another pseudo-column, ROWNUM. ROWNUM is assigned to each row of a result set, starting with 1:

SQL> select rownum from user_objects;

    ROWNUM
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11

    ROWNUM
----------
        12
        13
        14
        15
        16
        17
        18
        19
        20
        21
        22

    ROWNUM
----------
        23
        24
        25
        26
        27
        28
        29
        30
        31
        32
        33

    ROWNUM
----------
        34
        35
        36
        37
        38
        39
        40
        41
        42
        43
        44

    ROWNUM
----------
        45
        46
        47
        48
        49
        50
        51
        52
        53
        54
        55

    ROWNUM
----------
        56
        57
        58
        59
        60
        61
        62
        63
        64
        65
        66

    ROWNUM
----------
        67
        68
        69
        70
        71
        72
        73
        74
        75
        76
        77

    ROWNUM
----------
        78
        79
        80
        81
        82
        83
        84
        85
        86
        87
        88

    ROWNUM
----------
        89
        90
        91
        92
        93
        94
        95
        96
        97
        98
        99

    ROWNUM
----------
       100
       101
       102
       103
       104
       105
       106
       107
       108
       109
       110

    ROWNUM
----------
       111
       112
       113
       114
       115
       116
       117
       118
       119
       120
       121

    ROWNUM
----------
       122
       123

123 rows selected.

SQL>

So, to restrict the output to 1 row from any table you can write:

SQL> select sysdate
  2  from user_objects
  3  where rownum = 1;

SYSDATE
---------
03-JUN-08

SQL>

and be guaranteed of returning only one row (well, unless the table is completely empty). ROWNUM starts with 1 ONLY when rows are successfully returned based upon the specified criteria. The following query, as shown, returns nothing:

SQL> select object_name
  2  from user_objects
  3  where rownum = 44;

no rows selected

SQL>

Since ROWNUM can never assign the value of 1 it never gets to your desired value of 44 and, unfortunately, never returns any data. There is a ‘workaround’ for this, but it sidetracks the discussion on date strings and I’ll save it for another post.

To be guaranteed of always returning ONE value from a pseudo-column Oracle has provided a wonderful table named DUAL which contains, you guessed it, only one row. Miraculous. So, you can ALWAYS have one row returned without any fuss:

SQL> select sysdate from dual;

SYSDATE
---------
03-JUN-08

SQL>

I’ve digressed a bit from our date discussion to define a couple of (as I see them) useful pseudo-columns, ROWNUM and SYSDATE (and, gee, one of them is date-related). But the thrust here is to provide a way for you to extract useful information from Oracle DATE fields. And I think the examples provided should help you do just that. And, just to be on the safe side, let’s extract the date and time from SYSDATE in several ways:

SQL> select to_char(sysdate, 'MM-DD-YYYY') from dual;

TO_CHAR(SY
----------
06-04-2008

SQL> select to_char(sysdate, 'Month DDth, YYYY') from dual;

TO_CHAR(SYSDATE,'MON
--------------------
June      04TH, 2008

SQL> select to_char(sysdate, 'DD/MM/YYYY') from dual;

TO_CHAR(SY
----------
04/06/2008

SQL> select to_char(sysdate, 'HH24:MI:SS') from dual;

TO_CHAR(
--------
14:50:49

SQL> select to_char(sysdate, 'HH:MI:SS AM') from dual;

TO_CHAR(SYS
-----------
02:51:00 PM

SQL>

Let’s even convert a date string from one format to another:

SQL> select to_char(to_date('1/1/2009','mm/dd/rrrr'), 'dd-mon-rr')
  2  from dual;

TO_CHAR(T
---------
01-jan-09

SQL>

Or convert a date string with non-format characters:

SQL> select to_date('2011-05-24T23:21:30.000Z','yyyy-mm-dd"T"hh24:mi:ss".000Z"') from dual;


TO_DATE('2011-05-24
-------------------
2011-05-24 23:21:30


1 row selected.
SQL>

[My thanks to Wolfgang Breitling for reminding me that non-standard characters can be included in a format mask as long as they are enclosed in quotation marks and for providing the example shown above.]

I suppose you could also update just the time portion of a date field, but it gets rather convoluted:

SQL> select *
  2  from datetst;

MYDATE
--------------------
03-JUN-2008 07:49:12
30-MAY-2008 00:00:00

SQL> update datetst
  2  set mydate = to_date(to_char(mydate, 'MM-DD-YYYY')||' 23:59:59', 'MM-DD-YYYY HH24:MI:SS')
  3  where to_char(mydate, 'DD-MON-YY') = '30-MAY-08';

1 row updated.
SQL> select *
2 from datetst;

MYDATE
--------------------
03-JUN-2008 07:49:12
30-MAY-2008 23:59:59

SQL>

And you can also use Julian date format, which counts the number of days from 01-01-4712 BC to the desired date, putting these values in the 2.4 million range of numbers:

SQL> select to_char(sysdate, 'J') from dual;

TO_CHAR
-------
2454642

SQL>

And, yes, you can go through the gyrations of converting the dates in question to Julian format and subtracting them to get the number of days between, but simple subtraction of standard DATE format values will also provide basically the same result:

SQL> select to_number(to_char(sdate2, 'J')) - to_number(to_char(sdate1, 'J')) diff
2 from dual;

DIFF
-------
      1

SQL>

Straight subtraction of the two dates would produce the days difference as well, but would also include the fractional part of the day:

SQL> select sdate2 - sdate1 diff from dual;

DIFF
----------
.958333333

SQL>

A quick call to ROUND() ‘fixes’ the issue:

SQL> select round(sdate2 - sdate1, 0) diff from dual;

DIFF
-------
      1

SQL>

Yet another way is to truncate the date values, then subtract:

SQL> create table diffdt(
  2          sdate1  date,
  3          sdate2  date
  4  );

Table created.

SQL>
SQL> insert into diffdt
  2  values (trunc(sysdate) - .1, sysdate);

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select sdate2 - sdate1
  2  from diffdt;

SDATE2-SDATE1
-------------
   .668564815

SQL>
SQL> select trunc(sdate2) - trunc(sdate1)
  2  from diffdt;

TRUNC(SDATE2)-TRUNC(SDATE1)
---------------------------
                          1

SQL>

We can even get complex and convert a date string in one format to a date string in another format:

SQL> select to_char(to_date('17-dec-80','DD-mon-RR'), 'mm/dd/yyyy') mydate
  2  from dual
  3  /

MYDATE
----------
12/17/1980

SQL> select to_char(to_date('1/1/2009','mm/dd/rrrr'), 'Month, RRRR') mydate
  2  from dual;

MYDATE
---------------
January  , 2009

SQL> select to_char(to_date('1/1/2009','mm/dd/rrrr'), 'Month Dth, RRRR') mydate
  2  from dual;

MYDATE
-------------------
January   5th, 2009

SQL> select to_char(to_date('1/1/2009','mm/dd/rrrr'), 'Month DD, RRRR') mydate
  2  from dual;

MYDATE
------------------
January   01, 2009

SQL>

Using the RR format specifier in place of the YY specifier allows Oracle to return the ‘correct’ four-digit year (presuming the date in this example is a historical one). Using YY in place of RR produces:

SQL> select to_char(to_date('17-dec-80','DD-mon-YY'), 'mm/dd/yyyy') mydate
  2  from dual;

MYDATE
----------
12/17/2080

SQL>

which is, in my mind, clearly not the year the original string represents (as I expect this date comes from the standard EMP play table provided by Oracle). Remember, though, that display formats are just that and have no bearing on how the actual date is stored in Oracle.

Can Oracle handle ‘pre-historic’ dates? Well, it can handle dates as far back as January 1, 4712 BC:

SQL> create table old_dts(
  2          really_old_dt date
  3  );

Table created.

SQL>
SQL> insert into old_dts
  2  values (to_date('-4712/01/01', 'syyyy/mm/dd'));

1 row created.

SQL>
SQL> alter session set nls_date_Format = 'MM/DD/YYYY AD';

Session altered.

SQL>
SQL> select really_old_dt
  2  from old_dts;

REALLY_OLD_DT
-------------
01/01/4712 BC

SQL>

So, why stop at January 1, 4712 BC? Let’s find out:

SQL> select to_char(really_old_dt, 'J') julian
  2  from old_dts;

JULIAN
-------
0000001

SQL>

Seems it’s the starting point for Julian date generation. Since that mechanism is integral to generating dates in Oracle’s internal date format it’s the logical place to start. Which is inconvenient if you have those in your family tree born, say, on August 23, 4728 BC as no corresponding Julian date number exists.

You don’t need to specify four-digit dates to Oracle, either, as one, two and three-digit years are valid:

SQL> select to_date('01SEP1', 'DDMONRRRR') from dual;

TO_DATE('01
-----------
01-SEP-2001

SQL> select to_date('01SEP01', 'DDMONRRRR') from dual;

TO_DATE('01
-----------
01-SEP-2001

SQL> select to_date('01SEP001', 'DDMONRRRR') from dual;

TO_DATE('01
-----------
01-SEP-2001

When you’re using the RRRR format you get the current century by default; using YYYY instead produces different results:

SQL> select to_date('01SEP1', 'DDMONYYYY') from dual;

TO_DATE('01
-----------
01-SEP-0001

SQL>
SQL> select to_date('01SEP01', 'DDMONYYYY') from dual;

TO_DATE('01
-----------
01-SEP-0001

SQL>
SQL> select to_date('01SEP001', 'DDMONYYYY') from dual;

TO_DATE('01
-----------
01-SEP-0001

SQL>

No errors were generated proving that any valid year can be provided to the function and, depending on the format you use, valid dates are returned.

You can also adjust the SYSDATE value forwards, or backwards, by adding or subtracting hours but it requires that you divide the total hours of adjustment by 24 since the expected adjustment interval is in days. To add 72 hours to the current SYSDATE value:

SQL> select sysdate, sysdate +(72/24)
  2  from dual;

SYSDATE   SYSDATE+(
--------- ---------
01-JUL-08 04-JUL-08

SQL>

It’s just as easy to subtract 72 hours:

SQL> select sysdate, sysdate - (72/24)
  2  from dual;

SYSDATE   SYSDATE-(
--------- ---------
01-JUL-08 28-JUN-08

SQL>

You can also adjust the value hy minutes; use 1440 in place of 24 in the above query. And, if you want to adjust by seconds, use 86400 instead of 1440. Pretty slick stuff.

Oh, if you want the number of elapsed seconds you can get that, too, but it’s only reported since midnight of the date supplied:

SQL> select to_number(to_char(sysdate, 'SSSSS')) seconds
  2  from dual;

   SECONDS
----------
     33603

SQL>

So if you try to use that logic to add the total seconds for two dates:

SQL> select to_number(to_char(sysdate, 'SSSSS')) + to_number(to_char(sysdate-32, 'SSSSS')) seconds
  2  from dual;

   SECONDS
----------
     67206

SQL>

you return a horribly incorrect answer because the sum is only calculating the seconds from midnight for each day involved. To get the total seconds for a given date range a different query is required:

SQL> select (sysdate - (sysdate-32))*86400 seconds
  2  from dual;

   SECONDS
----------
   2764800

SQL>

Notice the difference in the values returned; there are obviously far more than 67206 elapsed seconds in a 32-day span. Time flies, but not nearly that quickly.

What if you want the difference displayed in hours, minutes and seconds? That’s an easy task because, since 9.2, Oracle provides the INTERVAL data type. Converting subtracted dates to an INTERVAL is easy, as shown below. The first set of output is from 9.2.0.8:

SQL> create table diffdt(
  2          sdate1  date,
  3          sdate2  date
  4  );

Table created.

SQL>
SQL> insert into diffdt
  2  values (trunc(sysdate) - .1, sysdate);

1 row created.

SQL>
SQL> insert into diffdt
  2  values (trunc(sysdate) - .3, sysdate);

1 row created.

SQL>
SQL> insert into diffdt
  2  values (trunc(sysdate) - .5, sysdate);

1 row created.

SQL>
SQL> insert into diffdt
  2  values (trunc(sysdate) - .8, sysdate);

1 row created.

SQL>
SQL> insert into diffdt
  2  values (trunc(sysdate) - 1, sysdate);

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select (sdate2 - sdate1) day(9) to second
  2  from diffdt;

(SDATE2-SDATE1)DAY(9)TOSECOND
---------------------------------------------------------------------------
+000000000 12:55:56
+000000000 17:43:56
+000000000 22:31:56
+000000001 05:43:56
+000000001 10:31:56

SQL>

The next set of results are from an 11.1.0.6 instance:

SQL> create table diffdt(
  2          sdate1  date,
  3          sdate2  date
  4  );

Table created.

SQL>
SQL> insert into diffdt
  2  values (trunc(sysdate) - .1, sysdate);

1 row created.

SQL>
SQL> insert into diffdt
  2  values (trunc(sysdate) - .3, sysdate);

1 row created.

SQL>
SQL> insert into diffdt
  2  values (trunc(sysdate) - .5, sysdate);

1 row created.

SQL>
SQL> insert into diffdt
  2  values (trunc(sysdate) - .8, sysdate);

1 row created.

SQL>
SQL> insert into diffdt
  2  values (trunc(sysdate) - 1, sysdate);

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select (sdate2 - sdate1) day(9) to second
  2  from diffdt;

(SDATE2-SDATE1)DAY(9)TOSECOND
---------------------------------------------------------------------------
+000000000 12:53:20.000000
+000000000 17:41:20.000000
+000000000 22:29:20.000000
+000000001 05:41:20.000000
+000000001 10:29:20.000000

SQL>

Note that in 9.2 the output is in days hours:minutes:seconds, and in 11.1 and later releases the output includes the fraction of a second placeholder (since these are dates, not timestamps, the additional timing detail is not available). If we choose to use the SYSTIMESTAMP pseudocolumn in place of SDATE2 we can return the fractions of a second:

SQL> select (systimestamp - sdate1) day(9) to second
  2  from diffdt;

(SYSTIMESTAMP-SDATE1)DAY(9)TOSECOND
---------------------------------------------------------------------------
+000000000 13:14:42.096000
+000000000 18:02:42.096000
+000000000 22:50:42.096000
+000000001 06:02:42.096000
+000000001 10:50:42.096000

SQL>

How do we do this? Look carefully at the query syntax and you’ll see the DAY(width) TO SECOND directive, where the (width) parameter is optional. This tells Oracle to return the difference as an interval of days, houre, minutes, seconds and, if in a 10g or later database, the fractional part of a second. Any interval less than a day is returned in the +days hours:minutes:seconds.fraction format with the +days value as 0. Any difference greater than a day includes the number of days difference plus the time less than a day in hours, minutes, seconds and fractions thereof. Boy, you can’t get much easier than that.

[You can also set the format for timestamp values to display military time using

SQL > alter session set NLS_TIMESTAMP_TZ_FORMAT='RRRR-MM-DD HH24:MI:SS.FF TZR';

Session altered.

SQL > select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
2010-04-15 08:15:13.287210 -07:00

SQL >

]

So, have yourself a date. Or two. Or three. They’re fun.

June 2, 2008

That’s A Wrap

Filed under: General,pl/sql — dfitzjarrell @ 16:10

Security is at the top of most management To-Do lists these days, and keeping application code obscured from prying eyes can be a priority in some organizations. Usually that’s solved by the nature of the compiler-based application (where one writes source code, compiles and links that into an executable and then distributes that executable and its ancillary files to end users), but PL/SQL isn’t a compiled language, so how does one obfuscate the source code so it can’t be read or modified by those not authorized to do so? Oracle comes to the rescue with the wrap utility.

The wrap utility (an external program installed with the Oracle software) has been around for years and uses a proprietary algorithm to ‘scramble’ the source code so as to make it essentially unreadable by the human eye. To illustrate what the wrap utility can accomplish let’s look at a pair of files declaring a package specification and a package body. First, the package specification:

CREATE PACKAGE dates_pkg
AS
    FUNCTION julian_date
        ( date_to_convert DATE )
        RETURN NUMBER;

    FUNCTION minutes_since_midnight
        ( timevalue DATE )
        RETURN NUMBER;

    FUNCTION minutes_elapsed
        ( lowdate DATE
        , highdate DATE )
        RETURN NUMBER;

END dates_pkg;
/

Nothing here the everyday user can’t see, so we’ll leave this one unscrambled. Now let’s look at the package body:

CREATE PACKAGE BODY dates_pkg
AS
    FUNCTION julian_date
        ( date_to_convert DATE)
        RETURN NUMBER
    IS
        varch_value VARCHAR (10);
        num_value NUMBER (20);
    BEGIN
 --
 -- First, we take a date and convert it to a date by converting it
 -- to a character string using the same format we will use to
 -- convert it BACK to a date again
 --
 -- Oh, then we convert it back to a character string
 --
 -- In Julian format, which is a number
 --
        SELECT TO_CHAR
               ( TO_DATE(TO_CHAR(date_to_convert,'MM/DD/YYYY'),'MM/DD/YYYY')
               , 'J')
        INTO   varch_value
        FROM   dual;

 --
 -- Okay, so we had a Julian date as a number but we changed it to
 -- a character string so we could go back and make it a ...
 -- NUMBER ... again
 --
        SELECT TO_NUMBER (varch_value)
        INTO   num_value
        FROM   dual;

 --
 -- So, we finally make up our mind and keep it a number and
 -- return it from the function
 --
        RETURN (num_value);
    END julian_date;


    FUNCTION minutes_since_midnight (
        timevalue DATE)
        RETURN NUMBER
    IS
        secs_elapsed NUMBER (20);
        mins_elapsed NUMBER (20);
    BEGIN
 --
 -- So now we take a date and extract the time portion of it,
 -- convert that BACK to a date, then convert THAT to a string
 -- of seconds and convert THAT to a number
 --
 -- Is it me, or are we essentially driving across town just to
 -- go next door?
 --
        SELECT TO_NUMBER
               ( TO_CHAR(TO_DATE(TO_CHAR(timevalue,'HH:MI AM'),'HH:MI AM')
               , 'SSSSS') )
        INTO   secs_elapsed
        FROM   dual;

 --
 -- Oooo, now we divide that total number of seconds by ...
 -- wait for it ...
 -- any second now ...
 -- 60!  Who would have thought that 60 seconds equals
 -- one minute?
 --
        SELECT (secs_elapsed / 60)
        INTO   mins_elapsed
        FROM   dual;

 --
 -- Before we rest on our laurels we return the minutes since midnight
 --
        RETURN (mins_elapsed);
    END minutes_since_midnight;


    FUNCTION minutes_elapsed
        ( lowdate DATE
        , highdate DATE )
        RETURN NUMBER
    IS
        final_number NUMBER (20);
        low_julian NUMBER (20);
        high_julian NUMBER (20);
        num_days NUMBER (20);
        num_minutes NUMBER (20);
        temp_mins NUMBER (20);
        min_low NUMBER (20);
        min_high NUMBER (20);
    BEGIN
 --
 -- Now, why didn't we use this julian_date function in the
 -- last installment of Julian conversions?
 --
 -- Oh, yeah, because we just WROTE that wonderful function
 --
 -- So, okay, we take our date values and return the Julian
 -- representations of them using all of the mathematical
 -- aerobics from earlier
 --
 -- I guess this is so much easier than simply subtracting
 -- them
 --
        SELECT julian_date (lowdate)
        INTO   low_julian
        FROM   dual;

        SELECT julian_date (highdate)
        INTO   high_julian
        FROM   dual;

 --
 -- Woo-hoo! Higher math time!  Subtract the Julian dates
 -- and get the number of days
 --
 -- Isn't that what we'd get if we just subtracted the
 -- submitted dates as-is?
 --
 -- Of course it is
 --
        SELECT (high_julian - low_julian)
        INTO   num_days
        FROM   dual;

 --
 -- Now we calculate the total minutes elapsed
 -- using our values generated by our extreme
 -- gyrations
 --
 -- I'm out of breath just thinking about all of this work
 --
        SELECT (num_days * 1440)
        INTO   num_minutes
        FROM   dual;

 --
 -- And now we put those other mathematical moves
 -- to use
 --
 -- Tell me again why we think we're smarter than
 -- the average bear?
 --
        SELECT minutes_since_midnight (lowdate)
        INTO   min_low
        FROM   dual;

        SELECT minutes_since_midnight (highdate)
        INTO   min_high
        FROM   dual;

 --
 -- Now this is disgusting
 --
 -- Using a TEMP variable to aid in simple mathematical
 -- processing
 --
        SELECT (min_high - min_low)
        INTO   temp_mins
        FROM   dual;

 --
 -- And this is better than:
 -- select (end_date - start_date)*1440 because?
 --
        SELECT (num_minutes + temp_mins)
        INTO   final_number
        FROM   dual;

        RETURN (final_number);

    END minutes_elapsed;
END dates_pkg;
/

There are some areas here which would be good to obscure (such as the actual program logic and my dripping sarcasm). Let’s run this through wrap and see what results:

wrap iname=datepkg.pls

PL/SQL Wrapper: Release 10.2.0.3.0- Production on Mon Jun 02 12:04:26 2008

Copyright (c) 1993, 2004, Oracle.  All rights reserved.

Processing datepkg.pls to datepkg.plb

(Note that wrap requires only an input file name; it will supply the output file name from the input file and change the extension to plb. You can, of course, supply an output file name using the oname parameter:

wrap iname=myfile.sql oname=yourfile.plq

and the wrap utility will be perfectly happy. I prefer to accept the default behaviour.)

So now we look at the contents of datepkg.plb and see what wrap hath wrought:

CREATE PACKAGE BODY dates_pkg wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
b
8fd 2ea
HqtWzGgdR01NBtyeAlYcu2V4y4Awg5DDLtAFyi/NDfmUx4K5yY1+DfxrrfMT24qrdohuXNxN
CQocIyZ3+aqU08q0OcZMwOM4QHgsu3+AcbwGHTdMupDu+MSdJPlNmp0/yVSRDrG8wWMaYgec
vGt1Cp4MwH91At1+jX5cMZu2KV0BDzASgJGaUo8ABfe5wPICeEG46jz8RhNBcZ1SW33eXn7t
B7wXzeo2Z0+QQoDLiRcsSSS/XMhAidNZyauxLPg0Da8aGNgdqz0tl4i66j2S2OqHdL8LS5Rd
lq8+Q8KkK8Eu+OIBkmT8UgPXybefgTkWt+e82r+lgJHKqnJh8aXFFOqadtNyFrts8/Jph9OV
EZUpa6epeksaX3HekigzRD4CZrIingkuKSoMxpAI45nKPfpNptHb0AKPbZNDMnONzN1H548z
pGsPdY0ffGsWVpvinmY6jDWcPKUBgr3zn97f21Q57pVnvVJht1EEgGBQoCDoa3wEjtVNKl8k
BwJmMV5K/f4EUWazs3dcNFKYvdoecIeEAnJmeQ1j1KQUOICGqPGrK3uVoLNdxM9FbJHTNIdn
YbW2a2h7jcj6dk0LIKyr0r/33QtmKK0jdJtf3HpqPZhB8UmStnoAJCBRj6f8/o+w6JEYywpf
AXEa3begV95iTZFoSWSXgJyGNlVt+cvPX+H+7BxIFrbkpTGa3sgTye6NFYNqUsnBd9+CB+gB
5qQPKAUotaZBuVV7

/

It doesn’t look the same as the source code, does it. The wrap utility was provided to do just that, obscure PL/SQL code/logic so the end user can’t see it. Installing either of the package bodies (datepkg.pls or datepkg.plb) results in the same functional code in the database, it’s just that the datepkg.plb script can’t be decoded by anyone except Oracle (and, in any of the SOURCE views [DBA_SOURCE, USER_SOURCE] all that is revealed is the wrapped text). That, of course, means THERE IS NO ORACLE-SUPPLIED OR ORACLE SUPPORTED UNWRAP UTILITY; you’ll need to save your original source code in the event you need to modify it due to bug fixes (heaven forbid) and/or functionality changes since you won’t get it back from the wrapped file.

How do you execute a .plb file? Pretty much like you’d run any other .sql script:

SQL> @datepkg
SQL> CREATE OR REPLACE PACKAGE dates_pkg
  2  AS
  3      FUNCTION julian_date
  4          ( date_to_convert DATE )
  5          RETURN NUMBER;
  6
  7      FUNCTION minutes_since_midnight
  8          ( timevalue DATE )
  9          RETURN NUMBER;
 10
 11      FUNCTION minutes_elapsed
 12          ( lowdate DATE
 13          , highdate DATE )
 14          RETURN NUMBER;
 15
 16  END dates_pkg;
 17  /

Package created.

SQL>
SQL> @datepkg.plb
SQL> CREATE or replace PACKAGE BODY dates_pkg wrapped
  2  a000000
  3  b2
  4  abcd
  5  abcd
  6  abcd
  7  abcd
  8  abcd
  9  abcd
 10  abcd
 11  abcd
 12  abcd
 13  abcd
 14  abcd
 15  abcd
 16  abcd
 17  abcd
 18  abcd
 19  b
 20  8fd 2ea
 21  HqtWzGgdR01NBtyeAlYcu2V4y4Awg5DDLtAFyi/NDfmUx4K5yY1+DfxrrfMT24qrdohuXNxN
 22  CQocIyZ3+aqU08q0OcZMwOM4QHgsu3+AcbwGHTdMupDu+MSdJPlNmp0/yVSRDrG8wWMaYgec
 23  vGt1Cp4MwH91At1+jX5cMZu2KV0BDzASgJGaUo8ABfe5wPICeEG46jz8RhNBcZ1SW33eXn7t
 24  B7wXzeo2Z0+QQoDLiRcsSSS/XMhAidNZyauxLPg0Da8aGNgdqz0tl4i66j2S2OqHdL8LS5Rd
 25  lq8+Q8KkK8Eu+OIBkmT8UgPXybefgTkWt+e82r+lgJHKqnJh8aXFFOqadtNyFrts8/Jph9OV
 26  EZUpa6epeksaX3HekigzRD4CZrIingkuKSoMxpAI45nKPfpNptHb0AKPbZNDMnONzN1H548z
 27  pGsPdY0ffGsWVpvinmY6jDWcPKUBgr3zn97f21Q57pVnvVJht1EEgGBQoCDoa3wEjtVNKl8k
 28  BwJmMV5K/f4EUWazs3dcNFKYvdoecIeEAnJmeQ1j1KQUOICGqPGrK3uVoLNdxM9FbJHTNIdn
 29  YbW2a2h7jcj6dk0LIKyr0r/33QtmKK0jdJtf3HpqPZhB8UmStnoAJCBRj6f8/o+w6JEYywpf
 30  AXEa3begV95iTZFoSWSXgJyGNlVt+cvPX+H+7BxIFrbkpTGa3sgTye6NFYNqUsnBd9+CB+gB
 31  5qQPKAUotaZBuVV7
 32
 33  /

Package body created.

SQL>

In 10g and later releases of Oracle a second method of wrapping PL/SQL source code is available with the DBMS_DDL.WRAP function. This is designed for use with dynamic PL/SQL statements:

declare
     ddl varchar2(32767);
begin
     ddl := 'create or replace procedure ...';
     execute immediate dbms_ddl.wrap(ddl);  -- 'Wraps' the procedure then executes it
end;
/

[There is a known bug with dbms_ddl.wrap and multibyte character sets (like Japanese) in base release 10.2.0.1; bug 4577670 causes an ORA-22921 error to be generated. This is fixed in patchset 10.2.0.2 and in release 11.1.0.6.]

If you’re concerned with unauthorized persons viewing sensitive PL/SQL source code then the wrap utility is probably something you should investigate. Remember, though, that you CAN’T* ‘unwrap’ the wrapped code later, so save your original source code somewhere safe for when you next need it.

* There are third-party unwrap tools for 9iR2 and earlier releases of Oracle, but, again, these are not supported by Oracle Corporation. As a result of this the wrap mechanism in 10g and later releases has changed and these tools will not work on wrapped code in any release after 9.2.0.x. You’re welcome to search google.com for them; I’ll not supply any links to them here.

And, that’s a wrap.

Blog at WordPress.com.