Incomplete conversion of creating a Type

[RuiFilipeGoncalvesPereira]

Good morning Mr Darold,

I hereby contact you because when converting a type into a package, via ora2pg, I noticed that the DROP TYPE IF EXIST “Name_type” clause was left out; I think it should be included before creating the type, otherwise every time the package is executed it will generate an error, thus making it impossible to compile.

Oracle code 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, ) IS TYPE varray_type IS VARRAY(3) OF PLS_INTEGER; pi varray_type := varray_type(); BEGIN pcoderro := 0; LOGGER.INFO('pck_w_int_criar_int_detalhes.sp_trata_limites_pendencia','Fim do processo TRATA PENDENCIAS'); WITH cte (id_sala, numero, designacao) AS ( SELECT r.id ID_SALA,(ROWNUM-1) NUMERO ,CASE WHEN id_tipo = '11' THEN '[PI-SALA] - ' || r.designacao ELSE '[SITE] - ' || r.designacao END FROM (SELECT id, id_pai, id_tipo, tipo, codigo, designacao, testes, path, id_sca, id_sca_pai, path_design, morada, codigo_postal, cp_descricao, id_pa,nome_pa, id_interface, inactivo, id_idioma FROM v_tra_dd_area_rede i WHERE id_site = vid_site AND (id_tipo = '11' OR id_pai IS NULL) ) r WHERE id_pai IS NULL and id_interface = vid_interface UNION ALL
SELECT r.id ID_SALA,(ROWNUM-1) NUMERO ,CASE WHEN id_tipo = '11' THEN '[PI-SALA] - ' || r.designacao ELSE '[SITE] - ' || r.designacao END FROM ( SELECT id, id_pai, id_tipo, tipo, codigo, designacao, testes, path, id_sca, id_sca_pai, path_design, morada, codigo_postal, cp_descricao, id_pa, nome_pa, id_interface, inactivo, id_idioma FROM v_tra_dd_area_rede i WHERE id_site = vid_site AND (id_tipo = '11' OR id_pai IS NULL) ) r JOIN cte c ON (c.id_sala = r.id_pai)) SELECT FROM cte ORDER BY numero;
EXCEPTION WHEN OTHERS THEN /CALL*/ LOGGER.ERROR('PCK_GTT_GERAL.SP_OBTEM_ESCALAMENTO_FUNCIONAL',Sqlerrm||'-'||SQLERRM); END sp_trata_limites_pendencia;
END pck_gmp_ws_preventive_maint; /

Exemplo do código Postgres:

DROP SCHEMA IF EXISTS pck_gmp_ws_preventive_maint CASCADE; CREATE SCHEMA IF NOT EXISTS pck_gmp_ws_preventive_maint; CREATE TYPE varray_type AS (varray_type numeric[3]); 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) AS $body$ DECLARE BEGIN pcoderro := 0; LOGGER.INFO('pck_w_int_criar_int_detalhes.sp_trata_limites_pendencia','Fim do processo TRATA PENDENCIAS'); WITH cte(id_sala, numero, designacao) AS ( SELECT r.id ID_SALA,(ROWNUM-1) NUMERO ,CASE WHEN id_tipo = '11' THEN '[PI-SALA] - ' || r.designacao ELSE '[SITE] - ' || r.designacao END FROM ( SELECT id, id_pai, id_tipo, tipo, codigo, designacao, testes, path, id_sca, id_sca_pai, path_design, morada, codigo_postal, cp_descricao, id_pa,nome_pa, id_interface, inactivo, id_idioma FROM v_tra_dd_area_rede i WHERE id_site = vid_site AND (id_tipo = '11' OR id_pai IS NULL) ) r WHERE id_pai IS NULL and id_interface = vid_interface UNION ALL SELECT r.id ID_SALA,(ROWNUM-1) NUMERO ,CASE WHEN id_tipo = '11' THEN '[PI-SALA] - ' || r.designacao ELSE '[SITE] - ' || r.designacao END FROM (SELECT id, id_pai, id_tipo, tipo, codigo, designacao, testes, path, id_sca, id_sca_pai, path_design, morada, codigo_postal, cp_descricao, id_pa, nome_pa, id_interface, inactivo, id_idioma FROM v_tra_dd_area_rede i WHERE id_site = vid_site AND (id_tipo = '11' OR id_pai IS NULL) ) r JOIN cte c ON (c.id_sala = r.id_pai)) SELECT FROM cte ORDER BY numero; EXCEPTION WHEN OTHERS THEN /CALL*/ LOGGER.ERROR('PCK_GTT_GERAL.SP_OBTEM_ESCALAMENTO_FUNCIONAL',Sqlerrm||'-'||SQLERRM); END; $body$ LANGUAGE PLPGSQL;

Is it possible to change this situation on the ora2pg side?,

I inform you that I already use version 23.2 of ora2pg, that is, the most recent.

Best regards,

Rui Pereira.

[darold]

Commit 4fe7642 fixes this issue.