Re: ProC/SQL question

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 1996/06/15
Message-ID: <31c34076.16974177_at_dcsun4>#1/1

On Sun, 16 Jun 1996 06:10:36 GMT, ben_at_webrider.be (Ben Van Hool) wrote:

>tkyte_at_us.oracle.com (Thomas J. Kyte) wrote:
>
>>If you are asking how to do this, look at array fetching. You can array fetch
>>25 rows, play around with them in the client, array fetch the next 25.
>
>What about doing this in PL/SQL? A short example would be very
>helpful.
>

If what you are asking is 'how do I simulate an array fetch using pl/sql' then the answer is you can't array fetch with pl/sql. You can however package cursors and use pl/sql tables to return 'slices' of the result set. The following pl/sql and pro*c example show how:

create or replace package afetch
as

	type numArray is table of number index by binary_integer;
	type enameArray is table of  varchar2(10) index by binary_integer;

	procedure start_query( p_ename in varchar2 );

	function get_data( p_empno out numArray, p_ename out enameArray ) 
	return number;

	procedure close_query;

end;
/
show errors

create or replace package body afetch
as

cursor c1( p_ename in varchar2 ) is

        select empno, ename from emp where ename like p_ename;

procedure start_query( p_ename in varchar2 ) is
begin

        open c1( p_ename );
end start_query;

function get_data( p_empno out numArray, p_ename out enameArray ) return number
is

        rows_fetched number default 0;
begin

	for i in 1 .. 25 loop
		fetch c1 into p_empno(i), p_ename(i);
		exit when c1%notfound;
		rows_fetched := i;
	end loop;
	return rows_fetched;

end get_data;

procedure close_query
is
begin

        close c1;
end close_query;

end;
/
show errors

..........................................

static void process()
{
EXEC SQL BEGIN DECLARE SECTION;

	int	empno[25];
	VARCHAR	ename[25][10];
	int	cnt;
EXEC SQL END DECLARE SECTION;
	int	i;

	for( i = 0; i < 25; i++ ) ename[i].len = 10;
	EXEC SQL EXECUTE 
	BEGIN
		afetch.start_query( '%' );
		:cnt := afetch.get_data( :empno, :ename );
	END;
	END-EXEC;

	for( ;; )
	{
		for( i = 0; i < cnt; i++ )
			printf( "%d, %*.*s\n", 
			empno[i], ename[i].len, ename[i].len, ename[i].arr );

		if ( cnt != 25 ) break;

		for( i = 0; i < 25; i++ ) ename[i].len = 10;
		EXEC SQL EXECUTE 
		BEGIN
			:cnt := afetch.get_data( :empno, :ename );
		END;
		END-EXEC;
	}
	EXEC SQL EXECUTE
	BEGIN
		afetch.close_query;
	END;
	END-EXEC;

}

>Ben.
>
>
>
>>>
>>>Bill Bathurst http://freerange.com
>>>Systems Engineer/Oracle DBA/Netrek Clue Freerange Media
>>>
>>>GCM d- s a C++ U*+A++B++++C--H---I+L-0++++S++++U++++V+++X+ P+++ L+ W++ N++
>>>K---- w+ !0 M V PS PE+ Y PGP t+ 5+ X++ R++ tv- b++ DI+++ !D G e+ h+ r z+
>>>
 

>>Thomas Kyte
>>Oracle Government
>>tkyte_at_us.oracle.com
>>
>>http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database
>
>
>>-------------------
 

>>statements and opinions are mine and do not necessarily
>>reflect the opinions of Oracle Corporation
>
>

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database

---

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Sat Jun 15 1996 - 00:00:00 CEST