Pessoal,
Estou com problemas para inserir o código XML em uma variável do tipo CLOB,
Selecionar tudo
DECLARE
v_file Utl_File.File_Type;
v_xml CLOB;
v_more BOOLEAN := TRUE;
v_error_code NUMBER := 0;
v_error_text VARCHAR2(300) := '';
BEGIN
V_XML := DBMS_XMLQUERY.getXML('SELECT
XMLELEMENT("ROWSET",
XMLELEMENT("ROW",
XMLFOREST(CTX.WAREHOUSE_CODE AS "WAREHOUSE_CODE",
CTX.RECEPTION_CODE AS "RECEPTION_CODE",
CTX.REG_REC_ORDER AS "REG_REC_ORDER",
CTX.RECEPTION_STATUS_CODE AS "RECEPTION_STATUS_CODE",
CTX.CONTAINERS_RECEIVED AS "CONTAINERS_RECEIVED",
CTX.DOOR_CODE AS "DOOR_CODE",
CTX.TYPE_CODE AS "TYPE_CODE",
CTX.CREATION_DATE AS "CREATION_DATE",
CTX.UPDATE_DATE AS "UPDATE_DATE",
CTX.SYSDATE_REG AS "SYSDATE_REG",
CTX.LINE_COUNT AS "LINE_COUNT"
),
XMLELEMENT(REG_LINES,
XMLELEMENT(REG_LINES_ROW,
XMLFOREST(CTX.PRODUCT_CODE AS "PRODUCT_CODE",
CTX.QTTY_TO_RECEIVE_BASE AS "QTTY_TO_RECEIVE_BASE",
CTX.QTTY_TO_RECEIVE_UOM AS "QTTY_TO_RECEIVE_UOM",
CTX.UOM_CODE AS "UOM_CODE",
CTX.QTTY_RECEIVED_BASE AS "QTTY_RECEIVED_BASE",
CTX.QTTY_RECEIVE_UOM AS "QTTY_RECEIVE_BASE",
CTX.DETAIL_COUNT AS "DETAIL_COUNT",
CTX.LOT_COUNT AS "LOT_COUNT"),
XMLELEMENT("REG_DETAILS",
XMLELEMENT("REG_DETAILS_ROW",
XMLFOREST(CTX.RECEPTION_STOCK_ID AS "RECEPTION_STOCK_ID",
CTX.CONTAINER_NO AS "CONTAINER_NO",
CTX.STOCK_QUANTITY AS "STOCK_QUANTITY",
CTX.STOCK_RECEPTION_DATE AS "STOCK_RECEPTION_DATE",
CTX.STOCK_WEIGHT AS "STOCK_WEIGHT"
)
)
)
)
)
)
)
--INTO V_XML
FROM CUSTOM_TESTE_XML CTX;');
V_FILE := UTL_FILE.fopen('D_SENDED_FILES', 'TESTE.XML', 'w');
WHILE V_MORE LOOP
UTL_FILE.PUT(V_FILE, SUBSTR(V_XML, 1, 32767));
IF LENGTH(V_XML) > 32767 THEN
V_XML := SUBSTR(V_XML, 32768);
ELSE
V_MORE := FALSE;
END IF;
END LOOP;
UTL_FILE.fclose(V_FILE);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(Substr(SQLERRM,1,255));
Utl_File.FClose(v_file);
v_error_code := SQLCODE;
v_error_text := SUBSTR(SQLERRM, 1, 300);
END;
Como é possível ver, aproveito o mesmo processo pra salvar o resultado em um arquivo no diretório, mas o arquivo retorna o erro abaixo:
Selecionar tudo
<?xml version = '1.0'?>
<ERROR>oracle.xml.sql.OracleXMLSQLException: ORA-00911: caractere inválido
</ERROR>
Já tentei utilizar VARCHAR2(4000), mas tenho o mesmo problema.
Alguém sabe me dizer onde está meu erro??
Obrigado,