Re: Fetching Long ( > 32 K ) into PL with Oracle 7.1.5 ?
Date: 1997/10/06
Message-ID: <343ac9ee.2315910_at_newshost>#1/1
Sorry, you need 7.2 or up to do piecewise fetches of longs in pl/sql.
On 06 Oct 1997 09:55:45 +0200, Marc Fleischeuers <Marc.Fleischeuers_at_kub.nl> wrote:
>noodles_at_aol.com (Noodles) writes:
>
>> The title says it all. Is this possible ? The built-in Long parsing functions
>> are not available in this old version but I want to process raw text data of >
>> 32 k in length. I thought of maybe processing 32 k chunks of data at a time
>> but I'm not sure of the feasibility of that method.
>>
>> Can anyone give me some pointers ?
>
>A PL/SQL VARCHAR2 is 32767 bytes long at most, so if you can handle
>your data in 32k at a time you should be ok there. To actually
>retrieve the data, you'd use a loop like:
>
>
> stmt := 'select ' || datacol || ' from ' || tabname ||
> ' where ' || keycol || ' = ' || keyval;
> dbms_sql.parse(exec_cursor, stmt, dbms_sql.native );
> dbms_sql.define_column_long(exec_cursor, 1);
> rowsprocessed := dbms_sql.execute_and_fetch (exec_cursor, false);
>
> loop
> if (ready) then exit; end if;
> dbms_sql.column_value_long (
> exec_cursor,
> 1,
> chunksiz,
> acculen,
> tempstring,
> templen);
> if (templen < chunksiz) then
> ready := true;
> end if;
> -- do your thing with tempstring here
>
> end loop;
>
> dbms_sql.close_cursor( exec_cursor );
>
> exception
> when others then
> if dbms_sql.is_open(exec_cursor) then
> dbms_sql.close_cursor(exec_cursor);
> end if;
> raise;
>
>(the above is adapted from a Thomas Kyte snippet). This works like it
>should on Oracle 7.3.3, I have no idea whether the crucial functions
>`dbms_sql.define_column_long' and `dbms_sql.column_value_long' are in
>7.1 already. A good enough reason to upgrade?
>
>Marc
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Oct 06 1997 - 00:00:00 CEST