11 Replies Latest reply: Aug 20, 2013 7:33 AM by Billy~Verreynne RSS

    Package Constant variable in CASE

    Aparna16


      Hi,

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

       

      CASE WHEN IN_TYPE = PKG_CONSTANTS.INV_TYPE_1

           then 'New'

        WHEN IN_TYPE = PKG_CONSTANTS.INV_TYPE_2

        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
          BluShadow

          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
            Billy~Verreynne

            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
              Aparna16

              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
                Billy~Verreynne

                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.

                 

                Example

                create or replace function PLconstant( name varchar2 ) return varchar2 deterministic is

                begin

                   return(

                      case

                         when name = 'FOO.BAR' then Foo.bar

                         when name = 'PACKAGE1.MAXROW' then Package1.MaxFow

                          ..

                     else

                        null

                     end

                  );

                end;

                • 5. Re: Package Constant variable in CASE
                  BluShadow

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

                   

                  http://docs.oracle.com/cd/E16655_01/server.121/e17906/chapter1.htm#AREANO02516

                  • 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
                      BluShadow

                      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
                        padders

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

                         

                        Oracle 12c New Features - 1.1.4.12 - 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
                          BluShadow

                          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
                            padders

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

                            • 11. Re: Package Constant variable in CASE
                              Billy~Verreynne

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