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

From: Ramesh <rkrishna_at_us.oracle.com>
Date: 28 Jul 1994 18:34:59 GMT
Message-ID: <318toj$bpk_at_dcsun4.us.oracle.com>

In article <27JUL94.10290908_at_swdev.si.com> dba_at_swdev.si.com (Tom Miheve) writes:
>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.
>

Since it is assumed that the column cpo_comments is not very long , you can write a PL/SQL routine to fetch the long column into a varchar2 variable.
Something like this

Declare tmp_comments varchar2(1000);

        cursor t1 is select cpo_comments from request; Begin

        fetch t1 into tmp_comments;
        etc. etc 

        Now that the data is available to you in the form of a varchar2

 variable , you can use any function like instr,substr etc and proceed  with your update. However , it is assumed that the maximum length of the  long column is only 1000. you can go upto 32K in PL/SQL (I have used this  and it works)

Hope this helps

Ramesh Krishnamurthy Received on Thu Jul 28 1994 - 20:34:59 CEST