Before You Begin
This 10 minute tutorial shows you how to create a connection to the hr schema in Oracle SQL Developer. This is the second tutorial in the series Oracle Application Express 18.1: Using REST Enabled SQL Service Reference. Read the tutorials in sequence:
- Oracle Application Express 18.1: Using REST Enabled SQL Service Reference (1 of 4)
- Oracle Application Express 18.1: Using REST Enabled SQL Service Reference (2 of 4)
- Oracle Application Express 18.1: Using REST Enabled SQL Service Reference (3 of 4)
- Oracle Application Express 18.1: Using REST Enabled SQL Service Reference (4 of 4)
Background
In this OBE, you will learn how to create a connection to the
hr schema, and REST enable the hr schema.
Note: You can REST enable the hr schema in two ways - using the SQL Developer GUI and using the command line interface.
What Do You Need?
- Install the latest version of Oracle VM VirtualBox
- Download and import the Database App Development VM in to the Oracle VM VirtualBox
- Oracle SQL Developer 4.2
- Oracle Database 12c
- Oracle Application Express 18.1
Note: SQL Developer 4.2, Oracle Database 12c, and Oracle Application Express 18.1 are installed in the Database App Development VM
Create a Connection to the hr Schema in SQL Developer
To create a connection to the hr schema:
- Right click the SQL Developer icon and click Open.
- In Oracle SQL Developer, click the plus icon and click New Connection to create a new connection to hr schema.
- In the New/Select Database Connection dialog, select system on the left pane and specify the following details:
- Connection Name: Enter
hr
- Username: Enter
hr
- Password: Enter
oracle
- Hostname: Enter
localhost
- Port: Enter
1521
- Service Name: Enter
orcl
- Click Test to test the connection. Once
the connection is tested successfully, the status of the test
is displayed as Success, as shown in the
screenshot.
- Click Save and then click Connect
to create the connection to the hr schema. The connection hr
is now displayed under the Connections pane in Oracle SQL
Developer. This completes the task of creating a connection to
the hr schema.
Enable RESTful Services for hr Schema in SQL Developer GUI
To enable RESTful service for the hr schema using GUI:
- In Oracle SQL Developer, right click hr under Connections in the Connections pane.
- Select REST Services and click Enable
Rest Services. The RESTful Services
Wizard opens.
- In the RESTful Services Wizard - Step 1 of 2 dialog, select
Enable Schema and click Next.
- In the RESTful Services Wizard - Step 2 of 2 dialog, the Summary tab displays the summary of the RESTful service, such as the alias, status, authorization and so on. Click SQL tab.
- The SQL tab in the RESTful Services Wizard - Step 2
of 2 dialog, displays the SQL query to enable the RESTful
service. Click Finish.
- Once the RESTful service for the hr schema is enabled successfully, the message "Successfully processed SQL command" displays.
Enable RESTful Services Schema by Using the Command Line Interface
Using the command line is an alternative to using SQL Developer
for those who prefer command line tools to graphical user
interfaces. In the previous section, you enabled the hr
schema using SQL Developer GUI. If you prefer command line tools
to a graphical user interface, then you can enable the RESTful
schema using a command line interface.
In this section you will use a command line interface to first disable the previously enabled RESTful schema and then enable it again. Step 3 shows you how to disable the RESTful schema.
To enable RESTful schema using the command line interface:
- Right click on your VM desktop and click Open Terminal.
- In the terminal window, type
sqlplus hr/oracle
and press Enter.
- Since you already REST enabled the hr schema, you first need
to disable it and then enable it again. To disable the RESTful
schema, type
exec ords.enable_schema(p_enabled => FALSE);
and press Enter. - To enable the RESTful schema, type
exec ords.enable_schema;
and press Enter. - Type
commit;
This completes the task of enabling RESTful services for hr schema.