Check if CONSTANT exists in Package

Question

I have a Package:

 CREATE OR REPLACE PACKAGE LETRAS AS
  possuiA     CONSTANT BOOLEAN NOT NULL := TRUE;
  possuiB    CONSTANT BOOLEAN NOT NULL := TRUE;
  possuiC    CONSTANT BOOLEAN NOT NULL := TRUE;
  possuiD     CONSTANT BOOLEAN NOT NULL := FALSE;
  possuiE    CONSTANT BOOLEAN NOT NULL := FALSE;
  possuiF     CONSTANT BOOLEAN NOT NULL := FALSE;
 END db1integra;

And though I would like to check in a select if there is a CONSTANT possesG, and if it exists then execute an action if it is TRUE if it is FALSE non-executive.

verification may be made in this way: (the script below does not work, I can not validate of a Non-constant that does not exist)

DECLARE
  vsql VARCHAR(1000);
BEGIN
  IF letras.possuig THEN
    vsql :=  'UPDATE EMPLOYEE
              SET JOB = DEFAULT
              WHERE EMPNO = ''000290''';
  ELSE
    vsql :=  'UPDATE EMPLOYEE
              SET JOB = DEFAULT
              WHERE EMPNO = ''000210''';
  END IF;
END;

Thank you very much.

Answer 1

Recompile your package with the parameter PLSCOPE_SETTINGS setado to IDENTIFIERS:ALL (for more information see Using PL/Scope).

ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL'/

CREATE OR REPLACE PACKAGE letras AS 
  possuiA     CONSTANT BOOLEAN NOT NULL := TRUE;
  possuiB    CONSTANT BOOLEAN NOT NULL := TRUE;
  possuiC    CONSTANT BOOLEAN NOT NULL := TRUE;
  possuiD     CONSTANT BOOLEAN NOT NULL := FALSE;
  possuiE    CONSTANT BOOLEAN NOT NULL := FALSE;
  possuiF     CONSTANT BOOLEAN NOT NULL := FALSE;
END letras; 
/ 

With this you can use the views USER_IDENTIFIERS and ALL_IDENTIFIERS to verify that a constant has been declared.

SELECT * 
FROM USER_IDENTIFIERS
WHERE 
  NAME = 'POSSUIF'
  AND TYPE = 'CONSTANT'
  AND USAGE = 'DECLARATION'
  AND OBJECT_NAME = 'LETRAS'
  AND OBJECT_TYPE = 'PACKAGE'; 

---

Functional example in SQL Fiddle