Table highlighting.

[mike]

Regarding, "The editor now highlights all table.column occurrences in a select statement when clicking on the table name in the statement"

I like this feature. It works in the SQL window. Could we get it in the program window also?

 

[Marco Kalter]

It works in all SQL and PL/SQL editors. If it does not work for you in a specific case, can you provide an example?

 

[mike]

CREATE OR REPLACE PACKAGE BODY BANANA AS

PROCEDURE MAIN IS

CURSOR c_junk IS
SELECT ppf.full_name
FROM per_people_f ppf
,per_assignments_f paf
WHERE ppf.person_id = 3
AND ppf.person_id = paf.person_id;

BEGIN
NULL;
END MAIN;
END BANANA;

 

[Marco Kalter]

Thanks. It seems that this is caused by the fact that this is a cursor declaration. We'll enhance this.

 

[Claus Pedersen]

Yes, this would be great to have fixed. Almost all my selects in packages are inside PL/SQL cursors, so this would be a great enhancement.

There are some issues with tables and aliases when the table name and the alias are both used in a select, e.g.:

SELECT employee.name,
       employee_boss.name
FROM   employee
JOIN   employee employee_boss ON employee_boss.empno = employee.boss

When I place the cursor on "employee" in line 3, employee.name and employee.boss are highlighted as expected, but also the second instance of the table in line 4 is marked. If I place the cursor on "employee" in line 4, I would expect nothing to happen, as this table is aliased as "employee_boss". Placing the cursor on "employee_boss" works as expected.

And a request:
one is never satisfied, I know, but I would request to be able to place the cursor on e.g. "employee_boss.name" and this would give the same highlight as if i placed the cursor on the table name "employee_boss" itself. This would make the table/column highlight even more useful.

 

[Marco Kalter]

The cursor issue has been fixed for beta 2. I have added others to the list of enhancement requests.

 

[Claus Pedersen]

Thanks. If possible, please fix the error with wrong marking of table with alias.

 

[Claus Pedersen]

There is also an issue with selects inside views.
Please fix this as well, so selects in views can be highlighted correctly.

There is also an issue with tables in e.g. sub-selects or unions when there is more than one instance of a table in two or more sub-selects or parts of an union. If you place the cursor on the table name in one sub-part of the select, all instances of that table are marked.
Example:

SELECT employee.name
FROM   employee
WHERE  employee.boss IS NULL
UNION
SELECT employee.name
FROM   employee
WHERE  employee.boss IS NOT NULL

If you place the cursor on table employee in line two, all lines are marked. Only lines 1-3 should be marked, as the employee table in line 6 is another instance of the table and these should be treated as two different selects.
Also, when cursor is on "employee.name" in line 1, the instance in line 5 is marked as well, even if it belongs to another select.
So there are still some few glitches to be looked into.

Otherwise, still a great feature, that I am sure will be appreciated by a lot of users.

 

[Marco Kalter]

We'll check it out.

 

[Stew Stryker]

I'm running today's Beta v 13.0.0.1872.

I've found that highlighting a SELECT statement doesn't work if it's the source of INSERT INTO new_table. For example:

INSERT INTO my_table
    SELECT a.id_number,
           a.street1,
           a.street2,
           a.street3,
           a.city,
           a.state_code,
           a.zipcode
      FROM address a

Or as the source for DELETE, like this:

DELETE FROM my_table t
 WHERE t.id_number IN (SELECT a.id_number
                         FROM address a
                        WHERE yadda-yadda)

Thanks!

 

[Marco Kalter]

It does indeed not work for sub-selects. We'll see if we can add this.

 

[Claus Pedersen]

Two more things to be investigated and hopefully corrected:

1) I have a select like:

SELECT *
FROM   user_objects
WHERE  EXISTS (SELECT NULL
               FROM   user_source
               WHERE  user_source.name = user_objects.object_name
               AND    user_source.type = user_objects.object_type
               AND    user_source.text LIKE '%UPDATE%')
AND    EXISTS (SELECT NULL
               FROM   user_source
               WHERE  user_source.name = user_objects.object_name
               AND    user_source.type = user_objects.object_type
               AND    user_source.text LIKE '%DELETE%');

When I place the cursor on "user_source" in 4, I would expect lines 4-7 to be highlighted. Also the lines 9-12 are highlighted, even if it is a separate select. Only when I place the cursor in line 9, lines 9-12 should be highlighted.

Also, if I place the cursor on e.g. "user_source.name" in line 5, the occurrence in line 10 is also highlighted, even if it is another select.

Please try to fix this.

2) When there are two or more occurrences of a table.column name in a select, they are all marked OK. But if you only have one instance of table.column name, nothing is marked.
E.g:

SELECT employee.name,
       employee.empno
FROM   employee
WHERE  employee.empno = 10;

If you place the cursor on employee.empno in line 2, line 2 and 4 are marked, because there are two occurrences.
If you place the cursor on employee.name in line 1, nothing is marked. I would like it marked, just to make sure, that this field is not used anywhere else. I know, I can deduct it from the fact that nothing is marked, but it is a very implicit way of showing it.

 

[Marco Kalter]

We'll check it out.

 

[Claus Pedersen]

OK, thanks

 

[Stew Stryker]

I checked this out in Beta 3 and it's better, but not perfect yet.

I found if I click on a table alias in an INSERT sub-select, it will highlight the fields, unless they're referenced within a function.

But the same query highlights correctly when it's just a SELECT.

Good luck!

 

[Marco Kalter]

We'll fix it.

 

[Claus Pedersen]

My select examples from 19.10, 21.10, 25.10 have all been corrected, thumbs up to the developers

My initial comment from 21.10 is not corrected, though. Selects inside views are not highlighted correctly:

CREATE VIEW my_tester AS
  SELECT employee.name
  FROM   employee
  WHERE  employee.boss IS NULL
  UNION
  SELECT employee.name
  FROM   employee
  WHERE  employee.boss IS NOT NULL;

Nothing is highlighted here. Please fix the same way as selects inside cursors have been fixed.

Also selects having the USING clause seem to confuse the highlighting:

SELECT *
FROM   user_tables
JOIN   user_tab_columns USING (table_name)
WHERE  table_name = 'ABC'
AND    user_tables.status = 'VALID'
AND    user_tab_columns.data_length = 20
AND    user_tab_columns.data_type = 'NUMBER';

When I mark "user_tab_columns" in line 3, nothing is marked. Please fix this.

Nice to have: Column table_name is highlighted when I place the cursor on "table_name" in line 3. This is OK. But when I mark "user_tables" in line 2, only line 5 is marked. I would expect line 3 to be marked as well as table_name is a column in table user_tables (as well as in user_tab_columns) because of the USING clause.

 

[Stew Stryker]

Claus,

Confirmed. It works fine with ON syntax, but not USING.

 

[Marco Kalter]

We'll check it out.

 

[Stew Stryker]

I confirm that Beta 4 fixed the issue I reported above:

I found if I click on a table alias in an INSERT sub-select, it will highlight the fields, unless they're referenced within a function.

 

[Claus Pedersen]

Beta 4:
Table/column marking inside view is partly working. I have the statement:

CREATE VIEW my_tester AS
  SELECT employee.name
  FROM   employee
  WHERE  employee.boss IS NULL
  AND    employee.name LIKE 'S%'
  UNION
  SELECT employee.name
  FROM   employee
  WHERE  employee.boss IS NOT NULL;

If I mark table name in line 3, all lines in first select are marked. This is OK. But if I place the cursor on "employee.name" in line 2, the same column in line 5 should be marked as well. This does not happen.

I have another select with (partially) the same problems:

SELECT *
FROM   user_tables
JOIN   user_tab_columns USING (table_name)
WHERE  table_name = 'ABC'
AND    user_tab_columns.data_length > user_tab_columns.column_id
AND    table_name LIKE 'A%'
AND    user_tables.status = 'VALID'
AND    user_tables.status != 'X'
AND    user_tab_columns.data_length = 10
AND    user_tab_columns.column_id > 3;

If I mark table in line 2, all lines using this table are marked. This is OK. If I mark the table name "user_tab_columns" in line 3, lines 5, 9, and 10 are marked. This is OK.
If I mark the column name "table_name" in line 3, nothing is marked. If I mark the column name "table_name" in line 4, line 3 and 4 are marked, but not the column in line 6. This is not OK. Does PLD wrongly assume that the column "table_name" is actually a table name and not a column name?
If I mark column "user_tab_columns.column_id" in line 5, the corresponding column in line 10 is not marked. This is not OK.
If I place the cursor in line 7, both lines 7 and 8 are marked. This is OK. But if I rewrite line 7 into " 'VALID' = user_tables.status" nothing in lines 7 or 8 are marked anymore. This is not OK.
It seems that if the column name is not the first part of the line, then the column name is ignored (e.g. line 5 and the rewritten line 7)?
Please try to fix these errors.

 

[Marco Kalter]

We'll check it out.

 

[Claus Pedersen]

I have a view definition like the following:

CREATE FORCE VIEW tester AS
  SELECT dual.dummy,
         dual.dummy AS dummy1
  FROM   dual;

When I place the cursor on dual in line 4, nothing is marked. If I remove the FORCE clause, it works.
Please make it work with the FORCE keyword.

If I place the cursor on line 2, nothing is marked. If I remove the "AS dummy1" in line 3, lines 2 and 3 are marked.
Please fix this.

 

[Marco Kalter]

This is fixed for beta 5.

 

[Claus Pedersen]

OK, thanks.

Please note that I reported 2 separate errors.

 

[Claus Pedersen]

The 'FORCE' issue is solved in beta 5.
Thanks to the developers.

The 7. November, I wrote:

If I place the cursor on line 2, nothing is marked. If I remove the "AS dummy1" in line 3, lines 2 and 3 are marked.

This is still a problem in beta 5, as well as the issues in my first post from the 6. November.

 

[Claus Pedersen]

Beta 5, the following select can not be marked:

SELECT *
FROM   user_tables
WHERE  user_tables.pct_used IS NULL
       OR
       user_tables.pct_used = 10

When I mark table name in line 2, lines 2, 3 and 5 are marked.

If I place the cursor on line 3, nothing is marked.

If I replace the code "IS NULL" in line 3 with e.g. "= 20", both lines 3 and 5 are marked.

I also have the code:

CREATE PROCEDURE tester IS
  CURSOR my_cursor IS
    SELECT NULL
    FROM   user_tables
    WHERE  user_tables.table_name = 'ABC';
BEGIN
  NULL;
END;

When I place the cursor on table name "user_tables" in line 4, nothing is marked. If you move the select away from the cursor, it works fine.

Please fix both issues.

 

[Marco Kalter]

We'll check it out.

 

[Claus Pedersen]

This is probably a sub-case of the second select statement I posted 6. November, but the following select can not be fully marked:

SELECT user_tables.table_name,
       trim(user_tables.table_name)
FROM   user_tables;

If I place the cursor on the table name in line 3, lines 1+2+3 are marked.
This is OK.

If I place the cursor on the table/colum name in line 1, line 1+2 are marked.
This is OK.

But I I place the cursor in line 2, nothing is marked. I would expect lines 1+2 to be marked.
This should be corrected.

It seems like if you have a parenthesis before the column name, the marking does not work. If I replace "trim(user_tables.table_name)" with just e.g. "(user_tables.table_name)" it also doesn't work.

 

[Marco Kalter]

We'll fix it.

 

[Claus Pedersen]

Thanks a lot

 

[Claus Pedersen]

Here we go again

I have the following cursor:

CURSOR cur_dual (param_dummy IN dual.dummy%TYPE) IS
  SELECT NULL
  FROM   dual
  WHERE  dual.dummy = param_dummy;

When I mark the table name in line 3, lines 1, 3 and 4 are marked. I would not expect "dual.dummy%TYPE" to be marked, as this is a part of the parameter declaration and not a part of the select.

Please fix this as well.

 

[Marco Kalter]

We will fix this.

 

[Claus Pedersen]

Beta 6, a lot of the errors in the posts above have been corrected. Thank you to the developers.

There are still some glitches left to be corrected.

My example no. 2 from 6. November is still not working. If I place the cursor on "user_tab_columns.column_id" in line 5 or 10, nothing is highlighted.

Also my comment from 13. November is still not working.

I have some further examples that are not quite right yet:

Ex. 1):

SELECT CASE WHEN 1=1 THEN dual.dummy END,
       CASE WHEN 2=2 THEN dual.dummy END
FROM   dual

When I place the cursor on "dual.dummy" in line 1, the keyword END is highlighted (!)

Ex. 2):

CURSOR my_cursor (param_dummy IN dual.dummy%TYPE) IS
  SELECT dual.dummy
  FROM   dual
  WHERE  (dual.dummy IN (1,2) OR
          dual.dummy IN (3,4))
  AND    dual.dummy = param_dummy
  AND    (dual.dummy = my_package.my_function AND 1=1)
  AND    (1=1)

When I place the cursor on "param_dummy" in line 1, the keyword IN is highlighted in lines 4 and 5, as if it was an alias.
When I place the cursor on "dual.dummy" in lines 2 or 6, only these two lines are highlighted.
When I place the cursor on "dual.dummy" in lines 4 or 5, the keyword IN is highlighted.
When I place the cursor on "param_dummy" in line 6, the keyword AND is highlighted in lines 7 and 8
These are all errors.

Ex. 3):

SELECT *
FROM   user_tables
WHERE  user_tables.table_name = 'A'
       OR
       EXISTS (SELECT '?'
               FROM   dual
               WHERE  user_tables.table_name = 'B')

When I place the cursor on "user_tables.table_name" in line 3, lines 3 and 7 are highlighted. This is OK.
When I place the cursor on "user_tables.table_name" in line 7, nothing is highlighted. This is an error.

Ex. 4):

SELECT nvl(dual.dummy,1),
       nvl(dual.dummy,2)
FROM dual

When I place the cursor on "nvl" in line 1, lines 1 and 2 are highlighted. This is an error.

Ex. 5):

SELECT dual.dummy FROM dual

I write the select above in a new SQL window editor and do not type space or new line or similar after the last letter.
When I place the cursor on the last "dual", nothing is highlighted.
If I enter e.g. a space or semicolon after the last letter on the line, it works.
Not a biggie because when you are aware of it, there is a work-around, but please fix this as well if possible.

Hmm, now I can not reproduce this last issue no. 5 anymore. I will write again if I can reproduce at will.

 

[Marco Kalter]

We'll check it out.

 

[Claus Pedersen]

Beta 7, all the issues from the 19.November post seem to be solved.

Thanks to the developers.

I was able to reproduce the issue no. 5 from the 19. November post. When you type

SELECT dual.dummy FROM dual;
SELECT dual.dummy FROM dual

in a new editor with no space or similar after the last letter, the last line can not be highlighted. The first line works fine.

Please correct, so line 2 can be highlighted.

 

[Claus Pedersen]

Beta 7, I have the select:

SELECT nvl(user_tables.blocks,0) AS nvl_blocks,
       user_tables.blocks        AS blocks_aux,
       user_tables.blocks
FROM user_tables

When I place the cursor in line 1, lines 1 and 3 are highlighted.
Why is line 2 not highlighted? I know it is aliased, but so is line 1, so why is this line 1 highlighted? Why does highlighting on line 1 and 2 work differently?

My last remark in the post from 28. October is still valid.
I find it contra-intuitive, that 'singleton' columns are not highlighted. E.g. in the select "select dual.dummy from dual". When cursor is on "dual.dummy", nothing is highlighted. Why? In all other instances (variables, parameters etc.) they are always highlighted, even if that variable or parameter is not used elsewhere. This is a visual help to identify that a variable or column is used in one place only.

 

[Marco Kalter]

I was able to reproduce the issue no. 5 from the 19. November post. When you type

SELECT dual.dummy FROM dual;
SELECT dual.dummy FROM dual

in a new editor with no space or similar after the last letter, the last line can not be highlighted. The first line works fine.

Please correct, so line 2 can be highlighted.

Indeed. We'll fix it.

 

[Marco Kalter]

Beta 7, I have the select:

SELECT nvl(user_tables.blocks,0) AS nvl_blocks,
       user_tables.blocks        AS blocks_aux,
       user_tables.blocks
FROM user_tables

When I place the cursor in line 1, lines 1 and 3 are highlighted.
Why is line 2 not highlighted? I know it is aliased, but so is line 1, so why is this line 1 highlighted? Why does highlighting on line 1 and 2 work differently?

If you use an alias for a column, only the alias names are highlighted. This is currently how the algorithm works.

 

[Marco Kalter]

I find it contra-intuitive, that 'singleton' columns are not highlighted. E.g. in the select "select dual.dummy from dual". When cursor is on "dual.dummy", nothing is highlighted. Why? In all other instances (variables, parameters etc.) they are always highlighted, even if that variable or parameter is not used elsewhere. This is a visual help to identify that a variable or column is used in one place only.

The PL/SQL variable highlighting and table.column highlighting are indeed inconsistent here. We'll check it out.

 

[Claus Pedersen]

OK, thanks

 

[Claus Pedersen]

Sorry to be such a kill-joy, but I have another error in beta 7.

I have the select

SELECT user_tables.table_name,
       CASE WHEN EXISTS(SELECT NULL
                        FROM   user_tables user_tables_aux
                        WHERE  user_tables_aux.table_name = user_tables.table_name)
            THEN 1 ELSE 0 END AS table_exists
FROM   user_tables
WHERE  user_tables.table_name = 'ABC';

When I place the cursor on "user_tables" in line 6, lines 1, 4, and 7 are highlighted. This is by design.

When I place the cursor on "user_tables.table_name" in line 7, lines 1,4, and 7 are highlighted. This is also by design.

But when I place the cursor on "user_tables.table_name" in line 1 or 4, nothing is highlighted.
Please fix this.

 

[Claus Pedersen]

And again (sorry):

SELECT *
FROM   user_source
WHERE  user_source.name = my_package.my_constant OR
       user_source.name = my_package.my_constant

When I place the cursor on "my_package.my_constant", lines 3 and 4 are highlighted, even if my_package.my_constant is not a column/table name. This is not correct.

I know, that the code is not parsed towards objects in the database, but when a constant/function like my_package.my_constant is referenced without a corresponding table named my_package in the select, then PLD should be able to identify the use of this constant/function as not being a table/column name.

 

[Marco Kalter]

We'll check it out.

 

[rbrooker]

@Claus ... you should be a tester on their payroll `

 

[Claus Pedersen]

Beta 8, select from 28. November still does not work:

SELECT user_tables.table_name,
       CASE WHEN EXISTS(SELECT NULL
                        FROM   user_tables user_tables_aux
                        WHERE  user_tables_aux.table_name = user_tables.table_name)
            THEN 1 ELSE 0 END AS table_exists
FROM   user_tables
WHERE  user_tables.table_name = 'ABC';

When I place the cursor on line 1, nothing is highlighted. It is better than in beta 7 (line 4 and 7 work now), but not quite perfect yet.

Otherwise most other issues seem to be fixed. Thanks a lot to the developers.

 

[Marco Kalter]

For line 1 it does indeed not seem to work. We'll fix it.

 

[Claus Pedersen]

Thanks

 

[Claus Pedersen]

It would be nice if highlighting also worked for selects like

SELECT dual.dummy,
       dummy
FROM   dual
WHERE  dummy > 'A'

If cursor is on line 3, only line 1 is highlighted, even if lines 2 and 4 also contains this column.
If cursor is on line 2, line 4 is highlighted and vice versa.
It would be nice if line would also be highlighted in this case.

 

[Marco Kalter]

I have added this to the list of enhancement requests.

 

[Claus Pedersen]

My example from 3. December seems to be solved in beta 9. Thanks.