HELP!! ORA-00902: invalid datatype

From: TrynTry <TrynTry.1w9gdy_at_no-mx.forums.yourdomain.com.au>
Date: Sun, 2 Oct 2005 06:18:15 +0200
Message-ID: <TrynTry.1w9gdy@no-mx.forums.yourdomain.com.au>

Hi,

I am creating a package in the schema matt with reference to Tables in Schema Scott.
The package and body pasted below. When I create the package body I get error "PL/SQL: ORA-00902: invalid datatype" for the statement "open p_recordset FOR select * from TABLE(CAST(tbl_order as typ_bas));" What am I doing wrong?

CREATE OR REPLACE PACKAGE TEST_PROC_PKG
IS

TYPE cur_type IS REF CURSOR;

PROCEDURE TEST_PROC(p_orno IN VARCHAR2, p_recordset OUT cur_type); END;
/

CREATE OR REPLACE PACKAGE BODY TEST_PROC_PKG IS PROCEDURE TEST_PROC(p_orno IN VARCHAR2, p_recordset OUT cur_type) IS

CURSOR cur_order IS select T$orno, T$pono, T$cpva from scott.orderline
where T$orno in '||p_orno||';
TYPE type_basket IS RECORD (

orno scott.orderline.T$orno%TYPE,
pono scott.orderline.T$pono%TYPE,
cpva scott.orderline.T$cpva%TYPE
	 );

rec_ord type_basket;
TYPE typ_bas is TABLE of type_basket
INDEX BY BINARY_INTEGER;
tbl_order typ_bas;
i NUMBER := 1;
BEGIN OPEN cur_order;
LOOP
FETCH cur_order INTO rec_ord;
EXIT WHEN cur_order%NOTFOUND;

tbl_order(i).cpva := rec_ord.pono + rec_ord.cpva;
tbl_order(i).orno :=  rec_ord.orno;
tbl_order(i).pono := rec_ord.pono;

DBMS_OUTPUT.PUT_LINE(tbl_order(i).cpva);

i := i + 1;

END LOOP;
DBMS_OUTPUT.PUT_LINE(cur_order%ROWCOUNT); CLOSE cur_order;

        open p_recordset FOR select * from TABLE(CAST(tbl_order as typ_bas));

EXCEPTION
WHEN OTHERS THEN

tbl_order(i).cpva := 0;
tbl_order(i).orno :=  ' ';
tbl_order(i).pono := 0;
	open p_recordset FOR select * from TABLE(CAST(tbl_order as typ_bas));

END TEST_PROC;
END;

-- 
TrynTry
------------------------------------------------------------------------
TrynTry's Profile: http://www.highdots.com/forums/m872
View this thread: http://www.highdots.com/forums/t3003472

Received on Sat Oct 01 2005 - 23:18:15 CDT