Getting samples schema to run against an Oracle DB

The samples domain that is able to be optionally created with a WebLogic Server installation provides a wealth of useful examples, covering core JavaEE APIs through to an full sample application called MedRec.

By default, the samples domain installs and uses a PointBase database to host its application data, which allows it to work immediately after installation with no additional requirements to install, configure a database.

It probably goes without saying, but there is interest in making the samples also work with an Oracle database.

When the samples domain is created, it provides its own set of online documentation that describes how to configure, install and use the various samples.

One section in the doc notes how to install the sample schema against an Oracle database, which ultimately results in the execution of the demo.ddl file against the specified Oracle database instance.

>ant db.setup.oracle

I just tried it against an Oracle XE (10.2) database I have lying around and noticed some problems when it executed, based on inserting date format data into several of the tables used by the MedRec application.

[sql] Failed to execute:   INSERT INTO patient (id,first_name,middle_name,last_name,dob,gender,ssn,address_id,phone,email) VALUES (101,'Fred','I','Winner','1965-03-26','Male','123456789',101,'4151234564','fred@golf.com')
[sql] java.sql.SQLDataException: ORA-01861: literal does not match format string

[sql] Failed to execute:   INSERT INTO record (id,pat_id,phys_id,record_date,vital_id,symptoms,diagnosis,notes) VALUES (101,101,102,'1999-06-18',101,'Complains about chest pain.','Mild stroke.  Aspiran advised.','Patient needs to stop smoking.')
[sql] java.sql.SQLDataException: ORA-01861: literal does not match format [sql] 

[sql] Failed to execute:   INSERT INTO prescription (id,pat_id,date_prescribed,drug,record_id,dosage,frequency,refills_remaining,instructions) VALUES (101,101,'1999-06-18','Advil',101,'100 tbls','1/4hrs',0,'No instructions')
[sql] java.sql.SQLDataException: ORA-01861: literal does not match format string

To workaround this, I found that by editing the $WLS_HOME\wlserver_10.3\samples\server\examples\src\examples\common\demo.ddl and appending the DATE function to the respective insert statements, the demo.ddl script executed without error and the data was inserted correctly.

Here are examples of the row inserts that have been modified:

INSERT INTO patient (id,first_name,middle_name,last_name,dob,gender,ssn,address_id,phone,email) 
VALUES 
(101,'Fred','I','Winner',DATE'1965-03-26','Male','123456789',101,'4151234564','fred@golf.com');

INSERT INTO record (id,pat_id,phys_id,record_date,vital_id,symptoms,diagnosis,notes) 
VALUES 
(101,101,102,DATE'1999-06-18',101,'Complains about chest pain.','Mild stroke.  Aspiran advised.','Patient needs to stop smoking.');

INSERT INTO prescription
(id,pat_id,date_prescribed,drug,record_id,dosage,frequency,refills_remaining,instructions) VALUES
(101,101,DATE'1999-06-18','Advil',101,'100 tbls','1/4hrs',0,'No instructions');

Once that was done, the demo.ddl executed successfully and the schema was created in the specified Oracle database.