Apps Developer

Developing your First Oracle Cloud Application

By Jag - September 08, 2013

Prerequisites

  • A local database, for development
  • SQL Developer version 3.2.20.09.87 or higher (Download)
  • Wallet Manager

Getting started

To get started, we first need an account on the Oracle cloud. So go to Oracle Cloud, login with your OTN account and request a trial period. You will need to enter a credit card for verification, but it will not get charged with anything.
Once you have an account, we first need to setup and configure some of the basics, when dealing with the Oracle cloud. At first you should login to your cloud account at cloud.oracle.com. Here it will list the different services that you subscribe to. Note down the Identity Domain name, since we will use this throughout the entire article. This is your unique identifier at oracle.
Click the services link as show here:
Show screen with details
Service Details
and note down the following from the details section, of the overview tab:
Service Details
Host – This is the host URL assigned to your account, and we need this for our SQL Developer connection and later on, as we develop a REST service, for our demo application.
Service SFTP Details
We need host, port and username for the Service SFTP account. This account is used in the SQL Developer connection, when we upload tables/data/objects to the cloud database. The other SFTP account we do not need for this demo. It is for downloading datapump exports, if you choose to close your account.
Changing SFTP account password
On the services page click on the Identity Console button:
When the identity console is loaded, click manage users link, enter a percent sign into the search box and press search. From the result list choose the username, that is equal to our SFTP details from the last paragraph. Click the change password button, and change the password to something known.
Creating Cloud SQL Developer user and connection
Once we changed the password we are ready to login to the database service for first time, and create the user we need for the connection from SQL Developer. Go back to the Service page, and click the “Launch Service” button:
Launch service button
Launch service button
Log in to APEX using your Identity Domain name and your cloud account.
Once we are logged into APEX we need to create a user for the SQL Developer connection. Click on the “Administration” tab, and choose “Manage Users and Groups”. Click on the “Create User” button, and create a user.
Fill in username, email address, a password (change requirement to change password at login, to no) and choose “SQL Developer” under user groups:
Now the user is created, we need to start up SQL Developer. Once started right click “Cloud Connections” and choose “New Cloud Connection”. Choose a name for your connection (I tend to use my identity domain name). The username for the Database, is the user we have just created in APEX, and the host, is the string that we noted down, from earlier, on the service details screen. It should be something like https://database-trial(identity-domain).db.(datacenter location).oraclecloudapps.com/apex/. The SFTP connection we also collected from the service details screen earlier:
SQL Developer connection screenshot
SQL Developer connection screenshot
Once all that is filled in, we should be able to expand that connection, type in the password, and see some of the demo tables, that are automatically deployed to a trial account.
Creating development schema in our local database
Create a sys connection to a local database, that we are going to use to develop our demo application. In that database create a user defined as follows:
create user localcloud identified by localcloud
default tablespace users
temporary tablespace temp;

grant create session, resource to localcloud;
When the user is created, create a connection within SQL Developer to that user.
Let us move on to the actual demo application.

Our demo application

Our small demo application, is going to be a central location, to register errors from all our local databases, so we can see which type of user errors are occuring the most, and we can spot trends in application usage. For this we will develop a REST webservice that our local databases will call whenever an error occurs, and we will create a small APEX application to report on the errors and occurrences.

Creating the database objects and deploying them to the cloud

For this small application we will create 2 tables. One to hold information about the databases that calls our webservice, and one to contain a row for each error that occurs in our databases. Create these tables inside the localcloud schema, that was created earlier.
DATABASES table:
create table databases (
 db_id  number,
 db_name  varchar2(4000),
 db_reg  date
);

alter table databases add constraint db_pk primary key(db_id);
DATABASE_ERRORS table:
create table database_errors (
 db_id  number  constraint db_err_id_ref references databases(db_id),
 error_num number  ,
 error_reg date
);
Once they are are created, I’ll insert a row for my local database, so I have a test database available.
To get the information to insert, perform the following select as sys against your local database:
SQL> select dbid, name from v$database;

      DBID NAME
---------- ---------
1404381942 ME11
with that I will insert a row into the databases table, as the localcloud user:
insert into databases values (1404381942, 'ME11', sysdate);
commit;

Transferring tables and data to your cloud database

Inside SQL Developer, navigate to View->Cart, to open the “cloud shopping cart”:
This is tool we use to transfer database objects from our local database to the cloud database. We simply drag and drop the objects from our local connection tree, into the cart. When in the cart, mark the option to transfer data for the databases table and click the “Deploy Cloud” button and SQL Developer will package it into a zip file, sftp it to your services account, and deploy it to the database:
SQL Developer - Deploy from cart to cloud
SQL Developer – Deploy from cart to cloud
To check the status of your deployment, navigate to the “Deployments” option the cloud connection tree, and look at the status field. When it says “Processed” the deployment is finished and we should be able to see the tables we created in our local database, inside the cloud database as well.

Creating a REST webservice

Oracle Cloud, does not accept sqlnet connections, and you cannot deploy software to the service. So the only way to communicate with the cloud from the outside world, is using http protocol. To allow our local databases to send data to our cloud application we will create a REST webservice inside APEX, and call that to register errors happening on the local database. To create a REST webservice log into your cloud account, and launch the APEX service. Inside APEX navigate to “SQL Workshop”->”RESTful Services”.
Click the create button, and fill out the first form page as shown here:
Create REST Module and Template
Create REST Module and Template
What we have created here is the basic definition of a REST service. Now we need to create a handler to do something when our REST url is called. Click the “Create Handler” link below the “demo_errors/{dbid}/{errornum}” template, and fill out the form as follows:
Create REST Handler
Create REST Handler
Now we can test the service. Click the “Set Bind Variables” button, and set the value of DBID to what you insert earlier into the databases table (in my case it was 1404381942), and set errornum to any number, and click the “Test” button. Depending on whether or not Oracle has fixed the bug, the test window might fail with the following:
As you can see, the second parameter {errornum} is not set to the value, that was typed in the set bind variables screen. To fix this copy and paste the url to a new browser window, change %7Berrornum%7D with a random number, and you should now see:
Successful REST call
Successful REST call

Creating a couple of APEX screens

To see the data that our webservice inserts, I will just create one simple screen. I am not an APEX expert, so it will be default all the way. So click the Application Builder option, and choose “Create” to make a new application. Choose a database application. On the next screen give it a name (I chose “ATO Demo App”), and click the “Create Application” button, defaulting all other steps. When done, you should see a list of pages like the following:
Apex page list
Apex page list
Click on the home link, and when you get to the page definition, right click on “Position 01″ and choose “Create”. Choose “Chart” as region type and click next. Choose a column chart and click next. Choose a simple 2D Column type, and continue. Give it a Title: “Daily Error Count” and click next. Give the same title, and just accept rest as default and click next. As the source we wil use the following SQL:
select
null link,
to_char(trunc(error_reg, 'DD'),'DD-MM') value,
count(error_num)
from database_errors
group by to_char(trunc(error_reg, 'DD'),'DD-MM')
order by 2 asc;
ignore the rest, and click next. Accept defaults, and click “Create Region”. Try and run the application, and you should see something like this:
Apex Application
Apex Application
So now we have a webservice that, when called will insert data into our database_errors table. We have a simple screen to display that data as a graph. So how do we call the webservice from outside the cloud.

Calling Oracle Cloud REST Services from a local database

The first thing we need to do is download the certificates from the Oracle Cloud. I used Firefox, so click on the litte lock to left of the url address bar, when you are logged into your service, and press more information. Go to the security tab and press show certificates:
Show Certificates
Show Certificates
Now export both the certificate for the oraclecloudapps.com domain, and the Verisign certificate above that, each to a .crt file:
Certificate Export
Certificate Export
Now we start up the Wallet Manager from our local database installation. Create a new wallet, and note down the password to the wallet, and select no to create a certificate request:
Wallet Manager - create wallet
Wallet Manager – create wallet
Go to the Operations menu, and select “Import trusted certificate”, and import the certificates, that we just exported. Save the wallet, and note down the folder you saved it to.
Next we log into the local database as the sys user. If the database version is 11g (like mine) we first need to create an ACL for the localcloud user. Run the following logged in as sys:
begin
  dbms_network_acl_admin.create_acl (
    acl         => 'cloud_http.xml',
    description => 'HTTP Access',
    principal   => 'LOCALCLOUD',
    is_grant    => TRUE,
    privilege   => 'connect',
    start_date  => null,
    end_date    => null
  );
 
  dbms_network_acl_admin.add_privilege (
    acl        => 'cloud_http.xml',
    principal  => 'LOCALCLOUD',
    is_grant   => TRUE,
    privilege  => 'resolve',
    start_date => null,
    end_date   => null
  );
 
  dbms_network_acl_admin.assign_acl (
    acl        => 'cloud_http.xml',
    host       => '*.oraclecloudapps.com',
    lower_port => 443,
    upper_port => 443
  );
  commit;
end;
/
Now log into the local database as localcloud. Lets say that I saved my wallet to c:\temp and I chose the password Manager123, and I use the URL from the REST service test, to execute the following:
exec utl_http.set_wallet('file:c:\temp', 'Manager123');
select utl_http.request('https://database-trialxxxxx.db.xxx.oraclecloudapps.com/apex/ato/demo_errors/1404381942/1555') from dual;
You should see a result like the following:
SQL> select utl_http.request('https://database-trialxxxx.db.xxx.oraclecloudapps.com/apex/ato/demo_errors/1404381942/1555') from dual;

UTL_HTTP.REQUEST('HTTPS://DATABASE-TRIALXXX.DB.XXX.ORACLECLOUDAPPS.COM/APEX/ATO
--------------------------------------------------------------------------------
Error registered
Now to start registering all errors automatically, we can create a system event trigger as sys, but owned by localcloud, that will call the webservice every time an error happens:
create or replace trigger localcloud.demo_errors
after servererror
on database

declare

 captured_sql_id   varchar2(1000);
 error_stack_count  number;

 sql_reg    varchar2(32000) := 'select utl_http.request(''https://database-trialxxxx.db.xxx.oraclecloudapps.com/apex/ato/demo_errors/1404381942/#ERR#'') from dual';

begin

 error_stack_count := ora_server_error_depth;

 exec utl_http.set_wallet('file:c:\temp', 'Manager123');
 for x in 1..error_stack_count loop
  execute immediate replace(sql_reg,'#ERR#', ora_server_error(x));
 end loop;

end db_ping_errors;
/
Note: Please remember to replace the “xxxx” with the correct identity domain and serverlocation.
Now log back into your cloud service, and run the ATO Demo App, and the bar chart should show at least one more error for today.
So there you have it, your first litte demo application running in the Oracle cloud. In the next article, I will talk about how you manage the different services in the cloud, compared to managing a normal oracle database.
  • Share:

You Might Also Like

0 comments