Re: Host Variable for VARCHAR2

From: Thomas Kyte <>
Date: Thu, 27 May 1999 12:21:11 GMT
Message-ID: <>

A copy of this was sent to (if that email address didn't require changing) On Wed, 26 May 1999 22:21:08 GMT, you wrote:

>What's the Host variable type in Pro*C that I can use for the ORACLE
>VARCHAR2(Y) internal data type, where Y is the number of characters.
>I've tried char array and it didn't work.
>Put another way, I have an Oracle table with a VARCHAR2 field that I
>swant to be able to query with an
>statement embedded in my C code; what's the proper type for the :dn
>--== Sent via ==--
>---Share what you know. Learn what you don't.---

Here are three options:

static void process()
exec sql begin declare section;
varchar vc_deptno[40];
char ch_deptno[40];
char nts_deptno[40];
exec sql var nts_deptno is string(40);
exec sql end declare section;

    exec sql select dummy

               into vc_deptno
               from dual;

    printf( "'%.*s'\n", vc_deptno.len, vc_deptno.arr );

    exec sql select dummy

               into ch_deptno
               from dual;

    printf( "'%s'\n", ch_deptno );

    exec sql select dummy

               into nts_deptno
               from dual;

    printf( "'%s'\n", nts_deptno );

The vc_deptno is an Oracle varchar. Its really a C STRUCT that has a .len and .arr field. When you select into it, we will set the .len field to the length of the selected item and put the bytes into the .arr component. *IT IS NOT* null terminated (not a C string) hence the use of the .len in the printf to let printf know when to stop printing.

The second shows the select going into a straight C char type. When we run this program, the output is:

'X                                      '

Notice how the second printf is blank padded out to the full length of ch_deptno. This is the default behaviour of strings (blank padded). Also note however that the string *is* null terminated this time.

The last example with nts_deptno (nts = null terminated C string) shows how to use the exec sql var ... directive to get a 'nice' C string back.

See for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
Oracle Service Industries
Reston, VA USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Thu May 27 1999 - 14:21:11 CEST

Original text of this message