How To Use Global Variable In Oracle Forms & Databse

hi
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
having fields
years number
month number
start_date  date
 end_date  date

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
declare
v_st_date date;
v_en_date date;
cursor my_curosr is select * from mytable where doc_date between v_st_date and v_en_date;
begin
v_st_date := '01-nov-15';
v_en_date := '30-nov-15';
open my_cursor
continue

Open in new window


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 ?
NiceMan331Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

flow01Commented:
I assume both functions have no argument and there is an index with column doc_date for mytable.

0.  You are not measuring the speed of compilation of the trigger (you do that creating your fmb or fmx), but the speed of execution of the trigger
1.  :global.v_st_date := '01-nov-15';  --
     be sure to assign a value to the global before reading is
     and assign dates with a format  to_date('01-nov-2015','dd-MON-yyyy');
2.  depends on the way you want to change the value
      :global.v_st_date :=  st_month;
     When assigned in the when_new_form_instance it keeps that value even if you change the selected   row in another session, so you will use the 'old' value as long as you don't leave the form.
 If you need other behaviour assign the value immediat before the select is executed.
2a.  An oracle forms global variable is like a VARCHAR2(255)  item : you can assign a date value to it, but it's not a date anymore and depending on your default date-format you can lose information.
3.  there is not exact a global variable in the database , but there are different ways of creating the functionality  (read more about SYS_CONTEXT or use packages with a set-procedure and get-function (I think I used the option in another of your questions)
4a.  You can use your function : you probably get a better performance if you use the following code
declare
v_st_date date;
v_en_date date;
cursor my_curosr is select * from mytable where doc_date between v_st_date and v_en_date;
begin
v_st_date := st_month;
v_en_date :=  en_month';
open my_cursor
-- it is always guessing on exection plans with no real information but with  the assumption there is an index on doc_date , that index is not used if you compare doc_date with a function , resulting in a full table scan and the index will be used if you compare it with a value in a bind-variable (= using v_st_date)
4b Using and modifying an existing table somewhere 'found' is taking the risk of changing functionality of your application on another place.   If not sure than created a table selected_period of your own.
And let the functions return a date-format  !!! if they don't already.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark GeerlingsDatabase AdministratorCommented:
I agree with what flow01 wrote.  The problems you describe are *NOT* related to the amount of time needed to *COMPILE* a trigger.  They are related to the time needed to *EXECUTE* a trigger or a forms query.

"1- what is the correct way to use the global variables in forms ?"
No, global variables are not your main problem here, nor are they the best solution to this problem.  Global variables in Oracle Forms are a useful way to pass values between forms, or to hold a value in a form but outside of any/all blocks in the forms.  These can help solve some problems in forms, but not the performance problem you described here.

"2-when i should start to assign the value to it : is  when_new_form_instance ?"
Since these are *NOT* useful in solving the performance problem you have here, this question does not apply  to your problem.

"3-if global variable solve this issue , what about the database ? is there global variables in database ?"
No, forms global variables do not solve this problem.  Yes, you can create PL\SQL package variables in the database.  These can work similar to forms global variables.  They can help solve some problems, but not this performance one.

"4- is my functions the best way?"
No
 "or there is any better advisable one ?"
Yes.  Write queries that allow Oracle to use existing indexes on the tables, based on values provided in the "where" clauses of your queries and do *NOT* force Oracle do to implicit data type conversions!  If necessary, you can add index(es) to support your query/ies.  Try to make sure that your queries provide bind variables in the "where" clauses in the same datatype as the column that they refer to.  

For example, in this sample that you provided:
declare
v_st_date date;
v_en_date date;
cursor my_curosr is select * from mytable where doc_date between v_st_date and v_en_date;
begin
v_st_date := '01-nov-15';
v_en_date := '30-nov-15';

First, make sure that the doc_date column of the table is a "DATE" column (and not a VARCHAR2 or NUMBER, for example).

Then, when you assign a varchar2 value to this "date" variable, use "to_date" and an explicit format mask like this:
v_st_date := _to_date('01-nov-15','dd-mon-yy';
or:
v_st_date := _to_date('01-11-15','dd-mm-yy');

Finally, make sure this "doc_date" column is indexed, either in a single-column index, or as the first column in a multi-column index.
NiceMan331Author Commented:
I assume both functions have no argument and there is an index with column doc_date for mytable.

Finally, make sure this "doc_date" column is indexed,

the main 2 tables i', dealing with are master/details
m_trans , d_trans
both of them having basicly 3 fields for dates
year  number
month number
doc_date date
i don't know what is the benifit of using year & month numbers as well we have date field , but anyhow , when i search for indexes i found
m_trans   indexing :        year & month
d_trans indexing              year,month & doc_date
my queries based on combination between m_trans & d_trans
i'm using doc_date because i worry of the true value of year & month and how the system insert those value , for me i trusted the value of doc_date more cause user is in-forced to fill in doc_date , but not all forms of our ERP system having fields of year & month , some of them , i worry if some values out of range
so , do you advice to skip doc_date as not indexed in m_trans and use condition based on year & month ?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

flow01Commented:
If you don't trust the values: check.
(select year, month , doc_date, ...
 from d_trans where
(year is null or month is null or doc_date is null)
or
not ( year = to_number(to_char(doc_date,'YYYY')) and month =  to_number(to_char(doc_date,'MM')))
---
But as doc_date is the last column  of the index, I doubt if the difference you reported is based on using the index or not.    
How was the performance using the code I mentioned ?
NiceMan331Author Commented:
How was the performance using the code I mentioned ?
you mean this one :
declare
v_st_date date;
v_en_date date;
cursor my_curosr is select * from mytable where doc_date between v_st_date and v_en_date;
begin
v_st_date := st_month;
v_en_date :=  en_month';
open my_cursor

Open in new window

flow01Commented:
Yes
Mark GeerlingsDatabase AdministratorCommented:
If you "worry of the true value of year & month" but those are the columns that are indexed, you have a problem!  If you don't have data integrity, then performance is not very important.  Who cares if the query runs fast, but the data isn't believable or isn't complete?

You first need to determine which columns have data that you can believe.  Then you need to make sure that those columns are indexed.  Then you adjust your queries to refer to those indexed columns with bind variable values in the same datatype as the database columns.  If you do that, you queries will perform quickly.
NiceMan331Author Commented:
How was the performance using the code I mentioned ?
no difference , same performance
then , i checked the value of year * month as you advice
(select year, month , doc_date, ...
 from d_trans where
(year is null or month is null or doc_date is null)
or
not ( year = to_number(to_char(doc_date,'YYYY')) and month =  to_number(to_char(doc_date,'MM')))

Open in new window


no rows selected
great
then i changed the where clause to be based on both current year & month
the result is wonderful
very fast

Who cares if the query runs fast, but the data isn't believable or isn't complete?
true logic , but as now i checked the data is correct so i think i have to continue using where based on year & month
but i still don't know what is the useful of using year & month skipping doc_date ?
i think they put a trigger on the table to update the value of year & month on every type of transactions
but the worry now , what if the assumed trigger fail ?
so , do i need to check the value of year & month every time to trust the data ?
NiceMan331Author Commented:
and then let me understand something about the indexes
when i view from Toad the indexes of both tables
i found many indexes names under each table
and under each index there are many columns
some columns are repeated in more than an index name under same table
what does it means ?
Mark GeerlingsDatabase AdministratorCommented:
Yes, it is common in many Oracle systems for a table to have multiple indexes, and for some columns in a table to be included in multiple indexes.  This question from you indicates that your understanding of how Oracle works is very incomplete.  If you don't understand how Oracle uses indexes to help queries execute efficiently, you will *NOT* be able to write Oracle queries that perform efficiently!
NiceMan331Author Commented:
yes you correct , I'm not understanding indexes , and I'm not feel shame about my lack knowledge , and that is why I'm subscribing to EE is to learn
Mark GeerlingsDatabase AdministratorCommented:
OK, but also please look for some documentation or articles on how Oracle uses indexes
NiceMan331Author Commented:
when I have better mind then only I will try to read the documentation
Mark GeerlingsDatabase AdministratorCommented:
But you are trying to write Oracle queries or programs now without a good understanding of how Oracle indexes work or how Oracle uses indexes?  That looks to me like a big waste of time, and/or something that will leave you frustrated.

I don't know how or when you might "have [a] better mind" if you don't take time to read the documentation.
NiceMan331Author Commented:
I don't know how or when you might "have [a] better mind"
markger
when you characterized me by "un_understanding" , i kept silent
i just tried to slow & slow my words till put my self under the ground in purpose to draw your attention that you already injure my feeling as you spoof me
but , i found you continue going ahead in same way
this is the first time i heard such expressions  from  professional expert of EE
for me , i admit , i'm not expert , i'm just beginner in Oracle , i have lack of knowledge of too many things in oracle , and that's why i'm here , i want to learn ,
and i think this is not a sin
so , you either explain to me , give me an answer , or let any other expert to continue explaining to me what is the useful of multi indexes in one table
i remember , sdstubar , slightwv , flow01 , paul maxwell , johnosn ,, and others ,, i asked them before too many things which i don't know or understand , they explain to me well without injuring me , in spite of that was very beginner level of  knowledge for them
Mark GeerlingsDatabase AdministratorCommented:
I've tried to help you in a number of questions that you have posted on this site.  But yesterday when you indicated that you are not clear on how indexes work in Oracle, that really surprised me.  It simply will not be possible for you to write queries in Oracle that perform efficiently if you do not understand Oracle indexes.

In this question, you described a somewhat complex problem that you have, and you mentioned some ideas you have that may help (Forms global variables, PL\SQL functions, etc.).  But using those objects in Oracle without an understanding of how indexes work is like trying to build the walls or put a roof on a building, when you haven't prepared the foundation.

To understand Oracle, you need to start with the basics.  That is: tables and indexes.  Next, learn how views can be used to help simplify your forms or reports significantly.  Only after you understand these database objects should you attempt to change (or write) forms or programs.
NiceMan331Author Commented:
there is always big difference between : the advice you want to submit , and the way of submitting the advice whether it formatted in a good words or in a bad words , as you sometimes likes to gives your children the correct advice , if you submit those advice by rising your voice  and shouting to them , or , by simple , kind , and polite word's ,   not both ways are same in listening , the 1st one is bad and not able to be listen by then , while the second one is able directly to be listen and apply
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.