Oracle by Example branding

Oracle Application Express 18.1: Using REST Enabled SQL Service Reference (2 of 4)

section 0Before 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:

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

section 1Create a Connection to the hr Schema in SQL Developer

To create a connection to the hr schema:

  1. Right click the SQL Developer icon and click Open.
  2. In Oracle SQL Developer, click the plus icon and click New Connection to create a new connection to hr schema.
    new connection
    Description of the illustration new_connection.png
  3. 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
  4. 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.
    new select database connection
    Description of the illustration connection_success.png
  5. 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.
    hr connection in SQL Developer
    Description of the illustration hr_connection.png

section 2Enable RESTful Services for hr Schema in SQL Developer GUI

To enable RESTful service for the hr schema using GUI: 

  1. In Oracle SQL Developer, right click hr under Connections in the Connections pane.
  2. Select REST Services and click Enable Rest Services. The RESTful Services Wizard opens.
    right click option under hr
    Description of the illustration enable_rest_services_option.png
  3. In the RESTful Services Wizard - Step 1 of 2 dialog, select Enable Schema and click Next.
    right click option under hr
    Description of the illustration restful_services_wizard_1.png
  4. 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.
    right click option under hr
    Description of the illustration restful_services_wizard_2.png
  5. The SQL tab in the RESTful Services Wizard - Step 2 of 2 dialog, displays the SQL query to enable the RESTful service. Click Finish.
    right click option under hr
    Description of the illustration restful_services_wizard_2sql.png
  6. Once the RESTful service for the hr schema is enabled successfully, the message "Successfully processed SQL command"  displays.
    message
    Description of the illustration restful_service_successful_msg.png

section 3Enable 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:

  1. Right click on your VM desktop and click Open Terminal.
  2. In the terminal window, type sqlplus hr/oracle and press Enter. This is the username and password to connect to Oracle Database. 
    command to connect to hr
    Description of the illustration terminal_1.png
  3. 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.
    command to disable schema
    Description of the illustration cli_disable_schema.png
  4. To enable the RESTful schema, type exec ords.enable_schema;  and press Enter.
    command to connect to hr
    Description of the illustration terminal_2.png
  5. Type commit; This completes the task of enabling RESTful services for hr schema.
    command to connect to hr
    Description of the illustration terminal_3.png

next stepNext Tutorial

                Oracle Application Express 18.1: Using REST Enabled SQL Service Reference (3 of 4)

more informationWant to Learn More?