Re: Host Variable for VARCHAR2

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

>Hi,
>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
>EXEC SQL SELECT DEPTNO INTO :dn FROM DEPTS WHERE DEPTNO = 1;
>statement embedded in my C code; what's the proper type for the :dn
>variable/structure?
>
>Thanks,
>-Abu-tarif
>
>
>--== Sent via Deja.com http://www.deja.com/ ==--
>---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'
'X                                      '
'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 http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

-- 
Opinions are mine and do not necessarily reflect those of Oracle Corporation