Problem in using CAST and TABLE function

[ram_jetwani]

Hi ,

I am having problem in using CAST and TABLE function on Collections.
Following is the synopsis of my package and its body.
I am using ORACLE 9.0.1.0.1.

/* PACKAGE LEVEL DECLARARIONS */
TYPE t_docseq_nbr IS TABLE OF t_document_uhh.doc_vpk_docseq_nbr%TYPE INDEX BY BINARY_INTEGER;

v_docseq_nbr t_docseq_nbr;
v_counter number(15) := 0;

The data type of t_document_uhh.doc_vpk_docseq_nbr is VARCHAR2.

/* PACKAGE BODY CODE */

begin
select doc_vpk_docseq_nbr into v_docseq_nbr from t_document_uhh;

select count(column_value) into v_counter from TABLE (CAST(v_docseq_nbr as t_docseq_nbr));

end;

It gives following error in compilation.

PL/SQL: ORA-00902: invalid datatype

[ram_jetwani]

Hi ,

I made a mistake last time in explaining my problem.
I am bulk collecting the data and then I use FORALL statement for updation. Instead of looping v_docseq_nbr.count , I want to use it the following way to get the count and then loop on that variable.
But it is giving error of INVALID DATATYPE.


/* PACKAGE LEVEL DECLARARIONS */
TYPE t_docseq_nbr IS TABLE OF t_document_uhh.doc_vpk_docseq_nbr%TYPE INDEX BY BINARY_INTEGER;

v_docseq_nbr t_docseq_nbr;
v_counter number(15) := 0;

The data type of t_document_uhh.doc_vpk_docseq_nbr is VARCHAR2.

/* PACKAGE BODY CODE */

begin
select
doc_vpk_docseq_nbr
bulk collect into
v_docseq_nbr
from
t_document_uhh;

select count(column_value) into v_counter from TABLE (CAST(v_docseq_nbr as t_docseq_nbr));

end;

It gives following error in compilation.

PL/SQL: ORA-00902: invalid datatype

Awaiting for a reply.