How to Access a Package Constant/Variable from oracledb?

[mkmoisen]

CREATE OR REPLACE PACKAGE foo_pkg
IS
    g_bar CONSTANT VARCHAR2(10) := 'baz';
END foo_pkg;
/

How can I access foo_pkg.g_bar from Python?

I've tried the following:

import oracledb

conn = oraclebd.connect(...)

cur = conn.cursor()


# DPY-2035: invalid object type name: "FOO_PKG.G_BAR"
type_ = conn.gettype('FOO_PKG.G_BAR')

The following works, but I would have thought that there would be a more straightforward way:

bar_var = cur.var(oracledb.STRING)

cur.execute('BEGIN :bar := foo_pkg.g_bar; END;', dict(bar=bar_var))

print(bar_var.getvalue())

Of course we can create a function that returns it, but I would rather just access the global package constant directly if possible.

[anthony-tuininga]

As far as I am aware there is no way to do so. You must use PL/SQL to access constants defined in PL/SQL packages. So the "awkward" way you noted at the end of your question is the only way possible at the moment. It would be possible, however, to create a special object (PlsqlConstant?) which could be passed as an argument to callproc() or callfunc() which could then generate the PL/SQL for you. An example might be as follows:

bar_const = oracledb.PlsqlConstant("foo_pkg.g_bar")
cur.callproc("foo_pkg.some_proc", [5, bar_const, "Some string"])

If that sounds like a good idea to you, log an enhancement request and we'll consider it further.