CREATE GLOBAL TEMPORARY TABLE demoapp_usage_statistics
(
	statname    varchar2(30)  not null,
	int_value   int,
	str_value   int,
	description varchar2(255)
	
	,primary key(statname)
)
ON COMMIT PRESERVE ROWS
--ON COMMIT DELETE ROWS

------------------------------------------------------------------------------------------
-- PROCEDURE: get_demoapp_usage_statistics
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE get_demoapp_usage_statistics
(
	a_cursor   OUT SYS_REFCURSOR
)
AS
BEGIN
	-- Create a placeholder for statistics
--	DECLARE LOCAL TEMPORARY TABLE demoapp_usage_statistics
--	(
--		statname    varchar2(30),
--		int_value   int,
--		str_value   int,
--		description varchar2(255)
--		
--		,primary key(statname)
--	);
	delete from demoapp_usage_statistics;

	-- insert the statistics to the placeholder
	insert into demoapp_usage_statistics values('stat 1', 11, null, 'description from ''stat 1''.');
	insert into demoapp_usage_statistics values('stat 2', 22, null, 'description from ''stat 2''.');
	insert into demoapp_usage_statistics values('stat 3', 33, null, 'description from ''stat 3''.');
	insert into demoapp_usage_statistics values('stat 4', 44, null, 'description from ''stat 4''.');
	
	-- Now get the statistics
	OPEN a_cursor FOR 
		select 
			statname,
			int_value,
			str_value,
			description
		from demoapp_usage_statistics
		order by statname;

	delete from demoapp_usage_statistics;
END;
/ 

exit
---------------------------------------------------------
-- test code
---------------------------------------------------------
\call get_demoapp_usage_statistics(?) :(ora_rs)




DROP TABLE demoapp_usage_statistics
