VARCHAR2 field filtering 1

[paoconnell]

I'm an experienced programmer, but am new to Oracle.

I have an Oracle table that I am querying, that contains a VARCHAR2 field (DESCRIPTION) whose text contains newlines (the kind is uncertain, but the client runs on Win2K and the server is Solaris). I need the query to use a function to remove (or convert to a space) the newlines, as they interfere with exporting this field to a comma separated text field.

Part of the query as it stands:

SPOOL C:\TEMP\WPOPERATION.CSV

  Select WONUM ||',' ||
       TO_CHAR(COMPLETIONDATE) ||','||
       '"'|| DESCRIPTION || '"'
  FROM wpoperation;
SPOOL OFF

DESCRIPTION gets truncated at the first newline.

The guy who exported this field before me resorted to an external C programming to do the filtering; he's not in town right now. I have an idea that PL/SQL may have functions that will help do the trick, but can't find a book in the office that gives me the info I need. Is there a good site out there which is a good reference to the PL/SQL language and functions? Alternatively, does anyone have a code snippet that can be run under SQL Plus and does the same kind of filtering? Thanks!


Pat O'Connell
Visualize Whirled Peas

 

[SantaMufasa]

Pat,

As you are aware, end-of-line character sequences, depending upon operating system, involve hex(0D)+hex(0A) [carriage return+line feed] on a Windows environment and just the line feed, hex(0A) on a Unix system. You can easily get rid of either/both with one function in the Oracle world. Using your code as an example:

Select WONUM ||',' ||
       TO_CHAR(COMPLETIONDATE) ||','||
       '"'|| [b]translate([/b]DESCRIPTION[b],'^'||chr(13)||chr(10),'^')[/b] || '"'
  FROM wpoperation;

In the above example, the TRANSLATE function says,:

Look at the value of DESCRIPTION...
if you encounter a '^', then translate it to a '^';
if you encounter a carriage-return, then translate it to a NULL;
if you encounter a line-feed, then translate it to a NULL.

I use the '^' as a dummy character to cause proper translation to NULLs of the characters that I really want translated.

If, instead of NULL, you truly want a blank space to replace either a <cr> or <lf>, then just alter the TRANSLATE..TO string to '^ ' from its current '^'.

Let us know if this satisfactorily resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 01:18 (27Aug04) UTC (aka "GMT" and "Zulu"), 18:18 (26Aug04) Mountain Time)

 

[paoconnell]

So far, so good. However, it seems like converting the CR and LF characters to nulls is the wrong thing to do when exporting as text; the Description is truncated at the first Null found.

I think I'll try again by converting the CRLFs to spaces.


Pat O'Connell
Visualize Whirled Peas

 

[paoconnell]

Mufasa, you've got a star for pointing me in the right direction, and your suggestion worked on the original field once I changed the newlines to blanks instead of nulls. However, the field I was searching was the wrong one. The right field is declared as a Long field (effectively a BLOB full of ASCII text), and can't be searched in the database.

I now have a copy of TOAD on my system, and figured out how to export the Long descriptions as individual documents. Next I use Windows Search to check each document for the text I'm looking for. This works, but is really ugly, and is hard to automate. A better way (either using TOAD or a script) would be appreciated.

Thanks!

Pat O'Connell
Visualize Whirled Peas

 

[SantaMufasa]

Pat,

While visualising Whirled Peas, I visualised a solution for you, but it requires your using an Oracle9 database. If you "ALTER TABLE <tablename> MODIFY <long_col_name> CLOB;", then the LONG column becomes a CLOB (without any penalty or downside) and you can then treat the column as you would a VARCHAR2.

So, is there any way that this application can reside on an Oracle9 database? Is there a good reason not to upgrade to Oracle9?

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:40 (01Sep04) UTC (aka "GMT" and "Zulu"), 12:40 (01Sep04) Mountain Time)

 

[paoconnell]

Belated reply--

The reason I can't move the database to Oracle 9 (or 10) is that we don't have licenses for these versions. Also, the database is normally accessed from a construction management program called Maximo, and I would be changing the design at my peril.

Finally, this project was a one-off, as the Long fields being searched are now also kept as a series of searchable Adobe PDFs, and future searches will be done on the PDFs. It's simple and it works...

Pat O'Connell
Visualize Whirled Peas