i have some tasks to perform it monthly bases to post , view & analyze data from couple of tables and views
my works based on Gregorian calendar monthly bases
and the tables i deal with , having date fields in each one
for example , now i'm working to analyze data of November 2015
in our system , i found available table name periods
i added this filed to it
selected number check in (0,1)
when i bee in November , i update filed selected to 1 for year = 2015 and month = 11
update all other fields to 0
function st_month to return start_date from periods when selected = 1
function en_month to return end_date from periods when selected = 1
those 2 functions working well for me , i based my views to its value
also working well in oracle forms
no problem , it is ok
but the only problem i face is time tracking ,
when i use to select * from table1 where doc_date between st_month and en_month
is consuming more time than using direct date
select * from table1 where doc_date between '01-nov-15' and '30-ov-15'
in a trigger of one form it consume much time when the cursor in declaration based on those functions
but when i changed temporary like this
cursor my_curosr is select * from mytable where doc_date between v_st_date and v_en_date;
v_st_date := '01-nov-15';
v_en_date := '30-nov-15';
here , i found much different in speed when compile the trigger
i'm thinking to use global variable for those date values in many forms, and the questions are :
1- what is the correct way to use the global variables in forms ?
2-when i should start to assign the value to it : is when_new_form_instance ?
3-if global variable solve this issue , what about the database ? is there global variables in database ?
4- is my functions the best way or there is any better advisable one ?