with function f return number as begin return 1; end; select f from dual
generates an empty line
/* Formatted on 01/07/2013 13:18:59 (QP5 v5.252.13127.32847) */
WITH FUNCTION f
RETURN NUMBER
AS
BEGIN
RETURN 1;
END;
SELECT f
FROM DUAL
while it is still a valid syntax in sqlplus 12.1 or as a script, it prevents you from using CTRL-ENTER
You are right, it looks like two statements. You can suppress the blank line by unchecking the option “blank line around function/procedure body”, but I guess you’ll want to keep that option checked. So I modified the behavior to get the following output for the following various cases:
WITH z AS (SELECT u FROM v)
SELECT f
FROM DUAL;
WITH z AS (SELECT u FROM v),
z2 AS (SELECT u2 FROM v2)
SELECT f
FROM DUAL;
WITH FUNCTION f
RETURN NUMBER
AS
BEGIN
RETURN 1;
END;
SELECT f
FROM DUAL;
WITH FUNCTION f
RETURN NUMBER
AS
BEGIN
RETURN 1;
END;
FUNCTION f2
RETURN NUMBER
AS
BEGIN
RETURN 1;
END;
SELECT f
FROM DUAL;
WITH FUNCTION f
RETURN NUMBER
AS
BEGIN
RETURN 1;
END;
z AS (SELECT u FROM v)
SELECT f
FROM DUAL;
WITH FUNCTION f
RETURN NUMBER
AS
BEGIN
RETURN 1;
END;
FUNCTION f2
RETURN NUMBER
AS
BEGIN
RETURN 1;
END;
z AS (SELECT u FROM v),
z2 AS (SELECT u2 FROM v2)
SELECT f
FROM DUAL;
Feel free to comment.
Thanks,
Andre
Hi Andre,
Thanks for the answer.
you still prevent using CTRL-ENTER when 2 functions exists
I would prefer
WITH FUNCTION f
RETURN NUMBER
AS
BEGIN
RETURN 1;
END;
FUNCTION f2
RETURN NUMBER
AS
BEGIN
RETURN 1;
END;
SELECT f
FROM DUAL;
with no empty line
but maybe it is not a formatter problem but a CTRL-ENTER problem
Yes, Ctrl-Enter doesn’t use the parser to determine where statements begin and end, because it’s often used to execute draft code written in the middle of other non-SQL or unvalid SQL.
This one does not format at all
SELECT /*+WITH_PLSQL */ * FROM (WITH FUNCTION f RETURN NUMBER AS BEGIN RETURN 1; END; SELECT f FROM DUAL)
not sure if you want to address this… I have requested the SQL Doc author to better document the WITH_PLSQL syntax
same for DELETE MERGE UPDATE INSERT
DELETE /+WITH_PLSQL/ FROM (WITH FUNCTION f RETURN NUMBER AS BEGIN RETURN 7788; END; SELECT * FROM emp WHERE empno=f)
Sorry, this is a parser problem, we’ll fix that asap.
CTRL+Enter should not be used for any purpose other than the draft example Andre mentions. For many long time Toad users it's habitual and should be treated like thumb sucking and stopped.
On 07/01/2013 11:27 AM, Andre Vergison wrote:
RE: embedded functions in SQL generates
empty lines when formatted
Reply by Andre Vergison
Yes, Ctrl-Enter doesn't use the parser to determine where statements begin and end, because it's often used to execute draft code written in the middle of other non-SQL or unvalid SQL.
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from Toad for Oracle - Beta notifications altogether.Toad for Oracle - Beta Discussion Forum
Flag
this post as spam/abuse.
