darold
Package variable packed with 'current_setting' while used for input
The Oracle package procedure:
PROCEDURE write_las (p_text VARCHAR2) IS
ERR_MSG VARCHAR2(100);
BEGIN
IF (g_bgm_seqnr IS NULL) THEN
SELECT NVL(MAX(sequence_nr), 0)
INTO g_bgm_seqnr
FROM BGM_TEXT_FILE
WHERE file_dbk = g_bgm_file_dbk;
END IF;
g_bgm_seqnr := g_bgm_seqnr + 1;
INSERT INTO BGM_TEXT_FILE
VALUES (g_bgm_file_dbk, g_bgm_seqnr, p_text);
EXCEPTION
WHEN OTHERS THEN
when_others('Procedure WRITE_LAS: '||SQLERRM);
END;
Get translated to:
CREATE OR REPLACE PROCEDURE bgm_exp.write_las (p_text text) AS $body$
DECLARE
ERR_MSG varchar(100);
BEGIN
IF (current_setting('bgm_exp.g_bgm_seqnr')::DINO_TEXT_FILE.sequence_nr%coalesce(TYPE::text, '') = '') THEN
SELECT coalesce(MAX(sequence_nr), 0)
INTO STRICT current_setting('bgm_exp.g_bgm_seqnr')::DINO_TEXT_FILE.sequence_nr%TYPE
FROM BGM_TEXT_FILE
WHERE file_dbk = current_setting('bgm_exp.g_bgm_file_dbk')::DINO_TEXT_FILE.file_dbk%TYPE;
END IF;
PERFORM set_config('bgm_exp.g_bgm_seqnr', current_setting('bgm_exp.g_bgm_seqnr')::DINO_TEXT_FILE.sequence_nr%TYPE + 1, false);
INSERT INTO BGM_TEXT_FILE
VALUES (current_setting('bgm_exp.g_bgm_file_dbk')::DINO_TEXT_FILE.file_dbk%TYPE, current_setting('bgm_exp.g_bgm_seqnr')::DINO_TEXT_FILE.sequence_nr%TYPE, p_text);
EXCEPTION
WHEN OTHERS THEN
CALL bgm_exp.when_others('Procedure WRITE_LAS: '||SQLERRM);
END;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;
Where the 'SELECT .. INTO STRICT current_setting('bgm_exp.g_bgm_seqnr')::DINO_TEXT_FILE.sequence_nr%TYPE' generates an error on compilation.
Yes, this is not covered by Ora2Pg, it just cast the setting with the data type used at Oracle variable declaration. You have to look at data type used for column DINO_TEXT_FILE.sequence_nr and fix the cast manually.
The issue seems to me that 'current_setting(....)' is a value and can therefore not be assigned a value through INTO. Maybe a workaround would be to introduce a temporary variable with the correct type that receives the value and then assign that value to the global variable. But that may be a bit complicated to recognize and apply in a general case.
In this particular case the construction seem to be a weird implementation of a 'sequence'. So it can be fixed differently :-)
Right sorry, I have looked too quickly the question. So the problem is that Ora2Pg should detect the INTO keyword before the call to the global variable and treat the code as an assignement. I will try to fix that.