couldn't find the description of wwv_flow.g_package_instantiated
Winston, May 24, 2004 - 12:43 am UTC
I searched Oracle HTML DB user guide and HTMLDB discussion forum but I couldn't find any details on wwv_flow. Can you point me to any docs on wwv_flow?
May 24, 2004 - 10:19 am UTC
To answer your direct question, this global is not documented because you don't really need it (I'll explain that in a second). However, you can easily look at the package spec to look at all of the globals by running the folling query from SQL Plus:
select text
from all_source
where name='WWV_FLOW'
AND TYPE='PACKAGE'
ORDER BY LINE;
The real answer is to add this to the footer of any of your regions:
#TIMING#
For example:
This report took #TIMING# seconds.
This is documented in the users guide.
Why is Tom doing something different? When this was added to AskTom, the #TIMING# approach was likely not implemented yet.
Thanks,
Tyler
HTML DB rocks!
Winston, May 25, 2004 - 1:20 am UTC
Thanks a lot for your explanation and I appreciate your sharing the history and knowledge of HTML DB.
I did look at WWV_FLOW source. It includes:
----------------------------------------------------
-- Optimization and performance feedback information
--
......
g_package_instantiated number;
......
I also looked up #TIMING# in the User Guide:
#TIMING# shows the elapsed time in seconds used when rendering a region.
You may use this for debugging purposes.
I just tried out both #TIMING# and g_package_instantiated and compared the result with the debug-on-mode information, both worked well. I had to creat a PL/SQL region in order to use g_package_instantiated.
#TIMING# is very useful for reporting reporting region's elapsed time, but if a HTML page contains multiple regions and we want to show the total elapsed time of the page we still need to use g_package_instantiated.
I assume that g_package_instantiated is the point of time when the entire page began.
Please verify.
May 25, 2004 - 10:08 am UTC
OK, I believe we are in undocumented territory here. In the package body "elaboration code" section, the very first line is:
g_package_instantiated := dbms_utility.get_time;
This will be run before any other code in the package and only once per session. In, short, yes, this is the time when the page was first requested. So Tom's technique of:
htp.p(to_char((dbms_utility.get_time -
wwv_flow.g_package_instantiated ) * .01,'999990.00'));
will give you the time for the whole page, asuming of course you display it at the end of the page.
Thanks,
Tyler
Should not rely upon global variables
Joel, May 25, 2004 - 11:43 am UTC
Winston,
As a recommendation, you should not rely upon any undocumented global variables in your application. They are subject to change (hence, why they're undocumented).
You could easily approximate this logic via two application-level processes, that compute Before Header and After Footer.
Thank you
Winston, May 25, 2004 - 3:53 pm UTC
Thanks a lot to Tyler & Joel for the the follow-ups!
I created an application-level Item and After Footer processes and I could display the elapsed time for all the pages in my Application without using the undocumented global variables. The elapsed time difference is only 0.07 Seconds.
I got a minor issue. I had to use Before Header Computation via PL/SQL expression to initilize that application item like (dbms_utility.get_time). I couldn't use Before Header Process to set it like below
begin
nv('F102_X'):=dbms_utility.get_time;
end;
I found the problem
Winston, May 26, 2004 - 1:52 am UTC
I was misled by the Table 7-9 on Page 7-48 of HTML DB User Guide, it tells me that I should refer to number type item F102_X as nv('F102_X') in PL/SQL while down a little bit on the same page it shows us to refer to that item as :F102_X in the process's PL/SQL block.
Now I am able to do what Joel suggested "You could easily approximate this logic via two application-level processes, that compute Before Header and After Footer."
May 26, 2004 - 8:38 am UTC
Winston,
You're correct, this is misleading in the documentation.
The functions v() and nv() only return a value, they cannot be used as an assignment of a value to an item in session state. To assign a value to an item, you can use the bind variable syntax in PL/SQL in an application-level process (as you have done), or you can define an application-level computation.
So there's really two ways you could have approximated this logic:
1) Before Header and After Footer processes (as you have done)
2) Before Header and After Footer computations
If you wished to do #2, you could define this as computations of type PL/SQL Expression, with a compuation on F102_X Before Header PL/SQL Expression of:
dbms_utility.get_time;
and the computation on F102_X After Header PL/SQL Expression of:
dbms_utility.get_time - :F102_X;
Joel
Actually there are four (or five) ways to do this
Winston, May 26, 2004 - 1:05 pm UTC
1) Before Header and After Footer processes (I tried this after I read the User Guide carefully and figured out the problem)
2) Before Header and After Footer computations
3) Before Header computations and After Footer processes (The 1st working approach I used)
4) Before Header processes and After Footer computations
and ...undocumented way....
Thanks a lot, Joel! Your explanation is really helpful!