embedded functions in SQL generates empty lines when formatted

Toad for Oracle - Beta
1

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

2

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

3

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

4

but maybe it is not a formatter problem but a CTRL-ENTER problem

5

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.

6

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

7

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)

8

Sorry, this is a parser problem, we’ll fix that asap.

9

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.