More of a prompt for me than an informative post 🙂
Oracle params/variables can be defined in a batch statement through a variable keyword and then assigned a value using the exec (execute) command. For example, we can define 2 variables and give them a value with the statements below (note the use of := for assignment)
variable v1 nvarchar2(20); variable v2 nvarchar2(20); variable v3 nvarchar2(20); exec :v1 := 'Val 1'; exec :v2 := 'Val 2'; exec :v3 := 'Val 3'; select :v1 as v1, :v3 as v2, from dual; select :v2 as v2 , :v1 as v1 from dual;
When executing the command (I’ve tried this on TOAD and SQL Developer), the function key used with either
- Execute the batch (if F5 is pressed) or
- Execute the current command where the cursor is (if F9 is pressed). This relates to the statement surrounding the cursor position.
Where the command is executed (F9), any variables in the command are prompted for entry (as shown below).