Home » Developer & Programmer » Precompilers, OCI & OCCI » SQLCURSOR variable and dynamic SQL
SQLCURSOR variable and dynamic SQL [message #93872] Fri, 28 February 2003 08:45 Go to next message
Peter Anson
Messages: 1
Registered: February 2003
Junior Member
I know you can use a cursor variable in the following way :

EXEC SQL ALLOCATE :ecursor;
EXEC SQL EXECUTE
BEGIN
OPEN :ecursor FOR SELECT empno, ename FROM emp;
END;
END-EXEC;
EXEC SQL FETCH :ecursor INTO :numbers, :names;

I also know that you can have a dynamic SQL statement in the following way :

sqlsmt = "SELECT empno, ename FROM emp"
EXEC SQL PREPARE STMT FROM :sqlstmt;
EXEC SQL DECLARE B_CURS CURSOR FOR STMT;
EXEC SQL OPEN B_CURS;
EXEC SQL FETCH B_CURS INTO :numbers, :names;

What I need to do is combined those two - have a SQLCURSOR cursor variable that uses a

dynamic sql statement. In my case building the "sqlstmt" isn't as simple as the above

example- it's a complex procedure (otherwise I would do it the same way as the first example). And I need to be able to do the fetch using the SQLCURSOR variable (so the second method isn't an option) (I thought I'd mention that before anyone suggested I just use the second method. I can give the details of exactly "why" that's the case, but I'm trying to keep my question as simple as possible)

Does anyone know how I can have a SQLCURSOR variable using a dynamically built SQL statement ?

Here is a portions of the pro*fortran code I tried the following but got an error in the precompiler :

sqlsmt = "SELECT empno, ename FROM emp"
EXEC SQL ALLOCATE :ecursor;
EXEC SQL EXECUTE
BEGIN
OPEN :ecursor FOR sqlstmt;
END;
END-EXEC;

- the OPEN statement generated the following error:
PCC-S-0026: Invalid host variable "ecursor"
(ecursor was declared as a SQLCURSOR in the "EXEC SQL BEGIN DECLARE SECTION")

I then tried the following:

sqlsmt = "SELECT empno, ename FROM emp"
EXEC SQL ALLOCATE :ecursor;
SQL_STMT = 'OPEN :ecursor FOR ' // SQL_STMT
EXEC SQL EXECUTE IMMEDIATE :SQL_STMT
EXEC SQL FETCH :ecursor INTO
1 :numbers,
1 :names;

This time I got a different error:
PCC-S-0003: Invalid SQL Identifier
on the FETCH line (and "numbers" and "names" *were* properly declared in the "EXEC SQL BEGIN DECLARE SECTION" section)

I would greatly appreciate any help !
Re: SQLCURSOR variable and dynamic SQL [message #93878 is a reply to message #93872] Sun, 02 March 2003 10:03 Go to previous message
langda
Messages: 5
Registered: March 2003
Junior Member
Ideally you should Use Dynamic SQL Method 4 for this.
but give it a shot for the below procedure ...

Write a Stored Procedure which returns REF cursor for sqlstatment passed. And call this SP passing the sql_cursor

CREATE PACKAGE demo_cur_pkg AS
TYPE cur_type IS REF CURSOR ;
PROCEDURE open_emp_cur (
curs IN OUT cur_type,
dept_num IN NUMBER);
END;

CREATE PACKAGE BODY demo_cur_pkg AS
CREATE PROCEDURE open_emp_cur (
curs IN OUT cur_type,
sqlstmt varchar2 ) IS
BEGIN
OPEN curs FOR sqlstmt;
END;
END

sql_cursor emp_cursor;
char sql_stmt[[20]]="select * from emp";
...
EXEC SQL ALLOCATE :emp_cursor; /* allocate the cursor variable */
...
/* Open the cursor on the server side. */
EXEC SQL EXECUTE
begin
demo_cur_pkg.open_emp_cur(:emp_cursor, :sqlstmt);
end;
END-EXEC;
EXEC SQL WHENEVER NOT FOUND DO break;
for (;;)
{
EXEC SQL FETCH :emp_cursor INTO :emp_name;
printf("%sn", emp_name);
}
Previous Topic: passing c structures to plsql stored procedures
Next Topic: Upper range of fetch
Goto Forum:
  


Current Time: Thu Mar 28 07:37:50 CDT 2024