We get weekly backups of the entire hospital database which is massive. For various reasons we can’t simply overwrite the previous week’s database. Instead the new backup is put into a new schema. So we have a core schema that has no tables of its own (ccdb_clarity) and three schemas that contain the weekly backups (ccdb_clarity_1, ccdb_clarity_2 and ccdb_clarity_3). Each backup is then deleted in turn every three weeks and then recreated and repopulated with the latest data. The core schema (ccdb_clarity) simply points to the latest ccdb_clarity_# via synonyms.
I want my solution to be able to point to ccdb_clarity and see those (synonymed) tables. I can run a query and name those tables (ccdb_clarity.patient) and that works fine. But I can’t see the tables in the tree and therefore it seems I can’t use them for creating forms pointing to them. I came across this post in the forum: https://www.servoy.com/forum/viewtopic.php?f=8&t=11026&hilit=oracle+synonym
This was 4 years ago and in that I gather that this was being added as a feature request. Was that ever done? Is there something I can do to access those tables via a schema that has them via synonyms? I can’t change the schema every week to point at the latest backup so I need to use the core schema that always is using synonyms to show that data from the current backup.
Any help/advice gratefully received! This is a pretty big deal for the major improvements I am implementing.
Thanks
I’m not sure I understand because I have no knowledge of ccdb_clarity but maybe you could define a seperate connection for each schema and switch between schemas (even at runtime) using switchServer(schema1, schema2)?
The issue is Oracle synonyms. The example I gave is for a simple schema (ccdb_clarity in this case) that actually has no tables defined. It is in the ‘real’ schemas where the tables and data reside and it switches every week which one has the most up to date data. Using Oracle synonyms however I can actually access those tables and data through the ‘empty’ schema ccdb_clarity. So say for instance we have a table ‘patient’ and this table resides in schemas ccdb_clarity_1, ccdb_clarity_2 and ccdb_clarity_3. If this week ccdb_clarity_2 has the most up to date data, then all I need to do to access that table is ‘select * from ccdb_clarity.patient’ because the synonym for that table points to ccdb_clarity_2.patient. That works fine in a query. But in Servoy you can’t ‘see’ those tables by having ccdb_clarity as the named server connection.
You are right in that it would be theoretically possible to use the switchServer function. The problem is:
I’d need to be able to do it dynamically as the ‘switch’ happens at different times and some weeks it might not happen at all. It might be that there’s a way to figure out which schema the synonym is actually pointing to in Oracle and I’m looking into that.
These schemas can change from week to week as we add more hospital tables that we are interested in. I won’t be using those tables immediately but I’m concerned with a switchServer command that Servoy might be bogged down with those changes even though they are not ‘used’ in the solution. Would the server need a restart?
Did you already ruled out that this might be a permission issue in Oracle for the DB user Servoy is using to connect to the database ?
I have had a situation once that I couldn’t see any database objects but still could query them but when using another user I could.
This was with Oracle’s own SQL Developer tool mind you.
Oh I can query them outside or inside of Servoy alright. And I can see them if I use the active schema. But I don’t think it is a permissions issue why I can’t see them but a ‘synonyms’ issue. I think synonyms might be kind of unique to Oracle. In trying to base a form on a table using that server connection (ccdb_clarity) nothing shows up (as there are no tables in that ‘empty’ schema as it acts purely as a synonym for the real schemas). I suppose another alternative is I could do a 'forms by query" but that would seem to be a lot of work…