Re: Problem: data in a column of datatype 'long'

Hi Tom,

     What version of oracle are you on? We are using v7 and I have been
     able to do this in a stored database package. If you fetch the column
     into a PL/SQL long variable and do the substr in PL/SQl and not part
     of the SQL it should work.

     x := substr(fetched_long,10,3);    will work

     So break the update and do all the selects and tests in PL/SQL

     OR

     VARCHAR2 data type can now hold up to 2000 char. Could you convert
     the column to a varchar2(2000)? Then your original code would work.

     OR

     On oracle 7.1 you can write your own SQL functions. Write your
     own substr function. example:

     looks like you can't pass a long into a function so it needs a
     extra select.

     create or replace function subcom (row varchar2,f NUMBER,l NUMBER)
     return  varchar2 is
     str long;
     begin
        select long_comment into str from my_table where rowid=row;
        return(substr(str,f,l));
     end;
     /
     select subcom(a.rowid,10,5) from my_table;

     This function could be made better using dynamic PL/SQL in 7.1

Regards
Nigel Noble.

Tom Miheve (dba_at_swdev.si.com) wrote:
: Problem : Accessing values in a long data type column.
 

: Hello out there in Oracle SqlPlus land:
 

: I am : Tom Miheve
: Smiths Industries
: Grand Rapids, Michigan 49509
: (616) 241-8292
: miheve_tom_at_si.com
 

: My envrionment is :
 

: Sun Sparc10 running SunOS 4.1.3
 

: ORACLE7 Server Release 7.0.16.4.0 - Production
: With the procedural and distributed options
: PL/SQL Release 2.0.18.1.0 - Production
 

: I have a problem accessing data in a column of datatype 'long'.
 

: The data in the long column looks like 'Table-name:345'
 

: I need to extract the number '345' in the string (I know at what location
: it exists) and then find data in another table with '345' in the key field.
 

: Here's how I did it when the column in question, cpo_comments, was NOT a
: long data type.
: ---------------------------------------------------------------------
: request is a table
: request_cpo_comments_temp is a table
 

: The objective is to move
: request_cpo_comments_temp(text_value) ===> request(cpo_comments).
 

: The column request(cpo_comments) contains 'xxxxxxxxxxx:345' from a data load.
 

: The '345' is needed to reference request_cpo_comments_temp(string_id).
 

: UPDATE request a
: set a.cpo_comments = (select b.text_value
: from request_cpo_comments_temp b
: where (substr(to_char(b.string_id),1,4) =
: substr(a.cpo_comments,22,4)) and (b.text_total not in (0,1)))
 

: This works.
: ---------------------------------------------------------------------
: However, when the column cpo_comments is a long data type,
: the function 'substr(a.cpo_comments,22,4)' fails.
 

: Does anyone know how to reference data in a long data type using
: SQL*Plus or PL/SQL ?
 

: Any ideas or hints would be appreciated very much.

--
                                  _____________
    Nigel Noble                       churchill        Tel: +44 81 313 5677
    Database Administrator        =============        Fax: +44 81 313 5699
    Churchill Technology Ltd
    15-17 London Rd, Bromley BR1 1DE, UK.    Email: nigel_at_churchill.co.uk