Substitution variable session persistance

[iShaymus]

Is it possible within Toad (25.x) to set a substitution variable to persist until undefined or the session / connection ends?

Our team is split between Toad, SQL Developer and SQL Developer (VS Code) users. The latter two do not have this issue, We often will create a table and then merge various transformations into the new table, Both SQL developer IDEs can run define target_schema = 'mySchema'; once per session and then change, and re-run the fifth or tenth merge in the script over and over again where it's using the substitution variable.

merge into &target_schema..target_table using ( ... ) without any issues.

In toad however the entire file must be run as a script for this to work, which in our case could be 1000+ lines and creating an entire model with millions upon millions of rows.

It's become a bit contentious in the team because having to hard code values for Toad users slows down everyone else.

It seems like there must be an easier way, a setting that can be changed in Toad? Does such a setting exist? PL?SQL blocks are out of the question as we have to turn the drop and create table block into execute immediate strings, which we do not want to do.

[JohnDorlon]

This doesn't address your broader question about variables, but it in your specific example, you could not specify the schema at all in the Editor, and then use the "Current Schema" dropdown at the top, then every statement will take place in the selected schema.

[iShaymus]

Yes we could, but we also have variables for processing_date that get's used in many joins. Because we're dealing with point in time data warehouse data.