pointer variables?

[agatamilanowska]

I have the following package:

create or replace package TEST_2 is

wach_reversal_const CONSTANT VARCHAR2(32767)
:= ' s.nmr_reversal_code=''RE''';
usbw_reversal_const CONSTANT VARCHAR2(32767)
:= ' s.reversal_flag=''Y''';

PROCEDURE recon_1;
PROCEDURE recon_2;
PROCEDURE recon_3;

end TEST_2;


create or replace package body TEST_2 is

PROCEDURE recon_1
(fiid VARCHAR2,
reversal_const VARCHAR2)
IS
sql_statement VARCHAR2(2000);
BEGIN
sql_statement := ''||reversal_const;
EXECUTE IMMEDIATE sql_statement USING fiid;
COMMIT;
END;

PROCEDURE recon_2
(fiid VARCHAR2) IS
BEGIN
IF fiid = 'wach' THEN
recon_1 (fiid, wach_reversal_const);
ELSIF fiid = 'usbw' THEN
recon_1 (fiid, usbw_reversal_const);
ELSE
DBMS_OUTPUT.PUT_LINE ('*** DO NOTHING.);
END IF;
END;

PROCEDURE recon_3
(fiid VARCHAR2) IS
sql_statement VARCHAR2(2000);
reversal_const VARCHAR2(32767);
BEGIN
-- reversal_const should be a variable that returns value of a constant,
-- not its name!
reversal_const := fiid||'_reversal_const';

sql_statement := ''||reversal_const;
EXECUTE IMMEDIATE sql_statement USING fiid;
COMMIT;
END;

end TEST_2;


Initially I wanted to use recon_3 but reversal_const variable was returning vaiable name, e.g. wach_reversal_const, instead of the variable value, e.g. ' s.nmr_reversal_code=''RE''.

Because of this, I replaced recon_3 with recon_1 and recon_2.

How can I fix recon_3 to refer to variable value not name?

[LKBrwn_DBA]

Try this:

create or replace package TEST_2 is

wach_reversal_const CONSTANT VARCHAR2(32767)
:= ' s.nmr_reversal_code=''RE''';
usbw_reversal_const CONSTANT VARCHAR2(32767)
:= ' s.reversal_flag=''Y''';

PROCEDURE recon_1(fiid VARCHAR2, reversal_const VARCHAR2);
PROCEDURE recon_2(fiid VARCHAR2);
PROCEDURE recon_3(fiid VARCHAR2);

end TEST_2;
/