About the Easysoft ODBC-Oracle WP Driver
The Easysoft ODBC-Oracle WP Driver provides real-time access to Oracle data from any application that supports ODBC.
The Easysoft ODBC-Oracle WP Driver accesses Oracle directly. The driver does not use Oracle client software.
The Easysoft ODBC-Oracle WP Driver does not require Oracle environment variables such as ORACLE_HOME
and TNS_ADMIN
to be set or make use of Oracle configuration files such as tnsnames.ora
.
In this section:
ODBC API and scalar functions
API functions
Use this table to find out what ODBC API functions the Easysoft ODBC-Oracle WP Driver supports:
Function | Status |
---|---|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Not supported |
|
Not supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Not supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Not supported |
|
Not supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
|
Supported |
Scalar functions
The Easysoft ODBC-Oracle WP Driver supports all scalar functions apart from:
-
DIFFERENCE
-
POSITION
-
COT
-
RAND
-
EXTRACT
-
TIMESTAMPDIFF
Use either the ODBC syntax with scalar functions. For example:
SELECT
Invoice_Id,
Customer_Name,
{fn EXTRACT(YEAR FROM Due_Date)} as "Year"
FROM
Invoice
Procedures
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(?)}
REF CURSORs
Oracles’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.
Example
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.
Prerequisites
The HR sample schema and user is included with Oracle. For information about creating the HR sample schemas and unlocking the HR user account, refer to your Oracle documentation.
-
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, deptid in number); END get_employees;
-
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, deptid in number) IS BEGIN OPEN managers FOR SELECT last_name, first_name FROM emp_details_view WHERE department_id = deptid AND job_title LIKE '%Manager%' ORDER BY employee_id ASC; OPEN non_managers FOR SELECT last_name, first_name FROM emp_details_view WHERE department_id = deptid AND job_title NOT LIKE '%Manager%' ORDER BY employee_id ASC; END get_employee_details; END get_employees;
C code sample
/*
* This C code sample calls get_employees.get_employee_details and
* returns both REF CURSORs from the packaged procedure as result
* sets.
*
* Before using this sample, you need to have created the
* get_employees package (refer to "Prerequisites").
*/
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>
#define DATA_LEN 100
/* Refer to "ODBC from C Tutorial Part 1", on the Easysoft web */
/* site for a definition of extract_error(). */
static void extract_error(
char *fn,
SQLHANDLE handle,
SQLSMALLINT type);
main() {
SQLHENV env;
SQLHDBC dbc;
SQLHSTMT stmt;
SQLRETURN ret;
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
/* Set ODBC version */
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION,
(void *) SQL_OV_ODBC3, 0);
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",
SQL_NTS, NULL, 0, NULL,
SQL_DRIVER_COMPLETE);
if (SQL_SUCCEEDED(ret)) {
SQLSMALLINT deptid;
SQLINTEGER deptid_len;
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);
deptid = 100;
/* 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 */
/* the REF CURSORs. */
ret = SQLExecDirect(stmt, "{call get_employees.get_employee_details(?)}",
SQL_NTS);
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
SQLCHAR data[ 100 ];
SQLINTEGER data_len;
SQLBindCol(stmt, 1, SQL_C_CHAR, data, sizeof( data ),
&data_len);
/* Get the first result set */
printf( "\nManagers\n");
printf( "--------\n");
while (SQL_SUCCEEDED(ret = SQLFetch(stmt))) {
printf("%s\n", data);
}
/* Get the next result set */
while ( SQLMoreResults(stmt) == SQL_SUCCESS) {
printf( "\nNon-managers\n");
printf( "--------\n");
while (SQL_SUCCEEDED(ret = SQLFetch(stmt))) {
printf("%s\n", data);
}
}
} else {
fprintf(stderr, "Failed to call procedure\n");
extract_error("SQLExecDirect", stmt, SQL_HANDLE_STMT);
}
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
SQLDisconnect(dbc);
} else {
fprintf(stderr, "Failed to connect\n");
extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
}
SQLFreeHandle(SQL_HANDLE_DBC, dbc);
SQLFreeHandle(SQL_HANDLE_ENV, env);
}
Data type mapping
The Easysoft ODBC-Oracle WP Driver maps Oracle data types to ODBC data types in this way:
Oracle data type | ODBC data type |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Finding out more about data types on Windows
If you need more information about a data types, for example, the precision and scale, use Microsoft’s ODBC Test to do this.
-
Download the version of ODBC Test that matches your application’s architecture from:
-
Copy both files to a folder on the machine where Easysoft ODBC-Oracle WP Driver is installed.
-
Double-click odbcte32.exe.
-
Select Con > Full Connect.
-
Choose your Easysoft ODBC-Oracle WP Driver data source from the list.
-
Choose Catalog > SQLGetTypeInfo.
-
Either choose SQL_ALL_TYPES=0 (1.0) or a specific data type from the DataType list.
-
Choose Results > Get Data All.
Example SQL statements
Example queries
-
To fetch all records from a table, use the asterisk symbol (
*
) in your queries. For example:SELECT * FROM Customers
-
To only fetch records whose values are different, use
DISTINCT
. For example:-- Which different sales regions are there? SELECT DISTINCT Region AS Different_Regions FROM SalesOrders -- How many different sales regions are there? SELECT COUNT(DISTINCT Region) AS Different_Regions FROM SalesOrders
-
To filter records, use
WHERE
. For example:SELECT OrderDate, SalesRepresentative FROM SalesOrders WHERE Region = 'Eastern' SELECT OrderDate, SalesRepresentative FROM SalesOrders WHERE Region = 'Eastern' OR Region = 'Western' SELECT OrderDate, SalesRepresentative FROM SalesOrders WHERE Region = 'Eastern' AND EXTRACT(YEAR FROM OrderDate) = 2025
You can also supply a
WHERE
clause value as a parameter. For example, to do this in Python:cursor.execute("SELECT OrderDate, SalesRepresentative FROM SalesOrders WHERE Region = ?", ['Eastern'])
-
To fetch records that don’t match the
WHERE
clause pattern useNOT
. For example:SELECT OrderDate, SalesRepresentative FROM SalesOrders WHERE NOT Region = 'Eastern'
-
To sort the result set in either ascending or descending order, use
ORDER BY
. For example:SELECT * FROM SalesOrders ORDER BY OrderDate ASC SELECT * FROM Contacts ORDER BY ( CASE WHEN Surname IS NULL THEN Title ELSE Surname END );
-
To group a result set into summary rows, use
GROUP BY
. For example:SELECT COUNT(Id) As "Number", ProductID FROM SalesOrderItems GROUP BY ProductID SELECT COUNT(Id) As "Number", ProductID FROM SalesOrderItems GROUP BY ProductID HAVING COUNT(Id) > 100;
-
To do calculations based on result set vales, use the SQL aggregate functions
MIN()
,MAX()
,COUNT()
,SUM()
, andAVG()
. For example:SELECT Max(Quantity) FROM SalesOrderItems SELECT Sum(Quantity) FROM SalesOrderItems
-
To convert between compatible data types, use
CAST
. For example:SELECT CAST(Quantity AS Char(100))FROM SalesOrderItems
-
To fetch records that contain column values between a given range, use
BETWEEN
For example:SELECT ProductID FROM SalesOrderItems WHERE Quantity BETWEEN 10 AND 20
-
To combine the result set of two or more
SELECT
statements, useUNION
. For example:SELECT City FROM Contacts UNION SELECT City FROM Customers
-
To combine rows from two or more tables, use
JOIN
. For example:SELECT SalesOrders.ID, Customers.Surname, SalesOrders.OrderDate FROM SalesOrders INNER JOIN Customers ON SalesOrders.CustomerID=Customers.ID;
-
To fetch records that contain column values matching a search pattern, use
LIKE
. For example:SELECT Surname, GivenName FROM Customers WHERE CompanyName LIKE 'R%' SELECT Surname, GivenName FROM Customers WHERE CompanyName LIKE '_he'
-
To search for columns without a value (
NULL
) or with a value (nonNULL
), use eitherIS NULL
orIS NOT NULL
. For example:SELECT * FROM Customers WHERE CompanyName IS NULL
-
To specify multiple values in a
WHERE
clause, you can useIN
as an alternative toOR
. For example:SELECT OrderDate, SalesRepresentative FROM SalesOrders WHERE Region = 'Eastern' OR Region = 'Western' OR Region = 'Central'
can be replaced with:
SELECT OrderDate, SalesRepresentative FROM SalesOrders WHERE Region IN ('Eastern', 'Western', 'Central')
-
To set the maximum number of records to return, use
LIMIT
. For example:SELECT * FROM Customers LIMIT 10
-
To test for the existence of records in a subquery, use
EXISTS
. For example:SELECT Name FROM Products WHERE EXISTS ( SELECT * FROM SalesOrderItems WHERE Products.ID = SalesOrderItems.ProductID AND Quantity < 20 )
Example inserts, updates, and deletes
-
To insert a Oracle record, use
INSERT INTO
. For example:INSERT INTO Customers ( Surname, GivenName, City, Phone, CompanyName ) VALUES ( 'Devlin', 'Michaels', 'Kingston', '2015558966', 'PowerGroup' )
-
Here’s a SQL Server linked server example:
EXEC ('INSERT INTO Customers (Surname, GivenName, City, Phone, CompanyName) VALUES (''Devlin'' , ''Michaels'' , ''Kingston'' , ''2015558966'' , ''PowerGroup'')')
-
Here’s an Oracle linked table example:
DECLARE num_rows integer; BEGIN num_rows:=DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@Link ('INSERT INTO Customers (Surname, GivenName, City, Phone, CompanyName) VALUES (''Devlin'', ''Michaels'', ''Kingston'', ''2015558966'', ''PowerGroup'')'); END; /
-
The Easysoft ODBC-Oracle WP Driver also supports parameterized inserts. Here’s an example of doing this in Perl:
my $sth = $dbh->prepare(q/INSERT INTO Customers (Surname, GivenName, City, Phone, CompanyName) VALUES (?, ?, ?, ?, ?)/) or die "Can't prepare statement: $DBI::errstr"; $sth->execute('Devlin', 'Michaels', 'Kingston', '2015558966', 'PowerGroup');
-
To update a Oracle record, use
UPDATE
. For example:UPDATE Customers SET Surname = 'Jones' WHERE Account_Id = 'PowerGroup'
The Easysoft ODBC-Oracle WP Driver also supports parameterized updates. Here’s an example of doing this in Perl:
my $sth = $dbh->prepare('UPDATE Customers SET Surname = \'Jones\' WHERE CompanyName = ?') or die "Can't prepare statement: $DBI::errstr"; $sth->execute('PowerGroup');
-
To delete a Oracle record, use
DELETE
. For example:-- Delete (mark inactive) a bank account DELETE FROM Customers WHERE CompanyName = 'PowerGroup'
The Easysoft ODBC-Oracle WP Driver also supports parameterized deletes. Here’s an example of doing this in Python:
sql = "DELETE FROM Customers WHERE CompanyName = ?" cursor.execute(sql, 'PowerGroup')