darold
Conversion errors when migrating procedures with dynamic sql.
Good afternoon Mrº Darold,
When migrating a procedure from oracle to Pg, via ora2pg, I validated that the migration of some of the syntax that ora2pg migrates correctly was not performed correctly, in the following procedure.
------------------------------------------------------ORACLE------------------------------------------------------------- PROCEDURE sp_equipamento_reg ( pcoderro OUT NUMBER, pmsgerro OUT VARCHAR2, pid_idioma IN NUMBER, pid_util IN NUMBER, pid_equipa IN NUMBER, pid_om IN VARCHAR2, pid_ao IN VARCHAR2, pid_area_planeamento IN VARCHAR2, pid_fornecedor IN VARCHAR2, pid_tipo_equip IN VARCHAR2, pid_partilha_site IN VARCHAR2, pid_area_rede IN VARCHAR2, pnome_equipamento IN VARCHAR2, plista OUT cursorType ) IS strqry VARCHAR2(1000); BEGIN pcoderro :=0; pmsgerro :='OK'; strqry := 'SELECT DISTINCT te.id_equipamento ID, te.nome DESIGNACAO ' || 'FROM tra_equipamento te, reg_reg r, tra_partilha t, tra_dd_partilha_site p ' || 'WHERE r.id_comb_partilha_site = t.id_comb_partilha(+) AND t.id_partilha = p.id(+) ';
IF (pid_om NOT LIKE '0%') THEN
strqry := strqry ||' AND te.id_ao IN ('||REGEXP_REPLACE(pid_om,'^[,]+|[,]+$','')||') ';
END IF;
IF (pid_ao NOT LIKE '0%') THEN
strqry := strqry ||' AND te.id_aoi IN ('||REGEXP_REPLACE(pid_ao,'^[,]+|[,]+$','')||') ';
END IF;
IF (pid_area_planeamento NOT LIKE '0%') THEN
strqry := strqry ||' AND te.id_area_planeamento IN ('||REGEXP_REPLACE(pid_area_planeamento,'^[,]+|[,]+$','')||') ';
END IF;
IF (pid_fornecedor NOT LIKE '0%') THEN
strqry := strqry ||' AND (te.id_fornecedor IN ('||REGEXP_REPLACE(pid_fornecedor,'^[,]+|[,]+$','')||') OR te.id_fornecedor IS NULL) ';
END IF;
IF (pid_tipo_equip NOT LIKE '0%') THEN
strqry := strqry ||' AND te.id_tipo_equip IN ('||REGEXP_REPLACE(pid_tipo_equip,'^[,]+|[,]+$','')||') ';
END IF;
IF (pid_partilha_site NOT LIKE '0%') THEN
strqry := strqry ||' AND p.id IN ('||REGEXP_REPLACE(pid_partilha_site,'^[,]+|[,]+$','')||') ';
END IF;
IF (pid_area_rede NOT LIKE '0%') THEN
strqry := strqry ||' AND te.id_area_rede IN ('||REGEXP_REPLACE(pid_area_rede,'^[,]+|[,]+$','')||') ';
END IF;
strqry := strqry ||' AND r.id_equipamento = te.id AND r.id_estado <> ' || pck_tra_def.cn_eliminado_registo||' AND **rownum < 10**' ;
IF (pnome_equipamento IS NOT NULL) THEN
strqry := strqry ||' AND (TRANSLATE(UPPER(te.nome),''ÃÂÁÀÉÈÊÍÌÓÒÕÔÚÙÇ'',''AAAAEEEIIOOOOUUC'') LIKE ''%''||TRANSLATE(UPPER('''||pnome_equipamento||'''),''ÃÂÁÀÉÈÊÍÌÓÒÕÔÚÙÇ'',''AAAAEEEIIOOOOUUC'')||''%'' OR TRANSLATE(UPPER(te.abreviatura),''ÃÂÁÀÉÈÊÍÌÓÒÕÔÚÙÇ'',''AAAAEEEIIOOOOUUC'') LIKE ''%''||TRANSLATE(UPPER('''||pnome_equipamento||'''),''ÃÂÁÀÉÈÊÍÌÓÒÕÔÚÙÇ'',''AAAAEEEIIOOOOUUC'')||''%'')';
END IF;
strqry := strqry || ' **ORDER BY NLSSORT(te.nome, ''NLS_SORT=WEST_EUROPEAN'') ASC**';
OPEN plista FOR strqry;
EXCEPTION
WHEN OTHERS THEN
pcoderro :=SQLCODE;
pmsgerro :=SQLERRM;
LOGGER.ERROR('pck_w_tra_con_consultas_ttk.sp_equipamento_reg','Erro; [SQLCODE]=' ||pcoderro ||' ; [SQLERRM]=' || pmsgerro);
pcoderro := 2;
pmsgerro := pck_tra_erros.f_msg_erro(pcoderro, pid_idioma);
END sp_equipamento_reg;
-------------------------------------------------------PG------------------------------------------------------------------- create or replace procedure pck_w_tra_con_consultas_ttk.sp_equipamento_reg ( pcoderro inout numeric, pmsgerro inout text, pid_idioma numeric, pid_util numeric, pid_equipa numeric, pid_om text, pid_ao text, pid_area_planeamento text, pid_fornecedor text, pid_tipo_equip text, pid_partilha_site text, pid_area_rede text, pnome_equipamento text, plista inout refcursor ) as $body$ DECLARE strqry varchar(1000); BEGIN pcoderro :=0; pmsgerro :='OK'; strqry := 'SELECT DISTINCT te.id_equipamento ID, te.nome DESIGNACAO ' || 'FROM tra_equipamento te, reg_reg r, tra_partilha t, tra_dd_partilha_site p ' || 'WHERE r.id_comb_partilha_site = t.id_comb_partilha(+) AND t.id_partilha = p.id(+) '; IF (pid_om NOT LIKE '0%') THEN strqry := strqry ||' AND te.id_ao IN ('||REGEXP_REPLACE(pid_om,'^[,]+|[,]+$','')||') '; END IF; IF (pid_ao NOT LIKE '0%') THEN strqry := strqry ||' AND te.id_aoi IN ('||REGEXP_REPLACE(pid_ao,'^[,]+|[,]+$','')||') '; END IF; IF (pid_area_planeamento NOT LIKE '0%') THEN strqry := strqry ||' AND te.id_area_planeamento IN ('||REGEXP_REPLACE(pid_area_planeamento,'^[,]+|[,]+$','')||') '; END IF; IF (pid_fornecedor NOT LIKE '0%') THEN strqry := strqry ||' AND (te.id_fornecedor IN ('||REGEXP_REPLACE(pid_fornecedor,'^[,]+|[,]+$','')||') OR te.id_fornecedor IS NULL) '; END IF; IF (pid_tipo_equip NOT LIKE '0%') THEN strqry := strqry ||' AND te.id_tipo_equip IN ('||REGEXP_REPLACE(pid_tipo_equip,'^[,]+|[,]+$','')||') '; END IF; IF (pid_partilha_site NOT LIKE '0%') THEN strqry := strqry ||' AND p.id IN ('||REGEXP_REPLACE(pid_partilha_site,'^[,]+|[,]+$','')||') '; END IF; IF (pid_area_rede NOT LIKE '0%') THEN strqry := strqry ||' AND te.id_area_rede IN ('||REGEXP_REPLACE(pid_area_rede,'^[,]+|[,]+$','')||') '; END IF; strqry := strqry ||' AND r.id_equipamento = te.id AND r.id_estado <> ' || pck_tra_def.cn_eliminado_registo||' AND rownum < 10'; IF (pnome_equipamento IS NOT NULL) THEN strqry := strqry ||' AND (TRANSLATE(UPPER(te.nome),''ÃÂÁÀÉÈÊÍÌÓÒÕÔÚÙÇ'',''AAAAEEEIIOOOOUUC'') LIKE ''%''||TRANSLATE(UPPER('''||pnome_equipamento||'''),''ÃÂÁÀÉÈÊÍÌÓÒÕÔÚÙÇ'',''AAAAEEEIIOOOOUUC'')||''%'' OR TRANSLATE(UPPER(te.abreviatura),''ÃÂÁÀÉÈÊÍÌÓÒÕÔÚÙÇ'',''AAAAEEEIIOOOOUUC'') LIKE ''%''||TRANSLATE(UPPER('''||pnome_equipamento||'''),''ÃÂÁÀÉÈÊÍÌÓÒÕÔÚÙÇ'',''AAAAEEEIIOOOOUUC'')||''%'')'; END IF; strqry := strqry || ' ORDER BY NLSSORT(te.nome, ''NLS_SORT=WEST_EUROPEAN'') ASC'; OPEN plista FOR strqry; EXCEPTION WHEN OTHERS THEN pcoderro :=SQLSTATE; pmsgerro :=SQLERRM; LOGGER.ERROR('PCK_W_TRA_CON_CONSULTAS_TTK.SP_EQUIPAMENTO_REG()','ERRO; [SQLSTATE]=' ||PCODERRO ||' ; [SQLERRM]=' || PMSGERRO); pcoderro := 2; pmsgerro := pck_tra_erros.f_msg_erro(pcoderro, pid_idioma); END; $body$ LANGUAGE PLPGSQL;
As it was possible to see ora2pg did not convert correctly left joins, rownum and NLSSORT to postegres, as well as not converting for example other concepts like the nvl function to the coloesce of Pg, will it be because it is dynamic sql? , is it possible to analyze this problem?.
Best regards,
Rui Pereira.
Good afternoon sir darold,
I would like to know, if possible, if there is any idea of when the dynamic SQL conversion functionality will be implemented in ora2pg?, or if there is any expected date for the release of this realese?
Best regards,
Rui Pereira.