oracle form default where date error

hi
i have view pur_bsh
having inv_date date
when i sql :
select * from pur_bsh where
inv_date = '01-feb-15'

Open in new window

it return correct rows
but in form , i have non_database block "basic_block" & non_database item "d_from"
type date , format mask  "MM/DD/YYYY"
now , in when button pressed trigger i wrote
declare
d_f date;
d_t date;   
begin
		d_f := :BASIC_BLOCK.D_from;
	d_t := :BASIC_BLOCK.D_to;
		SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date = d_f');

	GO_BLOCK('pur_bsh');

	EXECUTE_QUERY;
	end ;

Open in new window


it return nothing
what may be the cause ?
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.

slightwv (䄆 Netminder) Commented:
>>inv_date = '01-feb-15'

If inv_date is a DATE data type, don't do this.  It causes an implicit data type conversion and is based on environment settings of default format mask.

ALWAYS do explicit data type conversions:
inv_date = to_date('01-feb-15','DD-mon-YY')

If inv_date has the time portion of the value populated you might need:
(inv_date >= to_date('01-feb-15','DD-mon-YY') and inv_date < to_date('01-feb-15','DD-mon-YY')+1)


I cannot help with the FORMS code specifically but I'm guessing what I posted above might help.  It is either the fields have the time portion of it is an implicit data type conversion issue.
johnsoneSenior Oracle DBACommented:
One other thing.

You mention 2 different date formats.

You say that 01-FEB-15 works and that in the form the date format is MM/DD/YYYY.  If you specify 02/01/15 with a date format of MM/DD/YYYY, what you are actually searching for is 02/01/0015.  I thought there was a change to make that a format mask error, but I just tried it in a 11 database and I did not get an error.

Anyway, explicit conversion is always the way to go.  You can never count on the client not changing the default format mask.
NiceMan331Author Commented:
i adjust the code like this :
d_f := to_date('01-feb-15','DD-mon-YY'); 

message(d_f);

	SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date >= d_f ');

Open in new window


the message return , 01-feb-15
but then error , enable to perform query
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

slightwv (䄆 Netminder) Commented:
Looking up the function shouldn't you use a bind variable?

I'm thinking this needs to be:
SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date = :d_f');

Notice the ':' in front of d_f.
NiceMan331Author Commented:
now i return all records (regardless the date creteria)
slightwv (䄆 Netminder) Commented:
>>now i return all records (regardless the date creteria)

I cannot help with the FORMs specific logic.

Does if return the correct subset if you hard-code the date?
SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date = to_date(''01/01/2011'',''MM/DD/YYYY'')');

Using a date that has know values in the table.

You also never did post back about the time portion of the dates being populated.

What does this return:
select to_char(inv_date,'MM/DD/YYYY HH24:MI:SS') from pur_bsh where rownum<11;
NiceMan331Author Commented:
in table data , i didnt find any time portion
the latest query return :
01/01/2015 00:00:00
01/01/2015 00:00:00
01/01/2015 00:00:00
01/01/2015 00:00:00
01/01/2015 00:00:00
01/01/2015 00:00:00
01/01/2015 00:00:00
01/01/2015 00:00:00
01/03/2015 00:00:00
01/03/2015 00:00:00
NiceMan331Author Commented:
SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date = to_date(''01/01/2011'',''MM/DD/YYYY'')');

Open in new window


this now return the correct records
how should i adjust it to include filed name instead of the date value ?
slightwv (䄆 Netminder) Commented:
>>i didnt find any time portion

Cool.  That should make things a little easier.

>>how should i adjust it to include filed name instead of the date value ?

From what I was able to Google, it should work with the bind variable example posted above:
SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date = :d_f');

What I'm not sure about is if you can just declare a variable and use it as a bind variable.  I'm guessing not since it returned all data, the WHERE was getting ignored.  My guess is it was generating some error before.  Now it is at least running but just not doing what you want.

I cannot find an exact example for this and again, I'm not a Forms person.
NiceMan331Author Commented:
ok , as a zone advisor , could you please invite some one who expert in forms ?
thanx
slightwv (䄆 Netminder) Commented:
I really cannot invite people.  You need to give the Experts some time to locate this question and respond if they can.  It may be the Forms person you need is on the other side of the World and is asleep.

If you cannot figure it out and no other Expert has posted by this time tomorrow, I can set this as Neglected.  That may alert some additional Experts.
johnsoneSenior Oracle DBACommented:
I don't know forms either.  But the examples that I saw didn't include the bind variable in the string.  They looked more like:

SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date = ' || :d_f);

But that doesn't seem correct to me.  The examples were using strings, so I would think you want:

SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date = to_date(''' || to_char(:d_f, 'mmddyyyy') || ''',''mmddyyyy'')');

At least based on the examples I saw that seemed to be the syntax.  Seems to defeat the purpose of a bind variable.
slightwv (䄆 Netminder) Commented:
>>  The examples were using strings, so I would think you want:

I saw those as well.  I didn't suggest them as a viable option because of the SQL injection possibilities of string concatenation.

Will it work, sure but I wouldn't use it.
johnsoneSenior Oracle DBACommented:
But, I'm wondering if that is how forms has to work.  Is that the required syntax for a bind variable in a forms call like that.
NiceMan331Author Commented:
i google , i found this in an arabic  forum
set_block_property('pur_bsh',default_where,'inv_date >=to_date('''|| :BASIC_BLOCK.D_from ||''',''dd-mm-rrrr'')');

Open in new window

and it works
but you note that it changed the format
1- in the : MM/DD/YYYY it didn't work , why ?
2-how i can adjust the above code to make it in between
johnsoneSenior Oracle DBACommented:
Did you try what I posted?  That is essentially the same thing.  I just did explicit conversions because the variable being used is a date already.
NiceMan331Author Commented:
yes , it works
could you please adjust it to be in between 2 dates
slightwv (䄆 Netminder) Commented:
>>wondering if that is how forms has to work.

I would be very disappointed if that were the case.  It may be but I would still be disappointed...

Back to the question.

I was able to locate this in Oracle Support:
https://community.oracle.com/message/11682144#11682144

My guess is the actual value will be coming from a Form field not hard coded in the block itself.  The note above shows how to reference the form field object as a bind-like variable.

If the form field is a varchar you would still need to to the date conversion

Try something like:
SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date = to_date(:pur_bsh.field_name,''MM/DD/YYYY'')');

adding the necessary form and field name.
NiceMan331Author Commented:
SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date = to_date(:pur_bsh.field_name,''MM/DD/YYYY'')');

Open in new window


this not works , the code of johnosn works with me
johnsoneSenior Oracle DBACommented:
I would rather go with the in line bind variable.  Much better way to go, I just didn't see any examples that showed how to do it.

You need to change :pur_bsh.field_name to the correct form field that you are referencing.  We don't know what your field names are.
slightwv (䄆 Netminder) Commented:
>>this not works

I assume you changed "pur_bsh.field_name" to the correct objects?

>>the code of johnosn works with me

Then I'll let him continue assisting.
NiceMan331Author Commented:
here is the code of sligtwv after adjustment
SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date = to_date(:BASIC_BLOCK.D_from,''MM/DD/YYYY'')');
 but your code works well
johnsoneSenior Oracle DBACommented:
I just posted what I found by searching around.  It didn't seem like the best way to do it.  I would certainly go with the information that slightwv found.  That would  be the accepted way to use a bind variable.

What is wrong with:

SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date = to_date(:BASIC_BLOCK.D_from,''MM/DD/YYYY'')');
NiceMan331Author Commented:
unable to perform query
johnsoneSenior Oracle DBACommented:
I don't think it is recommended, but this might work:

SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date between to_date(''' || :BASIC_BLOCK.D_from || ''',''mm/dd/yyyy'') and to_date(''' || :BASIC_BLOCK.D_to || ''',''mm/dd/yyyy'')');

The bind variables should be part of the string, they should not be concatenated.  It really should be this:

SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date between to_date(:BASIC_BLOCK.D_from,''mm/dd/yyyy'') and to_date(:BASIC_BLOCK.D_to,''mm/dd/yyyy'')');

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
slightwv (䄆 Netminder) Commented:
>>'inv_date between to_date

For this question there isn't an issue using BETWEEN because the dates do not have a time portion.  If you ever run into dates with times, you need to be careful using BETWEEN because you might not get all the data that is available.
NiceMan331Author Commented:
i learn something now
SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date between to_date(''' || :BASIC_BLOCK.D_from || ''',''mm/dd/yyyy'') and to_date(''' || :BASIC_BLOCK.D_to || ''',''mm/dd/yyyy'')');

Open in new window

the above code return same error : unable to perform query

SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date between to_date(:BASIC_BLOCK.D_from,''mm/dd/yyyy'') and to_date(:BASIC_BLOCK.D_to,''mm/dd/yyyy'')'); 

Open in new window


this works , but i learned something
the above return opposite records
i mean , when i select between   03/01/2015 and  03/03/2015
it return data between : 03-jan and 03-mar
which is wrong
finally i goes back to the previous code of johnson , and adjust it like this

 
SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date >= to_date(''' || to_char(d_f, 'mmddyyyy') || ''',''mmddyyyy'') and inv_date <= to_date(''' || to_char(d_t, 'mmddyyyy') || ''',''mmddyyyy'')');

Open in new window


this return the correct data , between 01-mar and 03-mar
slightwv (䄆 Netminder) Commented:
>>it return data between : 03-jan and 03-mar

This is what I was talking about when I mentioned explicit data conversions.

Something in your environment took 03/01 to by January 3rd instead of March 1st.

You need to look at the data type of BASIC_BLOCK.D_to and/or d_f.

Remember dates do not have a format until they are displayed so you need to know what you are dealing with and when.
NiceMan331Author Commented:
Ok , in general , can I use my own format within function to_date ?
Mark GeerlingsDatabase AdministratorCommented:
You can always use any explicit date format you like when you are converting string values to dates, or vice versa, as long as your format mask matches your data.  So, you can use format masks like:
MM/DD/YYYY
DD/MM/YYYY
DD-MON-YYYY
etc., as long as your literal data matches the format mask you choose.

But, you will always get into trouble (sooner or later) if you force Oracle to do implicit datatype conversions (with no format mask) like this:
SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date = d_f');
or this:
SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date = '||:d_f)
slightwv (䄆 Netminder) Commented:
>>Ok , in general , can I use my own format within function to_date ?

Mark summed it up nicely but to answer this direct question:  Yes.

Pick a format and make sure everything matches.  What format you choose is entirely up to you.  There is almost an infinite number of possibilities.

I would suggest you consult the documentation:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements004.htm#SQLRF00212
PortletPaulEE Topic AdvisorCommented:
for fear of confusing the matter, I would strongly suggest you stop using "ambiguous" date formats
(03/01/2015 is it month then day? or day then month?)  and don't rely on just YY for year either

try this format:

YYYY-MM-DD

it is the very widely recognized (and an ISO standard) e.g.

to_date('2015-03-01','YYYY-MM-DD')



absolutely no points please
NiceMan331Author Commented:
thanx for every experts participated in here
i see how the experts are working
no one hurry for the points , unless he provide the benifitical knowledge
thanx for maxwell also for his last point , and his date format is what i will use from now
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.