Q

Dynamic query failing with a single date condition

A reader asks Oracle expert Dan Clamage about a single date condition mucking up a dynamic query.

After a migration, a dynamic query fails if the user introduces only one condition and this condition contains

a date (datefield > 'YYMMDD'). If the condition is not a date, the query works. If the condition is a date and the user introduces a second condition, the query works, too. The error message is something like, "Can't get folder." I can't read the SQL code. Could you help me?

More answers from expert Dan Clamage

See how to use certain SQL statements for sorting

Clearing up confusion over a couple PL/SQL functions

See all of Dan Clamage's answers

When working with dynamic SQL, it really helps to be able to see the query being generated. I'd write it to dbms_output or utl_file, then examine or even run it from a tool like Toad. Be sure to use bind variables (preceded with a colon) where feasible. Be cautious in your use of implicit type conversions. If you're comparing a date variable or column to a string, perform an explicit conversion on the date, including the date format mask. Lastly, include the century portion of the date string. We didn't suffer through Y2K for nothing!

This was first published in August 2013

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close