Olaf Heimburger's Blog

You may have noticed it, this is the missing step from the last post...

Deploying the Database

Once the database schema modeling is finished it needs to be deployed to the database. There are several options and a good database administrator (DBA) could improve the database behaviour considerably. Since this is not a DBA blog we leave this as an exercise to the reader.

Scripts to Create the Database User

In an Oracle Database a database user is the same as the schema user by default. It is also a good habit to create a tablespace for that user. First, we create a script for creating the tablespace:

1. Click on the Offline Database Sources
2. Open the Context Menu
3. Select New ...
4. Expand Database Tier
5. Select Database Files
6. On the right select SQL File
7. Click the OK button
8. In the opening Create SQL File window enter File Name: create_survey_tablespace.sql and make sure that the Directory Name has OnlineSurvey/Database/database (in JDeveloper 10.1.3) or OnlineSurvey/Database/database/SURVEY (in JDeveloper 11g) at the end.
9. Click on the OK button to create an empty file.
10. You will notice that the file appears right below the create_survey_schema.sql file in the Application Navigator.
11. In the tab named create_survey_tablespace.sql enter the following line. You might need to change your file path!
    

    CREATE TABLESPACE survey datafile 'C:\oracle\xe\oradata\XEsurvey.dbf'
    SIZE 500k reuse autoextend ON;

    
    
12. Repeat steps -10 to create a file called create_survey_user.sql. Put the following code in this file:
    

    DROP USER survey;
    CREATE USER survey IDENTIFIED BY survey DEFAULT TABLESPACE survey TEMPORARY
      TABLESPACE temp;
    GRANT connect,resource TO survey;
    

Running the SQL Files ...

All we need to do is to run the SQL files to complete the database deployment.

... In JDeveloper 10.1.3

Before we can do this, we need to check whether JDeveloper is configured to use SQL*Plus to execute the SQL scripts:

1. Open Tools->Preferences
2. Select Database Connections
3. If the text field for SQL*Plus Executable is completed you're done, otherwise we have to point it to the right executable.
4. Click on the Browse button
5. In the new Open file dialog navigate to the $ORACLE_HOME directory for XE. In the $ORACLE_HOME/bin directory you'll find an executable named sqlplus.exe or sqlplus.
6. Select sqlplus.exe and click on Open
7. Once this is done, select the create_survey_tablespace.sql and from the Context Menu follow the Run in SQL to select the sysXE entry. This will open a terminal window for SQL*Plus. For security reasons the password of the sys database user has to be entered. When this is done the script will be run and creates the tablespace.
8. Now do the same with the create_survey_user.sql.
   

To use this new user for later connections we create another Database Connection called surveyXE in the Connection Navigator (see ADF in Action: Setting up the Gear, Verifying the Database Connection for more details on this). With this connection we run the create_survey_schema.sql to create the full schema.

... In JDeveloper 11g

In 11g things are slightly different. We have Global Connections in the Resource Palette and Application-specific Connections in the Application Resources accordion.

To reuse the global database connection, we expand the sysXE database connection from the Resource Palette and drag it to the Application Resources Connections entry. (This is really cool!) It creates a Connections->Database->sysXE structure for the application also.

The rest is similar to JDeveloper 10.1.3. Just try it.

You should now have the Survey schema in your database. If not post a comment...