Choosing a Programming Environment

To choose a programming environment for a development project, read:

Sometimes the choice of programming environment is obvious, for example:

If no programming language provides all the features you need, you can use multiple programming languages, because:

Topics:

See Also: Developing Applications with Multiple Programming Languages for more information about multilanguage programming

Overview of Application Architecture

In this topic, application architecture refers to the computing environment in which a database application connects to an Oracle Database.

Topics:

See Also: Oracle Database Concepts for more information about application architecture

Client/Server Architecture

In a traditional client/server program, your application code runs on a client system; that is, a system other than the database server. Database calls are transmitted from the client system to the database server. Data is transmitted from the client to the server for insert and update operations and returned from the server to the client for query operations. The data is processed on the client system. Client/server programs are typically written by using precompilers, whereas SQL statements are embedded within the code of another language such as C, C++, or COBOL.

See Also: Oracle Database Concepts for more information about client/server architecture

Server-Side Programming

You can develop application logic that resides entirely inside the database by using triggers that are executed automatically when changes occur in the database or stored subprograms that are invoked explicitly. Off-loading the work from your application lets you reuse code that performs verification and cleanup and control database operations from a variety of clients. For example, by making stored subprograms invocable through a web server, you can construct a web-based user interface that performs the same functions as a client/server application.

See Also: Oracle Database Concepts for more information about server-side programming

Two-Tier and Three-Tier Architecture

Client/server computing is often referred to as a two-tier model : your application communicates directly with the database server. In the three-tier model , a separate application server processes the requests. The application server might be a basic web server, or might perform advanced functions like caching and load-balancing. Increasing the processing power of this middle tier lets you lessen the resources needed by client systems, resulting in a thin client configuration in which the client system might need only a web browser or other means of sending requests over the TCP/IP or HTTP protocols.

See Also: Oracle Database Concepts for more information about multitier architecture

Overview of the Program Interface

The program interface is the software layer between a database application and Oracle Database. The program interface:

The Oracle code acts as a server, performing database tasks on behalf of an application (a client), such as fetching rows from data blocks. The program interface consists of several parts, provided by both Oracle Database software and operating system-specific software.

See Also: Oracle Database Concepts for more information about the program interface

Topics:

User Interface

The user interface is what your application displays to end users. It depends on the technology behind the application and the needs of the users themselves. Experienced users can enter SQL statements that are passed on to the database. Novice users can be shown a graphical user interface that uses the graphics libraries of the client system (such as Windows or X-Windows). Any traditional user interface can also be provided in a web browser through HTML and Java.

Stateful and Stateless User Interfaces

In traditional client/server applications, the application can keep a record of user actions and use this information over the course of one or more sessions. For example, past choices can be presented in a menu so that they not be entered again. When the application can save information in this way, the application is considered stateful.

Web or thin-client applications that are stateless are easier to develop. Stateless applications gather all the required information, process it using the database, and then start over with the next user. This is a popular way to process single-screen requests such as customer registration.

There are many ways to add stateful action to web applications that are stateless by default. For example, an entry form on one web page can pass information to subsequent web pages, enabling you to construct a wizard-like interface that remembers user choices through several different steps. You can use cookies to store small items of information about the client system, and retrieve them when the user returns to a website. You can use servlets to keep a database session open and store variables between requests from the same client.

Overview of PL/SQL

PL/SQL, the Oracle procedural extension of SQL, is a completely portable, high-performance transaction-processing language. PL/SQL lets you manipulate data with SQL statements; control program flow with conditional selection and loops; declare constants and variables; define subprograms; define types, subtypes, and ADTs and declare variables of those types; and trap runtime errors.

Applications written in any Oracle Database programmatic interface can invoke PL/SQL stored subprograms and send blocks of PL/SQL code to Oracle Database for execution. Third-generation language (3GL) applications can access PL/SQL scalar and composite data types through host variables and implicit data type conversion. A 3GL language is easier than assembler language for a human to understand and includes features such as named variables. Unlike a fourth-generation language (4GL), it is not specific to an application domain.

You can use PL/SQL to develop stored procedures that can be invoked by a web client.

See Also:

Overview of Oracle Database Java Support

This section provides an overview of Oracle Database features that support Java applications. The database includes the core JDK libraries such as java.lang, java.io, and so on. The database supports client-side Java standards such as JDBC and SQLJ, and provides server-side JDBC driver that enables data-intensive Java code to run within the database.

Topics:

See Also:

Overview of Oracle JVM

Oracle JVM, the Java Virtual Machine provided with the Oracle Database, is compliant with the J2SE version 1.5.x specification and supports the database session architecture.

Any database session can activate a dedicated JVM. All sessions share the same JVM code and statics; however, private states for any given session are held, and subsequently garbage collected, in an individual session space.

This design provides these benefits:

Oracle JVM works consistently with every platform supported by Oracle Database. Java applications that you develop with Oracle JVM can easily be ported to any supported platform.

Oracle JVM includes a deployment-time native compiler that enables Java code to be compiled once, stored in executable form, shared among users, and invoked more quickly and efficiently.

Security features of the database are also available with Oracle JVM. Java classes must be loaded in a database schema (by using Oracle JDeveloper, a third-party IDE, SQL*Plus, or the loadjava utility) before they can be called. Java class calls are secured and controlled through database authentication and authorization, Java 2 security, and invoker’s rights (IR) or definer’s rights (DR).

Effective with Oracle Database 12c Release 1 (12.1.0.1), Oracle JVM provides complete support for the latest Java Standard Edition. Compatibility with latest Java standards increases application portability and enables direct execution of client-side Java classes in the database.

See Also:

Overview of Oracle JDBC

Java Database Connectivity (JDBC) is an Applications Programming Interface (API) that enables Java to send SQL statements to an object-relational database such as Oracle Database.

Oracle Database includes these extensions to the JDBC 1.22 standard:

Oracle Database supports all APIs from the JDBC 2.0 standard, including the core APIs, optional packages, and numerous extensions. Some highlights include datasources, JTA, and distributed transactions.

Oracle Database supports these features from the JDBC 3.0 standard:

Topics:

See Also:

Oracle JDBC Drivers

The JDBC standard defines four types of JDBC drivers:

Type Description
1 A JDBC-ODBC bridge. Software must be installed on client systems.
2 Native methods (calls C or C++) and Java methods. Software must be installed on the client.
3 Pure Java. The client uses sockets to call middleware on the server.
4 The most pure Java solution. Talks directly to the database by using Java sockets.

JDBC is based on Part 3 of the SQL standard, “Call-Level Interface.”

You can use JDBC to do dynamic SQL. In dynamic SQL, the embedded SQL statement to be executed is not known before the application is run and requires input to build the statement.

The drivers that are implemented by Oracle have extensions to the capabilities in the JDBC standard that was defined by Sun Microsystems.

Topics:

See Also:

JDBC Thin Driver

The JDBC Thin driver is a Type 4 (100% pure Java) driver that uses Java sockets to connect directly to a database server. It has its own implementation of a Two-Task Common (TTC), a lightweight implementation of TCP/IP from Oracle Net. It is written entirely in Java and is therefore platform-independent.

The thin driver does not require Oracle software on the client side. It does need a TCP/IP listener on the server side. Use this driver in Java applets that are downloaded into a web browser or in applications for which you do not want to install Oracle client software. The thin driver is self-contained, but it opens a Java socket, and thus can run only in a browser that supports sockets.

JDBC OCI Driver

The JDBC OCI driver is a Type 2 JDBC driver. It makes calls to OCI written in C to interact with Oracle Database, thus using native and Java methods.

The OCI driver provides access to more features than the thin driver, such as Transparent Application Fail-Over, advanced security, and advanced LOB manipulation.

The OCI driver provides the highest compatibility between different Oracle Database versions. It also supports all installed Oracle Net adapters, including IPC, named pipes, TCP/IP, and IPX/SPX.

Because it uses native methods (a combination of Java and C) the OCI driver is platform-specific. It requires a client installation of version Oracle8i or later including Oracle Net, OCI libraries, CORE libraries, and all other dependent files. The OCI driver usually runs faster than the thin driver.

The OCI driver is not appropriate for Java applets, because it uses a C library that is platform-specific and cannot be downloaded into a web browser. It is usable in J2EE components running in middle-tier application servers, such as Oracle Application Server. Oracle Application Server provides middleware services and tools that support access between applications and browsers.

JDBC Server-Side Internal Driver

The JDBC server-side internal driver is a Type 2 driver that runs inside the database server, reducing the number of round trips needed to access large amounts of data. The driver, the Java server VM, the database, the Java native compiler (which speeds execution by as much as 10 times), and the SQL engine all run within the same address space.

This driver provides server-side support for any Java program used in the database. You can also call PL/SQL stored subprograms and triggers.

The server driver fully supports the same features and extensions as the client-side drivers.

Sample JDBC 2.0 Program

This example shows the recommended technique for looking up a data source using JNDI in JDBC 2.0:

// import the JDBC packages
import java.sql.*;
import javax.sql.*;
import oracle.jdbc.pool.*;
...
   InitialContext ictx = new InitialContext();
   DataSource ds = (DataSource)ictx.**lookup**("jdbc/OracleDS");
   Connection conn = ds.getConnection();
   Statement stmt = conn.createStatement();
   ResultSet rs = stmt.executeQuery("SELECT last_name FROM employees");
   while ( rs.next() ) {
   out.println( rs.getString("ename") + " ");
   }
conn.close();

Sample Pre-2.0 JDBC Program

This source code registers an Oracle JDBC thin driver, connects to the database, creates a Statement object, runs a query, and processes the result set.

The SELECT statement retrieves and lists the contents of the last_name column of the hr.employees table.

DriverManager.registerDriver (new oracle.jdbc.OracleDriver());

One Oracle Database extension to the JDBC drivers is a form of the getConnection() method that uses a Properties object. The Properties object lets you specify user, password, database information, row prefetching, and execution batching.

To use the OCI driver in this code, replace the Connection statement with this code, where MyHostString is an entry in the tnsnames.ora file:

Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@MyHostString",
    "hr", "password");

If you are creating an applet, then the getConnection() and registerDriver() strings are different.

Overview of Oracle SQLJ

Note: In this guide, SQLJ refers to Oracle SQLJ and its extensions.

SQLJ is an ANSI SQL-1999 standard for embedding SQL statements in Java source code. SQLJ provides a simpler alternative to JDBC for client-side SQL data access from Java.

A SQLJ source file contains Java source with embedded SQL statements. Oracle SQLJ supports dynamic and static SQL. Support for dynamic SQL is an Oracle extension to the SQLJ standard.

The Oracle SQLJ translator performs these tasks:

SQLJ is integrated with JDeveloper. Source-level debugging support for SQLJ is available in JDeveloper.

This is an example of a simple SQLJ executable statement, which returns one value because employee_id is unique in the employee table:

String name;
#sql  { SELECT first_name INTO :name FROM employees WHERE employee_id=112 };
System.out.println("Name is " + name + ", employee number = " + employee_id);

Each host variable (or qualified name or complex Java host expression) included in a SQL expression is preceded by a colon (:). Other SQLJ statements declare Java types. For example, you can declare an iterator (a construct related to a database cursor) for queries that retrieve many values, as follows:

#sql iterator EmpIter (String EmpNam, int EmpNumb);

See Also: Oracle Database SQLJ Developer’s Guide for more examples and details about Oracle SQLJ syntax

Topics:

See Also: Oracle Database Concepts for additional general information about SQLJ

Benefits of SQLJ

Oracle SQLJ extensions to Java enable rapid development and easy maintenance of applications that perform database operations through embedded SQL.

In particular, Oracle SQLJ does this:

Comparison of Oracle JDBC and Oracle SQLJ

JDBC code and SQLJ code interoperate, enabling dynamic SQL statements in JDBC to be used with both static and dynamic SQL statements in SQLJ. A SQLJ iterator class corresponds to the JDBC result set.

Some differences between JDBC and SQLJ are:

prepStmt.prepareCall("{call fun(?,?)}");       //stored proc. JDBC esc.
prepStmt.prepareCall("{? = call fun(?,?)}");   //stored func. JDBC esc.
prepStmt.prepareCall("begin fun(:1,:2);end;"); //stored proc. Oracle
prepStmt.prepareCall("begin :1 := fun(:2,:3);end;"); //stored func. Oracle

The SQLJ equivalent is:

#sql {call fun(param_list) };  //Stored procedure
// Declare x
...
#sql x = {VALUES(fun(param_list)) };  // Stored function
// where VALUES is the SQL construct

These benefits are common to SQLJ and JDBC:

Overview of Java Stored Subprograms

Java stored subprograms enable you to implement programs that run in the database server and are independent of programs that run in the middle tier. Structuring applications in this way reduces complexity and increases reuse, security, performance, and scalability.

For example, you can create a Java stored subprogram that performs operations that require data persistence and a separate program to perform presentation or business logic operations.

Java stored subprograms interface with SQL using an execution model similar to that of PL/SQL.

See Also:

Overview of Oracle Database Web Services

Web services represent a distributed computing paradigm for Java application development that is an alternative to earlier Java protocols such as JDBC, and which enable applications to interact through the XML and web protocols. For example, an electronics parts vendor can provide a web-based programmatic interface to its suppliers for inventory management. The vendor can invoke a web service as part of a program and automatically order stock based on the data returned.

The key technologies used in web services are:

Web services can use a variety of techniques and protocols. For example:

You can invoke a web service statically, when you might know everything about it beforehand, or dynamically, in which case you can discover its operations and transport endpoints while using it.

Oracle Database can function as either a web service provider or as a web service consumer. When used as a provider, the database enables sharing and disconnected access to stored subprograms, data, metadata, and other database resources such as the queuing and messaging systems.

As a web service provider, Oracle Database provides a disconnected and heterogeneous environment that:

See Also: Oracle Database Concepts for additional general information about Oracle Database as a web service provider

Overview of JavaScript

This section provides you with an overview of how you can run server-side procedural logic in JavaScript using Oracle Multilingual Engine (MLE).

Topics:

This section is intended to be an introduction to how you can run JavaScript in Oracle Database. For a more comprehensive guide, see Oracle Database JavaScript Developer’s Guide.

Multilingual Engine Overview

Oracle Database Multilingual Engine (MLE) enables Oracle Database users to run JavaScript in Oracle Database using stored procedures and dynamic code snippets.

MLE employs a smart database (SmartDB) approach, allowing application logic and data to coexist in the same database. Processing data within the database helps mitigate against problems arising out of handling heavy data volumes and transferring information between servers (database, middle tier, and client).

Using the SmartDB approach, Oracle Database MLE provides these benefits:

If you have data-intensive applications that use up unnecessary resources in a three-tier architecture, you can move the processing logic from the middle tier to the database for faster throughput, better security, and seamless data processing that happens closer to the database.

MLE introduces three kinds of schema objects: MLE modules, MLE environments, and MLE call specifications. MLE provides DDL extensions to create, alter, and drop these objects, and users with the right database privileges can use these extensions. These objects have dictionary views that you can query for information about the objects. MLE also enables you to perform post-execution debugging using the runtime states that are collected during the program runtime.

See Also: Introduction to Multilingual Engine in Oracle Database JavaScript Developer’s Guide to learn more about JavaScript and MLE.

MLE Concepts

MLE Module

An MLE module is a unit of JavaScript code that is stored in the database as a schema object.

MLE Function

An MLE function is exported by an MLE module and made available for calling from PL/SQL and SQL, as a function or procedure.

MLE Call Specification

An MLE call specification publishes the MLE functions (as PL/SQL functions or procedures) that you can call from SQL and PL/SQL.

MLE Execution Context

An MLE execution context encapsulates all runtime state associated with the execution of JavaScript code.

MLE Environment

An MLE environment configures the properties of MLE execution contexts.

Dynamic MLE

Dynamic MLE uses the DBMS_MLE PL/SQL package to enable direct execution of anonymous JavaScript code snippets.

Understanding MLE Execution Context and Runtime Isolation

An MLE execution context is a standalone, isolated runtime environment for JavaScript and encapsulates all runtime state associated with the execution of the JavaScript code in a session.

MLE uses execution contexts to enforce runtime state isolation between MLE modules, anonymous code snippets, and database users. Isolation of different applications in separate contexts within the same session prevents any interference between the applications. Runtime state isolation is important for languages such as JavaScript, where non-local variables have global scope but there is no control over their visibility.

MLE uses execution contexts to ensure that:

MLE uses execution contexts in two different scenarios:

An MLE context has a lifetime that is limited to the session in which it was created. When a session ends or is reset, all the contexts created in that session are dropped. A session can run the JavaScript code as a dynamic MLE snippet or as a function that is exported from an MLE module.

When using multiple contexts, it is recommended that applications create necessary contexts only, and drop contexts when they are not referenced.

See Also: About MLE Execution Contexts in Oracle Database JavaScript Developer’s Guide for more information about MLE execution contexts.

MLE Environment Overview

You can use an MLE environment to configure specific properties of the execution contexts that MLE uses to run JavaScript code in dynamic MLE snippets and MLE call specifications.

With MLE environments, you can:

MLE environments are schema objects that you can manage (create, modify, or drop) and reuse across multiple execution contexts. To create an environment, use the CREATE MLE ENV DDL statement. Alternatively, you can create an environment as an independent copy of an existing environment.

Note: Any change made to the original environment after the cloning, is not propagated to the cloned environment.

Users must have the CREATE MLE privilege to create MLE modules and environments in their own schema, or the CREATE ANY MLE privilege to create MLE modules and environments in arbitrary schemas.

If no environment is explicitly specified, MLE uses a default environment (from the SYS schema) that configures default JavaScript-specific language options and makes built-in MLE modules available for import. The default environment is used for dynamic MLE contexts and module contexts.

Information about MLE environments is available in these dictionary views: [user | all | dba | cdb]_mle_envs.

See Also:

JavaScript MLE Modules Overview

You can have JavaScript code stored persistently as an MLE module in the database. An MLE module is a unit of MLE language code that is stored in the database as a schema object and contains code that is written in a single MLE language (in this case, JavaScript). An MLE module enables you to export MLE functions, which you can call from PL/SQL and SQL as a function or procedure.

MLE supports user-defined MLE modules and built-in MLE modules. Built-in modules are not deployed to the database like user-defined MLE modules, but are included as a part of MLE runtime. You cannot change or modify any Oracle-provided JavaScript module.

You can use DDL statements to manage (create, alter, drop) MLE modules as database objects. To get information about MLE modules and other schema objects, use the USER_MLE_MODULES, ALL_MLE_MODULES, DBA_MLE_MODULES, and CDB_MLE_MODULES dictionary views.

See Also:

JavaScript MLE Call Specification Overview

Call specifications publish JavaScript MLE functions so that they can be called from SQL and PL/SQL. When executing call specifications in a session, MLE loads the module specified in the call specification and calls the function(s) exported by that module.

You can create MLE call specifications using the CREATE FUNCTION or CREATE PROCEDURE DDL statement syntax with MLE-specific elements.

You can drop a call specification using the DROP FUNCTION or DROP PROCEDURE DDL statement.

You must have the CREATE PROCEDURE privilege to create MLE call specifications in your schema, and the CREATE ANY PROCEDURE privilege to create MLE call specifications in an arbitrary schema. You must have the EXECUTE privilege on an MLE call specification to call it.

The ` MLE MODULE clause in the CREATE FUNCTION PROCEDURE` statement specifies the MLE module that exports the underlying JavaScript function for the call specification. The specified module must always be in the same schema as the call specification being created.

MLE provides three dictionary views that you can query for information about MLE call specifications: USER_MLE_PROCEDURES, ALL_MLE_PROCEDURES, DBA_MLE_PROCEDURES, and CDB_MLE_PROCEDURES.

See Also:

Invoking JavaScript in the Database

You can invoke JavaScript in either of the following ways:

See Also:

Invoking JavaScript Using MLE Modules

This section provides a brief background of MLE modules and how you can use them to invoke JavaScript in the database.

For the complete documentation related to MLE modules, see Oracle Database JavaScript Developer’s Guide.

Topics:

Using MLE Module Contexts

When running a call specification in a session, the JavaScript runtime engine loads the JavaScript module that has the exported functions (JavaScript functions) to be called in the call specification. In any given session, execution contexts for each module called in call specifications are created implicitly on demand. Calls from SQL and PL/SQL to MLE functions are run in a dedicated execution context for the MLE module and for the user on whose behalf the call is executed.

MLE uses execution contexts to maintain runtime state isolation. Call specifications are isolated into separate contexts when they do not share the same user, module, or environment.

Here are some important points to note while using execution contexts with MLE modules:

See Also: About MLE Execution Contexts in JavaScript Developer’s Guide for more information about MLE module execution contexts

Specifying an Environment for Call Specifications

An MLE call specification can optionally define the environment for the module context that executes the MLE call specification. To define the environment for a module context, include the ENV clause when creating an MLE call specification:

CREATE PROCEDURE scott.print_hello(name IN VARCHAR2) AS
 MLE MODULE scott."mymodule"
 ENV scott."myenv"
 SIGNATURE 'printHello';

All calls to the scott.print procedure are executed in a module context in which the scott.mymodule module is loaded. In addition, the module context is configured according to the scott.myenv environment.

Module contexts are separated by environment; if two call specifications refer to the same module but different environments, separate module contexts are created, each configured by their respective environment.

See Also: Specifying Environments for MLE Modules in JavaScript Developer’s Guide for more information about adding MLE environments for MLE modules

Managing JavaScript MLE Modules

You can use SQL DDL statements to create MLE modules as schema objects, provided you have the right privileges. You can use DDL statements to add, alter, or drop JavaScript modules.

Use a CREATE MLE MODULE DDL statement to create an MLE module:

Use the ALTER MLE MODULE DDL statement to alter attributes of existing modules.

Use the DROP MLE MODULE DDL statement to drop modules.

Example 22-1 Creating an MLE Module

CREATE MLE MODULE scott."jsmodule"
 LANGUAGE JAVASCRIPT
 AS export function func() { ... }

Example 22-2 Replacing an MLE Module

CREATE OR REPLACE MLE MODULE scott."jsmodule"
 LANGUAGE JAVASCRIPT
 AS export function func() { ... }

Example 22-3 Dropping an MLE Module

DROP MLE MODULE scott."jsmodule"

See Also:

Built-in JavaScript MLE Modules

MLE provides a set of built-in modules that reside in the SYS schema. Examples include mle-js-oracledb (MLE JavaScript SQL driver), mle-js-bindings (used to exchange values between PL/SQL and dynamic MLE contexts), and mle-js-plsqltypes (SQL wrapper types definition).

The built-in MLE modules are available for import in any execution context. These modules are not deployed to the database but included as part of the MLE runtime.

See Also: Built-in MLE modules API Documentation

Running JavaScript Code Using MLE Modules

You can use JavaScript code in an MLE module in the following ways:

See Also:

Calling MLE JavaScript Functions

From SQL and PL/SQL, you can use MLE call specifications to publish the MLE JavaScript functions. You can call an MLE function from anywhere that you can call a regular PL/SQL function or procedure.

Here is an example:

create or replace mle module example_module language javascript as
export function string2obj(inputString) {
    if ( inputString === undefined ) {
        throw `must provide a string in the form of key1=value1;...;keyN=valueN`;
    }

    let myObject = {};

    if ( inputString.length === 0 ) {
        return myObject;
    }

    const kvPairs = inputString.split(";");

    kvPairs.forEach( pair => {
        const tuple = pair.split("=");
        if ( tuple.length === 1 ) {
            tuple[1] = false;
        } else if ( tuple.length != 2 ) {
            throw "parse error: you need to use exactly one '=' between key and value and not use '=' in either key or value";
        }
        myObject[tuple[0]] = tuple[1];
    });
    return myObject;
}
/



create or replace function p_string_to_JSON(p_str varchar2) return JSON
as mle module example_module signature 'string2obj(string)';
/


declare
    l_json   JSON;
    l_string VARCHAR2(100);

begin
    l_string := 'a=1;b=2;c=3;d';
    l_json := p_string_to_JSON(l_string);
    dbms_output.put_line(json_serialize(l_json PRETTY));
end;
/

Here is the output:

{
  "a" : "1",
  "b" : "2",
  "c" : "3",
  "d" : false
}

See Also: Calling MLE JavaScript Functions in Oracle Database JavaScript Developer’s Guide

Importing JavaScript MLE Modules

You can reuse the functionality in an MLE module in other MLE language code that is outside the MLE module. MLE language code in an existing execution context can import the code of an MLE module (if the code is in the same MLE language) using the language’s native import mechanism for example, import() in JavaScript.

Module imports in MLE include:

Here is an example of an MLE module importing an MLE function from another MLE module:

create or replace mle env default_export_env
    imports ('defaultExportModule' module default_export_module);

create mle module default_import_module language javascript as
import myMathClass from "defaultExportModule";

export function mySum(){
    const result = myMathClass.sum(4, 2);
    console.log(`the sum of 4 and 2 is ${result}`);
}
/

See Also: Overview of Importing MLE JavaScript Modules in JavaScript Developer’s Guide

Invoking JavaScript Using Dynamic MLE Execution

This section provides a brief background of dynamic MLE execution and how you can use it to invoke JavaScript in the database.

For the complete documentation related to dynamic MLE execution, see Oracle Database JavaScript Developer’s Guide.

Topics:

Dynamic MLE Execution Overview

Note: Dynamic MLE execution is suitable for developers working on frameworks (APEX) and server technology (REPL). For all other use cases, using MLE modules and environments to run JavaScript is highly recommended.

As an alternative to using JavaScript MLE modules, the dynamic MLE execution method enables you to run a JavaScript code snippet directly using the DBMS_MLE package without having to deploy it as an MLE module first. Dynamic MLE execution is analogous to the DBMS_SQL package that is used to execute dynamic SQL.

With dynamic MLE execution, you can invoke a JavaScript code snippet without storing the JavaScript code in the database. The code is not deployed as an MLE module, but is instead provided as VARACHAR2 or CLOB (for larger amounts of code). The code is passed to the DBMS_MLE package, which then evaluates and runs the code.

Values can be passed between PL/SQL and dynamic MLE snippets by reading and writing global variables in the appropriate execution context using functions provided in the DBMS_MLE package.

See Also:

Using Dynamic MLE Execution contexts

MLE enables you to have explicit control over which execution context to use for each dynamic MLE snippet. Dynamic MLE execution contexts are created explicitly using the DBMS_MLE.create_context() function.

FUNCTION create_context(
  environment IN VARCHAR2 DEFAULT NULL
) RETURN context_handle_t;

Here are some important points to note while using dynamic MLE execution contexts:

See Also: About MLE Execution Contexts in JavaScript Developer’s Guide for more information about dynamic MLE execution contexts

Specifying an Environment for Dynamic MLE Contexts

When creating a dynamic MLE context, you can mention the schema name of an environment object as an optional parameter:

DECLARE
  ctx DBMS_MLE.context_handle_t;
BEGIN
  ctx := DBMS_MLE.create_context(environment => 'SCOTT.myenv');
  ...
END;

If the environment parameter to the DBMS_MLE.create_context function is omitted, the code running in the execution context can import only the MLE built-in modules.

Note: The environment name passed as a string to the DBMS_MLE.create_context function is case sensitive and must be a valid schema name. The environment name is not implicitly converted to uppercase.

See Also: JavaScript Developer’s Guide for more information about environments.

Running JavaScript Code Using Dynamic MLE Execution

You can run JavaScript code snippets using the procedures that are defined in the DBMS_MLE PL/SQL package.

Using the DBMS_MLE package, you can:

Dynamic MLE snippets can import MLE modules using module-to-module import and execute SQL statements. You must have the EXECUTE DYNAMIC MLE privilege to call procedures and functions in the DBMS_MLE package. Dynamic MLE execution contexts created using DBMS_MLE are created with the privileges of the calling user. Users with the EXECUTE privilege on DBMS_SYS_MLE also have access to the DBMS_SYS_MLE package, which allows these users to create dynamic MLE execution contexts on behalf of any database user.

You can call the DBMS_MLE.create_context() function to create an execution context for dynamic MLE code. The function takes an optional parameter that specifies language options, and returns a handle to the created context.

See Also:

Running JavaScript Code Inline

The following example creates a dynamic MLE execution context and runs a JavaScript snippet in the context using the Q-Quote operator:

DECLARE
    l_ctx     dbms_mle.context_handle_t;
    l_snippet CLOB;
BEGIN
    l_ctx := dbms_mle.create_context();
    l_snippet := q'~
// the q-quote operator allows for much more readable code
console.log(`The use of the q-quote operator`);
console.log(`greatly simplifies provision of code inline`);
~';
    dbms_mle.eval(
                 l_ctx,
                 'JAVASCRIPT',
                 l_snippet
    );
    dbms_mle.drop_context(l_ctx);
EXCEPTION
    WHEN OTHERS THEN
        dbms_mle.drop_context(l_ctx);
        RAISE;
END;
/
Running JavaScript Code Using Files

Another method for providing JavaScript code is to read a CLOB using a BFILE operator, with PL/SQL code segregated from JavaScript code.

See Also: Loading JavaScript Code from Files in JavaScript Developer’s Guide for examples showing how to use files to run JavaScript.

Privileges for Working with JavaScript in MLE

MLE protects access to MLE features using database privileges, which it adds or reuses, as appropriate. Administrators can grant the necessary privileges to users, roles, or both. The minimum privilege that is required to work with MLE JavaScript code is the right to execute JavaScript code in the database. Privileges that allow users to create, alter, or drop MLE schema objects must be restrictive, as must be those with the ANY keyword.

To enable you to run any JavaScript code in your own schema, the following object grant must have been issued to your user account:

GRANT EXECUTE ON JAVASCRIPT TO <role | user>

To include dynamic execution of JavaScript using DBMS_MLE, an additional privilege is required:

GRANT EXECUTE DYNAMIC MLE TO <role | user>

Note: When granting privileges, MLE distinguishes between dynamic MLE execution based on the DBMS_MLE package and the MLE execution based on MLE modules and environments.

See Also:

MLE User Privileges

Privilege Name Description
CREATE MLE Create or replace modules or environments in your schema
CREATE ANY MLE Create or replace modules or environments in any schema
ALTER ANY MLE Alter modules or environments in any schema
DROP ANY MLE Drop modules or environments in any schema
CREATE PROCEDURE Create or replace call specification in your schema
CREATE ANY PROCEDURE Create or replace call specification in any schema
DROP ANY PROCEDURE Drop call specification in any schema
EXECUTE DYNAMIC MLE Execute any Dynamic MLE functionality
EXECUTE ON <mle-call-specification> Execute the named MLE call specification
EXECUTE ON <mle-language> Execute call specifications or dynamic MLE snippets in the named MLE language
EXECUTE ON <mle-module> Execute call specifications against the referenced MLE module, or define the module as import in an environment
EXECUTE ON <mle-env> Use the named MLE environment to configure a module context or a dynamic MLE context

Other Supported MLE Features

MLE (JavaScript) supports the following other features:

Calling SQL and PL/SQL from the MLE JavaScript SQL Driver

An MLE JavaScript SQL driver enables you to use JavaScript to execute SQL statements and PL/SQL blocks from within JavaScript code. JavaScript has a JavaScript-specific SQL driver API. This API is closely aligned with the API of the corresponding client-side driver. For example, the JavaScript MLE SQL Driver provides an API that is similar to the API of the node-oracledb add-on for Node.js.

See Also: Calling PL/SQL and SQL from MLE SQL Driver in JavaScript Developer’s Guide

Access to stdout and stderr from JavaScript

MLE provides the functionality to access data written to standard output and error streams from JavaScript code. Within a database session, these streams can be controlled individually for each database user, MLE module, and dynamic MLE context. Information that the executed JavaScript code writes to stdout and stderr is often valuable for debugging and analysis. The DBMS_MLE package allows for mapping stdout and stderr either to DBMS_OUTPUT, or to a user-provided CLOB.

See Also: Access to stdout and stderr from JavaScript in JavaScript Developer’s Guide

SODA Collections in MLE JavaScript Code

You can use any Simple Oracle Document Access (SODA) implementation to perform create, read, update, and delete (CRUD) operations on documents of nearly any kind (including video, image, sound, and other binary content). SODA is a set of NoSQL-style APIs that let you create and store collections of documents (in particular JSON) in Oracle Database, and also let you retrieve them, and query them.

See Also: Working with SODA Collections in MLE JavaScript Code in JavaScript Developer’s Guide

Post-execution Debugging of MLE JavaScript Modules

MLE provides the option to perform post-execution debugging on your JavaScript source code in addition to the standard print debugging. Post-execution debugging allows efficient collection of runtime state during program execution. Once execution of the code has completed, the collected data can be used to analyze program behavior and discover bugs that require attention.

See Also: Post-Execution Debugging of MLE JavaScript Modules in JavaScript Developer’s Guide

Database Resident Connection Pooling

Database Resident Connection Pooling (DRCP) provides a connection pool in the database server that enables applications to share connections and handle multiple data requests. You can use JavaScript in Oracle Database (MLE) in database connections that use DRCP. In DRCP sessions, you can have applications invoke JavaScript procedures and functions, and use the APIs from the DBMS_MLE package. Workloads using in-database JavaScript can leverage the benefits of using DRCP, which include better scalability and improved performance.

See Also: Using Database Resident Connection Pool for more information about using DRCP

Choosing PL/SQL, Java, or JavaScript

Oracle Database supports PL/SQL, Java, and JavaScript on the server-side and enables you to embed SQL and PL/SQL blocks in your applications using precompilers and APIs on the client-side. To derive maximum benefits from Oracle Database, programmers must choose server-side programming with Oracle Database. With the application logic executing closer to the data, you can avoid network round trips, reuse code with centrally stored functions, and impose business rules within the database to ensure conformity in every application.

Choosing the language to use for your application depends mainly on the type of your application or project, and how you can use a language to your advantage. PL/SQL, Java, and JavaScript have their individual strengths, and they complement each other when used with Oracle Database.

PL/SQL, Java, and JavaScript inter-operate seamlessly in the server through SQL and PL/SQL call signatures. When developing with PL/SQL, you can invoke Java or JavaScript code or reference Java or JavaScript functions from PL/SQL. When developing with Java, you can invoke PL/SQL packages from distributed CORBA or Enterprise Java Beans clients. When developing with JavaScript, you can call PL/SQL and SQL from within JavaScript using the Multi-lingual Engine (MLE) JavaScript SQL Driver (mle-js-oracledb).

PL/SQL packages have their Java and JavaScript equivalents as shown in Table 22-1.

Table 1 PL/SQL Packages and Their Java and

PL/SQL Package Java Equivalent JavaScript Equivalent
DBMS_ALERT Call package with JDBC. Call with mle-js-oracledb.
DBMS_DDL JDBC has this functionality. mle-js-oracledb has this functionality.
[DBMS_JOB](application-architecture.html#GUID-19324E9B-9D97-4E45-A41C-0413BA00714D__NOTE_ZK3_B51_5XB) Schedule a job that has a Java stored subprogram. Scheduling a job using JavaScript requires a call to DBMS_SCHEDULER.create_job() using the built-in JavaScript SQL driver.
DBMS_LOCK Call with JDBC. Call with mle-js-oracledb.
DBMS_MAIL Use JavaMail. Call with mle-js-oracledb.
DBMS_OUTPUT Use subclass oracle.aurora.rdbms.OracleDBMSOutputStream or Java stored subprogram DBMS_JAVA.SET_STREAMS. Use console.log.
DBMS_PIPE Call with JDBC. Call with mle-js-oracledb.
DBMS_SESSION Use JDBC to run an ALTER SESSION statement.

In most cases, you can use mle-js-oracledb to run an ALTER SESSION statement. For example: myConnection.execute(`alter session set events 'sql_trace wait=true'`);

Note:

Calling ALTER SESSION directly may require additional privileges to change settings, such as session attributes and set events.

DBMS_SNAPSHOT Call with JDBC. Call with mle-js-oracledb.
DBMS_SQL Use JDBC. Use mle-js-oracledb.
DBMS_TRANSACTION Use JDBC to run an ALTER SESSION statement. Use mle-js-oracledb to run an ALTER SESSION statement.
DBMS_UTILITY Call with JDBC. Call with mle-js-oracledb.
UTL_FILE Grant the JAVAUSERPRIV privilege and then use Java I/O entry points. Call with mle-js-oracledb.

Note:

The DBMS_JOB package has been superseded by the DBMS_SCHEDULER package, and support for DBMS_JOB might be removed in future releases of Oracle Database. In particular, if you are administering jobs to manage system load, you are encouraged to disable DBMS_JOB by revoking the package execution privilege for users.

For more information, see DBMS_SCHEDULER and “Moving from DBMS_JOB to DBMS_SCHEDULER” in the Oracle Database Administrator’s Guide.

Topics:

Similarities of PL/SQL, Java, and JavaScript

Object-oriented features are common in PL/SQL, Java, and JavaScript as explained here:

Other common aspect of these languages is the packages and libraries that each of them provides to enable you to execute your programs effectively and efficiently.

Advantages of PL/SQL

If you want to build very secure, easy-to-maintain, high performing applications on top of Oracle Database, you must use PL/SQL. As an extension of SQL, PL/SQL supports all SQL data types, data encapsulation, information hiding, overloading, and exception-handling.

The following are the advantages of using PL/SQL:

Note: Some advanced PL/SQL capabilities are unavailable for Java in Oracle9i (for example, autonomous transactions and the dblink facility for remote databases).

Advantages of Java

You can use Java if:

Thus, when you need to partition your application between the database tier and middle tier, migrate that part of your application, as needed, to the middle tier and use Java/J2EE.

The following are the advantages of using Java:

Advantages of JavaScript

If you are looking at ease of scripting to develop end-to-end application with fast execution, JavaScript is your preferred language. With MLE support, you can run stored procedures and user-defined functions written in JavaScript within Oracle Database.

The following are the advantages of using JavaScript:

Overview of Precompilers

Client/server programs are typically written using precompilers, which are programming tools that let you embed SQL statements in high-level programs written in languages such as C, C++, or COBOL. Because the client application hosts the SQL statements, it is called a host program, and the language in which it is written is called the host language.

A precompiler accepts the host program as input, translates the embedded SQL statements into standard database runtime library calls, and generates a source program that you can compile, link, and run in the usual way.

Topics:

See Also: Oracle Database Concepts for additional general information about Oracle precompilers

Overview of the Pro*C/C++ Precompiler

For the ProC/C++ precompiler, the host language is either C or C++. Some features of the ProC/C++ precompiler are:

See Also: [ProC/C++ Developer’s Guide](/pls/topic/lookup?ctx=en/database/oracle/oracle-database/26/adfns&id=LNPCC-GUID-20CBCC27-EC6B-4651-92CC-7E008BD73611) for complete information about the ProC/C++ precompiler

Example 22-4 is a code fragment from a C source program that queries the table employees in the schema hr.

Example 22-4 Pro*C/C++ Application

...
#define  UNAME_LEN   10
...
int   emp_number;
/* Define a host structure for the output values of a SELECT statement. */
/* No declare section needed if precompiler option MODE=ORACLE          */
struct {
    VARCHAR  last_name[UNAME_LEN];
    float    salary;
    float    commission_pct;
} emprec;
/* Define an indicator structure to correspond to the host output structure. */
struct {
    short emp_name_ind;
    short sal_ind;
    short comm_ind;
} emprec_ind;
...
/* Select columns last_name, salary, and commission_pct given the user's input
/* for employee_id. */
    EXEC SQL SELECT last_name, salary, commission_pct
        INTO :emprec INDICATOR :emprec_ind
        FROM employees
        WHERE employee_id = :emp_number;
...

The embedded SELECT statement differs slightly from the interactive (SQL*Plus) SELECT statement. Every embedded SQL statement begins with EXEC SQL. The colon (:) precedes every host (C) variable. The returned values of data and indicators (set when the data value is NULL or character columns were truncated) can be stored in structs (such as in the preceding code fragment), in arrays, or in arrays of structs. Multiple result set values are handled very simply in a manner that resembles the case shown, where there is only one result, because of the unique employee number. Use the actual names of columns and tables in embedded SQL.

Either use the default precompiler option values or enter values that give you control over the use of resources, how errors are reported, the formatting of output, and how cursors (which correspond to a particular connection or SQL statement) are managed. Cursors are used when there are multiple result set values.

Enter the options either in a configuration file, on the command line, or inline inside your source code with a special statement that begins with EXEC ORACLE. If there are no errors found, you can compile, link, and run the output source file, like any other C program that you write.

Use the precompiler to create server database access from clients that can be on many different platforms. Pro*C/C++ gives you the freedom to design your own user interfaces and to add database access to existing applications.

Before writing your embedded SQL statements, you can test interactive versions of the SQL in SQL*Plus and then make minor changes to start testing your embedded SQL application.

Overview of the Pro*COBOL Precompiler

For the ProCOBOL precompiler, the host language is COBOL. Some features of the ProCOBOL precompiler are:

Example 22-5 is a code fragment from a COBOL source program that queries the table employees in the schema hr.

Example 22-5 Pro*COBOL Application

...
 WORKING-STORAGE SECTION.
*
* DEFINE HOST INPUT AND OUTPUT HOST AND INDICATOR VARIABLES.
* NO DECLARE SECTION NEEDED IF MODE=ORACLE.
*
 01  EMP-REC-VARS.
     05  EMP-NAME    PIC X(10) VARYING.
     05  EMP-NUMBER  PIC S9(4) COMP VALUE ZERO.
     05  SALARY      PIC S9(5)V99 COMP-3 VALUE ZERO.
     05  COMMISSION  PIC S9(5)V99 COMP-3 VALUE ZERO.
     05  COMM-IND    PIC S9(4) COMP VALUE ZERO.
...
 PROCEDURE DIVISION.
...
     EXEC SQL
         SELECT last_name, salary, commission_pct
         INTO :EMP-NAME, :SALARY, :COMMISSION:COMM-IND
         FROM employees
         WHERE employee_id = :EMP-NUMBER
     END-EXEC.
...

The embedded SELECT statement is only slightly different from an interactive (SQL*Plus) SELECT statement. Every embedded SQL statement begins with EXEC SQL. The colon (:) precedes every host (COBOL) variable. The SQL statement is terminated by END-EXEC. The returned values of data and indicators (set when the data value is NULL or character columns were truncated) can be stored in group items (such as in the preceding code fragment), in tables, or in tables of group items. Multiple result set values are handled very simply in a manner that resembles the case shown, where there is only one result, given the unique employee number. Use the actual names of columns and tables in embedded SQL.

Use the default precompiler option values, or enter values that give you control over the use of resources, how errors are reported, the formatting of output, and how cursors are managed (cursors correspond to a particular connection or SQL statement).

Enter the options in a configuration file, on the command line, or inline inside your source code with a special statement that begins with EXEC ORACLE. If there are no errors found, you can compile, link, and run the output source file, like any other COBOL program that you write.

Use the precompiler to create server database access from clients that can be on many different platforms. Pro*COBOL gives you the freedom to design your own user interfaces and to add database access to existing COBOL applications.

The embedded SQL statements available conform to an ANSI standard, so that you can access data from many databases in a program, including remote servers networked through Oracle Net.

Before writing your embedded SQL statements, you can test interactive versions of the SQL in SQL*Plus and then make minor changes to start testing your embedded SQL application.

Overview of OCI and OCCI

The Oracle Call Interface (OCI) and Oracle C++ Call Interface (OCCI) are application programming interfaces (APIs) that enable you to create applications that use native subprogram invocations of a third-generation language to access Oracle Database and control all phases of SQL statement execution. These APIs provide:

OCI lets you manipulate data and schemas in a database using a host programming language, such as C. OCCI is an object-oriented interface suitable for use with C++. These APIs provide a library of standard database access and retrieval functions in the form of a dynamic runtime library that can be linked in an application at runtime. You need not embed SQL or PL/SQL within 3GL programs.

See Also:

For more information about OCI and OCCI calls:

Topics:

Advantages of OCI and OCCI

OCI and OCCI provide significant advantages over other methods of accessing Oracle Database:

OCI and OCCI Functions

Both OCI and OCCI have four kinds of functions:

Kind of Function Purpose
Relational To manage database access and process SQL statements
Navigational To manipulate objects retrieved from the database
Database mapping and manipulation To manipulate data attributes of Oracle Database types
External subprogram To write C callbacks from PL/SQL (OCI only)

Procedural and Nonprocedural Elements of OCI and OCCI Applications

OCI and OCCI enable you to develop applications that combine the nonprocedural data access power of SQL with the procedural capabilities of most programming languages, including C and C++. Procedural and nonprocedural languages have these characteristics:

The combination of both nonprocedural and procedural language elements in an OCI or OCCI program provides easy access to Oracle Database in a structured programming environment.

OCI and OCCI support all SQL data definition, data manipulation, query, and transaction control facilities that are available through Oracle Database. For example, an OCI or OCCI program can run a query against Oracle Database. The queries can require the program to supply data to the database using input (bind) variables, as follows:

SELECT name FROM employees WHERE empno = :empnumber

In the preceding SQL statement, :empnumber is a placeholder for a value to be supplied by the application.

Alternatively, you can use PL/SQL, Oracle’s procedural extension to SQL. The applications you develop can be more powerful and flexible than applications written in SQL alone. OCI and OCCI also provide facilities for accessing and manipulating objects in Oracle Database.

Building an OCI or OCCI Application

As Figure 22-1 shows, you compile and link an OCI or OCCI program in the same way that you compile and link a nondatabase application. There is no need for a separate preprocessing or precompilation step.

Figure 1: The OCI or OCCI Development Process

Description of the illustration adfns111.png

Note: To properly link your OCI and OCCI programs, it might be necessary on some platforms to include other libraries, in addition to the OCI and OCCI libraries. Check your Oracle platform-specific documentation for further information about extra libraries that might be required.

Comparison of Precompilers and OCI

Precompiler applications typically contain less code than equivalent OCI applications, which can help productivity.

Some situations require detailed control of the database and are suited for OCI applications (either pure OCI or a precompiler application with embedded OCI calls):

Overview of Oracle Data Provider for .NET (ODP.NET)

Oracle Data Provider for .NET (ODP.NET) is an implementation of a data provider for Oracle Database.

ODP.NET uses APIs native to Oracle Database to offer fast and reliable access from any .NET application to database features and data. It also uses and inherits classes and interfaces available in the Microsoft .NET Framework Class Library.

For programmers using Oracle Provider for OLE DB, ADO (ActiveX Data Objects) provides an automation layer that exposes an easy programming model. ADO.NET provides a similar programming model, but without the automation layer, for better performance. More importantly, the ADO.NET model enables native providers such as ODP.NET to expose specific features and data types specific to Oracle Database.

See Also: Oracle Data Provider for .NET Developer’s Guide

This is a simple C# application that connects to Oracle Database and displays its version number before disconnecting:

using System;
using Oracle.DataAccess.Client;

class Example
{
  OracleConnection con;

  void Connect()
  {
    con = new OracleConnection();
    con.ConnectionString = "User Id=hr;Password=password;Data Source=oracle";
    con.Open();
    Console.WriteLine("Connected to Oracle" + con.ServerVersion);
  }

  void Close()
  {
    con.Close();
    con.Dispose();
  }

  static void Main()
  {
    Example example = new Example();
    example.Connect();
    example.Close();
  }
}

Note: Additional samples are provided in directory ORACLE_BASE\ORACLE_HOME\ODP.NET\Samples.

Overview of OraOLEDB

Oracle Provider for OLE DB (OraOLEDB) is an OLE DB data provider that offers high performance and efficient access to Oracle data by OLE DB consumers. In general, this developer’s guide assumes that you are using OraOLEDB through OLE DB or ADO.

See Also: Oracle Provider for OLE DB Developer’s Guide for Microsoft Windows