Wrong conversion from Oracle PRAGMA AUTONOMOUS_TRANSACTION to PG DBLINK inside a procedure

[RuiFilipeGoncalvesPereira]

Good night Mr Darold,

I hereby contact you because in the migration of a procedure from a package, from oracle to postgresql, which contained the PRAGMA AUTONOMOUS_TRANSACTION clause in the conversion to the respective DBLINK of the PG, it invokes the procedure with the select * from clause when it should be invoked with CALL.

Here is the code with the example:

Oracle Example:

CREATE OR REPLACE PACKAGE BODY pck_reg_geral IS
	PROCEDURE sp_desbloqueia_registo_ta
	(
		pcoderro OUT NUMBER,
		pmsgerro OUT VARCHAR2,
		pid_idioma IN NUMBER,
		pid_util IN NUMBER,
		pid_equipa IN NUMBER,
		preferencia IN reg_reg.referencia%TYPE,
		pforca IN NUMBER,
		pid_janela IN VARCHAR2,
		pip IN VARCHAR2
	)
	IS
	PRAGMA AUTONOMOUS_TRANSACTION;
		vcontador PLS_INTEGER;
		vid_janela VARCHAR2(4000) := pid_janela||REGEXP_SUBSTR(preferencia,'[PI]');
	BEGIN
		pcoderro :=0;
		pmsgerro :='OK';
		IF (pid_util IS NOT NULL AND pforca = 0) THEN
			SELECT COUNT(t.id) INTO vcontador
			FROM tra_sca_utilizador t
			WHERE t.id = pid_util;
			IF (vcontador = 0) THEN
				RAISE e_util_inexistente;
			END IF;
		END IF;
IF (preferencia IS NOT NULL) THEN
   IF (pforca = 0) THEN
        DELETE FROM reg_reg_bloq b
        WHERE referencia = preferencia
            AND id_util_bloq = NVL(pid_util,'-6969');
   ELSE
        DELETE FROM reg_reg_bloq b WHERE b.referencia = preferencia;
  END IF;
		ELSE
			DELETE FROM reg_reg_bloq b
			WHERE id_util_bloq = NVL(pid_util,'-6969')
	AND nvl(b.origem_bloq,pck_tra_def.cn_varchar2_null) = NVL(vid_janela, pck_tra_def.cn_varchar2_null);
		END IF;
		COMMIT;
	EXCEPTION
		WHEN OTHERS THEN
			ROLLBACK;
			pcoderro := SQLCODE;
			pmsgerro := SQLERRM;
			LOGGER.ERROR('PCK_W_GTT_OPERACOES_REG.SP_DESBLOQUEIA_REGISTO_TA','ERRO; [PIDUTIL]=' ||pid_util ||'; [PREFERENCIA]=' ||preferencia ||'; [SQLCODE]=' ||pcoderro ||' ; [SQLERRM]=' || pmsgerro||' [BACKTRACE]='||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
			pcoderro := 1;
			pmsgerro := pck_tra_erros.f_msg_erro(pcoderro, pid_idioma);
END sp_desbloqueia_registo_ta;
END pck_reg_geral;
/

Postgres Example:

CREATE OR REPLACE PROCEDURE pck_reg_geral.sp_desbloqueia_registo_ta ( pcoderro INOUT numeric, pmsgerro INOUT varchar, pid_idioma numeric, pid_util numeric, pid_equipa numeric, preferencia reg_reg.referencia%TYPE, pforca numeric, pid_janela varchar, pip varchar ) AS $body$
DECLARE
-- Change this to reflect the dblink connection string
v_conn_str  text := format('port=%s dbname=%s', current_setting('port'), current_database());
v_query     text;
BEGIN
v_query := '_SELECT * FROM pck_reg_geral.sp_desbloqueia_registo_ta_atx_ ( ' || quote_nullable(pcoderro) || ',' || quote_nullable(pmsgerro) || ',' || quote_nullable(pid_idioma) || ',' || quote_nullable(pid_util) || ',' || quote_nullable(pid_equipa) || ',' || quote_nullable(preferencia) || ',' || quote_nullable(pforca) || ',' || quote_nullable(pid_janela) || ',' || quote_nullable(pip) || ' )';
	SELECT * FROM dblink(v_conn_str, v_query) AS p (v_ret0 numeric, v_ret1 varchar) INTO pcoderro, pmsgerro;
END;
$body$ LANGUAGE plpgsql SECURITY DEFINER;

Is it possible to confirm that this is an Ora2pg conversion error, is it possible to correct it?

Best regards,

Rui Pereira.

[darold]

Commit a253029 fix the call to the procedure.