[Bug] Comment Directive --Tab=Name uses first seen Tab= if multiple exist

[zitot]

-- Tab=Window 1
--select 1 from dual;

-- Tab=Window 2
select 2 from dual;

-- Tab=Window 3
select 3 from dual;

Expected Result: You should have two tabs, Window 2 and Window 3

Actual Result: Window 1, Window 3



It's a little annoying. As far as affects, here is an example where I am debugging a chain of CTEs. By commenting out the first select statement, the name used for CTE2+ was Window 1, which I didn't initially figure out.

With CTE1 as (select 1 from dual), CTE2 as (select 2 from dual), ...
-- Tab=Window 1
--select * from CTE1;
-- Tab=Window 2
select * from CTE2;
-- Tab=Window 3
select * from CTE3;

Strictly speaking, it uses the first comment directive -- Tab= that is seen AFTER the previous query which ended with a semicolon, best as I can tell. Then it looks for the next valid SQL query and applies that first comment directive it found. Don't know if this applies to other comment directive types or just this.

So, what do you think this results in?

-- Tab=Window 0
--select 0 from dual;
-- Tab=Window 1
select 1 from dual;
-- Tab=Window 2
--select 2 from dual;
-- Tab=Window 3
select 3 from dual;

 

[Marco Kalter]

This does indeed not work as expected. We'll fix it for the next patch release.

 

[zitot]

Thank you for the fix Marco ^^

Found an edge case. comment directives are allowed after a SQL query, or more technically, the query never ends until it hits a semicolon, so any comment directive can still be processed up to that point. I think this is fine. (To me the real issue was that the comment directive was not properly bounded before, here it respects the bounds of a SQL query by its semicolon so to me its all good)

--TAB=TEST3
select 3 from dual;
select 5 from dual
--TAB=Five

The manual says that comment directives should precede a SQL statement; however, they are currently allowed to be anywhere within the SQL statement (where the end is delimited by a semicolon outside of a comment). Personally, it allows users freedom to put the comment directive anywhere within said sql query, at the start or at the end. Although I don't think I would put mine at the end.

select 'A' as letter from dual; -- ignore, only present so that we have two tabs
--TAB=Kachow
--Excel=C:/Excel/Number-test.xlsx
select 1 as "number" from dual
--Excel=C:/Excel/test2.xlsx
--TAB=TEST1
;
--TAB=TEST2

Result: tab=TEST1 , if you save to excel it will save test2.xlsx and not Number-test.xlsx.

The only real issue imo is case like above where a user maybe doesn't know this, has some old code, didn't close the SQL query until later, and it runs into the second comment directive overwriting the first. Here it is easy to see what happens but if you have a random comment above maybe some CTE that used to be its own query then not so much. in my op that is a mix of user error and just a little better documentation needed.

Running PLSQL Developer Version 16.0.7.2172
Manual 16.0
7.12 comment directives:

You can use special directives in a comment before each SQL statement to control aspects of the query. For example:

-- Tab=All managers
select * from emp
where job = 'MANAGER';
-- Tab=Department 10
select * from emp
where deptno = 10;

Either way just bringing it to your(or the forum's) attention. if anyone relies on this it would technically be undocumented behavior, as the manual says directives should be in a comment before each sql statement. If the feature is kept maybe just update the manual. Or u can tighten the processor so it doesn't allow it.

 

[Marco Kalter]

We'll see if we can enhance this.