Re: Embedded SQL Problem: Cursor, Fetch

On 1 Nov 2002 20:35:17 -0800, edmondkkmo_at_hotmail.com (Eddie) wrote:

>Hello all,
>
>I am a newbie in using embedded SQL with oracle server. I will really
>appreciate any help from the experts around here.
>
>I am working on a small project that deals with a grading system.
>Every other parts of the project have been done, but
>I am having a problem that is driving me crazy. In the portions of
>code
>below:
>
>EXEC SQL declare grade_cur cursor for
> select last, first, phone, gender
> from student
> where last = :last and first = :first;
>
>EXEC SQL open grade_cur;
>
>EXEC SQL fetch grade_cur into :last, :first, :phone, :gender;
>
>if (sqlca.sqlcode != 0)
> printf("oops!\n");
>
>the word "oops" is printed everytime i run the program. When I checked
>the values of :last, :first, :phone and :gender, they are all nulls.
>I can sucessfully use the other SQL constructs like "select",
>"insert", etc without any problem. How come "cursor" doesn't work?
>I have beening searching for an answer in the newsgroup, but so far I
>couldn't
>find any. I noticed some comments on "fetch" that it won't work if the
>connection to the database is lost. It caused me to check the Oracle
>DBA studio, and I discovered that the session where my programm
>connected to has
>it's status being "inactive". Can this be the problem?
>Thanks for any suggestions.
>
>Eddie
>
>
>How to make the status of a session active in oracle DBA studio

Your session automatically switches to inactive, if your program doesn't do anything.
There are two problems in your progam
1 If you don't find any records, or stop finding records, sqlca.sqlcode will be 1403, 'No data found' 2 Columns/expressions that can be null, need to have an indicator variable attached in the fetch, like this fetch last_grade_cur into :last :ilast, :first :ifirst -- etc.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sat Nov 02 2002 - 00:52:57 CST