Refactor / Rename Identifier renames wrong code parts

Toad for Oracle - Beta
1

Hello,

I noticed that the function "Refractor / Rename Identifier" also renames parts of the code that have nothing to do with the variable.

Example code:

DECLARE
   id_person   persons.id_person%TYPE;
BEGIN
   id_person := getobjectid('PERSON');

   INSERT INTO persons(id_person, last_name, first_name)
        VALUES (id_person, 'Test', 'Test');
END;
/

If you rename the variable id_person to l_id_person, you get the following result:

DECLARE
   l_id_person   persons.l_id_person%TYPE;
BEGIN
   l_id_person := getobjectid('PERSON');

   INSERT INTO persons(l_id_person, last_name, first_name)
        VALUES (l_id_person, 'Test', 'Test');
END;
/

On the one hand, the column name is renamed in the declaration of the variable and on the other hand, the name of the column is renamed in the INSERT statement.

2

Still happening, here is a different example:

DECLARE
  dummy dual.dummy%TYPE;
BEGIN
  FOR cur IN ( SELECT * FROM dual )
  LOOP
    dummy := cur.dummy;
  END LOOP;
END;

DECLARE
  xxx_dummy dual.xxx_dummy%TYPE;  -- wrong
BEGIN
  FOR cur IN ( SELECT * FROM dual )
  LOOP
    xxx_dummy := cur.xxx_dummy;  -- wrong
  END LOOP;
END;
3

That code basically just does a global search and replace. There are probably cases where Dot+Variable should be replaced, so I am hesitant to change this code to exclude those.

When you want more control, you might want to do this instead:

  1. Double-click the word that you want to repalce
  2. CTRL+R to Invoke Find/Replace
  3. "Prompt On Replace" and then click "Replace All"

A similar option, with Toad 15.0 and newer:

  1. Double-click first occurrence of the word that you want to repalace
  2. CTRL+R to Invoke Find/Replace
  3. Click "Replace" to replace or "Find Next" to skip that one, and repeat until done.
4

I see. Thought that feature has a deeper understanding of the code.

While it will never be perfect, skipping the column list in an INSERT, the target columns in an UPDATE ( dummy = ... ) and everything where you do not allow Ctrl+Click in the other direction (cur.dummy) might be a good start. That would add some consistency.

Guess renaming the record member will never work without actually understanding the code:

DECLARE
  TYPE t_dummy IS RECORD (
    dummy PLS_INTEGER
  );
  dummy t_dummy;
BEGIN
  dummy.dummy := 1;
END;

Overall low priority for me, though.

5

The problem is that a search and replace, even if done with clever regular expressions, always hits its limits somewhere.
It probably only works reliably if you analyze the code with a parser and thus know the "meaning" of each individual code piece.

I use the function regularly, because we still have some legacy code. Most of the time everything works as intended, but I also had cases where texts were replaced in string literals (dynamic SQL). I only noticed this when the new version was productive. :wink:

6

With "Replace All", yes. But if you select the first occurrence, then CTRL+R, you can decide individually which occurrences you want to replace by clicking "Replace" or "Find Next".

Like this:
FindReplace

7

I found another issue:

Having this code:

DECLARE
   x   NUMBER;
BEGIN
   x := 0;
   test.x(x);
END;
/

After renaming x to y you get

DECLARE
   y   NUMBER;
BEGIN
   y := 0;
   test.y(y); -- <-- Procedure name is changed.
END;
/

I know that I can use search and replace, but from my point of view, the advantage I expect from the "Rename identifier" function is that I don't have to decide myself which code parts I have to modify.

I now use DataGrip for more complex codes, as it works very well:
Bildschirmaufnahme 2023-12-29 um 09.28.58.mov