Re: HELP!! ORA-00902: invalid datatype

TrynTry wrote:
> 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;
The thing you are doing wrong, first and foremost, is using a cursor loop unless you have an older verison of Oracle. Look up examples of bulk collection at http://tahiti.oracle.com or in Morgan's Library at www.psoug.org.

With respect to your original question? I fail to see the point of your TABLE(CAST statements. Why are you trying to do this?

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)