oracle 是否可以获得在数据库包中声明的常量值？（SQL 或 Java）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1132255/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is possible to get a constant value declared in a database package? (SQL or Java)
提问by Adam Paynter
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.
我在 Oracle 数据库中有一些包。它们包含存储过程、函数和常量。我可以使用 Java CallableStatement 在 Java 中调用函数。此外，我可以将 SQL 语句作为“select package1.function1(value1) from dual;”执行。但是我找不到如何在 Java 中获取包中声明的常量的值。
For example:
例如：
PACKAGE Package1 AS
A_CONSTANT CONSTANT VARCHAR2 := 'Constant value';
END Package1;
Thanks.
谢谢。
回答by Adam Paynter
You could try using an anonymous PL/SQL block in a CallableStatement:
您可以尝试在以下位置使用匿名 PL/SQL 块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();
}
回答by DCookie
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:
你不能这样做。您需要一个“getter”函数来返回在 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.
您可以从其他 PL/SQL 对象引用公共变量。
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.
为了回应您的最后一条评论，我添加了一些代码来展示如何编写一个函数来使用单个函数从包中获取不同的值。如果这不行的话，恐怕你是在众所周知的小溪上。
回答by user2861828
function getConstant(p_constant string) return string is
v_val string(100);
begin
execute immediate 'begin :a :='||p_constant||'; end;' using in out v_val;
return v_val;
end;
And then you can
然后你可以
Select getConstant('MyPkg.myConstant') from dual;
