PLS-00172: string literal too long when trying to optimize a long SQL statement
I have a single SQL statement that is very long that I am trying to optimizer (in this case, more then 500 lines). But in Tuning Lab, when I click the "Optimize" button, it starts to process, but after a second or two, I get the following error:
"The Parser did not identify the text as a valid SQL statement.
ORA-06550: line2, column22: PLS-00172: string literal too long
Invalid SQL statement. Verify that you have selected the right schema. Only one SQL statement can be optimized in this window. To analyze PL/SQL code or multiple SQL statements use the SQL Scanner."
The statement runs in SQL Plus, it is the right schema, and it is only a single SQL statement. Can the Tuning Lab handle a SQL statement this large?
This occurs when there is a very long SQL and "Run on server" is selected as the exection method. "Run on server" will construct a PL/SQL script to execute the SQL using DBMS_SQL package. This package cannot handle a long string literal in 10G or later.
WORKAROUND: Change the execution method to "Run on client". Go to the Options | Tuning Lab | Execution, Execution Method page. Select "Run on client".
STATUS: Waiting for fix in a future version of Quest SQL Optimizer for Oracle.