Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: HELP!! ORA-00902: invalid datatype

Re: HELP!! ORA-00902: invalid datatype

From: Vince <vinnyop_at_yahoo.com>
Date: 5 Oct 2005 09:27:44 -0700
Message-ID: <1128529664.481036.8420@g49g2000cwa.googlegroups.com>


Daniel or others,

Is there an upper limit to worry about with the bulk fetch + related processing? Suppose the bulk fetch were 10M rows of about 10k of data each? In a case like that, I would probably still write as a cursor + loop.

 I realize that the answer may be machine dependant as far as SGA or memory is concerned, however it seems like there may be an upper limit to using the bulk methods.

Vince

DA Morgan wrote:
> 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)
Received on Wed Oct 05 2005 - 11:27:44 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US