Oracle SQL Developer and the Oracle Database Schema Service

The Cloud! Over the last few years, you might have read a lot about Oracle and Cloud services. In this blog, we are going to discuss the offering of Database Schema Service. In this session, we will discuss uploading local data to the cloud service, configuring the connection to upload and browse data, browsing new data, using REST, etc. Begin your professional journey as an Oracle OSB professional by getting this Oracle service bus training where you can learn the concepts such as Oracle Service Bus Architecture, message processing, use of OSB, Service Orchestration, XML fundamentals, Message Enrichment, Proxy Services, UDDI, Transformations, etc.

That’s pretty much what it looks like. Your own schema is stored in a database that we handle on a low-cost Exadata box for you. contamination of Cross-schema is now impossible due to changes to the data dictionary. So no one else can see you; you’re the only one who can, and the dictionary views only belong to your account.

A new account was created, which is now active. The web portal can be used to handle anything. You can monitor customers, the installation of APEX, and check what’s going on in your service dashboard.

Account Details

You’ll get an email to open your account after you sign up for your service, followed by another with your account details and links to keep your subscription working.

To get started right away, all that was needed was to reset the passwords for the main and sftp accounts.

After that, the service can be used to view objects and data.

Uploading Data from the Local Computer to the Cloud Service

The connection must be described before stepping something up. You’ll find everything you need to get connected on your service dashboard or in your welcome email. Your SFTP account password had to be reset, but it just took two seconds.

You will describe your SQL Developer’s cloud connectivity once you know the password.

To transfer your data up, you can use either your SFTP interface or Oracle Storage Cloud Service (OSS).

Setting Up Your Connection For Uploading Data (Oss Or Sftp) And Browsing (Https)

User Name, Identity Domain, Password, Service Name: These details can be found in the OSS service’s Welcome to Oracle Cloud email.

You may also experiment with the schema objects using Application Express (APEX).

Let’s return to SQLDev now. Objects can be seen and browsed. Create a connection tree in the same way for ‘normal’ connections:

This is a compilation of operations that can only be read. You can only view the data and the objects under it, not edit them.

Why can’t SQL Developer be used for editing anything?

We are not using JDBC or SQL*Net for this connection. No, we are using HTTPS to make REST calls. As a result, some RESTful Services are designed on the service side to assist SQL Developer in viewing this data.

More resources will be added in the future to enable SQL Developer to do more work. Keep an eye on this space.

Moving the data

Using the Cart

You can drag and drop ‘local’ database objects into your Schema As A Service Cloud account using the Cart, which is essentially any Oracle database object accessible by normal JDBC connections.

Do you require the DDL? Would you need the data? Do you prefer to have both? Add some filters you’d like to use. That’s what there is to it. Just press the deploy button.

A cloud button can be found on the main Cart toolbar. Simply press the button.

After that, respond to the following queries:

What Cloud account will you use, where will the staging ZIP file be stored, and will any data or existing objects be overwritten?

A progress dialog appears after you click OK. It will prompt you for input on the objects that have been saved to a locally accessible file on your server – the ZIP file referred to in the previous dialog. It’s creating SQL*Loader and DDL data streams to upload to a FTP server of staging. When a process of daemon sees your zip file, it will search it for malwares and other nasty, naughty stuff before processing the schema service with the scripts.

The tree’s Deployments object will show you this.

You will get an update on your upload request when you click in the tree’s deployment. It will go through the stages from APPROVED to RUNNING to PROCESSED.

When we’re done, we’ll…

You may check the logs of SQLLDR to ensure that your complete data is successfully transferred.

The upload consumes just two minutes from beginning to end, according to the logs. The two tables were created and approximately 12,000 documents were uploaded.

Browsing the New Data

To search your database object, simply click on it as you would normally.

The ‘split editors’ strategy works here as well, allowing me to see both the table description and a single panel with data.

What can be done with new data?

Perhaps you’ve heard about Oracle Application Express’s free customer review instance at apex.oracle.com? The Schema As A Service offering is worth evaluating if you would like to move from a “play” space to a “production-ready” space.

APEX is already configured and running before you start using the Cloud Service. Also in a matter of seconds, you would change the default program to allow you to interact with your data. However, you could quickly upload your own local APEX app. 

REST is used to drive everything

Oracle REST Data Services (ORDS) might be familiar to you. This Cloud service’s entire backbone is driven by it. Both of these windows, including those in SQL Developer, are populated with data from RESTful endpoints. As a result, we’re making new things for you out of our own materials. This motivates us to improve our products. Whether or not you use the Cloud service, this is beneficial to you. 

Conclusion

This ends the session and you have seen uploading local data to the cloud service, the connection configurations to upload and browse data, browsing new data, using REST, etc.