Oracle Data Source

Oracle Data Source
calac41
This article includes information about how to install the Oracle JDBC driver, how to connect to an Oracle database, a list of the Oracle data types that 
CA Live API Creator
 supports, how to manage the packages that are defined in your Oracle database, and known Oracle data source limitations.
In this article:
3
Install the Oracle JDBC Driver
After you have installed the JDBC driver, the Oracle data source is available as an option in the connection wizard when creating an API.
For more information about the versions of the Oracle JDBC driver that 
CA Live API Creator
 supports, see Installation Requirements and Supported Platforms.
If you have installed 
CA Live API Creator
 on Tomcat, copy the license file and the 
ojdbc<version>.jar
 JAR file (the JDBC driver) into the 
%{CATALINA_HOME}/lib
 directory.
If you have installed the self-contained, single-user version of 
CA Live API Creator
 that is based on Jetty, copy the JDBC driver into the 
%JETTY_HOME%/caliveapicreator/lib/ext
 directory.
Connect to an Oracle Database
Prerequisites:
 You know your JDBC connection information.
Connect to your Oracle database by creating your API.
For more information about how to create an API using API Creator, see Create your API.
  1. In the connection wizard, select 
    Oracle
    as the target database.
  2. 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 name.
    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) username.
You are connected to the database.
Oracle Supported 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
varchar2
string
nchar
string
nvarchar2
string
clob
string
nclob
string
long
string
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.
date
SimpleTimestamp
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
bfile
bytes
urowid
bytes
blob
bytes
number
number
float
number
binary double
number
binary float
number
ref cursor
Varrays, Nested tables
Other Supported Oracle Data Types
You can use read and write the following other data types when you work with an Oracle database:
Data type
Modeled as...
Notes
ref cursor
This data type is a pointer or handle to a ResultSet on the database.
xml
string
This data type stores XML data.
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:
Use Sequences in Oracle Data Sources
Oracle typically handles auto-generated primary keys using sequences, which are database objects that the sequence generator in the database uses to generate sequential values. When you connect to an Oracle data source, 
CA Live API Creator
 detects the sequences that are defined in your schema.
You can have 
CA Live API Creator
 auto-generate primary keys using these sequences using the following methods:
Associate Entity Columns to Sequences
When you connect to a database that supports sequences, such as Oracle, 
CA Live API Creator
 detects the sequences that are defined in your schema. 
CA Live API Creator
 cannot know which entity (table or view) uses which sequence. If your database includes sequences, you want it to generate unique primary keys during row inserts, and you want 
CA Live API Creator
 to auto-generate numbers for the entity's column using the sequence, associate that entity's column to the sequence.
For more information, see Associate Entities to Sequences.
Define Rules that Return a Unique Primary Key
You can have 
CA Live API Creator
 generate the following primary keys for the table using sequences and assign it to a column by way of event rules:
  • Alphanumeric unique primary key:
     Define a 
    pre-insert event rule
    . Within the code for the rule, define how 
    CA Live API Creator
     computes the key. Pre-insert event rules fire only on client inserts. Rules of this type automatically handle inserting a parent and a set of children in a single transmission (Cascade Add).
    The
    Oracle Sequence Example
    API sample illustrates an example of how you can have
    CA Live API Creator
    insert a parent and a set of children in the same request.
    For more information about this example, see Oracle Sequence Example API Sample.
  • Unique primary key:
     Define an 
    early event rule
    . Define a rule of this type for row inserts that are triggered from within your rule logic (for example, audit changes into child tables).
    The
    Oracle Sequence Example
    API sample illustrates an example of how you can have
    CA Live API Creator
    compute an alphanumeric primary key in an early event rule using sequences.
    For more information about this example, see Oracle Sequence Example API Sample.
For more information about how to define pre-insert and early event rule types, see Event Rule Types.
Oracle Extensions
You can use the following Oracle extensions in 
CA Live API Creator
.
Sequences
Oracle provides sequences for automatic value generation. In many ways, they are superior to other databases identifier columns, but unless you are familiar with Oracle, they may seem obtuse.
You can use sequences in Oracle using one of the following approaches:
  • Traditional 
    SEQ.NEXTVAL
     on insert
  • BEFORE UPDATE
     trigger
  • 'Automatic' sequence
For more information about how to view or display sequences, see Database Connectivity.
Traditional SEQ.NEXTVAL on Insert
Inserts are in the following form:
INSERT INTO FOO(A, B, C) VALUES (MYSEQUENCE.NEXTVAL, 1.23, 'Hello')
For example, you can specify that column 
A
 in table 
FOO
 should use the sequence 
MYSEQUENCE
CA Live API Creator
 uses the sequence to generate a value for the specified column for all inserts. You can use the same sequence for more than one table.
BEFORE UPDATE Trigger
If you are porting from a database that has IDENTIFIER or autonum (identity) columns, use sequences in Oracle using the 
BEFORE UPDATE
 trigger approach. Insert statements are of the following forms, with special queries to get back the inserted value:
INSERT INTO FOO(B, C) VALUES (1.23, 'Hello')
or
INSERT INTO FOO(A, B, C) VALUES (NULL, 1.23, 'Hello') 
For example, you can tell 
CA Live API Creator
 that it should expect the database to automatically set a column.
Follow these steps:
  1. In the Integrate section, click 
    Data Sources
    .
    The Connection tab appears by default.
  2. Click the 
    Sequences
     tab.
    The Sequences page displays.
  3. For each column that uses sequences, select the column that you want the trigger to send, and then accept the default value (
    <None>
    ) for Sequences.
'Automatic' Sequence
CA Live API Creator
 performs inserts as:
INSERT INTO FOO(A, B, C) VALUES (NULL, 1.23, 'Hello')
or
INSERT INTO FOO(B, C) VALUES (1.23, 'Hello')
Follow these steps:
  1. In the Integrate section, click 
    Data Sources
    .
    The Connection tab appears by default.
  2. Click the 
    Sequences
     tab.
  3. The Sequences page displays.
  4. For each column that uses sequences, select the column that you want to automatic sequence, and then accept the default value (
    <None>
    ) for Sequences.
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, a table defined as the following is displayed in the Tables as 
FooBar
 with two 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 exression 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';
Boolean
Oracle does not have a boolean type. Oracle databases traditionally use char(1) with 'Y' and 'N' for boolean values. These are tested in rules as column == 'Y'.
User Defined Types, Nested Tables, VARRAYs
API Creator automatically recognizes Oracle's User Defined Types, Nested Tables, and VARRAYs. These extensions are fully supported for GET, PUT, POST, DELETE. In addition, API Creator supports stored procedures and Oracle functions that return or take UDTs, Nest 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:
[   {     "ID": 1,     "PERSON": {       "NAME": "David",       "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/el-dev/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/el-dev/demo/v1/test_demooracle:STAFF/2",       "checksum": "A:0ca691a1500e31b5",       "links": []     }   } ]
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.
The following code snippet shows an example of a POST to a stored procedure expecting a single 
FIRST_ARG
 argument:
{
  "FIRST_ARG": { "NAME": "David", "OCCUPATION": "Software Specialist" }
}
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": "David", "OCCUPATION": "Software Specialist" }
}
Pagination
Oracle supports LIMIT/OFFSET syntax. 
CA Live API Creator
 uses this syntax where possible.
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": []
  }
Oracle Data Source Limitation
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 timezone for API Server. Add the following configuration parameter to your startup script in Tomcat:
-Duser.timezone=GMT