In SQL Developer

SQL Developer Web now available for Oracle Autonomous Database

June 24, 2019 4 Mins Read

The Autonomous Database is offered via two services in our Oracle Cloud, Autonomous Data Warehouse and Autonomous Transaction Processing.

Available as of today, these services now also includes:

Oracle SQL Developer Web
Oracle REST Data Services for RESTful Services and SODA

From your Autonomous Database Service Console, you can launch SQL Developer Web for managing and working your Oracle Database. Run queries, create tables, generate schema diagrams, and much more.

SQL Developer is a mature desktop offering servicing more than 5,000,000 Oracle professionals around the world, and now it’s primary features are also available directly in your browser.

An Overview of SQL Developer Web

3 short videos demonstrating the major features

Accessing SQL Developer Web

Navigate to your Autonomous Database Service Console. Then, open the Development page, and you’re find the REST Data Services section. There you will find links for both APEX and SQL Developer Web.

You can find your Wallet/Zip on the Admin page if you want to connect with SQL Developer on your desktop.

This will open the application, and you will be prompted for a database user name and password.

Your ADMIN account is accessible via SQL Developer Web by default.

It’s all about the worksheet.

While SQL Developer Web has many compelling features, the biggest impact to your everyday experience with the Oracle Autonomous Database will be your ability to immediately start working with your instance via SQL and PL/SQL – directly in your browser via the SQL Worksheet.

Run queries, create objects, interact with your database with the same SQL and PL/SQL you use in SQL Developer on your desktop.

Help and Getting Started

In the Worksheet and Modeler screens, you’ll find a Binoculars button which launched a guide tour of that portion of the application.

The tour starts on your first visit to SQL Developer Web, but you can access it later any point using the Binoculars button next to the Help in the upper right hand corner of the worksheet.

And of course there is also the in-app Help and Documentation.

The underlined text at the end of the in-app feature help will navigate you to the relevant section of the Oracle Docs for SQL Developer Web.

Using APEX for the first time in Autonomous?

Yes, there’s also APEX support! When you click on the APEX link in the Console, you’ll get to here:

And now you you’ll want to go into the admin pages…

Click that box, and you’ll be into your administration pages where you can create your first workspace and assign it to a schema.

These pages should look familiar…

So you’re saying, well how do I create a new schema for my APEX application workspace?

Try SQL Developer Web!

Adding a new Oracle user, accessible by SQL Developer Web

Adding a user is adding a user, just like you’ve always done in an Oracle Database. Create the user, grant the CONNECT and whatever other privs they will need.

Now I have for example a new schema (THATJEFFSMITH)for my APEX Workspace.

BUT.

If I want to login as that new user using SQL Developer Web, I’ll need to REST Enable it:

--run this code as user THATJEFFSMITH, in say SQL Developer on your desktop
BEGIN
    ords.enable_schema (
        p_enabled               =>; TRUE,
        p_schema                => 'THATJEFFSMITH',
        p_url_mapping_type      => 'BASE_PATH',
        p_url_mapping_pattern   => 'tjs',
        p_auto_rest_auth        => TRUE
    );
    COMMIT;
END;
/
 
--or, logged in as ADMIN in SQL Developer Web, run this code:
BEGIN
    ords_admin.enable_schema (
        p_enabled               => TRUE,
        p_schema                => 'THATJEFFSMITH',
        p_url_mapping_type      => 'BASE_PATH',
        p_url_mapping_pattern   => 'tjs',
        p_auto_rest_auth        => TRUE
    );
    COMMIT;
END;
/

If you run this above code, you’ll be able to login to SQL Developer Web. Just mind that the URL will look have to look like this…

https://yourservicedomain-1.oraclecloudapps.com/ords/tjs/_sdw/?nav=worksheet

Note that I have /tjs/ in the URL? That’s the p_url_mapping_pattern. If you go to the same URL but with /admin/ in the link and try to login as THATJEFFSMITH/password, I’m going to get a bad user/password.

So make sure the url mapping pattern you include when REST enabling your schema is used in your url to login to your database when using SQL Developer Web.

Author thatjeffsmith

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

10 Comments

Hari Narayanan 3 years ago Reply

Hi Jeff,

I am an Oracle DB designer and developer who quit my corporate gig and trying to work on my own application. I just set up an Oracle Autonomous DB in OCI. I want to create REST access to tables and some PL/SQL packages that I have built. I clicked to “Database Actions” and “REST” and I see Modules, Autorest, Roles, Privileges, Clients. I have no clue what these are and how to go about setting up REST. I have been able to set up ORDS in standalone mode in my local machine using SQL Developer and command line. But I have hit a wall with OCI.

Is there a link to a document that explains these objects conceptually and a quick tutorial on setting up REST connection to a table in an Autonomous DB that is in the OCI? The Oracle documents I see are all talking about on-premise ORDS setup.

Thanks in advance. Looking forward to hearing from you.
- thatjeffsmith Post Author 3 years ago
  
  There is built in helpz click the ? On the screen to access that.
  
  On oracle.com/rest or on the sqldev web product page there is a video tour of the interface you’re asking about.
  
  I’ve also blogged about this topic..click around on this site.
  
  ORDS in Autonomous or ORDS on premises…it’s still just ORDS. The primary difference is ok Autonomous, we’re managing the software, not you.
- Hari Narayanan 3 years ago
  
  Hi Jeff,
  
  Thanks for your response. I did try the (?) but did not see any clear explanation of the set up. I will look for the video tour that you had mentioned.
  
  My issue is not so much with Autonomous DB vs On-premise. It is to do with OCI Cloud DB vs On-premise. In any case, I will explore some more based on your feedback.
  
  Thanks again.
- thatjeffsmith Post Author 3 years ago
  
  Here’s the video link
  https://www.youtube.com/watch?v=Ldvb9Nk_6Jo
Gary Crisci 4 years ago Reply

Jeff – What if you create a schema url with ORDS_ADMIN.ENABLE_SCHEMA and then forget the alias you assigned to it? Is there a way to read the ones you already set up?
- thatjeffsmith Post Author 4 years ago
  
  select * from user_ords_schemas
  
  the pattern will be your alias
  
  or just run the enable() command again and change the alias to something you’ll remember
- Gary Crisci 4 years ago
  
  Thank you Jeff, that worked for me. Appreciate the quick response.
Vic 5 years ago Reply

Thanks very much Jeff.
Vic
Vic 5 years ago Reply

Hi Jeff,

Will SQL Developer Web work on prem even if you’re not in the Oracle Cloud? Or do we stick to the current setup.

Thanks
Vic
- thatjeffsmith Post Author 5 years ago
  
  That’s the idea for later this fall.