11 Replies Latest reply: Aug 20, 2013 12:33 PM by Billy~Verreynne RSS

    Package Constant variable in CASE



      Below SQL query (for making a view) is not working.



           then 'New'


        THEN 'old'

        ELSE 'N/A'

         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

        • 1. Re: Package Constant variable in CASE

          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.

          • 2. Re: Package Constant variable in CASE

            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.

            • 3. Re: Package Constant variable in CASE

              Thanks Billy, that was really helpful to understand.
              Is there any other way to do the same. Can you suggest anything here. Thanks !!

              • 4. Re: Package Constant variable in CASE

                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







                • 5. Re: Package Constant variable in CASE

                  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!)...



                  • 6. Re: Package Constant variable in CASE
                    ranit B

                    Hi Blu,


                    Did you mean the 12c feature -- writing functions inside the WITH clause?


                    Docs link -- http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_10002.htm#BABJFIDC

                    • 7. Re: Package Constant variable in CASE

                      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. 

                      • 8. Re: Package Constant variable in CASE

                        Are you sure it wasn't this you heard...


                        Oracle 12c New Features - - 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.)

                        • 9. Re: Package Constant variable in CASE

                          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.

                          • 10. Re: Package Constant variable in CASE

                            Billy will be delighted that a new generation of developers can now abuse associative arrays in SQL.

                            • 11. Re: Package Constant variable in CASE

                              Very much so... more fodder for the lead pipe... Can't wait... <sigh>