Home » Developer & Programmer » Precompilers, OCI & OCCI » How to use Cursor with dynamic SQL in ProC?
How to use Cursor with dynamic SQL in ProC? [message #120877] Tue, 24 May 2005 07:53 Go to next message
tpstps
Messages: 1
Registered: May 2005
Location: Pakistan
Junior Member

I am using dynamic Pl/SQL with ProC and having problems with cursor. I've to execute stored procedure dynamically and get the result of select list into the cursor. The EXECUTE command is working fine but the FETCH gets failed with error "ORA-01002: fetch out of sequence". I've read that cursor variable can not be used with dynamic SQL.
Can anybody help me out and suggest the alternate solution.

Stored Procedure:
------------------
ROCEDURE open_mod_cur (
curs IN OUT cur_type,
module_id IN varchar2)
IS
BEGIN
OPEN lCurType FOR SELECT * FROM tblmodulerunninginfo
ORDER BY modulename ASC;
END open_mod_cur;

ProC problem.pc:
----------------
EXEC SQL BEGIN DECLARE SECTION;
char inModuleId[100];
char spName[1024];
SQL_CURSOR sqlCursor;
char tmpChar[2048];
EXEC SQL END DECLARE SECTION;

strcpy(inModuleId, "'00000001'");
sprintf(spName, "BEGIN demo_cur_pkg.open_mod_cur(:v1, :v2); END; ");

EXEC SQL PREPARE SPSTMT FROM :spName;
EXEC SQL EXECUTE SPSTMT USING :sqlCursor, :inModuleId;
EXEC SQL WHENEVER NOT FOUND DO break;
for (;{
EXEC SQL FETCH :sqlCursor INTO :tmpChar; /* getting above error */
printf("\ntuple [%s] ", tmpChar);
}
EXEC SQL CLOSE :sqlCursor;

Thanks in anticipation.

tps
Re: How to use Cursor with dynamic SQL in ProC? [message #120928 is a reply to message #120877] Tue, 24 May 2005 15:54 Go to previous messageGo to next message
Michael Hartley
Messages: 110
Registered: December 2004
Location: West Yorkshire, United Ki...
Senior Member

Hi,

this doesn't answer your question, however as a DBA with loads of years experience, I would guide you in another direction.

Firstly, never create dynamic PL/SQL, it's just such a disaster for database scaleabiliy for loads of reasons. Always create stored procedures/function/packages and call the stored procedures from your application.

Secondly, if what you are trying to attempt is "SELECT * FROM tblmodulerunninginfo", then take a look at sample12.pc in $ORACLE_HOME/precomp/demo/proc/sample12.pc

Sample12 defines an array of MAX_SELECT_ITEMS columns that contain the result set, however the select statement only requests 4 columns.

You'll have to dig into the Oracle documentation for the upper limit on select-list items, I can't remember if it's 255 anymore.

Using the demonstrated technique you can derive a mechanism within your application to deal with dynamic SQL.

I should point out that dynamic sql is also a problem because it requires additional work from the application, plus if you know the number and type of the returned columns you can save a network round trip. May sound like a small saving but put this small saving in an application server with many users and you soon have a massive saving. Or loose the network round trip on a slow WAN and you'll have complaining users on day 1.

Kind regards,
Michael Hartley, http://www.openfieldsolutions.co.uk
Re: How to use Cursor with dynamic SQL in ProC? [message #222657 is a reply to message #120877] Mon, 05 March 2007 18:52 Go to previous messageGo to next message
LAScorpion
Messages: 8
Registered: February 2007
Location: LA
Junior Member
Hi there,
Have you solved your problem.
I met the same problem, and spent the whole day.

for the pro*c dynamic cursor.

Joseph
Re: How to use Cursor with dynamic SQL in ProC? [message #222658 is a reply to message #222657] Mon, 05 March 2007 19:02 Go to previous messageGo to next message
LAScorpion
Messages: 8
Registered: February 2007
Location: LA
Junior Member
Kind of Dynamic SQL Method 4.
Using the DESCRIBE BIND VARIABLES.
or DESCRIBE SELECT LIST. etc.

--
Joseph
Re: How to use Cursor with dynamic SQL in ProC? [message #449863 is a reply to message #120877] Thu, 01 April 2010 12:33 Go to previous message
ncron77
Messages: 1
Registered: April 2010
Location: poland
Junior Member
Stored Procedure:
------------------
ROCEDURE open_mod_cur (
curs IN OUT cur_type,
module_id IN varchar2)
IS
BEGIN
OPEN lCurType FOR SELECT * FROM tblmodulerunninginfo
ORDER BY modulename ASC;
END open_mod_cur;

ProC problem.pc:
----------------
EXEC SQL BEGIN DECLARE SECTION;
char inModuleId[100];
char spName[1024];
SQL_CURSOR sqlCursor;
char tmpChar[2048];
EXEC SQL END DECLARE SECTION;

strcpy(inModuleId, "'00000001'");
sprintf(spName, "BEGIN demo_cur_pkg.open_mod_cur(:v1, :v2); END; ");

EXEC SQL ALLOCATE :sqlCursor;

EXEC SQL EXECUTE
BEGIN

execute immediate :spName using in out :sqlCursor, in inModuleId;

END;
END-EXEC;


EXEC SQL WHENEVER NOT FOUND DO break;
for ( ; ; ) {
EXEC SQL FETCH :sqlCursor INTO :tmpChar; /* getting above error */
printf("\ntuple [%s] ", tmpChar);
}
EXEC SQL CLOSE :sqlCursor;
Previous Topic: Proc compilation
Next Topic: Link error for ProC files (merged)
Goto Forum:
  


Current Time: Thu Mar 28 11:00:46 CDT 2024