6 Replies Latest reply on Nov 13, 2012 7:32 PM by 974042

    Variable refreshing in package header

    926353
      Hello Experts,

      Through a function in my package header I fill up a global variable. The function gets the value out of a table in my database:
      PV_FILENAME PARAMETERS.VALUE%TYPE DL$PARAMETERS.VALUE_BY_NAME('FILE_NAME');
      This works fine, however when I change the value in the table and commit the changes the problem begins.
      When I execute the following statement to check if the value changed:
      select DL$PARAMETERS.VALUE_BY_NAME('FILE_NAME') from dual;
      It shows me the new value...

      But the variable in the package still contains the old value. When I restart sql developer the variable adjusts.

      Does anyone know what I can do? It seems like the package is caching the value or something. Or is it obligatory to put global variables in the body?

      greets

      Edited by: iadgroe on May 21, 2012 6:37 AM
        • 1. Re: Variable refreshing in package header
          Arun Kumar Gupta
          How do you determine the package has the old value for variable?

          Post some sample code which re-generate this scenario.

          Regards
          Arun
          iadgroe wrote:
          Hello Experts,

          But the variable in the package still contains the old value. When I restart sql developer the variable adjusts.
          greets
          • 2. Re: Variable refreshing in package header
            926353
            Hello Arun,

            I can check this because the return value I use in the function is the name of an xml file (e.g. 'xmlfile_1.xml'):
            SELECT .....
            FROM XMLTABLE('/employees/employee' PASSING XMLTYPE(BFILENAME(PV_DIRECTORY, -->PV_FILENAME<--), NLS_CHARSET_ID('AL32UTF16')) 
            COLUMNS USERNAME VARCHAR2(20) PATH './name/userName' , TIMEREG XMLTYPE PATH './timeRegistration/days' ) H,
                XMLTABLE('days/day' PASSING H.TIMEREG COLUMNS TIMESHEET_DATUM
            In the above code I use the name to retrieve the xml file. I insert the data in the xml file into one of my own tables.

            When I change the value of the xml-name(e.g. 'xmlfile_2.xml') in my table that the function returns, the variable should reference another xml-file. However when I execute the procedure in my package it still insert data from the previous xml(xmlfile_1.xml) file into my table.

            I this clear enough? :-)

            Thanks a lot!
            • 3. Re: Variable refreshing in package header
              vijayrsehgal-Oracle
              Dear Iadgroe,

              you will have to reset the value of global variable or session is to be formed again.

              Hope this helps.


              Regards,
              Vijay.
              1 person found this helpful
              • 4. Re: Variable refreshing in package header
                Marwim
                Hello,

                if the value can change during the lifetime of a session, then you must not use a global variable, that is filled only when the package is initialized (when first called in a session).
                Is the table that stores the parameters big and how often is the variable called? Maybe you can simply call the function DL$PARAMETERS.VALUE_BY_NAME each time you need the value. Check out the DETERMINISTIC option for functions if you have at least 11g.

                Regards
                Marcus
                1 person found this helpful
                • 5. Re: Variable refreshing in package header
                  926353
                  Indeed the variable doesn't change in the body of the package so it is a constant value except when the package is called the first time, that's why made it a global variable. I will check out your tips thanks a lot ;-)
                  • 6. Re: Variable refreshing in package header
                    974042
                    What is the code to reset a global variable? Thanks for any help you can offer.