Dating with Oracle 11gR2

We all know that we should not use TWO DIGIT YEAR formats in database applications since more than a decade.
And we also know that we should never rely on implicit datatype conversion especially with dates.

I recently came across a very surprising effect with Oracle 11g R2 Enterprise Edition when I was testing with two digit years:

I convert a string with two digit year information into a date and then I convert this date into a string with four digit year format to see what the server made.

SQL> SELECT TO_CHAR(TO_DATE('07-11-58', 'dd-mm-yy'), 'dd-mm-yyyy') 
      FROM dual;

TO_CHAR(TO
----------
07-11-2058

The result is as expected: the ‘YY’ interpreted the ’58′ as belonging to the current century.

SQL> SELECT TO_CHAR(TO_DATE('07-11-58', 'dd-mm-rr'), 'dd-mm-yyyy') 
       FROM dual;

TO_CHAR(TO
----------
07-11-1958

Using the ‘RR’ format also does what what we expect, since 58 is more than 49 and we currently have the year 2012 which is less than 2050, the two digits of the year are interpreted as belonging to the past century.

So far so good and I agree with the server, but have a look at this:

SQL> SELECT TO_CHAR(TO_DATE('07-11-1958', 'dd-mm-rr'), 'dd-mm-yyyy')  
       FROM dual;

TO_CHAR(TO
----------
07-11-1958

I input a four digit year string and tell the server that it is a two digit year format. I am very surprised that the server is able to handle this anyway at all !!

And now let’s see what the server does with this:

SQL> SELECT TO_CHAR(TO_DATE('07-11-1958', 'dd-mm-yy'), 'dd-mm-yyyy') 
      FROM dual;

TO_CHAR(TO
----------
07-11-1958

This is strange, but obviously a feature, pls see the comment

APEX 4 for download

Oracle Technology Network now has the new APEX version 4 available for download.

The documentation and installation guides can be found here. 

The Advanced Tutorials Guide contains up-to-date How-Tos including:

• Create a Tabular Form
• Create a Parameterized Report
• Create a Drill Down Report
• Control Form Layout
• Work with Check Boxes
• Implement a Web Service
• Create a Stacked Bar Chart
• Upload and Download Files in an Application
• Incorporate JavaScript into an Application
• Build an Access Control Page
• Review a Packaged Application
• Create a Master Detail PDF Report
• Design an Issue Tracking Application
• Build and Deploy an Issue Tracking Application

The new features are documented here.

I will now update my courseware with the new features and deliver my courses as of August with the new version.

Arab Oracle User Group is online

I have just found the website of the Arab Orcale Usergroup

I was not aware that they are already online.

Congratulations!!!

=;-)

LH

APEX 4.0 for Early Adopters in phase II

Today I have received a notification from OTN that APEX 4 for Early Adopters is in testing phase II now.

It is possible to request a workspace now.

The documentation is still not available yet, instead the online docs for APEX 3.2 are linked.

Let’s test it.

=;-)

LH

Microsoft-Server-2008-Administration-Oracle

This week I deliver an 11g course in London downtown for a training partner of Oracle Education UK.

It is a very fun class and I have some very interesting people in the course.

 Mark Anderson
is a highly skilled trainer and delivers the Microsoft SQL Server 2008 for the Experienced Oracle Database Administrator course for internal Microsoft staff and is responsible for the high end Microsoft customers. This course is definitely one I would like to attend!

He is about to publish his latest  book on SQL SERVER for ORACLE DBAs shortly. I am pretty shure that this will be another candidate for my BOOKS WORTH THEIR MONEY category  and I will definitely get a copy ASAP..

=;-)

sysdba

Apex 4.0 available for testing

The new version of APEX is available for testing by early adopters.

Use this link to request a workspace as hosted environment:

I will post about my first impressions soon.

=;-)

Dropping composite Primary Key does not drop the index

Today I have encountered a surprising fact:

Scenario is 10.2.0.1  Express Edition on Win32.

I have a table called job_history in the HR scheme which has a composit primary key : and there is an index for the primary key:

SQL> select index_name from user_indexes where table_name=’JOB_HISTORY’;

INDEX_NAME

Does the following info help to explain?

——————————
JHIST_EMP_ID_ST_DATE_PK
JHIST_JOB_IX
JHIST_EMPLOYEE_IX
JHIST_DEPARTMENT_IX

This is my constraint in the data dictionary:

SQL> select column_name , position
  2   from user_cons_columns
  3  where constraint_name=’JHIST_EMP_ID_ST_DATE_PK’;

COLUMN_NAME      POSITION
———————–    ———————————
EMPLOYEE_ID        1

START_DATE          2

 

Now I drop the primary key constraint

SQL> alter table job_history drop constraint
  2  JHIST_EMP_ID_ST_DATE_PK including indexes;

Table altered.

And here is the surprise:

SQL> select index_name from user_indexes where table_name=’JOB_HISTORY’;

INDEX_NAME
——————————
JHIST_EMP_ID_ST_DATE_PK
JHIST_JOB_IX
JHIST_EMPLOYEE_IX
JHIST_DEPARTMENT_IX

I do not see the point here. Why is the index still there?

Evern though I have explicitly written INCLUDING INDEXES.

BUG or Feature?!

Can anyone explain?