Re: Oracle 7.3, parsing the contents of a LONG column?

Here's an extremely clumsy stored function I use to retrieve from a long column. This is specific to my tables -- I'd pass it a "project_id" in return for the first 2000 characters of the "conditions" column for a "Site Plan Review" meeting. I only return 2000 bytes because that's the limit for a varchar2 in the RDBMS. You could write several of these functions, each to substring out a different piece -- and it should work with longs up to 32767 bytes. It wasn't adequate for my project -- we finally broke down and changed the column's datatype to varchar2. Frankly, longs suck. But maybe it'll help you....

create or replace
FUNCTION "SPR_COND_EXTRACT" (vproject_id IN VARCHAR2) RETURN VARCHAR2
IS
  longvar VARCHAR2(32767);
/* One could use a long datatype above, but varchar2 is bigger in PL/SQL.*/ BEGIN
  SELECT conditions into longvar
    FROM project_reviews
    WHERE project_id = vproject_id AND

          meeting_type = 'Site Plan Review Meeting';   RETURN (substr(longvar, 1, 2000)); -- varchar2 is limited to 2000 in RDBMS END;
/

Then, I can issue a statement like this from the client: SELECT spr_cond_extract('SPR-98-003') from DUAL;

Hope this provides direction -- I worked on this for some time with inadequate success.
-Ian

Phill Edwards wrote:

> I believe the answer to the original question is thay you cannot perform any
> SQL functions on a long column in SQL*PLus or PL/SQL. Thw work around has
> traditionally been to do whatever it is you're trying to do in a Pro*C
> routine because you can then manipulate the long column as a large variable
> containing lots of text.
> Regards,
> -- Phill --
>
> acresr_at_cnwl.igs.net wrote in message <3698a6c8.6740979_at_news.igs.net>...
> >Hi all,
> >
> >In reviewing past Oracle newsgroup messages, it seems those questions
> >people have had with regard to parsing or extracting data/contents of
> >a LONG column in Oracle have gone unanswered. I am wondering if there
> >is anybody here who knows the answer to this?
> >
> >I have been trying via Excel (but would find it beneficial in VB6 as
> >well) to call up the contents of a LONG column as part of a query.
> >
> >Any ideas or pointers would be eXtremely appreciated!
> >
> >Thanks!
> >
> >
> >===> Roy
> >acresr_at_cnwl.igs.net
> >
> >
> >Guy Starbuck <guy.starbuck_at_syllogistics.com> wrote:
> >
> >>I want to use a PL/SQL procedure to process the contents of a single
> >>LONG column. This column will be filled with the contents of a fixed
> >>format but variable length flat file.
> >>
> >>Since you cannot use "substr" or other string parsing functions on a
> >>LONG column, does anyone know of any SQL or PL/SQL functions which can
> >>be used to extract sections of a LONG column?
> >>
> >>Any help would be appreciated.
> >>
> >>Guy Starbuck
> >>guy.starbuck_at_syllogistics.com
> >
Received on Wed Jan 20 1999 - 23:42:01 CET