The Oracle server waits indefinitely for lock conflicts between transactions, to be resolved. You can limit the amount of time that the Oracle server waits for locks to be resolved by calling the ODBC SQLSetConnectAttr function before connecting to the data source.

Specify a non-zero value for the SQL_ATTR_QUERY_TIMEOUT attribute in the ODBC SQLSetStmtAttr function. If you specify a lock timeout value using the ODBC SQLSetConnectAttr function, it overrides any value specified in the oraodbc.ini file.

For Windows platforms, when you link your program, you must link it with the import library odbc32.lib.

For UNIX platforms, an ODBC application must be linked to libodbc.so.

Oracle reference cursors (Result Sets) allow an application to retrieve data using stored procedures and stored functions. The following information identifies how to use reference cursors to enable Result Sets through ODBC.

• The ODBC syntax for calling stored procedures must be used. Native PL/SQL is not supported through ODBC. The following identifies how to call the procedure or function without a package and within a package. The package name, in this case, is RSET.

  Procedure call: 
  {CALL Example1(?)}
  {CALL RSET.Example1(?)} 
  Function Call: 
  {? = CALL Example1(?)}
  {? = CALL RSET.Example1(?)} 
  

• The PL/SQL reference cursor parameters are omitted when calling the procedure. For example, assume that the procedure Example2 is defined to have four parameters. Parameters 1 and 3 are reference cursor parameters and parameters 2 and 4 are character strings. The call is specified as:

  {CALL RSET.Example2("Literal 1", "Literal 2")}
  

The following example application shows how to return a Result Set using the Oracle Database ODBC driver:

/*
 * Sample Application using Oracle reference cursors via ODBC
 *
 * Assumptions:
 *
 * 1) Oracle Sample database is present with data loaded for the EMP table.
 * 2) Two fields are referenced from the EMP table ename and mgr.
 * 3) A data source has been setup to access the sample database.
 *
 * Program Description:
 *
 * Abstract:
 *
 * This program demonstrates how to return result sets using
 * Oracle stored procedures
 *
 * Details:
 *
 * This program:
 * Creates an ODBC connection to the database.
 * Creates a Packaged Procedure containing two result sets.
 * Executes the procedure and retrieves the data from both result sets.
 * Displays the data to the user.
 * Deletes the package then logs the user out of the database.
 *
 *
 * The following is the actual PL/SQL this code generates to
 * create the stored procedures.
 *
 * DROP PACKAGE ODBCRefCur;
 *
 * CREATE PACKAGE ODBCRefCur AS
 * TYPE ename_cur IS REF CURSOR;
 * TYPE mgr_cur IS REF CURSOR;
 * PROCEDURE EmpCurs(Ename IN OUT ename_cur, Mgr IN OUT mgr_cur, pjob IN VARCHAR2);
 * END;
 *
 * CREATE or REPLACE PACKAGE BODY ODBCRefCur AS
 * PROCEDURE EmpCurs(Ename IN OUT ename_cur, Mgr IN OUT mgr_cur, pjob IN VARCHAR2)
 * AS
 * BEGIN
 * IF NOT Ename%ISOPEN
 * THEN
 * OPEN Ename for SELECT ename from emp;
 * END IF;
 * IF NOT Mgr%ISOPEN
 * THEN
 * OPEN Mgr for SELECT mgr from emp where job = pjob;
 * END IF;
 * END;
 * END;
 *
 */

/* Include Files */
#ifdef WIN32
  #include <windows.h>
#endif
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>
 
/* Defines */
#define JOB_LEN 9
#define DATA_LEN 100
#define SQL_STMT_LEN 500
 
/* Procedures */
void DisplayError(SWORD HandleType, SQLHANDLE hHandle, char *Module);
 
/* Main Program */
int main()
{
  SQLHENV hEnv;
  SQLHDBC hDbc;
  SQLHSTMT hStmt;
  SQLRETURN rc;
  char *DefUserName ="scott";
  char *DefPassWord ="tiger";
  SQLCHAR ServerName[DATA_LEN];
  SQLCHAR *pServerName=ServerName;
  SQLCHAR UserName[DATA_LEN];
  SQLCHAR *pUserName=UserName;
  SQLCHAR PassWord[DATA_LEN];
  SQLCHAR *pPassWord=PassWord;
  char Data[DATA_LEN];
  SQLINTEGER DataLen;
  char error[DATA_LEN];
  char *charptr;
  SQLCHAR SqlStmt[SQL_STMT_LEN];
  SQLCHAR *pSqlStmt=SqlStmt;
  char *pSalesMan = "SALESMAN";
  SQLINTEGER sqlnts=SQL_NTS;

  /* Allocate the Environment Handle */
  rc = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv );
  if (rc != SQL_SUCCESS)
  {
    printf( "Cannot Allocate Environment Handle/n");
    printf( "/nHit Return to Exit/n");
    charptr = gets ((char *)error);
    exit(1);
  }

  /* Set the ODBC Version */
  rc = SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
  if (rc != SQL_SUCCESS)
  {
    printf("Cannot Set ODBC Version/n");
    printf("/nHit Return to Exit/n");
    charptr = gets((char *)error);
    exit(1);
  }


  /* Allocate the Connection handle */
  rc = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc);
  if (rc != SQL_SUCCESS)
  {
    printf("Cannot Allocate Connection Handle/n");
    printf("/nHit Return to Exit/n");
    charptr = gets((char*) error);
    exit(1);
  }

  /* Get User Information */
  lstrcpy((char*) pUserName, DefUserName);
  lstrcpy((char*) pPassWord, DefPassWord);

  /* Data Source name */
  printf( "/nEnter the ODBC Data Source Name/n" );
  charptr = gets((char*) ServerName);

  /* User Name */
  printf("/nEnter User Name Default [%s]/n", pUserName);
  charptr = gets((char*) UserName);
  if (*charptr == '/0')
  {
    lstrcpy((char*) pUserName, (char*) DefUserName);
  }

  /* Password */
  printf ("/nEnter Password Default [%s]/n", pPassWord);
  charptr = gets((char*) PassWord);
  if (*charptr == '/0')
  {
    lstrcpy((char*) pPassWord, (char*) DefPassWord);
  }

  /* Connection to the database */
  rc = SQLConnect(hDbc, pServerName, (SQLSMALLINT) lstrlen((char *)pServerName), pUserName,
                  (SQLSMALLINT) lstrlen((char*)pUserName), pPassWord,
                  (SQLSMALLINT) lstrlen((char *)pPassWord));
  if (rc != SQL_SUCCESS)
  {
    DisplayError(SQL_HANDLE_DBC, hDbc, "SQLConnect");
  }
 
  /* Allocate a Statement */
  rc = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt);
  if (rc != SQL_SUCCESS)
  {
    printf( "Cannot Allocate Statement Handle/n");
    printf( "/nHit Return to Exit/n");
    charptr = gets((char *)error);
    exit(1);
  }

  /* Drop the Package */
  lstrcpy((char *) pSqlStmt, "DROP PACKAGE ODBCRefCur");
  rc = SQLExecDirect(hStmt, pSqlStmt, lstrlen((char *)pSqlStmt));

  /* Create the Package Header */
  lstrcpy((char *) pSqlStmt, "CREATE PACKAGE ODBCRefCur AS/n" );
  lstrcat((char *) pSqlStmt, " TYPE ename_cur IS REF CURSOR;/n" );
  lstrcat((char *) pSqlStmt, " TYPE mgr_cur IS REF CURSOR;/n" );
  lstrcat((char *) pSqlStmt, " PROCEDURE EmpCurs (Ename IN OUT ename_cur," );
  lstrcat((char *) pSqlStmt, " Mgr IN OUT mgr_cur,pjob IN VARCHAR2);/n/n");
  lstrcat((char *) pSqlStmt, "END;/n" );

  rc = SQLExecDirect(hStmt, pSqlStmt, lstrlen((char *)pSqlStmt));
  if (rc != SQL_SUCCESS)
  {
    DisplayError(SQL_HANDLE_STMT, hStmt, "SQLExecDirect");
  }

  /* Create the Package Body */
  lstrcpy((char *) pSqlStmt, "CREATE PACKAGE BODY ODBCRefCur AS/n" );
  lstrcat((char *) pSqlStmt, " PROCEDURE EmpCurs (Ename IN OUT ename_cur," );
  lstrcat((char *) pSqlStmt, " Mgr IN OUT mgr_cur, pjob IN VARCHAR2)/n" );
  lstrcat((char *) pSqlStmt, " AS/n" );
  lstrcat((char *) pSqlStmt, " BEGIN/n" );
  lstrcat((char *) pSqlStmt, " IF NOT Ename%ISOPEN/n" );
  lstrcat((char *) pSqlStmt, " THEN/n" );
  lstrcat((char *) pSqlStmt, " OPEN Ename for SELECT ename from emp;/n" );
  lstrcat((char *) pSqlStmt, " END IF;/n/n" );
  lstrcat((char *) pSqlStmt, " IF NOT Mgr%ISOPEN/n THEN/n" );
  lstrcat((char *) pSqlStmt, " OPEN Mgr for SELECT mgr from emp where job = pjob;/n");
  lstrcat((char *) pSqlStmt, " END IF;/n" );
  lstrcat((char *) pSqlStmt, " END;/n" );


  lstrcat((char *) pSqlStmt, "END;/n" );

  rc = SQLExecDirect(hStmt, pSqlStmt, lstrlen((char *)pSqlStmt));
  if(rc != SQL_SUCCESS)
    DisplayError(SQL_HANDLE_STMT, hStmt, "SQLExecDirect");

  /* Bind the Parameter */
  rc = SQLBindParameter(hStmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, JOB_LEN, 0, pSalesMan, 0, &sqlnts);

  /* Call the Store Procedure which executes the Result Sets */
  lstrcpy( (char *) pSqlStmt, "{CALL ODBCRefCur.EmpCurs(?)}");

  rc = SQLExecDirect(hStmt, pSqlStmt, lstrlen((char *)pSqlStmt));
  if(rc != SQL_SUCCESS)
    DisplayError(SQL_HANDLE_STMT, hStmt, "SQLExecDirect");

  /* Bind the Data */
  rc = SQLBindCol(hStmt, 1, SQL_C_CHAR, Data, sizeof(Data), &DataLen);
  if(rc != SQL_SUCCESS)
    DisplayError(SQL_HANDLE_STMT, hStmt, "SQLBindCol");
 
  /* Get the data for Result Set 1 */
  printf("/nEmployee Names/n/n");

  while(rc == SQL_SUCCESS)
  {
    rc = SQLFetch(hStmt);
    if(rc == SQL_SUCCESS)
      printf("%s/n", Data);
    else
      if(rc != SQL_NO_DATA)
        DisplayError(SQL_HANDLE_STMT, hStmt, "SQLFetch");
  }

  printf( "/nFirst Result Set - Hit Return to Continue/n");
  charptr = gets ((char *)error);

  /* Get the Next Result Set */
  rc = SQLMoreResults( hStmt );
  if(rc != SQL_SUCCESS)
    DisplayError(SQL_HANDLE_STMT, hStmt, "SQLMoreResults");

  /* Get the data for Result Set 2 */
  printf("/nManagers/n/n");
  while (rc == SQL_SUCCESS)
  {
    rc = SQLFetch(hStmt);
    if(rc == SQL_SUCCESS)
      printf("%s/n", Data);
    else
      if (rc != SQL_NO_DATA)
        DisplayError(SQL_HANDLE_STMT, hStmt, "SQLFetch");
  }

  printf("/nSecond Result Set - Hit Return to Continue/n");
  charptr = gets((char *)error);

  /* Should Be No More Results Sets */
  rc = SQLMoreResults( hStmt );
  if (rc != SQL_NO_DATA)
    DisplayError(SQL_HANDLE_STMT, hStmt, "SQLMoreResults");

  /* Drop the Package */
  lstrcpy((char *)pSqlStmt, "DROP PACKAGE ODBCRefCur");
  rc = SQLExecDirect(hStmt, pSqlStmt, lstrlen((char *)pSqlStmt));

  /* Free handles close connections to the database */
  SQLFreeHandle( SQL_HANDLE_STMT, hStmt );
  SQLDisconnect( hDbc );
  SQLFreeHandle( SQL_HANDLE_DBC, hDbc );
  SQLFreeHandle( SQL_HANDLE_ENV, hEnv );

  printf( "/nAll Done - Hit Return to Exit/n");
  charptr = gets ((char *)error);
  return(0);
}
 
/* Display Error Messages */
void DisplayError( SWORD HandleType, SQLHANDLE hHandle, char *Module )
{
  SQLCHAR MessageText[255];
  SQLCHAR SQLState[80];
  SQLRETURN rc=SQL_SUCCESS;
  LONG NativeError;
  SWORD RetLen;
  SQLCHAR error[25];
  char *charptr;

  rc = SQLGetDiagRec(HandleType, hHandle, 1, SQLState, &NativeError, MessageText, 255, &RetLen);
  printf( "Failure Calling %s/n", Module );
  if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
  {
    printf( "/t/t/t State: %s/n", SQLState);
    printf( "/t/t/t Native Error: %d/n", NativeError );
    printf( "/t/t/t Error Message: %s/n", MessageText );
  }

  printf( "/nHit Return to Exit/n");
  charptr = gets ((char *)error);
  exit(1);
}

If the syntax of your SQL Server EXEC statement can be readily translated to an equivalent Oracle procedure call without change, the Oracle Database ODBC driver can translate it, if you enable this option.

The complete name of a SQL Server procedure consists of up to four identifiers:

• server name

• database name

• owner name

• procedure name

The format for the name is:

[[[server.][database].][owner_name].]procedure_name

During the migration of the SQL Server database to Oracle, the definition of each SQL Server procedure (or function) is converted to its equivalent Oracle syntax and is defined in a schema in Oracle. Migrated procedures are often reorganized (and created in schemas) in one of these ways:

• All procedures are migrated to one schema (the default option).

• All procedures defined in one SQL Server database are migrated to the schema named with that database name.

• All procedures owned by one user are migrated to the schema named with that user's name.

To support these three ways of organizing migrated procedures, you can specify one of these schema name options for translating procedure names. Object names in the translated Oracle procedure call are not case-sensitive.

If the SQL_ORCLATTR_FAILOVER_CALLBACK and SQL_ORCLATTR_FAILOVER_HANDLE attributes of the SQLSetConnectAttr function are set when a connection failure occurs in an Oracle Real Application Clusters (Oracle RAC) Database environment, event notification is enabled. Both attributes are set using the SQLSetConnectAttr function. The symbols for the new attributes are defined in the file sqora.h.

The SQL_ORCLATTR_FAILOVER_CALLBACK attribute specifies the address of a routine to call when a failure event takes place.

The SQL_ORCLATTR_FAILOVER_HANDLE attribute specifies a context handle that is passed as a parameter in the callback routine. This attribute is necessary for the ODBC application to determine which connection the failure event is taking place on.

The function prototype for the callback routine is:

void failover_callback(void *handle, SQLINTEGER fo_code)

The 'handle' parameter is the value that was set by the SQL_ORCLATTR_FAILOVER_HANDLE attribute. Null is returned if the attribute has not been set.

The fo_code parameter identifies the failure event that is taking place. The failure events map directly to the events defined in the OCI programming interface. The list of possible events is:

• ODBC_FO_BEGIN

• ODBC_FO_ERROR

• ODBC_FO_ABORT

• ODBC_FO_REAUTH

• ODBC_FO_END

The following is a sample program that demonstrates, using this feature:

/*
  NAME
  ODBCCallbackTest
 
  DESCRIPTION
  Simple program to demonstrate the connection failover callback feature.
 
  PUBLIC FUNCTION(S)
  main
 
  PRIVATE FUNCTION(S)
 
  NOTES
 
  Command Line: ODBCCallbackTest filename [odbc-driver]
 
*/
 
#include <windows.h>
#include <tchar.h>
#include <malloc.h>
#include <stdio.h>
#include <string.h>
#include <sql.h>
#include <sqlext.h>
#include "sqora.h"
 
/*
** Function Prototypes
*/
void display_errors(SQLSMALLINT HandleType, SQLHANDLE Handle);
void failover_callback(void *Handle, SQLINTEGER fo_code);
 
/*
** Macros
*/
#define ODBC_STS_CHECK(sts) \
  if (sts != SQL_SUCCESS) \
{ \
  display_errors(SQL_HANDLE_ENV, hEnv); \
  display_errors(SQL_HANDLE_DBC, hDbc); \
  display_errors(SQL_HANDLE_STMT, hStmt); \
  return FALSE; \
}
 
/*
** ODBC Handles
*/
SQLHENV *hEnv = NULL; // ODBC Environment Handle
SQLHANDLE *hDbc = NULL; // ODBC Connection Handle
SQLHANDLE *hStmt = NULL; // ODBC Statement Handle
 
/*
** Connection Information
*/
TCHAR *dsn = _T("odbctest");
TCHAR *uid = _T("scott");
TCHAR *pwd = _T("tiger");
TCHAR *szSelect = _T("select * from emp");
 
/*
** MAIN Routine
*/
main(int argc, char **argv)
{
  SQLRETURN rc;
 
  /*
  ** Allocate handles
  */
  rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, (SQLHANDLE *)&hEnv);
  ODBC_STS_CHECK(rc)
 
  rc = SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
  ODBC_STS_CHECK(rc);
 
  rc = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, (SQLHANDLE *)&hDbc);
  ODBC_STS_CHECK(rc);
 
  /*
  ** Connect to the database
  */
  rc = SQLConnect(hDbc, dsn, (SQLSMALLINT)_tcslen(dsn),
  uid, (SQLSMALLINT)_tcslen(uid),
  pwd, (SQLSMALLINT)_tcslen(pwd));
  ODBC_STS_CHECK(rc);
 
  /*
  ** Set the connection failover attributes
  */
  rc = SQLSetConnectAttr(hDbc, SQL_ORCLATTR_FAILOVER_CALLBACK, &failover_callback, 0);
  ODBC_STS_CHECK(rc);
 
  rc = SQLSetConnectAttr(hDbc, SQL_ORCLATTR_FAILOVER_HANDLE, hDbc, 0);
  ODBC_STS_CHECK(rc);
 
  /*
  ** Allocate the statement handle
  */
  rc = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, (SQLHANDLE *)&hStmt);
  ODBC_STS_CHECK(rc);
 
  /*
  ** Wait for connection failovers
  */
   while (TRUE)
  {
   Sleep(5000);
   rc = SQLExecDirect(hStmt,szSelect, _tcslen(szSelect));
   ODBC_STS_CHECK(rc);
 
   rc = SQLFreeStmt(hStmt, SQL_CLOSE);
   ODBC_STS_CHECK(rc);
  }
 
  /*
  ** Free up the handles and close the connection
  */
  rc = SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
  ODBC_STS_CHECK(rc);
 
  rc = SQLDisconnect(hDbc);
  ODBC_STS_CHECK(rc);
 
  rc = SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
  ODBC_STS_CHECK(rc);
 
  rc = SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
  ODBC_STS_CHECK(rc);
 
  return TRUE;
}
 
/*
** Failover Callback Routine
*/
void failover_callback(void *Handle, SQLINTEGER fo_code)
{
  switch(fo_code)
  {
  case ODBC_FO_BEGIN:
    printf("ODBC_FO_BEGIN recevied\n");
    break;
   
  case ODBC_FO_ERROR:
    printf("ODBC_FO_ERROR recevied\n");
    break;
  
  case ODBC_FO_ABORT:
    printf("ODBC_FO_ABORT recevied\n");
    break;
  
  case ODBC_FO_REAUTH:
    printf("ODBC_FO_REAUTH recevied\n");
    break;
  
  case ODBC_FO_END:
    printf("ODBC_FO_END recevied\n");
    break;
  
  default:
    printf("Invalid or unknown ODBC failover code recevied\n");
    break;
  } 
  return;
 
}
 
/*
** Retrieve the errors associated with the handle passed
** and display them.
*/
void display_errors(SQLSMALLINT HandleType, SQLHANDLE Handle)
{
  SQLTCHAR MessageText[256];
  SQLTCHAR SqlState[5+1];
  SQLSMALLINT i=1;
  SQLINTEGER NativeError;
  SQLSMALLINT TextLength;
  SQLRETURN sts = SQL_SUCCESS;
 
  if (Handle == NULL) return;
 
  /* Make sure all SQLState text is null terminated */
  SqlState[5] = '\0';
 
  /*
  ** Fetch and display all diagnostic records that exist for this handle
  */
  while (sts == SQL_SUCCESS)
  {
    NativeError = 0;
    TextLength = 0;

    sts = SQLGetDiagRec(HandleType, Handle, i, SqlState, &NativeError, (SQLTCHAR *)&MessageText, sizeof(MessageText), &TextLength);
    if (sts == SQL_SUCCESS)
    {
      printf("[%s]%s\n", SqlState, MessageText);
      if (NativeError != 0)
        printf("Native Error Code: %d\n", NativeError);
      i++;
    }
  } 
  return;
}

Use this option when you migrate any third party ODBC application to Oracle Database and you want to use implicit results functionality as supported by the previous vendor. The Oracle Database ODBC driver supports implicit results with stored procedures or an anonymous PL/SQL block. For the current release, implicit results are returned only for SELECT statements.

The following code example shows an example ODBC test case using an anonymous SQL script for implicit results.

const char *query1="declare \
                  c1 sys_refcursor; \
                  c2 sys_refcursor; \
                  begin \
                  open c1 for select empno,ename from emp where rownum<=3; \
                  dbms_sql.return_result(c1); \
                  open c2 for select empno,ename from emp where rownum<=3; \
                  dbms_sql.return_result(c2); end; ";
 
int main( )
{
  ...
   ...
 //Allocate all required handles and establish a connection to the database.
 
 //Prepare and execute the above anonymous PL/SQL block
    SQLPrepare (hstmt, (SQLCHAR *) query1, SQL_NTS);
    SQLExecute(hstmt);
 
 //Bind the columns for the results from the first SELECT statement in an anonymous block.
    SQLBindCol (hstmt, 1, SQL_C_ULONG, &eno, 0, &jind);
    SQLBindCol (hstmt, 2, SQL_C_CHAR, empname, sizeof (empname),&enind);
 
 //Fetch implicit results through the SQLFetch( ) call.
    while((retCode = SQLFetch(hstmt)) != SQL_NO_DATA)
    {
 //Do whatever you want to do with the data.
    }
 
     retCode = SQLMoreResults(hstmt);
 
     if(retCode == SQL_SUCCESS)
    {
      printf("SQLMoreResults returned with SQL_SUCCESS\n");
 
 //Bind the columns for the results from the second SELECT statement in an anonymous block.
    SQLBindCol (hstmt, 1, SQL_C_ULONG, &eno, 0, &jind);
    SQLBindCol (hstmt, 2, SQL_C_CHAR, empname, sizeof (empname),&enind);
 
 //Fetch implicit results through the SQLFetch( ) call.
    while((retCode = SQLFetch(hstmt)) != SQL_NO_DATA)
    {
 //Do whatever you want to do with data.
    }
  }
}