Oracle Data Source

Oracle Data Source
lac40
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. 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
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.
For more information about the versions of the Oracle JDBC driver that 
CA Live API Creator
 supports, see Installation Requirements and Supported Platforms.
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
number
number
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
bfile
bytes
urowid
bytes
long
string
float
number
date
SimpleTimestamp
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
binary double
number
binary float
number
blob
bytes
clob
string
nclob
string
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.
View Stored Procedures and Functions within Packages
You can view your stored procedures and functions that are contained in packages. 
CA Live API Creator
 exposes your packaged stored procedures and functions as API endpoints. You can view these on the Schema page by clicking the
Procedures
tab.
CA Live API Creator
 displays your packaged functions and stored procedures as a period-separated list between the package name and the name of the function or stored procedure.
For example, you can identify or reference the p
rocA
 stored procedure declared within the
package_name
 package
 as:
package_name.procA
For more information:
Overloaded Functions and Stored Procedures
You can use overloaded functions and stored procedures. The name of the overloaded function or stored procedure remains the same but the argument data type changes.
CA Live API Creator
 assigns a version number to each overloaded function or stored procedure. For example:
package.myFunction.1 (number)
package.myFunction.2 (string)
Test your 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 about how to test your functions and stored procedures using the REST Lab, see Test your API Using the REST Lab.
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