Extract function error?

I am making a script that will strip the data out of an xmltype.
Below is my code:




/*** Sample Function Call: select * from TABLE(CAST(convert_xml(XML)) AS out_table)); *****/


/********Create type of table for information****/

/* CREATE OR REPLACE TYPE table_row AS OBJECT
(

claim_no CHAR(10),
cert_no CHAR(9),
rec_code CHAR(2),
seq_no NUMBER(2),
------------------------------
from_date DATE,
to_date DATE,
service_code CHAR(2),
service_place CHAR(2),
ghi_service_place CHAR(1),
ghi_service_code CHAR(2),
procedure_code CHAR(8),
procedure_mod CHAR(2),
ghi_procedure_code CHAR(5),
ghi_procedure_lpv CHAR(1),
diagnosis_code CHAR(4),
charge NUMBER(9,2),
day_unit NUMBER(3),
epsdt VARCHAR2(3),
em3 VARCHAR2(3),
cob VARCHAR2(3),
insert_date DATE

);

CREATE TYPE out_table AS TABLE OF table_row;
*/
/*******DEFINE FUNCTION***************************/

CREATE OR REPLACE FUNCTION convert_xml(xml IN sys.XMLType) return out_table
PIPELINED IS

out_rec table_row;
i number;
xml_line sys.XMLType;
xml_new sys.XMLType;

BEGIN
i := 1;

DBMS_OUTPUT.PUT_LINE('okay. the function has been called successfully.');
LOOP
xml_new := xml;
--this part takes out the i'th entry
xml_line := xml_new.extract('//Payfields[' || i || ']');

EXIT WHEN xml_line IS NULL;

--then extract each entry from xml_line into record.
--out_rec.claim_no := xml_line.extract('/claim_no/text()').getStringVal();
--out_rec.cert_no := xml_line.extract('/cert_no/text()').getStringVal();
--out_rec.rec_code := xml_line.extract('/rec_code/text()').getStringVal();

--out_rec.seq_no := xml_line.extract('/seq_no/text()').getNumberVal();

out_rec.from_date := TO_DATE(xml_line.extract('/from_date/text()').getStringVal(), 'MM/DD/YY'); --DATE
out_rec.to_date := TO_DATE(xml_line.extract('/to_date/text()').getStringVal(), 'MM/DD/YY'); --DATE
out_rec.service_place := xml_line.extract('/service_place/text()').getStringVal();
out_rec.service_code := xml_line.extract('/service_code/text()').getStringVal();

--out_rec.ghi_service_place := xml_line.extract('/ghi_service_place/text()').getStringVal();
--out_rec.ghi_service_code := xml_line.extract('/ghi_service_code/text()').getStringVal();
out_rec.procedure_code := xml_line.extract('/procedure_code/text()').getStringVal();
out_rec.procedure_mod := xml_line.extract('/procedure_mod/text()').getStringVal();
--out_rec.ghi_procedure_code := xml_line.extract('/ghi_procedure_code/text()').getStringVal();
--out_rec.ghi_procedure_lpv := xml_line.extract('/ghi_procedure_lpv/text()').getStringVal();
out_rec.diagnosis_code := xml_line.extract('/diagnosis_code/text()').getStringVal();

out_rec.charge := xml_line.extract('/charge/text()').getNumberVal();
out_rec.day_unit := xml_line.extract('/day_unit/text()').getNumberVal();

--out_rec.epsdt := xml_line.extract('/epsdt/text()').getStringVal();
--out_rec.em3 := xml_line.extract('/em3/text()').getStringVal();
--out_rec.cob := xml_line.extract('/cob/text()').getStringVal();
--out_rec.insert_date := TO_DATE(xml_line.extract('/insert_date/text()').getStringVal(), 'MM/DD/YY'); --DATE

--now send information into table:
PIPE ROW(out_rec);
i := i + 1;
END LOOP;

return;
END convert_xml;

And here are the declarations I used to make my table types:

CREATE OR REPLACE TYPE table_row AS OBJECT
(

claim_no CHAR(10),
cert_no CHAR(9),
rec_code CHAR(2),
seq_no NUMBER(2),
------------------------------
from_date DATE,
to_date DATE,
service_code CHAR(2),
service_place CHAR(2),
ghi_service_place CHAR(1),
ghi_service_code CHAR(2),
procedure_code CHAR(8),
procedure_mod CHAR(2),
ghi_procedure_code CHAR(5),
ghi_procedure_lpv CHAR(1),
diagnosis_code CHAR(4),
charge NUMBER(9,2),
day_unit NUMBER(3),
epsdt VARCHAR2(3),
em3 VARCHAR2(3),
cob VARCHAR2(3),
insert_date DATE

);

CREATE TYPE out_table AS TABLE OF table_row;

And here is the way I am calling my function:


SELECT *
from TABLE(CAST(
convert_xml(
sys.XMLType.createXML(
'<POLIST>
<Payfields>
<from_date>12/12/04</from_date>
<to_date>12/12/04</to_date>
<service_place>A</service_place>
<service_code>99</service_code>
<procedure_code>AA010</procedure_code>
<procedure_mod>22</procedure_mod>
<diagnosis_code>1</diagnosis_code>
<charge>223.99</charge>
<day_unit>10</day_unit>
</Payfields>
</POLIST>'
)) AS out_table));

The errors I get are:
convert_xml(
*
ERROR at line 3:
ORA-30625: method dispatch on NULL SELF argument is disallowed
ORA-06512: at "CPR_DEV.CONVERT_XML", line 27

Does anybody know where I am going wrong here? Thanks

I got it to work for me ... first, you never constructed the table_row for out_rec. Since you didn't have a constructor, you would need to pre-populate every field in your initialization. That didn't sit well with me, so I added a constructor that just accepts the default (NULL) value. I also constructed the value with every iteration of the loop in the function just to reset it back to the default state:

CREATE OR REPLACE TYPE table_row AS OBJECT
(
claim_no CHAR(10),
cert_no CHAR(9),
rec_code CHAR(2),
seq_no NUMBER(2),
------------------------------
from_date DATE,
to_date DATE,
service_code CHAR(2),
service_place CHAR(2),
ghi_service_place CHAR(1),
ghi_service_code CHAR(2),
procedure_code CHAR(8),
procedure_mod CHAR(2),
ghi_procedure_code CHAR(5),
ghi_procedure_lpv CHAR(1),
diagnosis_code CHAR(4),
charge NUMBER(9,2),
day_unit NUMBER(3),
epsdt VARCHAR2(3),
em3 VARCHAR2(3),
cob VARCHAR2(3),
insert_date DATE,
constructor function table_row return self as result
);
/
show errors;

create or replace type body table_row as
constructor function table_row return self as result
is
begin
return;
end;
end;
/
show errors;

Now, in the function ... when you extract //Payfields[1], you get <Payfields>...</Payfields> ... so any future extractions have to start with "/Payfields". Also, you never check for NULL values, so your code got that error when it failed to extract the field and tried to call "getStringValue()" on the NULL object. You should probably extract into an XMLTYPE variable, check for NULL, and only set the field if not NULL. Here is my version of the function with all the parameters turned back on.

CREATE FUNCTION convert_xml(xml IN XMLType)
return out_table PIPELINED
IS
out_rec table_row;
i number := 1;
xml_line XMLType;
xml_text xmltype;
BEGIN
LOOP
out_rec := table_row();
--this part takes out the i'th entry
xml_line := xml.extract('//Payfields[' || i || ']');
EXIT WHEN xml_line IS NULL;
--then extract each entry from xml_line into record.
xml_text := xml_line.extract('/Payfields/claim_no/text()');
if xml_text is not null then
out_rec.claim_no := xml_text.getStringVal();
end if;
xml_text := xml_line.extract('/Payfields/cert_no/text()');
if xml_text is not null then
out_rec.cert_no := xml_text.getStringVal();
end if;
xml_text := xml_line.extract('/Payfields/rec_code/text()');
if xml_text is not null then
out_rec.rec_code := xml_text.getStringVal();
end if;
xml_text := xml_line.extract('/Payfields/seq_no/text()');
if xml_text is not null then
out_rec.seq_no := xml_text.getNumberVal();
end if;
xml_text := xml_line.extract('/Payfields/from_date/text()');
if xml_text is not null then
out_rec.from_date := TO_DATE(xml_text.getStringVal(), 'MM/DD/YY');
end if;
xml_text := xml_line.extract('/Payfields/to_date/text()');
if xml_text is not null then
out_rec.to_date := TO_DATE(xml_text.getStringVal(),'MM/DD/YY');
end if;
xml_text := xml_line.extract('/Payfields/service_place/text()');
if xml_text is not null then
out_rec.service_place := xml_text.getStringVal();
end if;
xml_text := xml_line.extract('/Payfields/service_code/text()');
if xml_text is not null then
out_rec.service_code := xml_text.getStringVal();
end if;
xml_text := xml_line.extract('/Payfields/ghi_service_place/text()');
if xml_text is not null then
out_rec.ghi_service_place := xml_text.getStringVal();
end if;
xml_text := xml_line.extract('/Payfields/ghi_service_code/text()');
if xml_text is not null then
out_rec.ghi_service_code := xml_text.getStringVal();
end if;
xml_text := xml_line.extract('/Payfields/procedure_code/text()');
if xml_text is not null then
out_rec.procedure_code := xml_text.getStringVal();
end if;
xml_text := xml_line.extract('/Payfields/procedure_mod/text()');
if xml_text is not null then
out_rec.procedure_mod := xml_text.getStringVal();
end if;
xml_text := xml_line.extract('/Payfields/ghi_procedure_code/text()');
if xml_text is not null then
out_rec.ghi_procedure_code := xml_text.getStringVal();
end if;
xml_text := xml_line.extract('/Payfields/ghi_procedure_lpv/text()');
if xml_text is not null then
out_rec.ghi_procedure_lpv := xml_text.getStringVal();
end if;
xml_text := xml_line.extract('/Payfields/diagnosis_code/text()');
if xml_text is not null then
out_rec.diagnosis_code := xml_text.getStringVal();
end if;
xml_text := xml_line.extract('/Payfields/charge/text()');
if xml_text is not null then
out_rec.charge := xml_text.getNumberVal();
end if;
xml_text := xml_line.extract('/Payfields/day_unit/text()');
if xml_text is not null then
out_rec.day_unit := xml_text.getNumberVal();
end if;
xml_text := xml_line.extract('/Payfields/epsdt/text()');
if xml_text is not null then
out_rec.epsdt := xml_text.getStringVal();
end if;
xml_text := xml_line.extract('/Payfields/em3/text()');
if xml_text is not null then
out_rec.em3 := xml_text.getStringVal();
end if;
xml_text := xml_line.extract('/Payfields/cob/text()');
if xml_text is not null then
out_rec.cob := xml_text.getStringVal();
end if;
xml_text := xml_line.extract('/Payfields/insert_date/text()');
if xml_text is not null then
out_rec.insert_date := TO_DATE(xml_text.getStringVal(),'MM/DD/YY');
end if;
pipe row(out_rec);
--now send information into table:
i := i + 1;
END LOOP;
return;
END convert_xml;
/
show errors;

Finally, in the SQL statement itself, not sure why you want to cast to an OUT_TABLE, it already is one, so it doesn't do anything that I can tell, so I took it out and the whole thing works:

SELECT * from TABLE(
convert_xml(
XMLType.createXML(
'<POLIST>
<Payfields>
<from_date>12/12/04</from_date>
<to_date>12/12/04</to_date>
<service_place>A</service_place>
<service_code>99</service_code>
<procedure_code>AA010</procedure_code>
<procedure_mod>22</procedure_mod>
<diagnosis_code>1</diagnosis_code>
<charge>223.99</charge>
<day_unit>10</day_unit>
</Payfields>
</POLIST>'
)));

Thanks a lot! You saved me. This thing was driving me crazy.