Xref: alice comp.databases.oracle.tools:25091 Path: alice!news-feed.fnsi.net!newsfeed.wli.net!su-news-hub1.bbnplanet.com!su-news-feed4.bbnplanet.com!news.gtei.net!inet16.us.oracle.com!not-for-mail From: tkyte@us.oracle.com (Thomas Kyte) Newsgroups: comp.databases.oracle.tools Subject: Re: Pro*C, '\0' and LONG columns Date: Wed, 19 May 1999 12:25:35 GMT Organization: Oracle Service Industries Lines: 184 Message-ID: <3744a8fd.4359648@newshost.us.oracle.com> References: Reply-To: tkyte@us.oracle.com Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Trace: inet16.us.oracle.com 927116701 1931 138.1.122.172 (19 May 1999 12:25:01 GMT) X-Complaints-To: usenet@inet16.us.oracle.com NNTP-Posting-Date: 19 May 1999 12:25:01 GMT X-Newsreader: Forte Agent 1.5/32.451 A copy of this was sent to jgr@cix.compulink.co.uk ("John Ratcliffe") (if that email address didn't require changing) On Wed, 19 May 1999 11:46:01 GMT, you wrote: >I would like to have a Pro*C routine that inserts and fetches text in and >out of a LONG column. The text can be longer than the 32k limit on PL/SQL. >In order to interface with other external systems the requirements are >that this text must contain embedded ASCII null characters ('\0' in C). > >Does anyone know of a way of using Pro*C to do this? > >John Ratcliffe > >jgr@cix.compulink.co.uk > > >BACKGROUND: > >Pro*C generates code that uses the C str* functions like strlen(), which >doesn't help. > no, pro*c doesn't insert str* function calls into the code. it packs a structure with pointers and calls internal routines. I don't think i've ever seen pro*c inject str* calls into code. >Pro*C data types are built around the idea of C style strings, so any data >type I tried did not help. I have tried VARCHARS, STRINGS, CHARZ but all varchar is not. a varchar structure is explicitly *not* null terminated. It has a .len field and a .arr field. >expect/use C style strings. Insertion meant that the string was inserted >up to the first null character then space filled up to the specified >length. > >I have tried altering the CHAR_MAP and DBMS pre compiler settings. > >I tried coercing data types for example, VARCHAR -> VARCHAR2, but that >didn't help. It needs more code, and I found that I was getting some >strange side effects, and life is too short. > >I know PL/SQL can do this, so it is not an Oracle restriction. However, I >can't use PL/SQL because I want to handle strings > 32k. Consider the following example: SQL> create table t ( x long ); Table created. SQL> desc t; Name Null? Type ------------------------------- -------- ---- X LONG with the pro*c code: static void process() { exec sql begin declare section; varchar my_string[100000]; exec sql end declare section; int i; #define TEXT "How Now \0 Brown Cow" #define TEXT_LEN 20 memmove( my_string.arr, TEXT, 20 ); my_string.len = TEXT_LEN; exec sql whenever sqlerror do sqlerror_hard(); exec sql insert into t values ( :my_string ); exec sql commit work; memset( my_string.arr, 0, sizeof(my_string.arr) ); my_string.len = 0; exec sql select x into :my_string from t; printf( "my_string.len = %d\n", my_string.len ); for( i = 0; i < my_string.len; i++ ) printf( "--> %3d, %c\n", my_string.arr[i], my_string.arr[i] ); } the output from that is: my_string.len = 20 --> 72, H --> 111, o --> 119, w --> 32, --> 78, N --> 111, o --> 119, w --> 32, --> 0, --> 32, --> 66, B --> 114, r --> 111, o --> 119, w --> 110, n --> 32, --> 67, C --> 111, o --> 119, w --> 0, After I run that, i use the plsql block SQL> declare 2 y long; 3 z varchar2(5000); 4 begin 5 select * into y from t; 6 select dump(y) into z from dual; 7 7 dbms_output.put_line( z ); 8 dbms_output.put_line( length(y) ); 9 9 for i in 1 .. length(y) 10 loop 11 dbms_output.put_line( '--> ' || 12 to_char(ascii(substr(y,i,1)),'999') || 13 ' ' || 14 substr(y,i,1) ); 15 end loop; 16 end; 17 / to verify my work independent of C and get: Typ=1 Len=20: 72,111,119,32,78,111,119,32,0,32,66,114,111,119,110,32,67,111,119,0 20 --> 72 H --> 111 o --> 119 w --> 32 --> 78 N --> 111 o --> 119 w --> 32 --> 0 --> 32 --> 66 B --> 114 r --> 111 o --> 119 w --> 110 n --> 32 --> 67 C --> 111 o --> 119 w --> 0 which shows that both the embedded and trailing \0 was inserted for me. Both 'dump' and a manual dump show that. See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'... Thomas Kyte tkyte@us.oracle.com Oracle Service Industries Reston, VA USA -- Opinions are mine and do not necessarily reflect those of Oracle Corporation