Suggestion: Transform selected text in editor with (package) function call?

[guttormvik]

Use-case:
In my pl/sql packages I have selects that use package constants instead of literals.
When I want to test these, I copy them to a SQL window, and then I have to manually replace the package constants with the literals.
I'd like to automate this search/replace somehow.

Original, and most generic idea was; What if PL/SQL Developer allowed me to add entries to the Selection submenu, that calls any oracle (package) function and replaces the selection with the result? I could then write my own multi-value search/replace function.

Or, is this way of referring to package constants in pl/sql selects so normal that maybe there should be a 'Copy from PL/SQL' that did this automatically?

Example:
select r.*
from t_reservation r
join t_person p on p.dw_id = r.dw_id
where r.reservation_status reservation_status.cancelled
and p.person_status = person_status.active;
->
select r.*
from t_reservation r
join t_person p on p.dw_id = r.dw_id
where r.reservation_status 'C' /*reservation_status.cancelled*/
and p.person_status = 'A' /*person_status.active*/;

 

[Scovron]

Definitely +1 from me on this topic.

 

[Marco Kalter]

Note that if you mark a select, insert, update or delete statement in a package body, right-click on it, and select "Test", you will already get a conversion of the PL/SQL variables, parameters, and constants to bind variables in a new Test Window. You can now run this in the Test Window, or copy the SQL statement to a SQL Window. The SQL Window in PL/SQL Developer 16.0 now supports bind variables as well.

I noticed that the bind variables names are the same as the PL/SQL Variable names, except when they are prefixed with the package name. We'll enhance this and will translate package.variable to ackage_variable if possible.

 

[guttormvik]

Interesting, but tested that on one of my queries. Didn't help much:

when lc_end.location_type = LOCATION_TYPE.HELIPORT then ...
when lc_end.location_type = LOCATION_TYPE.HELIPORT then ...
->
when lc_end.location_type = :Var10 then ...
when lc_end.location_type = :Var12 then ...


Each instance of a package variable got its own bind variable.
All together this query got 21 VAR bind variables..
Two package-local constants showed up once each with their name.
But, even if this had worked, I would still have to fill out the value for each constant once by hand.

One strange thing; The cursor uses a function parameter. This has been prefixed with ":" in the SQL, but it is not listed in the variables.

 

[Marco Kalter]

The 2 variables for the same package constant will be fixed in the next release:

I noticed that the bind variables names are the same as the PL/SQL Variable names, except when they are prefixed with the package name. We'll enhance this and will translate package.variable to ackage_variable if possible.