Format failure for MINUS within MERGE statement

Toad for Oracle & DevOps Toolkit
1

Toad for oracle failed to format this statement:

MERGE INTO person m USING (SELECT rperson.person_id person_id FROM person p MINUS SELECT person_id FROM person WHERE person_id = rperson.person_id ) s ON (s.person_id = m.person_id) WHEN MATCHED THEN UPDATE SET m.modify_dt = rTrn.modify_dt

WHEN NOT MATCHED THEN INSERT ( person_id ) VALUES ( s.person_id );

Can someome please help? Thanks! - Charlie

2

Charlie,

Here I can format your statement using default formatter options. Could you please post me the options you were using. You can copy them to the clipboard using the Copy options button on the top pane of the options gui.

Thanks,

Andre

3

When I first formatted the SQL, I had not ever changed any option in Toad. But when my package did not format correctly, I did change the options. I isolated and simplified one SQL statement which causes the problem.

I cannot find the “options button on the top pane of the options gui.” (See picture below). But I got the content of the option file and some other info for you.

C:\Users\guo\AppData\Roaming\Quest Software\Toad for Oracle\12.0\User Files\FmtPlus.opt

[Qp5FormatterOptions]

Version=252

OutputTabs=4

InsertTabs=1

IndentSize=4

RightMargin=160

LF_ConsecLFLimit=1

VariableCase=2

AssignAlignment=2

CommaStyle=2

AndOrOperatorPos=2

AddMulOperatorPos=0

ConcatOperatorPos=0

Tagline=0

How I formated the statement:

Highlight the SQL

right click

click on formatting tools -> format

Here is the version I’m using:

Toad for Oracle Xpert version 12.0.0.61

4

Using your options I obtained the following:

MERGE INTO person m
USING (SELECT rperson.person_id person_id
FROM person p
MINUS
SELECT person_id
FROM person
WHERE person_id = rperson.person_id) s
ON (s.person_id = m.person_id)
WHEN MATCHED
THEN
UPDATE SET m.modify_dt = rtrn.modify_dt
WHEN NOT MATCHED
THEN
INSERT ( person_id)
VALUES (s.person_id);

Is it that what it should be?

5

Andre:

That looks pretty neat! But mine is not working. What version of Toad for Oracle are you using? What specific steps did you use to do the formating?

Here is what I got:

MERGE INTO person m

USING (SELECT rperson.person_id person_id

FROM person p MINUS

SELECT person_id

FROM person

WHERE person_id = rperson.person_id

) s

ON (s.person_id = m.person_id)

WHEN MATCHED THEN UPDATE SET m.modify_dt = rTrn.modify_dt

WHEN NOT MATCHED THEN INSERT ( person_id ) VALUES ( s.person_id );

Thanks!

Charlie

6

It might be the remainder of your code. Since you’re selecting text it shouldn’t matter, but I don’t have the 12.0 source handy to confirm. Do you have any syntax errors upstream of your statement? Are all preceding statements valid SQL, SQL*Plus, or PL/SQL properly terminated with semi-colon where appropriate? What if you copy your merge to a new tab to isolate it; does it format then?

7

Indeed, I see that QP5 version 252 was used (internal release May 7, 2014), that one doesn’t digest this SQL, it barfs on the MINUS. This was fixed in 254.

8

Please go to Help|Support Bundle and send the contents of that window. Thanks.

9

Typo: May 7, 2013. But 252 is not the formatter version, it is rather the definition version of the options data. You should have a QP5.dll with a version greater than that. As of 254 all versions do format your statement.