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.