Oracle HR Schema Objects in the Cloud
Many of the Oracle Java development tutorials that access a database do so against the Oracle HR sample schema. See, for example, Developing Rich Web Applications With Oracle ADF. There are several approaches you can take to getting the sample schema installed in the Oracle Cloud, such as Using SQL Developer or JDeveloper as demonstrated in Shay's blog. In my case, we're going to run the SQL scripts directly.
One thing to note when working with the Oracle Cloud is that you are given a single database schema, which appears to be just some randomly generated GUID. For example, my assigned schema is QBNVJDSFBKHK:
So, technically, we will not be creating the HR schema, but rather the HR schema objects (tables, views, indices, ...) in our Oracle Cloud provided schema.
Step 1: Upload SQL Scripts to Your Database Service
The scripts to create the HR demo schema are included with your installation of the Oracle Database. I have provided copies of them here as well. Right click the scripts and save them to your hard drive:
- hr_main.sql - Creates the HR schema and calls the other scripts. Since we already have a pre-assigned schema in the Oracle Cloud, we will not be using this script. I'm just providing it here for reference.
- hr_cre.sql - Creates the tables, sequences and constraints.
- hr_popul.sql - Populates the tables with demo data.
- hr_idx.sql - Creates the indices.
- hr_code.sql - Creates the procedural objects.
- hr_comnt.sql - Adds comments to the tables and columns.
- hr_drop.sql - Drops above created objects from the schema.
Sign in to access your Oracle Cloud Services and launch the database service. Then select SQL Workshop > SQL Scripts. Use the Upload button to Upload your scripts. At this time you need to upload them one at a time. When completed it should look as follows:
Step 2: Run the SQL Scripts
Click the arrow to run the the hr_cre.sql script. You'll get a notice about the statements that will be ignored, but these are mostly for output formatting which are not a concern in our case:
Click Run Now at the bottom of the page to run the script. After a couple seconds the job will complete:
View the Results and most importantly, note the bottom of the page which summarizes the number of successful statements and statements with errors:
Run the remaining scripts in the following order:
And that's it. Now you have the Oracle HR schema objects available to you in the cloud.