Home » Developer & Programmer » Precompilers, OCI & OCCI » Issue with fetch in Pro*C (Oracle 10.1)
Issue with fetch in Pro*C [message #397520] Sat, 11 April 2009 14:17 Go to next message
oracle123
Messages: 56
Registered: March 2009
Member
Hi all,

I have a issue with FETCH operation using CURSORS in Pro*C.
Consider the following table structure:

desc emp

ename varchar2(10);
dept  varchar2(5);


Data in the emp table is as follows:

ename          dept
-------        --------
scott          sales
john           sales
matt           inven
rodd           sales


I have the following code written in Pro*C
        int cmp;

        EXEC SQL BEGIN DECLARE SECTION;
        char emp_name[10];
        EXEC SQL END DECLARE SECTION;

        EXEC SQL DECLARE cur_emp_name CURSOR FOR
                 SELECT ENAME
                   FROM EMP
                  WHERE DEPT = 'sales'
               ORDER BY ENAME;

        EXEC SQL OPEN cur_emp_name;

        EXEC SQL WHENEVER NOT FOUND DO break;

        for(;;)
        {
                EXEC SQL FETCH cur_emp_name INTO :emp_name ;
                
                cmp = strcmp(emp_name, 'scott');
                
                /* Strings are equal */
                if (cmp == 0)
                   printf("EQUAL \n");
                else
                   printf("NOT EQUAL \n");
                
        }

        EXEC SQL CLOSE cur_ftr_num;


Output for the above code is:
NOT EQUAL
NOT EQUAL
NOT EQUAL

Whereas I expect it to print EQUAL in the case of ename='scott'

When I printed 'emp_name' to output for each record, this is the output (Note- 'X' represents a whitespace in the following output):
'scottXXXXX'
'johnXXXXXX'
'roddXXXXXX'

Is this default behavior of Pro*C to append whitespaces for the rest of string array?

Please suggest me how can we eliminate those whitespaces?

Thanks,
Scott.
Re: Issue with fetch in Pro*C [message #397521 is a reply to message #397520] Sat, 11 April 2009 14:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't use char which a fixed length space padded datatype, but VARCHAR datatype which is a record with a char[] and a length.
Details in Pro*C manual.

Regards
Michel
Re: Issue with fetch in Pro*C [message #397522 is a reply to message #397520] Sat, 11 April 2009 17:01 Go to previous messageGo to next message
oracle123
Messages: 56
Registered: March 2009
Member
Michel,

Thanks for your prompt reply.

But if I declare "emp_name" as VARCHAR datatype, I am getting error at this line
cmp = strcmp(emp_name, 'scott');


As strcmp accepts CHAR datatype opposed to VARCHAR datatype.

Please advice me.

Thanks,
Scott.

Re: Issue with fetch in Pro*C [message #397531 is a reply to message #397522] Sat, 11 April 2009 23:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
VARCHAR datatype which is a record with a char[] and a length.
Details in Pro*C manual.

Please read the manual.

Regards
Michel
Re: Issue with fetch in Pro*C [message #397558 is a reply to message #397520] Sun, 12 April 2009 11:59 Go to previous messageGo to next message
oracle123
Messages: 56
Registered: March 2009
Member
Michel,

I had gone through this manual -> http://download.oracle.com/docs/cd/B10501_01/appdev.920/a97269/pc_04dat.htm#23303

As per my understanding from that, I should probably use emp_name.arr instead of emp_name like this:

cmp = strcmp(emp_name.arr, 'scott');


I could not test it as I do not have access to the compiler today being holiday.

I am a newbie to this Pro*C world. Please do let me know if I am going in the right direction or not.

Thanks,
Scott.

[Updated on: Sun, 12 April 2009 12:04]

Report message to a moderator

Re: Issue with fetch in Pro*C [message #397563 is a reply to message #397558] Sun, 12 April 2009 12:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes you have to do that and get the length in len element.

Regards
Michel
Re: Issue with fetch in Pro*C [message #397576 is a reply to message #397520] Sun, 12 April 2009 19:16 Go to previous message
oracle123
Messages: 56
Registered: March 2009
Member
Thanks a lot Michel for guiding me in the right direction.

- Scott.
Previous Topic: Pro*C and PL/SQL question
Next Topic: Errors during Insert operation in Pro*C
Goto Forum:
  


Current Time: Thu Dec 08 22:08:27 CST 2016

Total time taken to generate the page: 0.11180 seconds