Check if CONSTANT exists in the Package

Question

Check if CONSTANT exists in the Package

Navigation

#1 by (2 votes)

1

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'd like to check on a select if CONSTANT has G, and if it exists then I execute an action if it is TRUE if it is FALSE I do not execute.

can be checked as follows: (the script below does not work, I can not do validation of a 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.

oracle pl-sql

asked by anonymous 21.10.2014 / 22:40

1 answer

Entity Framework Navigation Property How to customize a listView (scrolling)?

score 2 · Accepted Answer

Recompile your package with the parameter PLSCOPE_SETTINGS set 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 USER_IDENTIFIERS and ALL_IDENTIFIERS views to check if 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