Leverage the Power of Oracle Database from a Browser: Part 1

This article is Part 1 of a two-part series that describes the steps for creating a JavaScript-based data management application that integrates with Oracle Database via RESTful service calls.

Overview

If you're a JavaScript developer, you're likely used to a diverse and constantly changing set of frameworks to choose from when starting a project. Trying to keep up with all of the features and functionality of this landscape is a full-time job in and of itself. While mastering the basics of multiple frameworks is something that you're probably good at by now, taking things to the next level—especially when it comes to more-sophisticated requirements such as analytics and security—is likely a constant struggle.

Fortunately, there is a better way: leverage the full power of Oracle Database, Oracle REST Data Services, JavaScript Extension Toolkit (Oracle JET).

Oracle Database is so much more than a place to just store data. It has a robust, mature, and powerful set of features and functions that can easily be leveraged from any JavaScript framework via RESTful calls. Features such as built-in analytics, auditing, security, and advanced text searching capabilities are just the beginning. And the best part is that if you leverage these features in Oracle Database, you won't have to change anything on the back end if and when you decide to change JavaScript frameworks.

Oracle REST Data Services is a free tool from Oracle that provides a seamless interface between Oracle Database and any technology than can interact with RESTful services. As a Java application that can run anywhere, Oracle REST Data Services can either run in an application server or by itself via Jetty. With its support for OAuth2, Oracle REST Data Services can provide a low-maintenance yet secure pipeline from any JavaScript library to Oracle Database 10g Release 2 and later.

Oracle JET is a new, open source JavaScript library provided by Oracle. In addition to a number of popular open source libraries, Oracle JET also includes custom libraries that provide beautiful, responsive visualizations; advanced two-way binding with a common model layer; and single-page application navigation controls. Developers can use as much or as little of Oracle JET as they need. This article will simply use one of the visualizations, which will get its data source from Oracle REST Data Services.

This series will walk you through the steps for creating a JavaScript-based data management application that integrates with Oracle Database via RESTful service calls. While it uses jQuery as a front end, the bulk of the logic is set up on the back end in PL/SQL. Thus, if you prefer a different JavaScript library, it should be fairly simple to adapt the jQuery examples to a framework of your choosing.

Preparing the Environment

In order to complete the examples described in this series, you'll need two things: a browser and access to an instance of Oracle Database 11g Release 2 or newer. Browsers are relatively easy to come by; chances are you're using one right now to read this. But on the other hand, not everyone has a spare Oracle Database instance lying around. That's OK, because they are easier and cheaper to get access to than you probably think.

Choosing a Database Environment

There are a number of different ways to get access to an Oracle Database instance quickly, easily, and for free. Let's review them:

• Oracle Cloud. You can easily request a trial account of the Oracle Cloud service—either Oracle Database Cloud Service or Oracle Database Exadata Express Cloud Service (Exadata Express). Once activated, you'll have all that you need to start building and populating your tables. The steps outlined in this article should work with either of these options; however, connecting via Oracle SQL Developer might differ. To get started with a free trial of any of the Oracle Cloud services, visit this site: developer.oracle.com/free.html.
   
• Oracle Technology Network Developer Day virtual machines (VMs). If you want to have something local to your machine and don't want to sign up for anything other than a free Oracle Technology Network account, you can use one of the freely available Oracle Technology Network Developer Day VMs. These VMs are based on Oracle VM VirtualBox, which is also free, and can be used for learning or tinkering environments. However, they are not suitable nor are they licensed to be used for developing production applications. They can run natively in Oracle VM VirtualBox or they can easily be converted to run in other VM software, such as VMware. The best-suited VM for this article is the Database App Development VM. Note: Oracle SQL Developer might have to be updated to at least version 4.2 if you are using the Oracle Technology Network Developer Day VMs. Oracle SQL Developer 4.2 was released in late April, and might not yet be found in the VMs.
   
• Ask your DBA. If your organization already has Oracle Database on premises or in the cloud, you might be able to get access to a schema for testing purposes. Reach out to your DBA and see if that's possible. Your systems will need to meet the following minimum criteria to follow the steps in this article accurately:
  • Database tier: Oracle Database 11g Release 2 Enterprise Edition or later (auditing requires Oracle Database 12c Release 1 Enterprise Edition)
  • Web server tier: Oracle REST Data Services 3.0.9 or later on the web server
  • Client tier: Oracle SQL Developer 4.2 or greater

Downloading and Installing Oracle SQL Developer

Once you have chosen a database platform, the next step is to download and install Oracle SQL Developer. Oracle SQL Developer is Oracle's free IDE designed for database developers who work in either on-premises or cloud environments. In addition to the wide variety of tools designed for database developers, Oracle SQL Developer also includes a DBA console that can be used to manage databases. Make sure that the version of Oracle SQL Developer you download is at least 4.2; anything earlier will not have the required components for managing web services.

Oracle SQL Developer can be downloaded for free here: oracle.com/sqldeveloper. Simply choose your corresponding platform. To complete the download, you will need to log in to your Oracle Technology Network account. Oracle Technology Network accounts are free; simply navigate to the following site and click the Create Account button: https://login.oracle.com/mysso/signon.jsp.

Depending on your platform, you might have to also download and install a JDK. JDKs for several different platforms can be downloaded here.

Configuring Oracle SQL Developer

Once Oracle SQL Developer has been installed, the next step is to create a connection to your database. Depending on which Oracle Cloud environment you selected, the steps for connecting will differ slightly.

• For Oracle Database Cloud Service, see Using Oracle Database Cloud Service.
• For Oracle Database Exadata Express Cloud Service (Exadata Express), see Using Oracle Database Exadata Express Cloud Service.

If you're using a local VM or an on-premises instance of Oracle Database, you can simply create a new database connection using the schema name, password, and connection details as you normally would. You might need your DBA to assist with the account creation.

Creating the EMP and DEPT Tables

Before any front-end development starts, it's best to look at the two tables that will be used in this article. For those who have used Oracle Database in the past, the tables EMP and DEPT should be nothing new. But for those who are new to Oracle Database, EMP and DEPT are the tried-and-true demonstration tables that have been used for years. Figure 1 illustrates a data model of the two tables.

The elegance of the EMP and DEPT tables lies in their simplicity: with only a few columns and a handful of rows, it's possible to demonstrate almost any relational database concept with these two tables.

Your schema might or might not include EMP and DEPT. If it does, no further action is required. However, if it does not, you'll have to create them. This can be done by running the script shown in Listing 3 of Appendix A.

To create the EMP and DEPT tables, do the following:

1. Start Oracle SQL Developer.
2. Connect to the schema defined in the previous step.
3. Locate the script in Listing 3 of Appendix A and copy it.
4. Paste the contents of the script into the Worksheet tab of Oracle SQL Developer.
5. Click the Run Script icon, which is circled in red in Figure 2. Note: If you have an EMP and DEPT table, this script will first remove and then re-create them.
    

As the script runs, a list of messages should be displayed in a new window called Script Output. Once the script is finished running, the last line will say "Commit Complete."

Scroll through the Script Output window to ensure that there were no errors when the tables were created or populated.

Next, let's inspect the tables in our schema. We can do that by expanding the connection node in the tree to reveal a list of schema objects and other associated services. If you expand the Tables node, you should see at least two entries: EMP and DEPT. Simply select EMP and notice that a new tab called EMP was created in the main section of Oracle SQL Developer. This tab has a number of subtabs.

By default, the Columns tab is selected. Take a minute to explore the different properties of the EMP table by clicking each of the subtabs. Ensure that the Data tab contains the 14 records that were created as part of the script that was just run. Repeat this process with for the DEPT table and ensure that four rows exist there.

Creating the Web Services

In the following sections, we will create the needed web services.

Creating the Module and the First Web Service

Before a database schema can be used with a web service, REST services for that schema must be enabled. This allows Oracle REST Data Services—the web services application server—to access data stored in the specified schema. This step takes only a few seconds to complete and needs to be done only once.

To enable your schema to work with Oracle REST Data Services, do the following:

1. Right-click the name of your schema. Then select REST Services > Enable REST Services.

   

   Figure 4: Selecting your schema to enable REST services.
    
2. A wizard will appear. For the first step, ensure that Enable Schema checkbox is selected. It's also a good idea to create an alias for the schema, so that we're not letting a potential malicious user know our source schema name. While this bit of information by itself is harmless, it could augment other attacks. Thus, we'll set the Schema Alias to db. Deselect the Authorization required checkbox and click Next.

   

   Figure 5: Specify Details page of the wizard.
    
3. Click Finish to complete the wizard.

   

   Figure 6: Completing the RESTful Services wizard.
    

That's it! REST services are now enabled for your schema, and we can begin creating the required web services.

The first web service will generate a JSON document based on all rows of data in the EMP table. But before we can create a web service, we need to create a module. Think of a module as a package: a place where multiple programs can be stored. All of the required web services will be added to a single module, making them easier to manage over time.

1. In the hierarchy on the left side of the page, locate and expand the node for REST Data Services. It should be about half-way down.
2. Right click Modules and select New Module. A new wizard should pop up. When it does, enter demo in the Module Name and URI Prefix fields, select the Publish – Make this RESTful Service available for use checkbox, and click Next.

   

   Figure 7: Specify Module screen.
    
3. On the next page, enter emp in the URI Pattern field and click Next.

   

   Figure 8: Specify template screen.
    
4. On the next screen, glance at the confirmation details, and then click Finish. A new module—demo—was created with an associated resource template—emp. As a final step, a resource handler needs to be associated with the resource template. A resource handler associates a resource template with some sort of data source and action.
    
5. Expand the Modules node in the tree by clicking the small triangle icon.
6. Expand the demo node in the tree by doing the same.
7. Right-click the emp node in the tree and select Add Handler > GET.
8. Set Source Type to Query, ensure that Data Format is set to JSON, and click Apply.

   

   Figure 9: Create Resource Handler screen. For the next and final step, enter the SQL statement for the handler. This simple query will return a list of employees based on their salary, from highest to lowest.
    
9. In the Worksheet tab, enter the following SQL commands:
   Copy

      SELECT  empno,
      dname,
      ename, 
      sal, 
      job, 
      sal_diff,
      comm, 
      rownum rank
      FROM  
      (
      SELECT   e.empno,
      d.dname,
      e.ename, 
      e.sal, 
      e.job, 
      0 sal_diff,  
      NVL(e.comm, 0) commFROM emp e,
      dept dWHERE e.deptno = d.deptnoORDER BY e.sal DESC )

10. Click the floppy disk icon to save your changes. Be sure to click the single floppy disk icon in the SQL Worksheet tab, not the double floppy disk icon in the top of the main window.

At this point, the first RESTful module and web service have been created and are ready for use.

Testing Web Services

Testing web services can be very simple or somewhat involved, depending on the type of web service that is being tested. Typically, GETs are fairly simple to test, and testing can be done with nothing more than a browser. The others—specifically POST, PUT, and DELETE—require a tool designed to help test web services. While there are several tools that can be used to do this, one that is easy to use, is free, and works on all major platforms is called Postman. Postman makes it simple to test almost any web service, even those that require authentication or header variables. We'll use Postman in this article to verify that our web services are working properly, but any similar tool would also work.

To test our first web service, simply start Postman and enter the following into the Enter request URL field:

Note: Be sure to replace servername with the name of your server.

If Postman successfully runs the web service, it will display the JSON document from our first web service, as shown in Figure 10. Postman will also let us toggle between Pretty, Raw, and Preview mode to see the document formatted in various modes.

Note: The first parameter of the URL might vary. Check with your DBA for the specifics.

Creating the Remaining Web Services

We need to create four more web services: one for the DEPT table, one for a specific record from the EMP table, one for the source of the chart, and one to facilitate updates to the EMP table.

dept Web Service

Let's start with the dept web service, because it is nearly identical to the one we just created. This web service will be used to populate the select list on the edit form.

1. Locate and right-click the demo node in the tree. Select Add Template.
2. Enter dept in the URI Pattern field and click Next.
3. Click Finish.
4. Next, locate the dept node in the tree and right-click it. Select Add Handler, and when that expands, select GET.
5. Set Source Type to Query, ensure that Data Format is set to JSON, and click Apply.
6. In the resulting worksheet, enter the following SQL statement:
   Copy

     SELECT 
      dname,
      deptno
      FROM 
      dept
      ORDER BY 
      dname

7. Click the single floppy disk icon to save your changes.

To test out the dept web service, enter the following URL into either your browser or Postman:

Note: Be sure to replace servername with the name of your server.

The results should contain a JSON document with four records, one for each of the departments in the table.

emp Web Service: GET

Next, we'll create the web service that brings back a single row from the EMP table. This will be used when selecting an employee to edit. This web service will make use of a parameter that will be passed in as part of the requesting URI.

1. Locate and right-click the demo node in the tree. Select Add Template.
2. Enter emp/{empno} in the URI Pattern field and click Next.
3. Click Finish.
4. Next, locate the emp/{empno} node in the tree and right-click it. Select Add Handler, and when that expands, select GET.
5. Set the Source Type field to Query One Row and click Apply.
6. In the resulting worksheet, enter the following SQL statement:
   Copy

      SELECT empno,
      deptno, 
      ename,
      sal, 
      job,
      NVL(comm, 0) commFROM empWHERE empno = :empno

7. Click the Parameters tab.
8. Next, click the green plus icon.
9. A new row will be added, with the Name and Bind Parameter columns blank. Click the Name column and enter empno. Repeat this for the Bind Parameter column. Set Data Type to INTEGER. The end result should resemble the following:

   

   Figure 11: Adding a new row.
    
10. Click the SQL Worksheet tab and then click the single floppy disk icon to save your changes.

Testing this web service is a bit more involved, because we have to manually substitute the value of empno when we test. Thus, a sample URL that we can try looks like this:

Note: Be sure to replace servername with the name of your server.

This should bring back a JSON document with a single record for the employee KING.

salByJob Web Service

We also need a simple web service that will produce the data for an Oracle JET chart. This web service will simply return a job title and the total amount of the salaries associated with each job.

Oracle JavaScript Extension Toolkit (Oracle JET) is a free, open source JavaScript library that is maintained by Oracle. Here's a description of it from the Oracle JET home page: "Oracle JET is targeted at intermediate to advanced JavaScript developers working on client-side applications. It's a collection of open source JavaScript libraries along with a set of Oracle contributed JavaScript libraries that make it as simple and efficient as possible to build applications that consume and interact with Oracle products and services, especially Oracle Cloud services."

One of the Oracle JET libraries that we can easily incorporate into our demonstration is the visualizations library. Oracle JET contains a wide range of visualizations—charts, gauges, and other popular components—that can be used either with the rest of the Oracle JET components or on their own. In this case, we're going to use a simple pie chart visualization on the same page as our report.

For the pie chart to work, it needs a data source. Do the following to create a new web service that will act as the source of the pie chart; it will return the aggregate amount for each job role.

1. Locate and right-click the demo node in the tree. Select Add Template.
2. Enter salByJob for the URI Pattern field and click Next.
3. Click Finish.
4. Next, locate the salByJob node in the tree and right-click it. Select Add Handler, and when that expands, select GET.
5. Set the Source Type field to Query, ensure that Data Format is set to JSON, and click Apply.
6. In the resulting worksheet, enter the following SQL commands: SELECT job, SUM(sal) salFROM empGROUP BY jobORDER BY 2 DESC
7. Click the single floppy disk icon to save your changes.

Testing this web service is just as straightforward as the last few tests we did. Navigate to the following URL:

Note: Be sure to replace servername with the name of your server.

This should bring back a JSON document that shows the total salary for each distinct job.

emp Web Service: POST

Last, we need to create the web service that will be called when we're updating a record. This one will be the most involved, because multiple parameters need to be mapped.

1. Locate the emp node in the tree and right-click it. Select Add Handler, and when that expands, select POST.
2. Click the green plus icon, and then click the white area just below MIME Types. Enter application/json and press the tab key. The results should look like this:

   

   Figure 12. Create Resource Handler screen.
    
3. Click Apply.
4. In the resulting worksheet, enter the following SQL statement:
   Copy

      BEGIN
      UPDATE emp SET 
      sal = :sal,
      deptno = :deptno,
      ename = :ename,
      comm = :comm,
      job = :jobWHERE empno = :empno;END;

5. Click the Parameters tab.
6. Next, click the green plus icon six times to create six rows. Add the parameters shown in the Table 1: Table 1. Parameters to add

1. Click the SQL Worksheet tab and then click the single floppy disk icon to save your changes.

Because this web service involves a POST, we will need to use Postman to help test it. The following steps outline how to do this.

1. Change the transaction type from GET to POST.

   

   Figure 13. Changing the transaction type.
    
2. Enter the following URL into the Enter Request URL field in Postman: http://servername/ords/db/demo/emp Note: Be sure to replace servername with the name of your server.
    
3. Click the Headers tab and then enter the six rows of data shown in Table 2: Table 2. Data to add
4. Click Send.

There will be no feedback, but this request should change the COMM value of the employee KING to 1000. To verify that it worked, you can simply view the data in the table via Oracle SQL Developer, or you can rerun the web service that is built on emp.

Creating the Front End

Now that we have all the web services up and running, the next step is to create the front end. In this example, we're going to use jQuery. There are other more-modern frameworks available, but most people have used jQuery at some point in their career, and the provided code can be translated for a different framework.

The functionality of the front end will be extremely simple: produce a report and a form based on the results of the web services. For this example, we'll use two HTML pages to do this. The report page contains some jQuery code that will call the web service, parse it into a JSON document, and then loop through the results. The result will be a simple HTML table that contains the results of the SQL commands in the web service. There will also be an Oracle JET chart on this page, which is fed by a separate web service.

The code snippet in Listing 1 illustrates the function used to parse and display the data in the report. Using the getJSON function, jQuery will make an asynchronous call to the web service, which will in turn return a JSON document. That document will then be parsed and each record will be examined. For each record in the document, the values will be extracted and appended to a table tag with an ID of rpt.

Any JavaScript framework can be used to do this; the only requirement is to use the same URI for the web service. Formatting and displaying the data can be done any way you choose.

  <script type="text/javascript" language="javascript"> $(document).ready(function() {   $.getJSON("http://servername/ords/db/demo/emp", function(data) {     $(data.items).each(function (index, value) {         $("#rpt").append(         '<tr>'         + '<td class="text-center"><a href="form.html#'         + value.empno + '">Edit</a></td>'         + '<td class="text-right">'  + value.empno    + '</td>'         + '<td class="text-center">' + value.dname    + '</td>'         + '<td class="text-left">'   + value.ename    + '</td>'         + '<td class="text-left">'   + value.job      + '</td>'         + '<td class="text-right">'  + value.sal      + '</td>'         + '<td class="text-right">'  + value.sal_diff + '</td>'         + '<td class="text-right">'  + value.comm     + '</td>         + '<td class="text-center">' + value.rank     + '</td>'         + '</tr>');     });   }); });</script>

Listing 1: The jQuery function that calls the web service and parses and prints the data to the page.

This all happens as soon as the page is loaded and, thus, the data is displayed on the page as if it were a static page. A couple of CSS lines from Bootstrap and the Oracle JET chart add some polish to an otherwise drab looking report, as shown in Figure 14. Bootstrap is a free, open source library that's a combination of JavaScript, CSS, and HTML templates designed to provide a solid, responsive, and compatible user interface framework.

When a user clicks an Edit link, the form page will be called. This page will call the web service for the corresponding row that was clicked and populate the form elements. The results are also spruced up a bit with Bootstrap, as seen in Figure 15.

When the form page is loaded, two separate web service calls are made, as shown in Listing 2.

  <script type="text/javascript" language="javascript"> $(document).ready(function() {   $.getJSON("http://servername/ords/db/demo/dept", function(data) {     $(data.items).each(function( i ) {     $('#deptno').append($('<option>',         {value:data.items[i].deptno, text:data.items[i].dname}));     });   });   setTimeout(function(){     $.getJSON("http://servername/ords/db/demo/emp/"        + window.location.hash.substring(1), function(data) {       $(data).each(function (index, value) {         $("#empno").val(value.empno);           $("#ename").val(value.ename);           $("#job").val(value.job);           $("#sal").val(value.sal);         $("#deptno").val(value.deptno);         $("#comm").val(value.comm);       });     });   }, 100); });</script>

Listing 2: The jQuery on the form will call two web services: one for the select list and one for the data.

The first web service call—dept—will be used to populate the Department select list. The name of the department will be displayed while the department ID will be stored in the database. The second web service—emp—will fetch a single row from EMP and populate the corresponding fields on the form.

When the user clicks Save, the page is posted to the web service, sending the updated data back to the server where it will be processed and update the corresponding record. The user will then be returned to the report page, where the updated values can be seen.

Now that you have a basic understanding as to how things will work, the only thing left to do is to create three files based on Listing 4, Listing 5, and Listing 6 (all are in Appendix A). Simply copy the source code from each listing and paste it into a new text file. Save Listing 4 as rpt.html, save Listing 5 as form.html, and save Listing 6 as chart.js. Be sure that all files are in the same directory on your computer and that you change any occurrences of servername to your server's name.

Once the three files are created, simply open rpt.html in any modern browser. You should see a report with 14 rows of data and a pie chart summarizing salaries by job, similar to Figure 14.

Conclusion

Now that you've prepared the environment, created the web services, and created the front end, in Part 2 of this series, all you'll have to do is simply reload the HTML pages to see changes, because they will all be made on the server side in the database. Keep in mind that this example is intentionally simple so that you can focus more on understanding how the server-side components work. The front end can capture and parse JSON from the web service any number of ways and for any number of purposes, once you understand the basics.

About the Author

Scott Spendolini is president and founder of Sumner Technologies, a world-class Oracle services, education, and solutions firm. Throughout his career, he has assisted clients with their Oracle Application Express development and training needs. Spendolini is a long-time, regular presenter at many Oracle-related conferences, including Oracle OpenWorld, Kscope, and Rocky Mountain Oracle Users Group (RMOUG). He is an Oracle Ace Director, the author of Expert Oracle Application Express Security, and a coauthor of Pro Oracle Application Express. Spendolini is also an Oracle Certified Oracle Application Express developer.

Spendolini started his career at Oracle Corporation, where he worked with Oracle E-Business Suite for almost seven years and was a senior product manager for Oracle Application Express for over three years. He holds a dual bachelor's degree from Syracuse University in management information systems and telecommunications management.

Appendix A

Listing 3: Scripts to Create the EMP and DEPT Tables

  DROP TABLE EMP/DROP TABLE DEPT/CREATE TABLE DEPT ( DEPTNO NUMBER(2), DNAME VARCHAR2(14), LOC VARCHAR2(13), CONSTRAINT dept_pk PRIMARY KEY (deptno) )/CREATE TABLE EMP ( EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7, 2), COMM NUMBER(7, 2), DEPTNO NUMBER(2), CONSTRAINT EMP_PK PRIMARY KEY (empno), CONSTRAINT deptno_fk FOREIGN KEY (deptno) REFERENCES dept (deptno) )/INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839, TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL, TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698, TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788, TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698, TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566, TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782, TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);COMMIT/

Listing 4: Sample Report HTML File

Save this file as rpt.html.

Note: Be sure to change the value of servername to your server's name. There is one instance in this file.

  <!DOCTYPE html><meta content="text/html;charset=utf-8" http-equiv="Content-Type"><meta content="utf-8" http-equiv="encoding"><head><script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script><script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/require.js/2.2.0/require.min.js"></script><link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous"><link rel="stylesheet" href="http://rawgit.com/oracle/oraclejet/2.1.0/dist/css/alta/oj-alta-min.css"><script type="text/javascript" src="chart.js"></script><style type="text/css">th { text-align: center !important }</style><title>Employees</title><script type="text/javascript" language="javascript"> $(document).ready(function() {   $.getJSON("http://servername/ords/db/demo/emp", function(data) {     $(data.items).each(function (index, value) {         $("#rpt").append(         '<tr>'         + '<td class="text-center"><a href="form.html#' + value.empno    + '">Edit</a></td>'         + '<td class="text-right">'  + value.empno    + '</td>'         + '<td class="text-center">' + value.dname    + '</td>'         + '<td class="text-left">'   + value.ename    + '</td>'         + '<td class="text-left">'   + value.job    + '</td>'         + '<td class="text-right">$'  + value.sal      + '</td>'         + '<td class="text-right">$'  + value.sal_diff + '</td>'         + '<td class="text-right">$'  + value.comm     + '</td>'         + '<td class="text-center">' + value.rank     + '</td>'         + '</tr>');     });   }); });</script></head><body><div class="container"> <div class="row">   <div class="col-sm-8">     <h2>Employees</h2>     <table id="rpt" class="table table-striped table-hover">     <tr>       <th>EDIT</th>       <th>EMPNO</th>       <th>DEPT</th>       <th>ENAME</th>       <th>JOB</th>       <th>SAL</th>       <th>SAL_DIFF</th>       <th>COMM</th>       <th>RANK</th>     </tr>     </table>   </div>   <div class="col-sm-4" id='chart-container'>     <h2>Salaries by Job</h2>     <div id="pieChart" data-bind="ojComponent: {               component: 'ojChart',               type: 'pie',               series: pieSeriesValue,               animationOnDisplay: 'false',               animationOnDataChange: 'auto',               hoverBehavior: 'dim'               }"               style="max-width:500px;width:100%;height:350px;">     </div>   </div> </div></div></body></html> 

Listing 5: Sample Form HTML File

Save this file as form.html. Be sure to save it in the same directory as rpt.html.

Note: Be sure to change the value of servername to your server's name. There are three instances in this file.

  <!DOCTYPE html><meta content="text/html;charset=utf-8" http-equiv="Content-Type"><meta content="utf-8" http-equiv="encoding"><head><script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script><link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous"><style type="text/css">label { padding: 10px; width:150px; text-align: right;}button { margin: 10px;}</style><title>Employee Details</title><script type="text/javascript" language="javascript"> $(document).ready(function() {   $.getJSON("http://servername/ords/db/demo/dept", function(data) {     $(data.items).each(function( i ) {     $('#deptno').append($('<option>', {value:data.items[i].deptno, text:data.items[i].dname}));     });   });   setTimeout(function(){     $.getJSON("http://servername/ords/db/demo/emp/" + window.location.hash.substring(1), function(data) {       $(data).each(function (index, value) {         $("#empno").val(value.empno);           $("#ename").val(value.ename);           $("#job").val(value.job);         $("#sal").val(value.sal);         $("#deptno").val(value.deptno);         $("#comm").val(value.comm);       });     });   }, 100); });</script></head><body><form action="rpt.html" method="post" name="registration" id="form"> <div class="container" style="outline: 1px solid black;background-color:#eee;margin-top:20px;">   <h2>Employee Details</h2>   <input type="hidden" name="empno" id="empno" />   <label for="ename">Employee Name</label>   <input type="text" name="ename" id="ename" />   <label for="deptno">Department</label>   <select name="deptno" id="deptno"></select>   <label for="job">Job</label>   <input type="text" name="job" id="job" />   <br />   <label for="sal">Salary</label>   <input type="text" name="sal" id="sal" />   <label for="comm">Commission</label>   <input type="text" name="comm" id="comm" />   </br >   <span class="pull-right">     <a href="rpt.html" style="color:black;"><input type="button" value="Cancel" /></a>     <button type="submit">Save</button>   </span> </div></form><script>$( "form" ).submit(function( event ) { $.post( "http://servername/ords/db/demo/emp", $( "#form" ).serialize() );});</script></body></html>

Listing 6: chart.js File

Save this file as chart.js. Be sure to save it in the same directory as rpt.html.

Note: Be sure to change the value of servername to your server's name. There is one instance in this file.

    
  
      window.onload=function(){       require(['knockout',       'ojs/ojcore',       'jquery',       'ojs/ojknockout',       'ojs/ojcore',       'ojs/ojbutton',       'ojs/ojchart'       ], function(ko, oj, $) {       'use strict';       function ChartModel(pieSeries) {       var self = this;       self.threeDValue = ko.observable('off');       self.sortingValue = ko.observable('descending');       self.pieSeriesValue = ko.observableArray(pieSeries);       /* toggle buttons*/       self.threeDOptions = [       {id: '2D', label: '2D', value: 'off', icon: 'oj-icon demo-2d'},       {id: '3D', label: '3D', value: 'on', icon: 'oj-icon demo-3d'}       ];       self.threeDValueChange = function(event, data) {       self.threeDValue(data.value);       return true;       }       }              $(document).ready(function(){       $.getJSON("http://servername/ords/db/demo/salByJob").       then(function(ret) {       var pieSeries=[];       $.each(ret.items, function(idx,emp) {       var nextEl= {"name" :emp.job,"items": [emp.sal]};       pieSeries.push(nextEl);       });       console.log(pieSeries)       ko.applyBindings(new ChartModel(pieSeries), document.getElementById('chart-container'));       });       });       });       //RequireJS configs (usually these come first in main.js, but they don't have to)       requirejs.config({       // Path mappings for the logical module names       paths: {       'knockout': 'http://cdnjs.cloudflare.com/ajax/libs/knockout/3.4.0/knockout-min',       'jquery': 'http://cdnjs.cloudflare.com/ajax/libs/jquery/3.1.0/jquery.min',       "jqueryui-amd": "http://rawgit.com/jquery/jquery-ui/1.12.0/ui",       "promise": "http://cdnjs.cloudflare.com/ajax/libs/es6-promise/3.2.1/es6-promise.min",       "hammerjs": "http://cdnjs.cloudflare.com/ajax/libs/hammer.js/2.0.8/hammer.min",       "ojdnd": "http://rawgit.com/oracle/oraclejet/2.1.0/dist/js/libs/dnd-polyfill/dnd-polyfill-1.0.0.min",       "ojs": "http://rawgit.com/oracle/oraclejet/2.1.0/dist/js/libs/oj/debug",       "ojL10n": "http://rawgit.com/oracle/oraclejet/2.1.0/dist/js/libs/oj/ojL10n",       "ojtranslations": "http://rawgit.com/oracle/oraclejet/2.1.0/dist/js/libs/oj/resources",       "text": "http://cdnjs.cloudflare.com/ajax/libs/require-text/2.0.12/text.min",       "signals": "http://cdnjs.cloudflare.com/ajax/libs/js-signals/1.0.0/js-signals.min",       },       // Shim configurations for modules that do not expose AMD       shim: {       'jquery': {       exports: ['jQuery', '$']       }       }       });       }//]]>