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!
Related Q&A from Dan Clamage, Senior Oracle Developer, Bettis Atomic Power Laboratory
Expert Dan Clamage explains how to use SQL SELECT and SQL UNION ALL statements to sort and visualize a set of sales figures.continue reading
One reader asks expert Daniel Clamage about the PL/SQL to_date and to_char functions and how to properly convert date and string values.continue reading
Oracle PL-SQL expert Daniel Clamage answers a question about a problem with doing an “insert” in PL-SQL to create a table dynamically.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.