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

SQLAllocConnect

Supported

SQLAllocEnv

Supported

SQLAllocHandle

Supported

SQLAllocStmt

Supported

SQLBindCol

Supported

SQLBindParameter

Supported

SQLBrowseConnect

Not supported

SQLBulkOperations

Not supported

SQLCancel

Supported

SQLCloseCursor

Supported

SQLColAttribute

Supported

SQLColAttributes

Supported

SQLColumnPrivileges

Supported

SQLColumns

Supported

SQLConnect

Supported

SQLCopyDesc

Not supported

SQLDisconnect

Supported

SQLDriverConnect

Supported

SQLDrivers

Supported

SQLEndTran

Supported

SQLError

Supported

SQLExecDirect

Supported

SQLExecute

Supported

SQLExtendedFetch

Supported

SQLFetch

Supported

SQLFetchScroll

Supported

SQLForeignKeys

Supported

SQLFreeConnect

Supported

SQLFreeEnv

Supported

SQLFreeHandle

Supported

SQLFreeStmt

Supported

SQLGetConnectAtt

Supported

SQLGetConnectOption

Supported

SQLGetCursorName

Supported

SQLGetData

Supported

SQLGetDescField

Supported

SQLGetDescRec

Supported

SQLGetDiagField

Supported

SQLGetDiagRec

Supported

SQLGetEnvAttr

Supported

SQLGetFunctions

Supported

SQLGetInfo

Supported

SQLGetStmtAttr

Supported

SQLGetStmtOption

Supported

SQLGetTypeInfo

Supported

SQLMoreResults

Supported

SQLNativeSql

Supported

SQLNumParams

Supported

SQLNumResultCols

Supported

SQLParamData

Supported

SQLParamOptions

Supported

SQLPrepare

Supported

SQLPrimaryKeys

Supported

SQLProcedureColumns

Supported

SQLProcedures

Supported

SQLPutData

Supported

SQLRowCount

Supported

SQLSetConnectAttr

Supported

SQLSetConnectOption

Supported

SQLSetCursorName

Supported

SQLSetDescField

Supported

SQLSetDescRec

Supported

SQLSetEnvAttr

Supported

SQLSetParam

Supported

SQLSetPos

Not supported

SQLSetScrollOptions

Not supported

SQLSetStmtOption

Supported

SQLSetStmtAttr

Supported

SQLStatistics

Supported

SQLTablePrivileges

Supported

SQLTables

Supported

SQLTransact

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

Cursor support

The Easysoft ODBC-Oracle WP Driver supports FORWARD_ONLY and STATIC cursors.

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.

  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,
                              deptid in number);
    
    END get_employees;
  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,
                              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

BLOB

SQL_LONGVARBINARY

LONG RAW

SQL_LONGVARBINARY

BFILE

SQL_LONGVARBINARY

RAW

SQL_VARBINARY

CLOB

SQL_LONGVARCHAR

LONG

SQL_LONGVARCHAR

CHAR

SQL_CHAR

NUMERIC

SQL_NUMERIC

DECIMAL

SQL_DECIMAL

INTEGER

SQL_INTEGER

SMALLINT

SQL_SMALLINT

BINARY_FLOAT

SQL_REAL

BINARY_DOUBLE

SQL_FLOAT

DOUBLE PRECISION

SQL_DOUBLE

DATE

SQL_TYPE_DATE

TIMESTAMP

SQL_TYPE_TIMESTAMP

TIMESTAMP WITH TIME ZONE

SQL_TYPE_TIMESTAMP

VARCHAR2

SQL_VARCHAR

NCHAR

SQL_WCHAR

NVARCHAR2

SQL_WVARCHAR

NCLOB

SQL_WLONGVARCHAR

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.

  1. Download the version of ODBC Test that matches your application’s architecture from:

  2. Copy both files to a folder on the machine where Easysoft ODBC-Oracle WP Driver is installed.

  3. Double-click odbcte32.exe.

  4. Select Con > Full Connect.

  5. Choose your Easysoft ODBC-Oracle WP Driver data source from the list.

  6. Choose Catalog > SQLGetTypeInfo.

  7. Either choose SQL_ALL_TYPES=0 (1.0) or a specific data type from the DataType list.

  8. 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 use NOT. 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(), and AVG(). 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, use UNION. 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 (non NULL), use either IS NULL or IS NOT NULL. For example:

    SELECT * FROM Customers WHERE CompanyName IS NULL
  • To specify multiple values in a WHERE clause, you can use IN as an alternative to OR. 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')