Re: Forms4.5 Query

From: Uniscape Seminar Program <seminar_at_uni-scape.com>
Date: 1996/10/08
Message-ID: <53e2tq$98t_at_news0-alterdial.uu.net>#1/1

"Garry M. Filimonov" <garry_at_fly.triniti.troitsk.ru> wrote:

>> From a trigger i execute a 'SELECT' statement and want to populate
>> the multi-record block with all the resultant records from the 'SELECT'.
>> How do i do this without using Record-Groups?

Do you want to use dynamic SQL ? If you do, then please repost your article stating so. I have assumed you want static SQL.

This procedure will do all the work for you

PROCEDURE Populate_Block IS
BEGIN

	go_block ('EMP');		-- clear the block
	clear_block;
					-- loop through the query
	FOR C in (
		select 	ename
		from 	scott.emp
		)
	LOOP				-- and populate the record
		:EMP.ENAME := C.ename;
		create_record;
	END LOOP;

	first_record;			-- navigate to the first record

END; P.S I write 'em, you debug 'em

This technique will work fine for small to medium data sets returned by the query. For large query results, you should use a package with a cursor and use an ON-QUERY trigger to open the cursor (by calling a package procedure to do the work) then an ON-FETCH trigger to retrieve each row from the query. This way, the rows will only be returned when they are required by the form (My example code retrieves ALL rows from the query) I am assuming that your query returns a small number of rows, so I didnt bother providing example code for this suggestion.

I am not sure why you do not just create a base table block, but I assume you have your reasons.

NOTE:	using a a record group would be faster because it has a fetch 
	size of 100   (ie it sucks 100 records at a time from the database, 
	instead of this example which sucks them over one at a time)


=============================================================
   * Expert Oracle consultants : RDBMS, Designer, Developer *
Scott Hollows - Managing Principal	shollows_at_ricochet.net
Uniscape, Professional Services		+1.415.596.1437

Redwood Shores, California Received on Tue Oct 08 1996 - 00:00:00 CEST