Connect with Built-in SQL Developer Web

You can access SQL Developer Web, a browser-based interface of Oracle SQL Developer, from Autonomous Transaction Processing. You can run SQL statements and scripts in a worksheet and perform other tasks on your database with SQL Developer Web.

About SQL Developer Web

Oracle SQL Developer Web in Autonomous Transaction Processing provides a development environment and a data modeler interface for Autonomous Transaction Processing.

The main features of SQL Developer Web are:

  • Run SQL statements and scripts in the worksheet

  • Export data

  • Design Data Modeler diagrams using existing objects

  • Provide a real-time SQL monitoring interface

SQL Developer Web is a browser-based interface of Oracle SQL Developer and provides a subset of the features of the desktop version. See About Oracle SQL Developer Web for more information.

Access SQL Developer Web as ADMIN

SQL Developer Web is bundled with each Autonomous Transaction Processing instance.

Oracle SQL Developer Web runs in Oracle REST Data Services and access is provided through schema-based authentication. To use Oracle SQL Developer Web, you must sign in as a database user whose schema is enabled for SQL Developer Web. By default the ADMIN user is enabled to access SQL Developer Web. See Provide SQL Developer Web Access to Database Users to enable another database user's schema to access SQL Developer Web.

To access SQL Developer Web:

  • Sign in to your Oracle Cloud Account at cloud.oracle.com.

  • From the Oracle Cloud Infrastructure left navigation list click Autonomous Transaction Processing.

  • On the Autonomous Databases page select an Autonomous Transaction Processing instance from the links under the Name column.

  1. On the instance details page click Service Console.
  2. Click Development.
  3. Click SQL Developer Web.
  4. In the SQL Developer Web Sign in page, enter your Username and Password.
  5. Click Sign in.

    This shows the SQL Developer Worksheet tab.

Provide SQL Developer Web Access to Database Users

The ADMIN user can provide access to SQL Developer Web to other database users.

Database users, who are not service administrators, do not have access to the Autonomous Database service console. The ADMIN user provides access to SQL Developer Web by enabling access for a user and providing a URL to access SQL Developer Web.

See Create Users with Autonomous Transaction Processing for information on adding database users.

To enable schema access to SQL Developer Web:

  1. Run the following code as the ADMIN user:
    BEGIN
       ORDS_ADMIN.ENABLE_SCHEMA(
         p_enabled => TRUE,
         p_schema => 'schema-name',
         p_url_mapping_type => 'BASE_PATH',
         p_url_mapping_pattern => 'schema-alias',
         p_auto_rest_auth => TRUE
       );
       COMMIT;
    END;
    /

    where:

    • schema-name is the database schema name in all-uppercase.
    • schema-alias is an alias for the schema name to use in the URL to access SQL Developer Web.
    • p_auto_rest_auth specifies the REST /metadata-catalog/ endpoint requires authorization. REST uses the metadata-catalog to get a list of published services on the schema. Set this parameter to TRUE.

    Note:

    Oracle recommends that you use the schema-allias and do not use the schema name itself to access SQL Developer Web, as described in the following step, as a security measure to keep the schema name from being exposed.
  2. After enabling user access for the specified schema, the ADMIN provides a user with the URL to access SQL Developer Web, as follows:
    1. Select the Autonomous Transaction Processing instance.
    2. On the instance details page click Service Console.
    3. Click Development.
    4. Right-click SQL Developer Web and choose Copy URL.

      The copied URL is the same as the URL the ADMIN enters to access SQL Developer Web. For example:

      https://dbname.adb.us-ashburn-1.example.com/ords/admin/_sdw/?nav=worksheet
    5. To provide a user with access to SQL Developer Web you need to edit the copied URL to use the alias for the schema specified with the parameter p_url_mapping_pattern in step 1.

      For a user to access SQL Developer Web the part of the copied URL with "admin" is replaced with the "schema-alias".

      For example, after editing you would provide this URL for the user to login:

      https://dbname.adb.us-ashburn-1.example.com/ords/schema-alias/_sdw/?nav=worksheet
  3. Provide the user with the modified URL.

To access SQL Developer Web a user pastes the URL into their browser and then enters the schema's Username and Password in the Sign-in dialog.