SQL & PL/SQL

Hi, I am using the following SQL:

select l.description
from XMLTABLE(XMLNAMESPACES('PostcodeAnywhere2' as "e"), '/e:InterimResults/e:Results/e:InterimResult' PASSING
XMLTYPE.createxml(ws_postcode_aw.getByPostcodeXML('WR2 6NJ','1', 'XML'))
COLUMNS description varchar2(400) PATH '//e:Description'
) l

which returns data OK.

However, when I incorporate it into a cursor, I get a "no more data to read from socket" error. When I replace the cursor with a simple "SELECT 'dasda' description FROM dual" the code complies OK. Can anyone assist with this? I have no idea what could be causing this. Code is included below:


CREATE OR REPLACE
PACKAGE BODY "GET_PC_DATA" AS

PROCEDURE disp_pc_data IS

v_str clob;

v_post_code varchar2(10) := 'WR2 6NJ';
v_fast_add_ret_xml xmltype;
v_pc_lookup_ret_xml xmltype;

TYPE c_get_addresses_t IS REF CURSOR;
c_get_addresses c_get_addresses_t;

TYPE r_get_addresses_t IS RECORD ( description varchar2(400));
r_get_addresses r_get_addresses_t;

FUNCTION getHouseNumber(p_desc IN varchar2) RETURN varchar2
IS
BEGIN
return substr(p_desc,1,instr(p_desc,v_1_line_add));
END;

BEGIN


OPEN c_get_addresses FOR
SELECT l.description
FROM XMLTABLE(XMLNAMESPACES('PostcodeAnywhere2' as "e"), '/e:InterimResults/e:Results/e:InterimResult' PASSING
XMLTYPE.createxml(ws_postcode_aw.getByPostcodeXML('WR2 6NJ','1', 'XML'))
COLUMNS description varchar2(400) PATH '//e:Description'
) l;


LOOP
FETCH c_get_addresses INTO r_get_addresses;
EXIT WHEN c_get_addresses%NOTFOUND;
v_str := v_str || getHouseNumber(r_get_addresses.description);
END LOOP;

CLOSE c_get_addresses;

END;

END;

Message was edited by:
steve_macleod