Oracle Data Source

Oracle Data Source
lac51
This article includes information about how to install the Oracle JDBC driver, how to connect to an Oracle database, and how to manage the packages that are defined in your Oracle database. It also includes a list of the Oracle data types that 
CA Live API Creator
 supports and known Oracle database limitations. With the Oracle JDBC driver installed, the 
Oracle
 data source is available as an option in the connection wizard when creating an API.
In this article:
 
 
3
 
 
Install the Oracle JDBC Driver
Copy the license file and the 
ojdbc<version>.jar
 JAR file (the JDBC driver) into the directory that is based on the Java container on which you have installed 
CA Live API Creator
:
The self-contained, single-user version of Live API Creator that is based on Jetty
Copy the files into the 
%JETTY_HOME%/caliveapicreator/lib/ext
 directory.
Apache Tomcat
Copy the files into the 
%{CATALINA_HOME}/lib
 directory.
Oracle WebLogic
Copy the files into the 
<Your WebLogic Domain Directory>/lib
 directory.
JBoss/WildFly
For more information, see Install on JBoss.
IBM WebSphere
Add the JDBC driver into your Web application server.
Using the WebSphere administrative console, create a JDBC provider for the driver in the server.
For more information about how to create a JDBC provider, see the IBM WebSphere documentation
For more information about the versions of the JDBC drivers that 
CA Live API Creator
 supports, see Installation Requirements and Supported Platforms.
Connect to an Oracle Database
You can connect to your Oracle database using the following methods:
  • (If you have not created your API yet) When you create your API.
    For more information about how to create an API, see Creating APIs.
  • (If you have an existing API) By adding a connection to an Oracle data source.
    For more information about how to add a connection to a data source, see Database Connectivity
The following procedure details how to connect to your Oracle database when creating your API.
Prerequisites:
 You know your JDBC connection information.
Follow these steps:
 
  1. In API Creator, on the APIs page, click 
    Create New API
    .
    The connection wizard opens.
  2. Click 
    Database First
    .
  3. Select 
    Oracle
     as the data source type.
  4. Complete the Database, host, port, and password fields, including the following fields, and then click 
    Continue
    :
    Type
    The Oracle connection type.
    Default:
     SID
    Options:
     SID, Service Name, TNS
    SID/Service/Alias
     
    The alphanumeric database identifier.
    Schema
     
    The schema name for this database. You can use double quotes (") for the schema name to accept the value of mixed-case (non-standard) schema names.
    Example:
     
    MySchema
    .
    Username
     
    The username that is used to connect to this database. You can use double quotes (") for the username to accept the value of mixed-case (non-standard) usernames.
You are connected to the Oracle database.
Supported Oracle Data Types
When you are setting up a connection to an external data source, 
CA Live API Creator
 must map the data types in the data source to data types in 
CA Live API Creator
.
 
CA Live API Creator
 supports a subset of the full range of Oracle data types.
For more information about the Oracle data types, see the Oracle documentation.
You can use read and write the following data types when you work with an Oracle database:
Data type
Modeled as...
Notes
char
string
For more information about how to use this data type in JavaScript, see the "Boolean Data Type" section.
varchar2
string
long
string
nchar
string
nvarchar2
string
clob
string
nclob
string
xml
string
JSON
string
date
SimpleTimestamp
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
timestamp
SimpleTimestamp
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
timestamp with timezone
SimpleTimeOffset
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
timestampwithlocal timezone
SimpleTimeOffset
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
interval year to month
IntervalYearMonth
interval day to second
IntervalDaySecond
raw
bytes
long raw
bytes
ROWID
bytes
If a table does not have a primary key, 
CA Live API Creator
 exposes the 
ROWID
 column. You can view the column on the Schema page, call GET/POST/PUT and DELETE on a table with the 
ROWID
 column.
UROWID
bytes
bfile
bytes
blob
bytes
float
number
binary double
number
binary float
number
number
number
The data type supports the identity column constraint. The constraint supports
ALWAYS
,
BY DEFAULT
, and
BY DEFAULT ON NULL
.
ref cursor
This data type is a pointer or handler to a ResultSet on the database.
varrays, nested tables
For more information about how to use these data types in JavaScript, see the "User Defined Types, Nested Tables, and VARRAYs" section and
Manage Database Functions and Stored Procedures within Packages
 
CA Live API Creator
 exposes the stored procedures and database functions that are contained in packages as RESTful resource endpoints.
View Database Functions and Stored Procedures
You can view the stored procedures and database functions on the Schema page in API Creator. 
CA Live API Creator
 displays your database functions and stored procedures as a period-separated list between the package name and the name of the function or stored procedure.
For more information:
Identify and Reference Database Functions and Stored Procedures
The following example shows how you can identify or reference the 
procA
 stored procedure that is declared within the 
package_name
 package:
package_name.procA
Use Overloaded Database Functions and Stored Procedures
When you use an overloaded database function and stored procedure, the name of the overloaded database function or stored procedure remains the same but the argument data type changes. 
CA Live API Creator
 assigns a version number to each overloaded database function or stored procedure.
The following example shows how you can use the 
myFunction
 overloaded database function within the 
package
 package by using the specific name argument data types:
package.myFunction-int (number)
package.myFunction-lvarchar (string)
Test Database Functions and Stored Procedures
Test your functions and stored procedures in the REST Lab. Issue a POST request while passing the 
IN
 argument values.
For more information:
Generate Sequential Values using Data Definition Language (DDL)
If you are using Oracle 12c, your database can use identity columns and it can obtain a unique primary key value for the identity column using sequences. The following Oracle sample code shows how to configure your database to generate sequential values using Data Definition Language (DDL) in the Oracle database: 
CREATE TABLE orders(
ident INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL
,amount_total decimal(19,4)
);
If your database does not use identity columns, you can generate sequential values using one of the following approaches:
  • Have your database generate the primary keys for entities with sequences using triggers.
  • Have 
    CA Live API Creator
     auto-generate sequential values for the primary keys for the entities.
These approaches are the only ones that require that you associate entities to sequences using API Creator.
For more information about how to use these approaches, see Associate Entities to Sequences.
Manage SQL Syntax and Data Type Handling in Oracle Data Sources
 
CA Live API Creator
 provides special handling of certain Oracle SQL syntax and data types as follows.
Case Sensitivity
 
CA Live API Creator
 uses Oracle in a manner where case does not matter. 
select 1 from dual
 and 
select 1 from DUAL
 read from the 
DUAL
 table. 
CA Live API Creator
 treats Oracle tables and columns as case-sensitive. For example, 
CA Live API Creator
 displays a table defined as the following in the tables as FooBar with columns 
A
 and 
a
:
create table "FooBar"("A" number, "a" number)
 
CA Live API Creator
 generates SQL expressions that honor the Oracle rules and emit 
"a"
 providing the proper quotations as needed:
select A, "a" from "FooBar" order by A
You can access the expression in JavaScript using 
row.A
 and 
row.a
 respectively. 
CA Live API Creator
 emits columns that have non-alphanumeric columns, such as 
"CUST!NUM"
, correctly in SQL statements. In JavaScript, use object property notation, for example:
row["CUST!NUM"] = 'Yes';
User Defined Types, Nested Tables, VARRAYs
 
CA Live API Creator
 recognizes Oracle's User Defined Types, Nested Tables, and VARRAYs. You can use these extensions for GET, PUT, POST, and DELETE. In addition, you can use stored procedures and database functions that return or take UDTs, nested tables, and VARRAYS. You can invoke stored procedures through the POST interface with a JSON payload representing the arguments.
For example, given a schema such as: 
createorreplacetype address_ty as object (
street_num number
,street varchar2(30)
,city varchar2(50)
,zipcode char(5)
)
/
createorreplacetype person_ty as object (
name varchar2(30)
,address address_ty
)
/
createorreplacetype person_nt astable of person_ty
/
createorreplacetype person_va as varray(5) of person_ty
/
createtable staff (
id number(9) notnullprimarykey
,person person_ty notnull
,friends person_va
,relatives person_nt
)
nested table relatives store as relatives_nt_tab
/
A simple GET request might return the following. This code snippet illustrates the automatic JSON structure for the PERSON fields and the VARRAYs and Nested Tables. POST (insert) and PUT (update) use the same style for JSON:
[ { "ID": 1, "PERSON": { "NAME": "Joe", "ADDRESS": { "STREET_NUM": 123, "STREET": "Main Street", "CITY": "Happyville", "ZIPCODE": "90210" } }, "FRIENDS": [ { "NAME": "Alpha", "ADDRESS": {"STREET_NUM": 123, "STREET": "Main Street", "CITY": "Happyville", "ZIPCODE": "90210" } } ], "RELATIVES": [ { "NAME": "Beta", "ADDRESS": { "STREET_NUM": 124, "STREET": "Main Street", "CITY": "Happyville", "ZIPCODE": "90210" } }, { "NAME": "Beta", "ADDRESS": { "STREET_NUM": 124, "STREET": "Main Street", "CITY": "Happyville", "ZIPCODE": "90210" } } ], "@metadata": { "href": "https://server.acme.com/rest/default/demo/v1/test_demooracle:STAFF/1", "checksum": "A:549420f2213e6beb", "links": [] } }, { "ID": 2, "PERSON": { "NAME": "Max", "ADDRESS": { "STREET_NUM": 345, "STREET": "Rue de Paris", "CITY": "Ville de Joyeux", "ZIPCODE": "00000" } }, "FRIENDS": null, "RELATIVES": null, "@metadata": { "href": "https://server.acme.com/rest/default/demo/v1/test_demooracle:STAFF/2", "checksum": "A:0ca691a1500e31b5", "links": [] } } ]
The following code snippet shows an example of a POST to a stored procedure expecting a single 
FIRST_ARG
 argument:
{
"FIRST_ARG": { "NAME": "Joe", "OCCUPATION": "Accountant" }
}
You can also provide this in the GET interface using query parameter arguments, where you quote the curlies according to RFC 3896:
{
"FIRST_ARG": { "NAME": "Joe", "OCCUPATION": "Accountant" }
}
Boolean Data Type
Oracle models the Boolean data type as the 
char(1)
 data type ('Y'/'N'). You can access and test these values in the JavaScript code within your logic, for example:
row.[column name] == 'Y'
XMLTYPE Data Type
You can use the Oracle 
XMLTYPE
 data type for inbound and outbound JSON messages. 
CA Live API Creator
 formats the extension as a text string of the XML content. The following code block shows an example:
{
"ID": 1,
"XMLTYPE_DEFAULT": "<foo/>",
"@metadata": {
"href": "http://example.com/rest/default/demo_oracle/v1/demo:STRESS_XMLTYPE/1",
"checksum": "A:79513c82a7740b47",
"links": []
}
Pagination
Oracle supports LIMIT/OFFSET syntax. 
CA Live API Creator
 uses this syntax where possible.
Oracle Data Source Limitations
The following are known limitations in Oracle data sources:
Timezone Error When Connecting to Oracle Data Source
In certain versions of the Oracle JDBC driver, you might encounter the following error when connecting to an Oracle data source:
ORA 00604 error occurred at recursive SQL level 1 ORA01882 timezone region not found
You can resolve this issue by setting the time zone for API Server. Add the following option when you start API Server:
-Duser.timezone
Example:
 
The following example sets the time zone to Eastern Standard Time (EST):
-Duser.timezone=America/New_York