将参数传递给Oracle存储过程

This may have been asked before, so I'm sorry if this is repeated. If you can link to where I can find an answer, I would really appreciate it. I've looked around at other answers and on Google but nothing seems to have worked yet. So...

这可能是之前被问过的,所以如果重复这一点,我很抱歉。如果你可以链接到我能找到答案的地方,我会非常感激。我已经环顾了其他答案并在Google上,但似乎没有任何效果。所以...

I have a stored prodecure in Oracle 11g (that I cannot change) and I have to use OCI (no choice here).

我在Oracle 11g中存储了一个prodecure(我无法更改),我必须使用OCI(这里没有选择)。

The stored procedure declaration is like:

存储过程声明如下:

PROCEDURE GetEmployee(EID IN NUMBER, rcursor IN OUT cursor_type);

How do I call this in C?

我怎么称呼C?

I tried to use the OCI example here as a basis, modifying it so:

我试着在这里使用OCI示例作为基础,修改它:

char * query =  "DECLARE \
     EID NUMBER; \
     RCURSOR CORP.EMPASSIST.cursor_type; \
     BEGIN \
       EID:= NULL; \
       RCURSOR := NULL; \
       EMPASSIST.GetEmployee( EID=> EID, RCURSOR => RCURSOR ); \
       :RCURSOR := RCURSOR; --<-- Cursor \
     END;";

OCIError * db_error;
OCIStmt * statement;
OCIEnv * environment;
OCIServer * server;
OCISession * session;
OCISvcCtx * service;

OCIBind * cursor_bind;
OCIBind * eid_bind;
OCIStmt * cursor_stm;
OCIStmt * eid_stm;

retval += OCIStmtPrepare(statement, db_error, (OraText *) query, strlen(query), OCI_NTV_SYNTAX, OCI_DEFAULT);
retval += OCIHandleAlloc(environment, (void **) &eid_stm, OCI_HTYPE_STMT, 0, NULL);
retval += OCIHandleAlloc(environment, (void **) &cursor_stm, OCI_HTYPE_STMT, 0, NULL);
retval += OCIBindByPos(statement, &eid_bind, db_error, 1, &eid_stm, 0, SQLT_NUM, NULL, 0, NULL, 0, 0, OCI_DEFAULT);
retval += OCIBindByPos(statement, &cursor_bind, db_error, 2, &cursor_stm, 0, SQLT_RSET, NULL, 0, NULL, 0, 0, OCI_DEFAULT);

retval += OCIStmtExecute(service, statement, db_error, 1, 0, NULL, NULL, OCI_COMMIT_ON_SUCCESS);

but this does not seem to work for me. All the handle allocation seems to work just fine. No errors. However, it fails on the OCIStmtExecute step. I have to BindByPos after that, and they fail too, but I'm guessing that is because of the Statement Execution Failure.

但这似乎对我不起作用。所有的句柄分配似乎都运行得很好。没有错误。但是,它在OCIStmtExecute步骤失败。之后我必须使用BindByPos,它们也会失败,但我猜这是因为语句执行失败。

Please help!

==========================================================================

Fixed: Posting in answers in case it helps someone else

修正:在答案中发布,以防其他人帮助

2 个解决方案

#1

It would help if you post the actual error.

如果你发布实际错误会有所帮助。

However as with named data types, binding REFs is a two-step process. First, call OCIBindByName() or OCIBindByPos(), and then call OCIBindObject().

但是,与命名数据类型一样,绑定REF是一个两步过程。首先,调用OCIBindByName()或OCIBindByPos(),然后调用OCIBindObject()。

REFs are bound using the SQLT_REF datatype. When SQLT_REF is used, then the program variable being bound must be of type OCIRef *.

REF使用SQLT_REF数据类型绑定。使用SQLT_REF时,绑定的程序变量必须是OCIRef *类型。

With inheritance and REF substitutability, you can bind a REF value to a subtype instance where a REF to the supertype is expected.

通过继承和REF可替代性,您可以将REF值绑定到期望REF到超类型的子类型实例。

#2

Changed:

char * query =  "DECLARE \ 
 EID NUMBER; \ 
 RCURSOR CORP.EMPASSIST.cursor_type; \ 
 BEGIN \ 
   EID:= NULL; \ 
   RCURSOR := NULL; \ 
   EMPASSIST.GetEmployee( EID=> EID, RCURSOR => RCURSOR ); \ 
   :RCURSOR := RCURSOR; --<-- Cursor \ 
 END;";

int eid = /* whatever value */;
char * query_template = "CALL EMPASSIST.GetEmployee(%d, :RCURSOR)";
char query[1023] =  {'\0'};
snprintf(query, sizeof(query) - 1, query_template, eid);

Changed the rest to:

将其余部分改为:

OCIError * db_error;   
OCIStmt * statement;   
OCIEnv * environment;   
OCISvcCtx * service;   

OCIBind * cursor_bind;   
OCIStmt * cursor_stm;   

retval += OCIHandleAlloc(environment, (void **) &statement, OCI_HTYPE_STMT, 0, NULL);
retval += OCIStmtPrepare(statement, db_error, (OraText *) query, strlen(query), OCI_NTV_SYNTAX, OCI_DEFAULT);   
retval += OCIHandleAlloc(environment, (void **) &cursor_stm, OCI_HTYPE_STMT, 0, NULL);   
retval += OCIBindByName(statement, &cursorbind, db_error, (OraText *) ":RCURSOR", strlen(":RCURSOR"), &cursor, 0, SQLT_RSET, 0, 0, 0, 0, 0, OCI_DEFAULT);   

retval += OCIStmtExecute(service, statement, db_error, 1, 0, NULL, NULL, OCI_COMMIT_ON_SUCCESS);

#1