Use bind variables in adhoc sql similar to "execute function/procedure"

Ray007

Could the same sort of bind variables be implemented for adhoc scripts as when you execute a procedure or function?

Currently, I may write a script that does

update table1 set col = '&new_val' where id = &id;
update table2 set col = '&new_val' where id = &id;

And when I click run, I get prompted for new_val. Then I get prompted for id. Then I get prompted for new_val. Then I get prompted for id. This sucks... 😝

I'd rather write a script that is more like

update table1 set col = :new_val where id = :id;
update table2 set col = :new_val where id = :id;

And get prompted with a single sheet asking me for values to bind to :new_val and :id. Let me choose the datatypes to bind in and set values....

Billy Verreynne

Dislike this script-approach. It is primitive. It does not provide for proper UI interaction for and with the WIMP user.

If SQL scripts need to be executed, wrap these into an o/s scripting language like bash (Linux) or Powershell (Windows).

If UI interaction is key, use APEX, design a web page (with validation and exception handling) that enables the user to enter the data and execute the script contents via a PL/SQL post page submit process.