This section contains extra information relating to the deployment of the Easysoft ODBC-Oracle WP Driver.
The Easysoft ODBC-Oracle WP Driver complies with the ODBC 3.52 specification.
The driver is Level 2 compliant.
All ODBC 3.52 calls are supported.
The driver partially supports SQLSetPos. An application can use the driver to specify a cursor position by calling SQLSetPos with the SQL_POSITION argument.
The Easysoft ODBC-Oracle WP Driver supports FORWARD_ONLY and STATIC cursors.
The Easysoft ODBC-Oracle WP Driver supports all scalar functions apart from:
Functions need to be called by using the ODBC escape sequence {fnscalar-function}. For example:
SELECT {fn UCASE(Name)} FROM Employees;
SELECT e.last_name, {fn IFNULL(e.job_id, j.job_id)) "Old Job ID"
FROM employees e, job_history j
WHERE e.employee_id = j.employee_id
The Easysoft ODBC-Oracle WP Driver supports the following Oracle® data types and subtypes:
To call procedures, you must use the ODBC escape sequence rather than the native Oracle® PL/SQL syntax. If the procedure is contained in a package, include the package name in the procedure call. For example:
{CALL mypackage.myprocedure(?)}
Oracle's REF CURSOR data type allows a procedure to return a result set to a client application. Procedures can accept multiple REF CURSOR input parameters, which allows them to return multiple result sets.
Because the REF CURSOR type is not part of the ODBC specification, the Easysoft ODBC-Oracle WP Driver returns the results from REF CURSORs as a standard ODBC result set,
When calling procedures that use REF CURSORs, omit any REF CURSOR parameters from the procedure call.
If a procedure returns multiple REF CURSORs, the Easysoft ODBC-Oracle WP Driver returns them as multiple result sets in the order that the REF CURSOR parameters are defined in the Create Procedure statement.
The C sample in this section shows how to execute and return the results from a packaged procedure that uses two REF CURSORs to return two result sets.
Using Employee data in the HR sample schema, the procedure returns managers and non managers in a particular department.
The HR sample schema and user is included with Oracle® 9i and later. For information about creating the HR sample schemas and unlocking the HR user account, see your Oracle® documentation.
1. As the HR user, run this SQL to create the package.
create or replace PACKAGE get_employees AS
TYPE managers_cur IS REF CURSOR;
TYPE non_managers_cur IS REF CURSOR;
PROCEDURE get_employee_details(managers in out managers_cur,
non_managers in out non_managers_cur,
2. As the HR user, run this SQL to create the procedure in the package body.
create or replace PACKAGE BODY get_employees AS
PROCEDURE get_employee_details(managers in out managers_cur,
non_managers in out non_managers_cur,
AND job_title LIKE '%Manager%'
AND job_title NOT LIKE '%Manager%'
* This C code sample calls get_employees.get_employee_details and
* returns both REF CURSORs from the packaged procedure as result
* Before using this sample, you need to have created the
* get_employees package (see "Prerequisites").
/* See "ODBC from C Tutorial Part 1", on the Easysoft web */
/* site for a definition of extract_error(). */
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
/* Set ODBC version */
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION,
ret = SQLAllocHandle( SQL_HANDLE_DBC, env, &dbc );
/* Connect to the Oracle® data source as the HR user */
ret = SQLDriverConnect(dbc, NULL,
"DSN=ORACLE_SAMPLE;UID=hr;PWD=hr_password",
SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
/* The procedure needs to be given a department ID */
SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_SSHORT,
SQL_INTEGER, 0, 0, &deptid, 0, &deptid_len);
/* Specify both the package name and procedure name in the */
/* procedure call. Just pass the department ID to the */
/* procedure, the PL/SQL package is responsible for passing */
ret = SQLExecDirect(stmt, "{call get_employees.get_employee_details(?)}",
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
SQLBindCol(stmt, 1, SQL_C_CHAR, data, sizeof( data ),
/* Get the first result set */
while (SQL_SUCCEEDED(ret = SQLFetch(stmt))) {
while ( SQLMoreResults(stmt) == SQL_SUCCESS) {
while (SQL_SUCCEEDED(ret = SQLFetch(stmt))) {
fprintf(stderr, "Failed to call procedure\n");
extract_error("SQLExecDirect", stmt, SQL_HANDLE_STMT);
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
fprintf(stderr, "Failed to connect\n");
extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
SQLFreeHandle(SQL_HANDLE_DBC, dbc);
SQLFreeHandle(SQL_HANDLE_ENV, env);
The Easysoft ODBC-Oracle WP Driver supports materialized views. A materialized view is a database object that contains the results of a query. Materialized views stored in the same database as their base tables can improve query performance through query rewrites.
The query rewrite mechanism reduces response time for returning results from the query. It does this by automatically rewriting the SQL query to use the materialized view instead of accessing the original tables. Query rewrites are particularly useful in a data warehouse environment.
For more information about materialized views, see your Oracle® documentation.
If problems occur with metadata, the MetaData_ID parameter needs to be set to 1 in the odbc.ini file.
If you are using OpenOffice.org 1.0, you need to set Metadata_Dont_Do_Schema to 1 in your odbc.ini file. This is not necessary for OpenOffice.org 2.0.
The Easysoft ODBC-Oracle WP Driver is thread-safe in accordance with the ODBC specification and can safely be used behind threaded applications. Usually, applications use one connection handle and multiple threads, which execute SQL statements on that connection.
The ODBC calls an application makes can be traced:
An application can turn tracing on in the Driver Manager by using the ODBC API SQLSetConnectAttr (...,SQL_ATTR_TRACE,...).
The trace file name may also be specified with the SQLSetConnectAttr attribute SQL_ATTR_TRACEFILE.
For the unixODBC Driver Manager, add two attributes to the [ODBC] section (create one if none exists) in odbcinst.ini.
Ensure that the user who is running the application to be traced has write permission to the log file (and to the directory containing it), or no tracing information will be produced.
Driver manager trace files show all the ODBC calls applications make, their arguments and return values. Easysoft ODBC-Oracle WP Driver driver tracing is specific to the Easysoft driver and is of most use when making a support call.
To enable Easysoft ODBC-Oracle WP Driver logging, add a LOGFILE and a LOGGING attribute to the relevant DSN section of the odbc.ini file.
LOGFILE = /tmp/oracle-wp-driver.log
The LOGFILE value is the path and file name of the log file. The value shown in the example specifies a log file named /tmp/oracle-wp-driver.log. The LOGGING value specifies the actions to log. The value shown in the example specifies that all actions should be logged.
Ensure that the user who is running the application to be traced has write permission to the log file (and to the directory containing it).
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
© 1993 - 2024 Easysoft Limited. All rights reserved. Oracle® is a registered trademark of Oracle® Corporation and/or its affiliates. Other trademarks and registered trademarks appearing on easysoft.com are the property of their respective owners.
