Home » Developer & Programmer » Precompilers, OCI & OCCI » How to: bind and define a subselect?? (Oracle Enterprise 11g)
How to: bind and define a subselect?? [message #338262] Mon, 04 August 2008 06:07
Messages: 6
Registered: April 2008
Junior Member

i want to execute with OCI:
cSQLStmt = select routing_obj_id from table where son_region_id = (select region_id FROM table where routing_obj_id = :routingobjid)

This is my pseudo code
/* allocate stmt handle */
OCIHandleAlloc((dvoid *)pEnvH, (dvoid **)&pStmtH, (ub4)OCI_HTYPE_STMT, (size_t)0, (dvoid **)0)))

/* prepare the statement */
OCIStmtPrepare(pStmtH, pErrorH, (text *) cSQLStmt, (ub4)strlen(cSQLStmt), (ub4) OCI_NTV_SYNTAX, (ub4) (OCI_DEFAULT))))

/* Set up define for routing_obj_id */
OCIDefineByPos(pStmtH, &pDefineH, pErrorH, 1, &pParentNode->cRoutingObjID, (sb4)strlen(pParentNode->cRoutingObjID), SQLT_STR, 0, 0, 0, OCI_DEFAULT)))

/* Set up bind for routing_obj_id */
OCIBindByName(pStmtH, &pBindH, pErrorH, (text *) ":routingobjid", (sb4)strlen(":routingobjid"), this->cRoutingObjID, (sb4)(strlen(this->cRoutingObjID) + 1), (ub2)SQLT_STR, (dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)0, (ub4 *)0, (ub4)OCI_DEFAULT)))

OCIStmtExecute(pServiceH, pStmtH, pErrorH, (ub4)1, (ub4)0, 0, 0, (ub4)OCI_DEFAULT)))

I get OCI_NO_DATA, but thats "wrong", i checked the sql in sqlplus with values. The inner select returns always one row.

What am I doing wrong? Do I have to define region_id? I tried DefinebyPosition with position 2, but that failed.

Is it even possible to do a subselect with OCI?


[Updated on: Mon, 04 August 2008 06:09]

Report message to a moderator

Previous Topic: cursor
Next Topic: Missing libraries when compiling OCI 7.3.2 C program with Oracle 8i Client
Goto Forum:

Current Time: Fri Feb 03 17:19:19 CST 2023