Problem with defined types in stored procedures

From: Jeffrey C. Dege <jdege_at_jdege.visi.com>
Date: Mon, 18 Oct 1999 23:22:42 GMT
Message-Id: <slrn80nbam.eab.jdege@jdege.visi.com>

I've extracted some store procedures from dba_source, with the intent of making a few changes. Unfortunately, a few of them won't go back in even before I make the changes. In particular, those with a specific user-defined type fail. The shortest example follows.

I've tried a number of variations of declaring this, both from SQL Worksheet and from the Schema Manager, and I'm stymied.

Ideas?

create or replace

FUNCTION       MES.F_GETLISTPKGS (param_station IN VARCHAR2,
	                                 param_mfg_run IN NUMBER,
					 param_run_number IN NUMBER)
	RETURN typCharTable IS
	TYPE typCharTable is TABLE OF VARCHAR2(2000)
				INDEX BY BINARY_INTEGER;
	param_tblInv typCharTable;
	var_finish_run 		NUMBER(4);
	CURSOR c_Inv IS
		SELECT to_char(package_id, '99999999')||'   '||
		       to_char(prod_date, 'MM/DD/RR HH24:MI:SS')||'   '||
		       to_char(ft3, '999,999,999.999')
		       output_string
		FROM   product_inv
		WHERE  mfg_run=param_mfg_run and finish_run=var_finish_run;
	i	NUMBER := 1;
BEGIN
		-- Get station id for station selected
		SELECT TO_NUMBER(station_id)*1000 + param_run_number
		INTO   var_finish_run
		FROM   stations
		WHERE  station = param_station;
		FOR rec_Inv IN c_Inv
		LOOP
			param_tblInv(i) := rec_Inv.output_string;
			i := i + 1;
		END LOOP;
		RETURN param_tblInv;

END; --
Politician, n.:

        An eel in the fundamental mud upon which the superstructure of organized society is reared. When he wriggles, he mistakes the agitation of his tail for the trembling of the edifice. As compared with the statesman, he suffers the disadvantage of being alive.

• Ambrose Bierce, "The Devil's Dictionary"

Received on Mon Oct 18 1999 - 18:22:42 CDT