SQL & PL/SQL

Hi All,

I am sorry if this has been posted earlier. Kindly help me out.

Issue description:

I have created a package like the following:

create or replace PACKAGE TEST_PACK
is

type w040_tableA_xml is record
(
vara tableA.dato%type,
varb tableA.timer%type,
varc tableA.timer_fraver%type,
vard tableA.fraverskode%type,
vare tableA.fritekst%type,
varf varchar2(1),
varg tableA.vno%type,
varh varchar2(1)
);
type t_w040_tableA_xml is table of w040_tableA_xml index by binary_integer;
g_w040_tableA_xml t_w040_tableA_xml;

END TEST_PACK;


Then in created a body like:

CREATE OR REPLACE
PACKAGE BODY TEST_PACK
is

procedure form_xml
is
cursor a_cursor
is
......

v_query_clob xmltype;
begin
...
.
.
for r_cursor in a_cursor
loop
v_count := v_count+1;
g_w040_tableA_xml(v_count).vara := r_cursor.col1;
g_w040_tableA_xml(v_count).varb := r_cursor.col2;
g_w040_tableA_xml(v_count).varc := r_cursor.col3;
g_w040_tableA_xml(v_count).vard := r_cursor.col4;
g_w040_tableA_xml(v_count).vare := r_cursor.col5;
g_w040_tableA_xml(v_count).varf := r_cursor.col6;
g_w040_tableA_xml(v_count).varg := r_cursor.col7;
g_w040_tableA_xml(v_count).varh := r_cursor.col8;
end loop;

then i want to form an xml which needs to be returned to an external system. So i write the following query:

SELECT XMLAgg(XMLforest( vara AS "vara ", varb AS "varb ", varc AS "varc ", vard AS "vard ", vare AS "vare ", varf AS "varf ", varg AS "varg ", varh AS "varh " ) )
INTO v_query_clob
FROM table(cast(g_w040_tableA_xml as t_w040_tableA_xml));



but i get the following error when compiling:

Error(292,44): PL/SQL: ORA-00902: invalid datatype

Please help me with this.
Thanks in advance

Akhil

Edited by: Akhil K on Sep 26, 2010 1:49 AM