Getting data from long to Varchar2 1

[pierreo]

I really don't know how can I retrieve first 2,000 characters using select command from a long column into a VARCHAR2 variable.

Thanks in advance, Pierre

 

[SantaMufasa]

Pierre,

Following is a function ("GETLONG") that does what you want:

***********************************************************************************
create or replace function GetLong
	(RowID_of_Long in rowid) 
	return varchar
is
	Long_to_Varchar		varchar(32767);
begin
	select Y into Long_to_Varchar
		from temp_long
		where rowid = rowid_of_long;
	return Long_to_Varchar;
exception
	when others then
		return 'Error: cannot return contents of LONG for RowID: '||rowid_of_long;
end;
/

Function created.

col a heading "Length|of|Long" format 99,999
col b heading "First 2000 characters of LONG column" format a60
select length(getLong(rowid)) a, substr(getLong(rowid),1,2000)b from temp_long;

 Length
     of
   Long First 2000 characters of LONG column
------- ------------------------------------------------------------
      5 Hello
      7 This is
  3,996 This string is 4000 characters long. This string is 4000 cha
        racters long. This string is 4000 characters long. This stri
        ng is 4000 characters long. This string is 4000 characters l
        ong. This string is 4000 characters long. This string is 400
        0 characters long. This string is 4000 characters long. This
         string is 4000 characters long. This string is 4000 charact
        ers long. This string is 4000 characters long. This string i
        s 4000 characters long. This string is 4000 characters long.
         This string is 4000 characters long. This string is 4000 ch
        aracters long. This string is 4000 characters long. This str
        ing is 4000 characters long. This string is 4000 characters
        long. This string is 4000 characters long. This string is 40
        00 characters long. This string is 4000 characters long. Thi
        s string is 4000 characters long. This string is 4000 charac
        ters long. This string is 4000 characters long. This string
        is 4000 characters long. This string is 4000 characters long
        . This string is 4000 characters long. This string is 4000 c
        haracters long. This string is 4000 characters long. This st
        ring is 4000 characters long. This string is 4000 characters
         long. This string is 4000 characters long. This string is 4
        000 characters long. This string is 4000 characters long. Th
        is string is 4000 characters long. This string is 4000 chara
        cters long. This string is 4000 characters long. This string
         is 4000 characters long. This string is 4000 characters lon
        g. This string is 4000 characters long. This string is 4000
        characters long. This string is 4000 characters long. This s
        tring is 4000 characters long. This string is 4000 character
        s long. This string is 4000 characters long. This string is
        4000 characters long. This string is 4000 characters long. T
        his string is 4000 characters long. This string is 4000 char
        acters long. This string is 4000 characters long. This strin
        g is 4000 characters long. This string is 4000 characters lo
        ng. This string is 4000 characters long. This string is 4000
         characters long. Th


3 rows selected.

To tailor this function to your needs, replace the column name, "Y" to your LONG column, and the table name, "temp_long" to your table (that contains the LONG column), and re-create the function.

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:49 (08Sep04) UTC (aka "GMT" and "Zulu"), 09:49 (08Sep04) Mountain Time)