Stored function from ODBC = Syntax error or access violation

From: Jamie Pearson <jpearson_at_nspr.com>
Date: Tue, 25 Mar 2003 17:22:19 -0500
Message-ID: <b5qkqs$mt0$1@slb9.atl.mindspring.net>

All,

Testing Oracle ODBC driver 8.1.7.8b with VB application. Using direct ODBC API calls in a wrapper C dll--no ADO, RDO, etc. Attempts to call a stored function fail with: Syntax error or access violation.

Here's info from the ODBC trace:

ScanCol         e60-63c ENTER SQLPrepare
  HSTMT               081A2188
  UCHAR *             0x082A08A8 [      -3] "{? = CALL

dev.pkg.function(dev.get_code(9),{fn
NOW()},USER,NULL,NULL,2,NULL,NULL,NULL,1109,'012400000005','24','2116',NULL,
'SMITH','Joe

Smith',NULL,'PA',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,'01','24',{ ts'2003-03-25 00:00:00.000000'},NULL,'0')}\ 0"

  SDWORD                    -3

ScanCol         e60-63c EXIT  SQLPrepare  with return code -1 (SQL_ERROR)
  HSTMT               081A2188
  UCHAR *             0x082A08A8 [      -3] "{? = CALL

dev.pkg.function(dev.get_code(9),{fn
NOW()},USER,NULL,NULL,2,NULL,NULL,NULL,1109,'012400000005','24','2116',NULL,
'SMITH','Joe

Smith',NULL,'PA',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,'01','24',{ ts'2003-03-25 00:00:00.000000'},NULL,'0')}\ 0"

  SDWORD                    -3

  DIAG [37000] [Oracle][ODBC]Syntax error or access violation. (0)

This call works fine when using the Microsoft ODBC for Oracle or the DataDirect Technologies (Merant/Intersolv) Oracle ODBC drivers. I see in Oracle documentation that procedures should be wrapped with begin/end pairs. Don't see any example for a stored fuction. Seems begin/end pair would defeat the purpose of using ODBC by making the application backend dependent.

Any insight would be appreciated.

Thanks,
Jamie Received on Tue Mar 25 2003 - 16:22:19 CST