Replacing Carriage Returns in LONG columns 1

[TStriker]

Hi all,

The things I don't know about Oracle would fill a library so I have what I hope is a simple question.

I want to import data from a LONG column in Oracle 8.1.7 database into a Filemaker database. I have set up the ODBC connection and all works well except that the end-of-line stuff from Oracle produces strange characters in Filemaker. I would love to have a simple clean import but Filemaker's limitations will prevent this I think.

I can imagine a workaround where I convert all end-of-line stuff to '<br>'s and then convert the all of the '<br>'s to native Filemaker carriage returns once the import is complete.

So my question is; how can I convert the end-of-line data in a LONG column to '<br>'s?

-Striker

 

[SantaMufasa]

Striker,

There are methods for achieving your objective. The options depend on the answers to these questions:

1) Do you have access to an Oracle 9i database?
2) How many rows are in your LONG table?
3) What is the length (in characters) of your longest LONG?

Also, cannot Filemaker modify incoming data? (i.e., strip the <CR>s from the incoming data?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[TStriker]

Dave,

1) I'm sorry to say that I do not have access to Oracle 9i, only 8i.

2) There are 701 rows today but it grows by a few dozen every year.

3) I guessing here but I would estimate that the longest value is only several hundred characters in length.

4) Filemaker does not have any built-in ability to transform data on the way in. I could possibly replace the unrecgonized eol characters once the data has been imported but I'm having trouble getting FM to recognize the characters.

Thanks!

-Striker

 

[SantaMufasa]

Striker,

Since you do not have access to 9i, and since the longest value is under Oracle's 8i column maximum of 2000 bytes, you have a distinct opportunity to resolve your issue.

I will be glad to post a resolution for you if you will tell me the name of the table and of its LONG column.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[TStriker]

Sure Dave, the table name is PART_COMNT and the column is COMNT_TXT.

Thanks!

-Striker

 

[SantaMufasa]

Striker,

Some code that should do what you want appears below. Here is DESCRIBE of a cut-down version of your PART_COMNT table:

desc PART_COMNT
Name                    Null?    Type
----------------------- -------- ------
ID                               NUMBER
COMNT_TXT                        LONG

Next, let's confirm some data in the PART_COMNT table where row 2 has multiple embedded carriage returns:

select * from PART_COMNT;

ID COMNT_TXT
-- ---------------------------------------------
 1 This is short text in a LONG column.
 2 This is row 2; a <carriage return> is here ->
   This is row 2; a <carriage return> is here ->
   This is row 2; a <carriage return> is here ->

Here is a function that accesses the LONG column for rows matching the ROWID, returning a VARCHAR2 expression (up to 4000 bytes) for each LONG column you request.

REM **********************************************************
REM Author: David Hunt 
REM Date Created: July 18, 2007
REM Purpose: Creates function that returns contents of
REM 		PART_COMNT:COMNT_TXT. (a LONG field)
REM	Warning: if the contents of COMNT_TXT exceed 32767 chrs, then
REM			none of the data from that row's LONG RETURN;
REM                     an error message results. 
REM **********************************************************
create or replace function Get_Comnt_txt
	(RowID_of_Long in rowid) 
	return varchar2
is
	Long_to_Varchar		varchar(32767);
begin
	select comnt_txt into Long_to_Varchar
		from part_comnt
		where rowid = rowid_of_long;
	return long_to_varchar;
exception
	when others then
		return 'Error occurred on rowid: '||rowid_of_long;
end;
/

Function created.
**********************************************************************

Lastly, here is a query that uses the "Get_Comnt_txt" function, replacing the carriage returns (Oracle CHR(10)) with NULL:

select replace(get_Comnt_txt(rowid),chr(10),null)comnt_txt
from part_comnt;

COMNT_TXT
----------------------------------------------------------------
This is short text in a LONG column.
This is row 2; a <carriage return> is here ->This is row 2; a <carriage return> is here ->This is row 2; a <carriage return> is here ->

The line wrapping you see, above, is from Tek-Tips frame width, not from any embedded carriage return.

Let us know if this does what you want.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[TStriker]

Dave,

Sorry for the delay, I got derailed for a day. I'll check this out. Thank you sir!

-Striker