Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to (in Pro*C) pass a cursor variable as a pointer between functions

649123Sep 30 2008 — edited Feb 19 2010
I am opening a cursor in a called function that accepts as one argument a pointer to a cursor variable, and a second argument for the sql string defining the cursor select. That works fine, and in that same function can successfully fetch and access the records from the cursor. However, my objective is to fetch the records in another function that calls the first function. I want to pass back to the calling function the pointer to the cursor variable from the first function. In the second (calling) function, is where I want to fetch the records. What I am getting is SQLCODE = -1002 (fetch out of sequence).

Here is the relevent code in the first called function that calls a PL/SQL package procedure to open the cursor, followed by the code in the second calling procedure where I am attempting to fetch the records:

/******Called function code starts here ******/

EXEC SQL INCLUDE SQLCA;

long db_makeCursor(cursorID, SQLstr)
EXEC SQL BEGIN DECLARE SECTION;
sql_cursor cursorID; / a pointer to a cursor variable */
char *SQLstr;
EXEC SQL END DECLARE SECTION;
{
long SQLCODE;
EXEC SQL BEGIN DECLARE SECTION;
sql_cursor dbCursor; /* a cursor variable */
EXEC SQL END DECLARE SECTION;

EXEC SQL WHENEVER SQLERROR DO sql_error();

dbCursor = *cursorID;

EXEC SQL EXECUTE
BEGIN
db_util_ref_cursor_pkg.open_dbNameCursor( :dbCursor, :SQLstr );
END;
END-EXEC;

return;

}

/******Calling function code starts here ******/

EXEC SQL INCLUDE SQLCA;

EXEC SQL BEGIN DECLARE SECTION;
static PROCLOG PROC_Rec;
EXEC SQL END DECLARE SECTION;


long retrieveProcesses( _proclog, sqlForProcLog )
EXEC SQL BEGIN DECLARE SECTION;
PROCLOG _proclog;
char *sqlForProcLog
EXEC SQL END DECLARE SECTION;
{
long rc;
long SQLCODE;

EXEC SQL BEGIN DECLARE SECTION;
sql_cursor dbCursor; /* a cursor variable */
sql_cursor cursorID; / a pointer to a cursor variable */
PROCLOG proclog;
short end_ts_ind;
short proc_name_ind;
short comments_ind;
EXEC SQL END DECLARE SECTION;

cursorID = &dbCursorName; /* assign a value to the pointer */


EXEC SQL ALLOCATE :dbCursor;

rc = dbUtilities_makeCursor( cursorID, sqlForProcLog);
if (rc == TRUE)
{
while (SQLCODE == 0)
{
EXEC SQL FETCH :dbCursorName INTO
:proclog.PROC_ID,
:proclog.START_TS,
:proclog.END_TS:end_ts_ind,
:proclog.PROC_NAME:proc_name_ind,
:proclog.COMMENTS:comments_ind,

if (SQLCODE == 0)
{
printf("PROC_ID: %d; COMMENTS: %s\n",proclog.PROC_ID, proclog.COMMENTS);
}
}
}

} /* end retrieveProcesses */
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 19 2010
Added on Sep 30 2008
1 comment
3,025 views