OraFAQ Forum: Precompilers, OCI & OCCI » How to: bind and define a subselect??

Home » Developer & Programmer » Precompilers, OCI & OCCI » How to: bind and define a subselect?? (Oracle Enterprise 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to: bind and define a subselect?? [message #338262]

Mon, 04 August 2008 06:07

phaeuschen
Messages: 6
Registered: April 2008

Junior Member

Hi,

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?

Regards
Paddy

[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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 20:20:16 CST 2024