Re: Fetching Long ( > 32 K ) into PL with Oracle 7.1.5 ?

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 Received on Mon Oct 06 1997 - 00:00:00 CEST