SQL snippet executed from PL/SQL in editor does not display in the Edit SQL window correctly when working with a WITH clause.
After highlighting a SQL stamen from within a PL/SQL script, running with F9, it is suppose to load the snippet into a pop up Edit SQL window. When this happens for a WITH clause it incorrectly sets all the columns names as bind variables.
Sample:
Original SQL statement--
WITH some_const
AS (SELECT c.*, p.col1
FROM tbl1 c, tbl2 p
WHERE c.col2 = p.col2)
SELECT t.col3 col3, t.col4 cl4, r.col5 col5
FROM tbl3 t, tbl4 r
WHERE t.col4 = r.col4 AND t.col6 = r.col5 AND r.col6 = 'Z'
Incorrectly displayed statement--
WITH some_const
AS (SELECT c.*, p.col1
FROM tbl1 c, tbl2 p
WHERE c.col2 = p.col2)
SELECT :T_5 /*t.col3*/ col3,
:T_4 /*t.col4*/ col4,
:R_2 /*r.col5*/ col5
FROM tbl3 t, tbl4 r
WHERE :T_3 /*t.col5*/ = :R_1 /*r.col4*/
AND :T_2 /*t.col6*/ = :R_0 /* r.col6*/
AND :T_5 /*t.col3*/ = 'Z'
WORKAROUND:
none
STATUS:
Waiting for fix in a future version of Toad for Oracle.
Maybe related to but not limited to WITH clause in the package body.
Other similar issues:
F9 does not recognize bind variable (e.g. :var) -- https://support.quest.com/Search/SolutionDetail.aspx?id=SOL84370&category=Solutions&SKB=1
Execute embedded SQL with bind variables fails (issue with triggers) -- https://support.quest.com/Search/SolutionDetail.aspx?id=SOL83796&category=Solutions&SKB=1
© ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center