Wrong conversion of data from Oralce XMLTYPE to postgres XML by ora2pg

[RuiFilipeGoncalvesPereira]

Good morning Mr Darold,

I hereby inform you because in the conversion of a procedure within a package from oracle to pg via ora2pg, the type of variable/input parameter XMLTYPE(Oracle) was wrongly converted to the respective one of the pg XML, as visible in the example below:

Oracle example:

CREATE OR REPLACE PACKAGE BODY pck_gmp_ws_preventive_maint IS
PROCEDURE sp_trata_limites_pendencia ( pcoderro OUT NUMBER, pmsgerro OUT VARCHAR2, pidutil IN NUMBER, pididioma IN NUMBER, preferencia IN VARCHAR2, pid_ficha IN NUMBER, pid_cfg_ficha IN NUMBER, pxml_bd IN XMLTYPE, pxml_web IN XMLTYPE, pip IN VARCHAR2, pid_particularidade IN NUMBER DEFAULT NULL ) IS vaux VARCHAR2(4000); vcondicao VARCHAR2(32767); vxml_bd SYS.XMLTYPE; BEGIN pcoderro := 0; LOGGER.INFO('pck_w_int_criar_int_detalhes.sp_trata_limites_pendencia','Fim do processo TRATA PENDENCIAS'); EXCEPTION WHEN OTHERS THEN pcoderro := SQLCODE; pmsgerro := SQLERRM; END sp_trata_limites_pendencia; END pck_gmp_ws_preventive_maint; /

Postgres example:

CREATE OR REPLACE PROCEDURE pck_gmp_ws_preventive_maint.sp_trata_limites_pendencia ( pcoderro INOUT numeric, pmsgerro INOUT varchar, pidutil numeric, pididioma numeric, preferencia varchar, pid_ficha numeric, pid_cfg_ficha numeric, pxml_bd xmltype, pxml_web xmltype, pip varchar, pid_particularidade numeric DEFAULT NULL) AS $body$ DECLARE vaux varchar(4000); vcondicao varchar(32767); vxml_bd xmltype; BEGIN pcoderro := 0; LOGGER.INFO('pck_w_int_criar_int_detalhes.sp_trata_limites_pendencia','Fim do processo TRATA PENDENCIAS'); EXCEPTION WHEN OTHERS THEN pcoderro := SQLSTATE; pmsgerro := SQLERRM; END; $body$ LANGUAGE PLPGSQL;

The conversion done by ora2pg should have placed the variables/parameters with XML in the PG and not kept the Oracle XMLTYPE, Can you confirm that this is an ora2pg error?, is it possible to correct it?

Best regards,

Rui Pereira.

[darold]

Hi Rui, please update to latest development code this have been fixed.