Skip to Main Content

SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Custom Format: Assigning string to target.input is causing undesired output.

User_CG9ABJul 6 2022 — edited Jul 7 2022

I routinely have to copy code from reporting software that uses substitution variables in the format of :obj.value. I am trying to build a custom format that changes the variable from :obj.value to :obj_value so that SQL Developer treats it as a bind variable. I have limited knowledge on arbori and have been scouring the web for additional documentation but so far have come up empty. So any help with this would be greatly appreciated.
Keep in mind this is just sample code. The code that I copy is generally hundreds of lines long with many substitution variables. I am just looking for a method to speed up my development.
My sample code

call dbms_output.put_line(:region.id);
/

select *
from regions
where
  region_id = :region.id;

My desired change after running the format would be

call dbms_output.put_line(:region_id);
/

select *
from regions
where
  region_id = :region_id;

My custom format code at this point is

FixBindVariables:
  runOnce
-> {
  var LexerToken = Java.type('oracle.dbtools.parser.LexerToken');
  var Substitutions = Java.type('oracle.dbtools.parser.Substitutions');
  var Token = Java.type('oracle.dbtools.parser.Token');
  
  var tokens = LexerToken.parse(target.input,true);
  var substitutions = new Substitutions(target.input);
  
  for(i = 0; i < tokens.length; i++) {
    if(
      tokens[i].type == Token.OPERATION
      && tokens[i].content == ':'
      && tokens[i+1].type == Token.IDENTIFIER
      && tokens[i+2].type == Token.OPERATION
      && tokens[i+2].content == "."
      && tokens[i+3].type == Token.IDENTIFIER
    ) {
      substitutions.put(tokens[i+2].begin,tokens[i+2].end,'_');
    }
  }
  
  target.input = substitutions.transformInput();
};

This is the output that I end up with in testing the custom format code.

call dbms_output.put_line (:region)id/select*
from
  regions
where
  regions
=
  region_id region_id ;

With my limited knowledge and the limited amount of documentation on the web, I am stuck. I am not even 100% certain this is possible.
Here are some of the sources that I have read through:
https://community.oracle.com/tech/developers/discussion/4336648/formatting-pl-sql-in-sqldev-20-2-0-unwanted-indentation-after-comment-solution
https://github.com/Trivadis/plsql-formatter-settings/blob/main/settings/sql_developer/trivadis_custom_format.arbori
https://www.salvis.com/blog/2020/04/13/formatting-code-with-sql-developer/
https://www.opensourceagenda.com/projects/plsql-formatter-settings
https://vadimtropashko.wordpress.com/
Thoughts, ideas, and solutions are greatly welcomed.

Edit: I failed to mention initially. I am using SQL Developer 22.2.0.173.
Edit2: Left out LexerToken line from arbori code.

Comments
Post Details
Added on Jul 6 2022
7 comments
197 views