Is possible to get a constant value declared in a database package? (SQL or Java)

Question

I have some Packages in a Oracle database. They contain stored procedures, functions and constants. I can call functions, in Java, using a Java CallableStatement. Also, I could execute a SQL statement as "select package1.function1(value1) from dual;". But I can't find how to get the value of a constant declared in the package, in Java.

For example:

PACKAGE Package1 AS
    A_CONSTANT CONSTANT VARCHAR2 := 'Constant value';
END Package1;

Thanks.

Answer 1

You can't do this. You'll need a "getter" function to return the value of a public variable or constant defined in a pacakge:

CREATE OR REPLACE PACKAGE Package1 IS
    A_CONSTANT CONSTANT VARCHAR2(100) := 'Constant value a';
    B_CONSTANT CONSTANT VARCHAR2(100) := 'Constant value b';
    FUNCTION get_const(p_id NUMBER) RETURN VARCHAR2;
END Package1;

CREATE OR REPLACE PACKAGE BODY Package1 IS
  FUNCTION get_const(p_id NUMBER) RETURN VARCHAR2
  IS
  BEGIN
    IF p_id = 1 THEN
      RETURN package1.a_constant;
    ELSIF p_id = 2 THEN
      RETURN package1.b_constant;
    END IF;
    RETURN NULL;
  END;
END Package1;

SQL> select package1.a_constant from dual;

select package1.a_constant from dual

ORA-06553: PLS-221: 'A_CONSTANT' is not a procedure or is undefined

SQL> select package1.get_const(1) from dual;

PACKAGE1.GET_CONST(1)
--------------------------------------------------------------------------------
Constant value a

SQL>

EDIT: If you can't modify these packages, can you create new functions or packages? If you can, you can workaround the issue thusly:

CREATE OR REPLACE FUNCTION get_const(p_id NUMBER) RETURN VARCHAR2 IS
BEGIN
  IF p_id = 1 THEN
    RETURN package1.a_constant;
  ELSIF p_id = 2 THEN
    RETURN package1.b_constant;
  END IF;
  RETURN NULL;
END;
/


SQL> select get_const(1) from dual;

GET_CONST(1)
--------------------------------------------------------------------------------
Constant value a

SQL> select get_const(2) from dual;

GET_CONST(2)
--------------------------------------------------------------------------------
Constant value b

SQL>

You are allowed to reference public variables from other PL/SQL objects.

In response to your last comment, I've added some code to show how one might write a function to get different values from the package with a single function. If this won't do, I'm afraid you're up the proverbial creek.

Answer 2

@DCookie

I'm afraid that I won't be allowed to do that. But thanks for all your effort!

John Pollancre

Answer 3

You could try using an anonymous PL/SQL block in a CallableStatement:

String constantValue;

CallableStatement statement = connection.prepareCall("BEGIN ? := Package1.A_CONSTANT; END;");
try {
    statement.registerOutParameter(1, Types.VARCHAR);
    statement.execute();
    constantValue = statement.getString(1);
} finally {
    statement.close();
}