1 2 Previous Next 26 Replies Latest reply on Jul 20, 2010 3:32 PM by Billy~Verreynne

    How to declare a global variable from a PL/SQL package

    user4184769
      Hi All,

      Using a global variable is a bad practise for all language. However, in my case, I have a PL/SQL package defines all constants, and I want to use them directly via SQL statement, for instance,
      PACKAGE my_const
      IS
           DEFAULT_ZIP_CODE CONSTANT VARCHAR2(5) := '00000'; 
      END;
      And I cannot referrence this variable from my select statement as
      SELECT my_const.DEFAULT_ZIP_CODE from dual;
      I have to create a function via my package, as,
      FUNCTION get_default_zip_code RETURN VARCHAR2 
      IS
      BEGIN
           RETURN DEFAULT_ZIP_CODE; 
      END;
      and
      SELECT my_const.get_default_zip_code from dual;
      I don't want to create functions to referrence the default varaibles. Does anyone have any clues?

      thanks

      Edited by: user4184769 on Jul 19, 2010 8:36 AM
        • 1. Re: How to declare a global variable from a PL/SQL package
          Dom Brooks
          Have you thought about using a table for your constants instead.

          Otherwise you going to have to have a getter function.
          Or alternatively use application contexts.
          http://download.oracle.com/docs/cd/E11882_01/network.112/e10574/app_context.htm#DBSEG70071
          • 2. Re: How to declare a global variable from a PL/SQL package
            riedelme
            What verson of the database are you on?

            I'm having the same problem on Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production. Oddly, the constant references work when the SQL is defined inside PL/SQL

            I know you don't want to create any functions at all, but if you're careful you can write one function with the an arugment name as an input parameter and return the needed value.

            A different option would be to use column default values.

            Edited by: riedelme on Jul 19, 2010 9:14 AM
            • 3. Re: How to declare a global variable from a PL/SQL package
              Dom Brooks
              Oddly, the constant references work when the SQL is defined inside PL/SQL
              Because the value is substituted / bound at runtime - i.e. if you look at v$sql you will see something like SELECT :B1 FROM DUAL;

              Edit:
              Actually I'm not comfortable with that explanation.
              I want to say something using terms like optimisation and constant inlining using a bind at compile time but I'm not 100%.

              Edited by: DomBrooks on Jul 19, 2010 5:32 PM
              • 4. Re: How to declare a global variable from a PL/SQL package
                riedelme
                DomBrooks wrote:
                Oddly, the constant references work when the SQL is defined inside PL/SQL
                Because the value is substituted / bound at runtime - i.e. if you look at v$sql you will see something like SELECT :B1 FROM DUAL;

                Edit:
                Actually I'm not comfortable with that explanation.
                I want to say something using terms like optimisation and constant inlining using a bind at compile time but I'm not 100%.

                Edited by: DomBrooks on Jul 19, 2010 5:32 PM
                DomBrooks,

                thanks for the post. I need to look into this when time permits but since its not a showstopper I haven't pursuied the issue yet. The V$SQL query is a good idea that I'll need to try when I get a chance, probably checking MOS later too

                Its not much of a problem for me at this time - just another bit of Oracle wierdness to be endured. I'm really hoping that the OP can get a workable solution
                • 5. Re: How to declare a global variable from a PL/SQL package
                  user4184769
                  I am using Oracle10g, version 10.2.0.4.0. My const object contains more than 600 variables, and the constants return different types of data. It is super painful to create 600+ getters or create a function takes one param and returns the corresponding value as riedelme suggested. It seems like no any easy way to archive it.
                  • 6. Re: How to declare a global variable from a PL/SQL package
                    Billy~Verreynne
                    riedelme wrote:

                    I'm having the same problem on Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production. Oddly, the constant references work when the SQL is defined inside PL/SQL
                    All to do with scope.

                    When inside PL code, you can reference PL data - i.e. variables and constants. So when you refer to a PL constant/global variable in SQL in PL code, the PL engine parses it using a bind variable and binds the PL variable to it.

                    The SQL engine can thus "see" the value via the bind variable.

                    When running SQL natively, it can only access PL/SQL functions. It does not have the "intelligence" to crack open PL code units, peek inside and access PL variables directly. Which makes perfect sense - as PL is not integrated with the SQL language. SQL is integrated with the PL language.
                    • 7. Re: How to declare a global variable from a PL/SQL package
                      Billy~Verreynne
                      user4184769 wrote:
                      I am using Oracle10g, version 10.2.0.4.0. My const object contains more than 600 variables, and the constants return different types of data. It is super painful to create 600+ getters or create a function takes one param and returns the corresponding value as riedelme suggested. It seems like no any easy way to archive it.
                      That is because data is suppose to be stored in SQL tables - why on earth use a database and then stuff data, and loads of it, into its procedural language?

                      It is expensive ito memory. It cannot be filtered and sorted using SQL. It is duplicated for each session - no concurrency exist. No acid controls. Etc.

                      The best place for data in a database should be quite obvious - in a SQL table.
                      • 8. Re: How to declare a global variable from a PL/SQL package
                        Billy~Verreynne
                        DomBrooks wrote:
                        Oddly, the constant references work when the SQL is defined inside PL/SQL
                        Because the value is substituted / bound at runtime - i.e. if you look at v$sql you will see something like SELECT :B1 FROM DUAL;

                        Edit:
                        Actually I'm not comfortable with that explanation.
                        But you are perfectly correct Don. :-)
                        • 9. Re: How to declare a global variable from a PL/SQL package
                          riedelme
                          Billy,

                          thanks for the info. Your scope explanation makes sense even though it is not intuitive to me. I think the usage of package variables should be supported by SQL (they're just values to be copied) but I also want taxes to go down. If wishes were horses ...

                          I searched MOS and google for info on using packaged constants in SQL but did not find anything. Chances are I didn't use the proper keywords.

                          I tried DomBrooks idea to check the value in V$SQL and found no bind variable substitution listed in V$SQL.SQL_TEXT for the executed query supporting the scope theory.

                          The real question is how to help the OP solve his problem. The only think I can think of is a generic function using dynamic PL/SQL in EXECUTE IMMEDIATE (which I really don't like) to receive the name of the variable and use EXECUTE IMMEDIATE to get the value and return it to the query. I'm hoping someone posts something better. I don't think the one function would be "super painful" but would be painful enough :(
                          • 10. Re: How to declare a global variable from a PL/SQL package
                            Billy~Verreynne
                            riedelme wrote:

                            thanks for the info. Your scope explanation makes sense even though it is not intuitive to me. I think the usage of package variables should be supported by SQL (they're just values to be copied)
                            Maybe look at it from another language's perspective. You want to use a global PL package variable in Java/C#/Delphi/VB/etc. How would you do it?

                            None of these languages can crack open the data segment of a PL code unit, inspect the variables in it, and extract a value from it. Instead, it needs to be done as follows:

                            Using sqlplus as the client illustrates how all these languages will need to do it:
                            SQL> var value varchar2(20);
                            SQL> begin
                              2>     :value := SomePackage.someVar;
                              3> end;
                              4> /
                            So why should SQL behave differently? It is not the same as the PL language. It is not a subset of the PL language. Yeah, PL/SQL blurs the line between these 2 languages making it very simple for us to mix their source code. But PL/SQL is SQL integrated with PL - not PL integrated with SQL. PL has tight hooks into SQL, creating cursors for you, defining bind variables, binding variables and doing the whole Oracle Call Interface bit for you.

                            But SQL has no need for PL code, just as it has no need for Java code, or Delphi code or VB code. Yes, it is possible for it to call Java stored procs. As it is possible for it to call PL procs. But these are via the formal call interface of those languages - not via tight integration hooks that blur the languages and make SQL and Java, or SQL and PL, look like a single integrated source code unit.

                            Thus SQL has the pretty much the same constraints in calling the PL language as other languages do. What SQL can do is use the PL engine's call interface and tell it "+execute this function and return the result of the function+".
                            • 11. Re: How to declare a global variable from a PL/SQL package
                              Dom Brooks
                              Billy,

                              Hmm - I know that's how the SQL ends up when it gets passed to the SQL engine - you get a bound value e.g. if this is in your package
                              SELECT * FROM my_table WHERE flag1 = my_package.constant_1;
                              What actually gets passed to the SQL engine is
                              SELECT * FROM my_table WHERE flag1 = :B1;
                              What I was unsure about was the actual mechanics of when this transformation happens.
                              Is it an optimisation that happens at compile time - m-code for example? Or is it just a runtime thing?
                              Maybe it's not something we can tell.

                              Do you get what I mean?

                              P.S. OP - Sorry for being a bit off-topic
                              • 12. Re: How to declare a global variable from a PL/SQL package
                                user4184769
                                DomBrooks, I don't think it is off-topic, I want to know that too.

                                In my case, I usually do the following to referrence the contants from other functions or stored procedures
                                PROCEDURE prc_data 
                                IS
                                ...
                                EXECUTE IMMEDIATE 'INSERT INTO my_target_table 
                                                                AS 
                                                                SELECT :1, :2, :3, :4, <some other data> 
                                                                  FROM my_source_table 
                                                                WHERE abc = :5'
                                                      USING my_const.default_zip_code,
                                                                my_const.default_xyz,
                                                                ...
                                however, I have to use binding valuables everywhere if I need to reference a default contant from my package.
                                • 13. Re: How to declare a global variable from a PL/SQL package
                                  152149
                                  There is a solution - just use pl/sql! That way the sql statements inside the pl/sql can refer to the constants you've declared in your package!

                                  By the way though, if you define constants in a pl/sql package, if you ever want to change them you'll have to recompile all the pl/sql code that references these constants. If you store them in a tabel you can change the values without having to recompile any code and as others have said the values will be visible in stand alone sql.

                                  Also, have get (and set) functions for constants/variables defined in other pl/sql packages is good practice - that way you control access to the variables/constants and gain slightly more indepedence for non-local code. Check out Steven Fuerstein's books on pl/sql.
                                  • 14. Re: How to declare a global variable from a PL/SQL package
                                    Billy~Verreynne
                                    DomBrooks wrote:

                                    What I was unsure about was the actual mechanics of when this transformation happens.
                                    Is it an optimisation that happens at compile time - m-code for example? Or is it just a runtime thing?
                                    PL and SQL are different languages and executed by different "run-time engines".

                                    So when PL sees a SQL statement like:
                                    SELECT * FROM my_table WHERE flag1 = my_package.constant_1

                                    It needs to make a call like any other client to the SQL runtime and do the whole cursor thing.

                                    PL however needs to parse that SQL statement first - else how will it know that the statement is SQL? Part of this PL parsing is PL discovering what of its variables and constants you are using in that SQL statement. It replaces these with bind variables and send the resulting SQL to the SQL engine to be parsed. I.e.
                                    SELECT * FROM my_table WHERE flag1 = :B1

                                    It then binds the bind variables using the PL variables and constants you've used. So it will bind PL variable my_package.constant_1 to bind variable B1

                                    So PL follows the very same steps as you do when using execute immediate or DBMS_SQL - PL also has to use the SQL call interface, pass a SQL statement (with bind variables), bind and execute.

                                    Only PL is clever enough to do this behind the scenes for us - seamlessly. Instead of us having to code every single SQL call using something like execute immediate (as is the case with other programming languages), PL has the savvy to allow us to code SQL directly as part of the PL source code, and use PL variables and constants inside that SQL code. It does the rest.
                                    1 2 Previous Next