1 person found this helpful
You cannot access package variables from SQL like that. You would have to create a 'getter' function to return the value, and then call the function to obtain the value instead.
1 person found this helpful
You cannot in plain vanilla SQL, refer to a PL/SQL package variable. Reason: scope.
You have (for example) package FOO and static constant BAR in package.
You execute the following SQL:
select * from some_table where some_col = FOO.BAR
The parser's scope is SQL. It looks at the SQL scope and do not see any matching object for Foo.Bar. It then tries PL/SQL scope - by looking at any PL/SQL functions that are available. There is no Bar function in package Foo.
Why does it not accept Foo.Bar as a value? The answer is that it needs to be a BIND VARIABLE for that to happen. SQL expects 2 types of external (non-SQL) "things" that supply values inside the SQL code. It expects PL/SQL functions. It expects bind variables.
It does not expect PL/SQL variables (or constants), just as it does not expect Java/C/Pascal/etc variables (or constants). If you want to supply a variable value from such a language context, the caller needs to use a bind variable, and bind its variable to that. E.g.
execute immediate 'select * from some_table where some_col = :BIND1' ... using IN Foo.Bar;
So why then is the following PL/SQL SQL statement valid?
select * into myColllectionVar from some_table where some_col = Foo.Bar
The answer is that PL/SQL parses this first. It recognise that you want to make a call to the SQL engine. It sees that Foo.Bar is a PL/SQL variable (or constant). It crafts a SQL statement with a bind variable. It parses that as a cursor. It then binds the Foo.Bar value to the bind variable.
The SQL engine sees a bind variable in the SQL. Not a PL/SQL variable or constant.
Thanks Billy, that was really helpful to understand.
Is there any other way to do the same. Can you suggest anything here. Thanks !!
Blu already told you that - you need a getter to provide the PL/SQL constant. In PL/SQL that means a function is needed that returns the PL/SQL constant to the SQL engine.
create or replace function PLconstant( name varchar2 ) return varchar2 deterministic is
when name = 'FOO.BAR' then Foo.bar
when name = 'PACKAGE1.MAXROW' then Package1.MaxFow
What "other way" do you want? You've had it explained to you why you can't do it, so are you expecting miracles?
I believe in Oracle 12c there was mention of changes to scope so it would now span SQL and PL/SQL more seamelessly, but I can't find it in the new features (at a glance anyway!)...
No, not that one.
I'm sure I heard somewhere that 12c would offer the ability to use PL variables/collections directly in SQL as the scope would be opened up to allow it. Can't recall where I saw it now.
Are you sure it wasn't this you heard...
Oracle 12c New Features - 184.108.40.206 - PL/SQL-Specific Data Types Allowed Across the PL/SQL-to-SQL Interface
The table operator can now be used in a PL/SQL program on a collection whose data type is declared in PL/SQL. This also allows the data type to be a PL/SQL associative array. (In prior releases, the collection's data type had to be declared at the schema level.)
That sounds like the one padders. I first heard about it before 12c was released, so the actual detail wasn't clear, but that certainly reads along the right lines. If only I had a version of 12c to play with. Maybe soon.
Billy will be delighted that a new generation of developers can now abuse associative arrays in SQL.
Very much so... more fodder for the lead pipe... Can't wait... <sigh>