Problem: data in a column of datatype 'long'

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

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.

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. Received on Wed Jul 27 1994 - 12:29:09 CEST