We have multiple schemas in the Oracle database with the same table name.
All the tables have the exact same structure (both column names and types). I want to create a view to Union them together. In addition, there will be new tables of the same type that will be added later into the database, so I need the view to be able to 'run through' all existing tables in the database and add them (either adding or creating the view from scratch).
I've created a simple view with the table's name's hardcoded but I'm having difficulty how to approach the issue of having the view created dynamically. I'm using Oracle 19c
I assumed I'll probably have to write some script code that would actually run and check for new table names but I wanted to see some example to figure out how to do that.
In an Oracle procedure, how can we create the view adding the union selects dynamically?