query regarding OCI_SFM_SCROLLABLE

[Anonymous]

Hi Vincent,
I have a query regarding SCROLLABLE CURSOR. I have a Oracle Stored Procedure which is returning SYS_REFCURSOR as OUT parameter.

---

USP_GET_CLUSTER_DETAILS(
START_TIME IN INTEGER,
END_TIME IN INTEGER,
ERROR_CODE OUT INTEGER,
RESULT_SET OUT SYS_REFCURSOR)

---

Now In C application I make code like this

OCI_Statement* st=0;
OCI_Statement* st2=0;
st = OCI_StatementCreate(GlobalData.cn);
st2 = OCI_StatementCreate(GlobalData.cn);
OCI_SetFetchMode(st2, OCI_SFM_SCROLLABLE);  //Case 1 Error

OCI_Prepare(st,"declare rc SYS_REFCURSOR;begin USP_GET_CLUSTER_DETAILS(:startTime,:endTime,:retVal,:rc); end;");

OCI_BindUnsignedInt(st, ":startTime",&startTime);
OCI_BindUnsignedInt(st, ":endTime",&endTime);
OCI_BindShort(st, ":retVal",&retVal);
OCI_BindStatement(st, ":rc", st2);
if(!OCI_Execute(st))
{
   return -1;
}
rs = OCI_GetResultset(st2);
nRows = OCI_FetchLast(rs); //Case 2 Error

---

I have 2 cases:

Case 1) If I comment OCI_SetFetchMode(st2, OCI_SFM_SCROLLABLE); then I get error that "The statement is not scrollable"

Case 2) With OCI_SetFetchMode(st2, OCI_SFM_SCROLLABLE); I get error "Invalid Fetch"

Is there any limitations on making cursor SCROLLABLE or Am I making mistake elsewhere?

I didn't find any example which returns RESULT_SET as SYS_REFCURSOR

Regards
Deepak

[Vincent Rogier]

hi,

CURSORs retrieved from PL/SQL are not scrollable.
They are fetch forward only.
This is a limitation of the Oracle OCI C API, not OCILIB.

Regards,

vincent

[Anonymous]

Hi Vincent,

Thanks for your reply.I found this link yesterday which says the same.

http://docs.oracle.com/cd/E17781_01/appdev.112/e18751/procedures_plsql.htm#TDPNG60035

Thanks for your help again

Regards

[Vincent Rogier]

You're welcome !