This content has been marked as final. Show 6 replies
How do you determine the package has the old value for variable?
Post some sample code which re-generate this scenario.
But the variable in the package still contains the old value. When I restart sql developer the variable adjusts.
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'):
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.
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
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!
Dear Iadgroe,1 person found this helpful
you will have to reset the value of global variable or session is to be formed again.
Hope this helps.
Hello,1 person found this helpful
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.
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 ;-)
What is the code to reset a global variable? Thanks for any help you can offer.