Oracle Data Source
Oracle Data Source
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 Creatorsupports, 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:
Install the Oracle JDBC Driver
If you have installed
CA Live API Creatoron Tomcat, copy the license file and the
ojdbc<version>.jarJAR file (the JDBC driver) into the
If you have installed the self-contained, single-user version of
CA Live API Creatorthat is based on Jetty, copy the JDBC driver into the
For more information about the versions of the Oracle JDBC driver that
CA Live API Creatorsupports, 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.
- In the connection wizard, selectOracleas the target database.
- Complete the Database, host, port, and password fields, including the following fields, and then clickContinue:TypeThe Oracle connection type.Default:SIDOptions:SID, Service Name, TNSSID/Service/AliasThe alphanumeric database identifier.SchemaThe 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".UsernameThe 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 Creatormust map the data types in the data source to data types in
CA Live API Creator.
CA Live API Creatorsupports 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:
timestamp with timezone
interval year to month
interval day to second
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:
This data type is a pointer or handle to a ResultSet on the database.
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 Creatorexposes your packaged stored procedures and functions as API endpoints. You can view these on the Schema page by clicking the
CA Live API Creatordisplays 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
rocAstored procedure declared within the
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 Creatorassigns 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
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: