Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> PL/SQL VARCHAR2 Handles Control Chars How?

PL/SQL VARCHAR2 Handles Control Chars How?

From: Oci-One Kanubi <Richard.L.Hopley_at_cpmx.saic.NoSpam.com>
Date: 1998/03/13
Message-ID: <3509C1C0.3D4@cpmx.saic.NoSpam.com>#1/1

How does PL/SQL handle control and NUL characters embedded in UTL_FILE input? Specifically, the NUL, LF, CR, and BEL (which I have selected as an end-of-grouping delimiter)? My problem is as follows:

"UTL_FILE.GET_LINE ( cv_filehandle, dv_rval );" will return a string of all characters in the file until it encounters a "newline" character, I am told by Urman's ORACLE PL/SQL PROGRAMMING, but he does not give the ASCII code of the "newline". The input file has been written by a C program with an fwrite of a structure. Most fields in the structure are not fully populated (i.e., a variable "char Datum[16]", initialized to 16 NULs, may contain only five meaningful characters, after which has been placed a C newline [ASCII 10]), so the ten trailing NULs will be the first characters input in the subsequent GET_LINE operation. Two fields are text fields of up to 8192 characters in which the user has probably embedded returns (these fields will be processed into as many related-table VARCHAR2[2000] fields as necessary.) Following the meaningful characters in these text fields has been placed a BEL/newline
(ASCII 7/ASCII 10) combination as delimiter, so that my code can go back
to recognizing a simple "newline" as field delimiter.

Specifically:

(1) is the UTL_FILE.GET_LINE "newline" delimiter ASCII 10 or ASCII 13?

(2) if the C programmer finds it useful to delimit his fields by a NL/CR
combination (ASCII 10/ASCII 13), will I need to code to trim the other one out, or will that combination result in two GET_LINE operations? Or...?

(3) will leading NULs (the balance of the previous field), preceding a
set of meaningful printable characters on input, be ignored, or does PL/SQL see them as string delimiters and assume the GET_LINE receiving variable to contain a string of length 0?

(4) will the BEL character simply occupy a position in the GET_LINE
receiving variable, so that I can parse it out by comparison to control constant "cc_BEL CHAR(1) CONSTANT := CHR(7);"?

I am new to Oracle (though not to DBMS design & programming) and am writing import code for an Oracle installation that is not yet up and running, and I have been alloted a tiny time-frame for debugging, so I really want to get my code as clean as possible *before* I get to load & compile it, i.e., I will probably have very little time to write little test routines to learn these answers. It may not be too late for me to influence the choice of delimiter characters to be used by the C programmer, though I have precious little other influence on his choice of formatting, darnit. And the only documentation I have says nearly nothing about whether PL/SQL is blind to control characters or simply treats them as it treats any printable character byte; if the latter than I can parse with full control.

Thanks for any insight anyone can give me on this. Oh, and, uh... don't be surprised to see me back with lots more questions!

-- 
Richard Hopley, concise and to the point, as always.

Note 1: To send me eMail, remove ".NoSpam" from my address
Note 2: Sometimes I just forget to type that smiley-face emoticon.
Note 3: Nothing really matters except Boats, Sex, and Rock'n'Roll.
Received on Fri Mar 13 1998 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US