NiceMan331
asked on
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
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 ?
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
continuehere , 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 ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
---
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 ?
(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
---
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 ?
ASKER
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
Yes
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.
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.
ASKER
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')))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 ?
ASKER
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 ?
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 ?
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!
ASKER
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
OK, but also please look for some documentation or articles on how Oracle uses indexes
ASKER
when I have better mind then only I will try to read the documentation
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.
I don't know how or when you might "have [a] better mind" if you don't take time to read the documentation.
ASKER
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
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.
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.
ASKER
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
ASKER
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 ?