Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

VARCHAR2 field filtering 1

Status
Not open for further replies.

paoconnell

Programmer
Jun 11, 2002
41
US
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:
Code:
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
 
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:
Code:
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,:
Code:
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.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA @ 01:18 (27Aug04) UTC (aka "GMT" and "Zulu"), 18:18 (26Aug04) Mountain Time)
 
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
 
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
 
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?

[santa]Mufasa
(aka Dave of Sandy, Utah, USA @ 19:40 (01Sep04) UTC (aka "GMT" and "Zulu"), 12:40 (01Sep04) Mountain Time)
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top