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
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!
Dig Deeper on Oracle and SQL
Related Q&A from Dan Clamage
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