OBDC driver error using ref cursor
460833Oct 18 2005 — edited Oct 27 2005Hi,
I'm not sure this forum precisely fits my problem but its nature force me to post here :).
While migrating a big application from MS SQL I've bumped in many obstacles. Most of them where "by design" which was imposed by using stored procedures for data selection. I've no intentions to discuss this approach but need help in my struggle with oracle obdc driver which does not support procedures with default parameters and result sets at the same time.
suppose I have procedure test
PROCEDURE test (q2 my_int default 1, q3 out my_int, q4 OUT SYS_REFCURSOR);
using
{call test(,?,?)}
I got error "syntax error or access violation"
using binding parameters by name:
{call test(?, ?)}
SQLGetStmtAttr(handle, SQL_ATTR_IMP_PARAM_DESC, &hIpd, 0, 0);
SQLSetDescField(hIpd, 1, SQL_DESC_NAME, "Q2", SQL_NTS);
SQLSetDescField(hIpd, 1, SQL_DESC_UNNAMED, SQL_NAMED, 0);
SQLSetDescField(hIpd, 2, SQL_DESC_NAME, "Q3", SQL_NTS);
SQLSetDescField(hIpd, 2, SQL_DESC_UNNAMED, SQL_NAMED, 0);
I got error:
[Oracle][ODBC][Ora]ORA-06550: line 1, column 43: PLS-00312: a positional parameter association may not follow a named association ORA-06550: line 1, column 7: PL/SQL: Statement ignored (odbcstate=HY000)
in this case I can view in profiler following sql text generated by odbc driver:
BEGIN TEST(Q2=>:1,Q3=>:2,:Q4); END;
:1 = 0
:2 = 1
:3 = <NULL>