Help using Table type

From: <sweetman_p_c_at_my-deja.com>
Date: Tue, 17 Oct 2000 21:19:38 GMT
Message-ID: <8sifp1$brk$1@nnrp1.deja.com>

I'm trying to convert the following MSS/Sybase syntax to Oracle. Many of our stored procedures use #tmp tables, and I don't want to litter the schema with many Oracle Temporary Global tables so I am trying to use Table type fields:-
<begin quote>

CREATE TABLE #tmp_control(entity	varchar(30)	unique,
			attribute	varchar(30)	null,
			staff_ref	integer		null,
   			the_type	char(1)		null,
			the_level	integer		null)

INSERT INTO #tmp_control
SELECT Tscontrol.entity,

            Tscontrol.attribute,
            @al_staff_ref as staff_ref,
            'F' as the_type,
            1 as the_level

FROM Tscontrol
WHERE ( Tscontrol.entity >= @entity ) AND

       ( Tscontrol.entity <= @entity_high )
<end quote>

What my Oracle attempt looks like is:-

<begin quote>

DECLARE
TYPE type_rec_temp IS RECORD (

	 attribute	 varchar2(30),
	 staff_ref	 number(6),
	 the_type	 char(1),
	 the_level	 number(6));

TYPE type_table_temp IS TABLE OF type_rec_temp INDEX BY BINARY_INTEGER;

t_tmp_control type_table_temp;
BEGIN INSERT INTO t_tmp_control

	   SELECT Tscontrol.entity,
              Tscontrol.attribute,
              al_staff_ref as staff_ref,
              'F' as the_type,
              1 as the_level
	FROM Tscontrol
	WHERE ( Tscontrol.entity >= as_entity ) AND
	  ( Tscontrol.entity <= as_entity_high );

END;
<end qupte>

I get an error saying thae t_tmp_control needs to be declared, but this is decalred. It is a variable of type type_table_temp. Can someone please put me straight on this?

Paddy

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Oct 17 2000 - 16:19:38 CDT