Forum Stats

  • 3,769,993 Users
  • 2,253,045 Discussions
  • 7,875,263 Comments

Discussions

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

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....

Answers

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,596 Red Diamond

    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.