The Cloud! You may have heard a few things about Cloud services and Oracle over the past few years. What I’m going to talk about today is our Database Schema Service offering.

It’s what it sounds like, pretty much. On a kick-butt Exadata box, you get your own schema in a database that we take care of for you. The data dictionary has been modified to make any cross-schema contamination impossible. So you just see you, no one else can see you, and the dictionary views only reference your account.

I have a new account up and running. It can be managed all through our web portal. You can create and manage users, your APEX installation, and consult your service dashboard to see what’s going on.

My Account Details
My Account Details

After signing up for your service you’ll receive an email to activate your account, and then another with your account details plus links to maintain your subscription.

The only real thing I needed to do to get started right away was to reset my main and sftp account passwords.

With that done, I can start shoving data and objects up into the service.

Uploading Local Data to My Cloud Service

Before I can move anything up, I need to define my connection. Everything you need to know to connect is available in your welcome email or in your service dashboard. I did have to reset my SFTP user password, but that takes two seconds.

Not sure it's called a hamburger, but that's what another smart person I know called it.
Not sure it’s called a hamburger, but that’s what another smart person I know called it.

Once you know the password, you can define your Cloud connection in SQL Developer.

You have the option of using your SFTP interface or your Oracle Storage Cloud Service (OSS) to move your data up.

DEFINING YOUR CONNECTION – FOR BROWSING (HTTPS) AND UPLOADING DATA (SFTP or OSS)
Identity Domain, Service Name, User Name, Password: You can find these details in the Welcome to Oracle Cloud email that you received for the OSS service.

By the way, you can always also use Application Express (APEX) to mess around with your schema objects…

My Schema is seeded with some test data to play with...also has a Demo/Play type APEX Application
My Schema is seeded with some test data to play with…also has a Demo/Play type APEX Application

But I’m the tools guy, specifically, Oracle SQL Developer.

So let’s go back to SQLDev.

I can browse my objects. I get a connection tree, just like with ‘normal’ connections:

This is a read only set of operations. I can see the data, but not edit it or the underlying objects.
This is a read only set of operations. I can see the data, but not edit it or the underlying objects.

Why can’t I edit anything with SQL Developer?

Well, we’re not connecting with SQL*Net or JDBC. Nope, we’re making REST calls over the HTTPS. So on the service side, I have some RESTful Services configured to help SQL Developer show this information.

We’ll be adding more services to allow for more work to be done via SQL Developer. Stay tuned!

Move the data up already!

Remember the Cart?

Well, with the Cart, I can drag ‘local’ database objects – really any Oracle database object reachable via standard JDBC connections, and copy them up to my Schema As A Service Cloud account.

Drag and drop, that's it!
Drag and drop, that’s it!

Do you want the data? Do you want the DDL? Do you want both? Add any filters you want to apply. And that’s it. Just hit the deploy button.

There’s a ‘cloud’ button in the main Cart toolbar. Click it.

Then answer these questions:

Which Cloud account, where to you want to put the staging ZIP file to be uploaded, and do you want to clobber any existing objects/data?
Which Cloud account, where to you want to put the staging ZIP file to be uploaded, and do you want to clobber any existing objects/data?

Once you hit OK, you’ll see a progress dialog. It will give you feedback on the objects being exported to a local file on your machine – this is the ZIP file being referenced in the above dialog. It’s creating a series of DDL and SQL*Loader data streams, that will be uploaded to a staging FTP server. A daemon process will see your zip sitting there, and scan it for viruses and other bad, naughty things, and then process the scripts into your schema service.

You can see this via the Deployments item in the tree.

If I click on the deployment in the tree, you can get an update on your upload request. It will move from APPROVED to RUNNING to PROCESSED.

Files have been received, now wait your turn. Should take less than 5 minutes.
Files have been received, now wait your turn. Should take less than 5 minutes.

And then when we’re done…

I can inspect the SQLLDR logs to make sure all of my data went through.
I can inspect the SQLLDR logs to make sure all of my data went through.

According to the logs, my upload took only 2 minutes from start to finish. I created 2 tables and uploaded about 12,000 records.

Browse Your New Data

Just like 'normal', click on your database object to browse it.
Just like ‘normal’, click on your database object to browse it.

The ‘split editors’ trick works here too – so I can view my Schema As A Service table definition and its data in one panel.

New data, what to do with it?

Maybe you are familiar with the free customer evaluation instance of Oracle Application Express at apex.oracle.com? If you want to go from a ‘play’ space to a production ready space, the Schema As A Service offering is worth an evaluation.

The Cloud Service starts out with APEX already up and running. And in a few seconds I’ve modified the default application to let me play with my data. I could have just as easily uploaded my own local APEX app though…

Please don't judge our cloud tech by my horrible web design skills - it's really quite easy!
Please don’t judge our cloud tech by my horrible web design skills – it’s really quite easy!

This whole thing runs on REST

Perhaps you’ve heard of Oracle REST Data Services (ORDS?) It is powering the entire backbone of this Cloud service. RESTful endpoints are serving up the data to populate all of these screens – including the ones you see in SQL Developer. So, we’re using our own stuff to build you new stuff. Which in turn drives us to make our stuff even better. Which benefits you, whether you use our Cloud service or not.

Whew, way too many words today.

My next post will show how to use a command line call in SQL Developer to upload a cart to the Cloud service. I promise it will be MUCH shorter.

thatjeffsmith
Author

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

3 Comments

  1. John Bogoev Reply

    Using SQL Developer 4.1.5.21 I can connect to my Database Schema Service Connection and view my tables, however when attempting to perform a data upload I cannot drag and drop the tables to the cart.

    When right clicking on a table no options are presented either. Am I connected in a READ-ONLY mode?

    The user account used to login to my Database Schema Service has the “workspace administrator” and “developer privileges”, and is assigned to the “SQL Developer” Group.

    • thatjeffsmith

      You can use the cart to upload data from an on premises database to your schema service, but you can’t add schema service tables to a cart.

  2. Rajeshwaran, Jeyabal Reply

    Able to access the Cloud service from the Web browser ( and able to use sql-worksheet, apex , Team Development etc)

    But when tried to connect via SQL Developer 4.1.3 and 4.1.5 under cloud connection got this error. please help.

    oracle.dbtools.raptor.cloud.auth.AuthenticationFailedException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
    at oracle.dbtools.raptor.cloud.auth.basic.BasicAuthenticationEngine.doPost(BasicAuthenticationEngine.java:162)
    at oracle.dbtools.raptor.cloud.auth.basic.BasicAuthenticationEngine.authenticate(BasicAuthenticationEngine.java:63)
    at oracle.dbtools.raptor.cloud.auth.basic.GUIBasicAuthenticationEngine.authenticate(GUIBasicAuthenticationEngine.java:26)
    at oracle.dbtools.raptor.cloud.connection.ConnectionCreator.createConnection(ConnectionCreator.java:24)
    at oracle.dbtools.raptor.cloud.connection.CConnections$C.getConnection(CConnections.java:39)
    at oracle.dbtools.raptor.cloud.connection.CConnections.getConnection(CConnections.java:176)
    at oracle.dbtools.raptor.cloud.navigator.CloudConnection.openConnectionImpl(CloudConnection.java:127)
    at oracle.dbtools.raptor.cloud.navigator.CloudConnection.getConnection(CloudConnection.java:89)
    at oracle.dbtools.raptor.cloud.navigator.ConnectionTreeNode$LoadTask.doWork(ConnectionTreeNode.java:70)
    at oracle.dbtools.raptor.cloud.navigator.ConnectionTreeNode$LoadTask.doWork(ConnectionTreeNode.java:34)
    at oracle.dbtools.raptor.backgroundTask.RaptorTask.call(RaptorTask.java:193)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$RaptorFutureTask.run(RaptorTaskManager.java:621)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)
    Caused by: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
    at sun.security.ssl.Alerts.getSSLException(Alerts.java:192)
    at sun.security.ssl.SSLSocketImpl.fatal(SSLSocketImpl.java:1949)
    at sun.security.ssl.Handshaker.fatalSE(Handshaker.java:302)
    at sun.security.ssl.Handshaker.fatalSE(Handshaker.java:296)
    at sun.security.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1509)
    at sun.security.ssl.ClientHandshaker.processMessage(ClientHandshaker.java:216)
    at sun.security.ssl.Handshaker.processLoop(Handshaker.java:979)
    at sun.security.ssl.Handshaker.process_record(Handshaker.java:914)
    at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:1062)
    at sun.security.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1375)
    at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1403)
    at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1387)
    at org.apache.http.conn.ssl.SSLSocketFactory.createLayeredSocket(SSLSocketFactory.java:494)
    at org.apache.http.impl.conn.DefaultClientConnectionOperator.updateSecureConnection(DefaultClientConnectionOperator.java:232)
    at org.apache.http.impl.conn.AbstractPoolEntry.layerProtocol(AbstractPoolEntry.java:279)
    at org.apache.http.impl.conn.AbstractPooledConnAdapter.layerProtocol(AbstractPooledConnAdapter.java:146)
    at org.apache.http.impl.client.DefaultRequestDirector.establishRoute(DefaultRequestDirector.java:840)
    at org.apache.http.impl.client.DefaultRequestDirector.tryConnect(DefaultRequestDirector.java:647)
    at org.apache.http.impl.client.DefaultRequestDirector.execute(DefaultRequestDirector.java:479)
    at org.apache.http.impl.client.AbstractHttpClient.execute(AbstractHttpClient.java:906)
    at org.apache.http.impl.client.AbstractHttpClient.execute(AbstractHttpClient.java:1138)
    at org.apache.http.impl.client.AbstractHttpClient.execute(AbstractHttpClient.java:1076)
    at org.apache.http.impl.client.AbstractHttpClient.execute(AbstractHttpClient.java:1050)
    at oracle.dbtools.raptor.cloud.connection.DefaultClient.executeRequest(DefaultClient.java:96)
    at oracle.dbtools.raptor.cloud.auth.basic.BasicAuthenticationEngine.doPost(BasicAuthenticationEngine.java:153)
    … 17 more
    Caused by: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
    at sun.security.validator.PKIXValidator.doBuild(PKIXValidator.java:387)
    at sun.security.validator.PKIXValidator.engineValidate(PKIXValidator.java:292)
    at sun.security.validator.Validator.validate(Validator.java:260)
    at sun.security.ssl.X509TrustManagerImpl.validate(X509TrustManagerImpl.java:324)
    at sun.security.ssl.X509TrustManagerImpl.checkTrusted(X509TrustManagerImpl.java:229)
    at sun.security.ssl.X509TrustManagerImpl.checkServerTrusted(X509TrustManagerImpl.java:124)
    at sun.security.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1491)
    … 37 more
    Caused by: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
    at sun.security.provider.certpath.SunCertPathBuilder.build(SunCertPathBuilder.java:141)
    at sun.security.provider.certpath.SunCertPathBuilder.engineBuild(SunCertPathBuilder.java:126)
    at java.security.cert.CertPathBuilder.build(CertPathBuilder.java:280)
    at sun.security.validator.PKIXValidator.doBuild(PKIXValidator.java:382)
    … 43 more

Write A Comment