Home » Developer & Programmer » Precompilers, OCI & OCCI » Pro* C code to fetch cursor and loop.  () 1 Vote
Pro* C code to fetch cursor and loop. [message #431613] Wed, 18 November 2009 09:21 Go to next message
varun_p_n
Messages: 4
Registered: November 2009
Location: India
Junior Member

i HAVE THE FOLLOWING CODE WRITTEN IN A *.pc FILE. I am trying to loop to fetch data from cursor. But the code exist after it fetches the first record. Let me know what is it the right way to fetch data from cursor ?

EXEC SQL BEGIN DECLARE SECTION;

char str[64];

EXEC SQL END DECLARE SECTION;

/*cursor declarations*/
EXEC SQL DECLARE Get_SQLText_Cursor CURSOR FOR
SELECT SQL_TEXT FROM v$SQLTEXT_WITH_NEWLINES WHERE address = ( SELECT prev_sql_addr FROM v$session WHERE audsid = userenv('SESSIONID'))ORDER BY piece;

/* open the cursor*/
EXEC SQL OPEN Get_SQLText_Cursor;

NextSelect = TRUE;
NumSelects = 0;


while(NextSelect)
{
EXEC SQL WHENEVER NOT FOUND GOTO NOTFND;

EXEC SQL FETCH Get_SQLText_Cursor
INTO :str;

strcat (sqlText,str);

NOTFND: NextSelect=FALSE;
}

EXEC SQL CLOSE Get_SQLText_Cursor;

if ( sqlca.sqlcode != ORA_OK )
{
return FALSE;
}


Re: Pro* C code to fetch cursor and loop. [message #431616 is a reply to message #431613] Wed, 18 November 2009 09:57 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Are you sure you are allowed to query the views you query?
2/ Check return code after open and fetch or branch somewhere when there is an sql error.
3/ Study the examples in $ORACLE_HOME/precomp/demo/proc directory

Regards
Michel

[Updated on: Wed, 18 November 2009 09:58]

Report message to a moderator

Re: Pro* C code to fetch cursor and loop. [message #431628 is a reply to message #431616] Wed, 18 November 2009 10:38 Go to previous messageGo to next message
varun_p_n
Messages: 4
Registered: November 2009
Location: India
Junior Member

Hi Michel

I granted permission to querry the view.

I am sucessfully able to fetch the records. problem here is i wan't get out of the while loop after the last record is fetched. Though i worked out to empty the variable after the record is fetched and then again fetch the record until the record fetched is empty.

Wanted to know a better way to achive this using Pro C standard code.



Regards,
Varun.
Re: Pro* C code to fetch cursor and loop. [message #431629 is a reply to message #431628] Wed, 18 November 2009 10:59 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I can't see what you see, you didn't post the whole code and you didn't post the output.
The only help I can give you is to repeat: "do what I said and try again".

Regards
Michel
Re: Pro* C code to fetch cursor and loop. [message #431630 is a reply to message #431629] Wed, 18 November 2009 11:17 Go to previous messageGo to next message
varun_p_n
Messages: 4
Registered: November 2009
Location: India
Junior Member

bool Function(char * sqlText)
{
EXEC SQL BEGIN DECLARE SECTION;

char str[64];

EXEC SQL END DECLARE SECTION;

/*cursor declarations*/
EXEC SQL DECLARE Get_SQLText_Cursor CURSOR FOR
SELECT SQL_TEXT FROM v$SQLTEXT_WITH_NEWLINES WHERE address = ( SELECT prev_sql_addr FROM v$session WHERE audsid = userenv('SESSIONID'))ORDER BY piece;

/* open the cursor*/
EXEC SQL OPEN Get_SQLText_Cursor;

if (sqlca.sqlcode != ORA_OK)
{
return FALSE;
}
while(1)
{
EXEC SQL WHENEVER NOT FOUND GOTO NOTFND;

EXEC SQL FETCH Get_SQLText_Cursor
INTO :str;

strcat (sqlText,str);

NOTFND: break;
}

EXEC SQL CLOSE Get_SQLText_Cursor;

if ( sqlca.sqlcode != ORA_OK )
{
return FALSE;
}

return TRUE;
}


In the above code the while loop does not work properly. Exists the loop in the first iteration.
the code EXEC SQL WHENEVER NOT FOUND GOTO NOTFND;
doesnot work. which i thought is a standard Pro* C code to exit the loop after the data is fetched from cursor.


[Updated on: Wed, 18 November 2009 11:18]

Report message to a moderator

Re: Pro* C code to fetch cursor and loop. [message #431634 is a reply to message #431630] Wed, 18 November 2009 11:27 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
In the above code the while loop does not work properly. Exists the loop in the first iteration.

Which is obvious as the loop ends with "break".
(By the way this is not the same code than the previous one.)

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code and use code tags.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Pro* C code to fetch cursor and loop. [message #431706 is a reply to message #431634] Thu, 19 November 2009 03:55 Go to previous message
varun_p_n
Messages: 4
Registered: November 2009
Location: India
Junior Member

I have started using the forums recently. This is my first post in the oracle forum. Will make sure to read the oracle forum guidelines and then post it accordingly. Any way i have worked around this issue, got it solved.

Thanks Michel for your replies.


Regards,
Varun.
Previous Topic: PLX,PLL to c++ Compile
Next Topic: Visual C++ 8.0 OCCI ORACLE - проблема с линкер
Goto Forum:
  


Current Time: Sun Sep 25 06:07:33 CDT 2016

Total time taken to generate the page: 0.07331 seconds