Below SQL query (for making a view) is not working.
CASE WHEN IN_TYPE = PKG_CONSTANTS.INV_TYPE_1
WHEN IN_TYPE = PKG_CONSTANTS.INV_TYPE_2
end as Status
PKG_CONSTANTS.INV_TYPE_1 ==> PKG_CONSTANTS is a package and INV_TYPE_1 is a constant variable.
INV_TYPE_1 CONSTANT VARCHAR2(10) := '55600';
INV_TYPE_2 CONSTANT VARCHAR2(10) := '55601';
error: oracle.dbtools.raptor.controls.sqldialog.ObjectActionController$EditorObjectActionListener .. some 10 similar errors are coming in Loggin Page of SQL Developer
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.
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!)...
Did you mean the 12c feature -- writing functions inside the WITH clause?
Are you sure it wasn't this you heard...
Oracle 12c New Features - 126.96.36.199 - 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.