Querying data

1. Default query

After the .js.sql file is executed an implicit query is initiated, sending to the database everything that was present in memory buffer.

If the query returned a result set, its name in the final JSON response will be “resultSet”

If the query retuned multiple result sets, their default names will be “resultSet”,”resultSet2”,” resultSet3”,… etc

Select * from countries

2. Explicit query

Alternatively it’s possible to execute the buffer as SQL statement explicitly :

Select * from countries
--% sql.query();

Same as implicit query, the result sets go to “resultSet”, “resultSet2”, “resultSet3”, etc..

2.1. Update count

If the query updated one or many records, a script can find out the number of lines updated (actually the value returned depends on the database and its jdbc driver) for each statement like this:

DELETE * FROM TEMP_RECORDS;
--% response.writeObject("records",sql.getUpdateCount ());

for the first/unique update or

INSERT INTO CITIES(name, country) values (:p.name, :p.country);
DELETE * FROM TEMP_RECORDS;
--% response.writeObject("records",sql.getUpdateCount (1));

if multiple update statement were executed (with a stored procedure or multiple statements within one query if the DB allows this) where N is the number of the statement (starting with 0)

2.2. Explicit resut set name

In order to indicate the resultSet property name in the final JSON response, we can pass a parameter to sql.query()

Select * from countries

--% sql.query("countries");

Or, in case of multiple result sets returned, a comma separated list of resultset names.

Select * from clinets;
Select * from orders;
Select * from cities;

--% sql.query("clients,orders,cities");

3. Custom row mapper

In case there is a need to transform the returned resultset on  the fly, it’s possible to use custom row mappers:

Example :

SELECT * FROM employees ORDER BY EMPLOYEE_ID
<%
 sql.query("employeesHiddenPersonalData",function(row, index, rsName){
      row.EMAIL = row.EMAIL.substring(0,2)+row.EMAIL.substring(2).replace(/./g,"*");  // HIDE private information
      return row;
    });
%>

or

SELECT * FROM employees ORDER BY EMPLOYEE_ID
<%
 sql.query("employeesArray",function(row, index, rsName){
      return [ row.EMPLOYEE_ID, row.FIRST_NAME,
        row.LAST_NAME, row.EMAIL, row.PHONE_NUMBER,
        row.HIRE_DATE, row.JOB_ID, row.SALARY,
        row.COMMISSION_PCT ]; // return each row as an array instead of map
    });
%>

or

SELECT EMPLOYEE_ID FROM employees ORDER BY EMPLOYEE_ID
<%
 sql.query("employeesIds",function(row, index, rsName){
      return row.EMPLOYEE_ID // return only one numeric column per row
    });
%>

or

SELECT FIRST_NAME, LAST_NAME FROM employees ORDER BY EMPLOYEE_ID
<%
 sql.query("employeesNames",function(row, index, rsName){
      return row.FIRST_NAME+" "+row.LAST_NAME // performs a server-side operation, return only one string column per row
    });
%>

4. Return additional data.

To send some additional objects as a JSON reply to the client along with or instead of SQL streaming, use

response.writeObject("name", value);

For example,  calling a static method of a Java class 

response.writeObject("appServerIP", Java.type("java.net.Inet4Address").getLocalHost().getHostAddress());

5. Query to memory (buffering mode)

Instead of streaming the result set directly to the client, it’s possible to buffer it in memory for eventual additional handling. Example :

SELECT *
FROM EMPLOYEES
ORDER BY EMPLOYEE_ID
<% 
  var employees  = sql.queryToBuffer();
  var salarySum = 0;
  for (var i=0;i<employees.length;i++)
    salarySum+=employees[i].SALARY;
  var avgSalary = salarySum/employees.length;
  response.writeObject("employees ",employees);
  response.writeObject("avgSalary", avgSalary);
%>

6. Static include

To statically include another file (usually a library) use  "--@include" command:

Example:

--@include library.js.sql
--@include library.js
<%
   selectEmployees();
   response.writeObject("testFunction",testFunction());
%>

Where library.js.sql :

--% function selectEmployees(){
SELECT *
FROM EMPLOYEES
--% }

And library.js:

function testFunction(){
    return "testFunction";
}

To include a file that is located in a different folder branch, use 

--@include ../../common/libs/library.js.sql

Static includes can also be used to include JavaScript files/functions from the webapp folder. This allows to be able to execute some code (if appropriate) between the server and the client. For example : risk calculation preview in-browser and the same calculation during update on the server-side within a transaction, in secure environment.

CALL TEST_SIMPLE_SP(

    :(inout)(date)p.date ,

    :(inout)(number)p.number ,

    :p.name ,

    :(out)p.helloString

)

{

  call TEST_TYPES(

   :(oStructArr.PERSON_ARRAY)p.personsIn ,
   :(out)(oStructArr.PERSON_ARRAY)personsOut ,
   :(oArr.MY_INT_ARRAY)p.inArr ,
   :(out)(oArr.MY_INT_ARRAY)outArr
    )
}

JavaScript API : 

oracle.testTypes({

        personsIn:[

            { name: "John Smith", email: "john @ smith.com" },

            { name: "James Bond", email: "james @ bond.com" }

        ],

        inArr:[1,2,3,4,5]},
        function(res){

            console.log(res.personsOut[0].name, res.outArr.length)

        }

); 

Where PERSON_ARRAY and MY_INT_ARRAY are defined as following:

CREATE TYPE MY_INT_ARRAY IS VARRAY(100) OF INTEGER; 

CREATE TYPE PERSON AS OBJECT  (
    name    VARCHAR(30),
    email   VARCHAR(30)
); 

CREATE TYPE PERSON_ARRAY AS TABLE OF PERSON; 

And the procedure TEST_TYPES is :

CREATE OR REPLACE PROCEDURE "TEST_TYPES" (
  personsIn IN PERSON_ARRAY,
  personsOut OUT PERSON_ARRAY,
  intArrayIn IN MY_INT_ARRAY,
  intArrayOut IN MY_INT_ARRAY
)
 IS
BEGIN
  personsOut := personsIn ;
  intArrayOut := intArrayIn ;
END;