OPEN Cursor Error in PB Code

[stacyp]

I am trying to use an Oracle cursor in my PB code and it is returning an "Invalid SQL Statement" error after I run the OPEN cursor_name line of code.

I am creating the SQL statement as a string variable (strSQL). Then I have the following:
DECLARE prem_cursor CURSOR FOR strSQL;

OPEN prem_cursor;

When I look at the SQLDBCode after the declare it is zero. When I look at the SQLDBCode after the open it is ORA-0900 - "Invalid SQL Statement". I copied the value of the strSQL variable and ran it against the same database and it returned 19 rows.

Is there an issue with using a variable to create a cursor? I cannot see any other issues????

Thanks!
Stacy

 

[royjimcam]

you wouldn't use cursors, they ' re a problem...

Use DATASTORE.

If you need more help, just ask.

 

[mbalent]

You could try:

// Set up select statement using passed parameters
PREPARE sqlsa FROM :sql_param;

DECLARE dyn_cursor DYNAMIC CURSOR FOR sqlsa;

OPEN DYNAMIC dyn_cursor;
if sqlca.sqlcode < 0 then
MessageBox (&quot;DataBase Error!&quot;, sqlca.sqlerrtext)
return sqlca.sqlcode
end if

or

DECLARE cursor_name CURSOR FOR
SELECT col from filename;

IF sqlcode <> 0 THEN
// error declaring cursor
END IF
OPEN cursor_name;
IF sqlcode <> 0 THEN
// error opening cursor
END IF


HOWEVER,

I agree with royjimcam; use a datastore