This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Aug 1, 2013 2:12 PM by Pablolee RSS

Synonyms on the fly

Pablolee Journeyer
Currently Being Moderated

Hi, I'm trying to remember why I shouldn't be doing this:

Oracle 10.2.0.5

A very simplified version of the situation, but I reckon it should get the point across.

I'm building a Package/ Procedure to build some report outputs. No major PL/SQL some moderately large and complex SQL (nothing that'd break my limited intellectual bank balance tho')

The procedure points to tables in (let's say) user1 schema at the moment. However, at some point I will need the code to point to an identical table in user2 schema (imaginative names, I know). I really want to avoid dynamic SQL if I can. My proposal is:

1. Create a synonym (local to the owner of the Procedure) for each table in user1.

2. Point the table references in the procedure to the synonyms instead of fully qualified table names.

3. Create a procedure that will dynamically 'create or replace' those synonyms, to point to the table(s) in user2. (I know, I know, I wanted to avoid dynamic sql, but here it is relatively minimal as opposed to the comparatively large and complex code of the actual procedure).

 

The environment cannot and will not change, we have two similar schemas and the proc needs to be able to point to either on demand.

Fairly straightforward process. There is, of course, the risk that a table will be created in the procedure owner's schema with the same name as one of the synonyms, but I can code an exception for that and it is extremely unlikely to happen as that schema will be ringfenced and access highly restricted.

 

As I said at the start of the post, I'm looking for reasons to NOT do this. On the surface it seems ok, but I have this nagging voice (not the wife for once) in the back of my head, screaming "NO! This is a bad idea! Because if.blah.' and then the voice fades.

So, what is the problem with this approach that I'm blundering over?

Obviously, if you feel that more detail is required, let me know.

Cheers.

  • 1. Re: Synonyms on the fly
    Arild Explorer
    Currently Being Moderated

    Can't you simply have schema as a parameter in your procedure?

     

    exec mypackage.myreportprocedure ( dataschema => 'user1', ....

     

    That's arguably clearer/ more readable than using synonyms which sometimes change?

     

    Rgds, A

  • 2. Re: Synonyms on the fly
    bencol Pro
    Currently Being Moderated

    Authid current_user: Using PL/SQL Subprograms?

  • 3. Re: Synonyms on the fly
    Pablolee Journeyer
    Currently Being Moderated

    As I said, I want to avoid Dynamic SQL (Which would be necessary if I was to try to parameterise schema names).

    Thanks for the input though.

  • 4. Re: Synonyms on the fly
    Pablolee Journeyer
    Currently Being Moderated

    A decent thought but no use for a couple of reasons,

    1.the proc is definer's rights

    2. There is actually more than 1 schema per query (i.e. User1a, user1b etc)

    Thanks for the input.

  • 5. Re: Synonyms on the fly
    BluShadow Guru Moderator
    Currently Being Moderated

    Sounds like it's time to consolidate the data into a single table in a single schema and provide each of the 'user' schemas with a synonym and view that queries their own data from that one table.

  • 6. Re: Synonyms on the fly
    Pablolee Journeyer
    Currently Being Moderated

    As I said in my first post

    "The environment cannot and will not change, we have two similar schemas and the proc needs to be able to point to either on demand."

    But thanks for your input. If you have any input on the actual question, i.e.

    "So, what is the problem with this approach that I'm blundering over?" I'd love to get your insight.

    Cheers.

  • 7. Re: Synonyms on the fly
    BluShadow Guru Moderator
    Currently Being Moderated

    Well saying "the environment cannot and will not change" and then saying you're going to dynamically change synonyms on the fly kind of contradicts itself.

     

    Are the schemas fixed and known?  If so, you could create a view that unions the date from all the different schemas and, if necessary, includes another column to indicate the schema name as data so they can be selected individually.  That way the view is created once, nothing needs changing on-the-fly and the procedure can access whichever data it wants from the combined view of data.

  • 8. Re: Synonyms on the fly
    Pablolee Journeyer
    Currently Being Moderated

    OK, thanks for your input Blushadow, much appreciated. Do you, by any chance have any input on the question which is:

    "So, what is the problem with this approach that I'm blundering over?"

     

    I would also say that you have quote mined me there.

    Your quote:

    "The environment cannot and will not change,"

    What I actually said:

    "The environment cannot and will not change, we have two similar schemas and the proc needs to be able to point to either on demand."

    The missing part supplies enough context to the comment to render you point around changing synonyms, invalid. No offense intended there, just clarifying my position.

    Once again, thanks for your input.

  • 9. Re: Synonyms on the fly
    BluShadow Guru Moderator
    Currently Being Moderated

    Do you, by any chance have any input on the question which is:

    "So, what is the problem with this approach that I'm blundering over?"

     

    Yes, it doesn't scale.  Is the procedure ever going to be called by more than one person or process?

    If so, they will be competing to change the synonyms (which is DDL statements so automatically commit).

     

    Aside from that, it's not an elegant solution... yes it may work if you can guarantee it's only ever run by one user/process at any one time, but it would certainly be a 'last option' in my book, after looking at consolidating the data (physically or via a view), or using current user rights rather than definer rights etc.

  • 10. Re: Synonyms on the fly
    Hoek Guru
    Currently Being Moderated

    How about doing a search on http:/asktom.oracle.com for 'CURRENT_SCHEMA'.

    Examples:

    Ask Tom "Synonyms vs Alter session current_schema"

    Ask Tom "Synonym problems"

     

    You can probably use that instead of the synonyms on the fly.

    Normally you resort to dynamic SQL if 'things' (like column names or whatever) are only known at runtime.

    That is not the case in your situation.

    Doing DDL on the fly is generally considered a bad approach.

     


  • 11. Re: Synonyms on the fly
    Pablolee Journeyer
    Currently Being Moderated

    >>Yes, it doesn't scale.

    Ahhh! I have a feeling that that is what the fading voice was trying to tell me

    >>Is the procedure ever going to be called by more than one person or process?

    I would not expect so, but it is, of course, a risk.

    >>Aside from that, it's not an elegant solution...

    No argument here

    >>yes it may work if you can guarantee it's only ever run by one user/process at any one time,.

    Makes sens. Probably this is the killer to this option

    >>but it would certainly be a 'last option' in my book, after looking at consolidating the data (physically or via a view), or using current user rights rather than definer rights etc.

    Hmm, current_user. OK, that's also been mentioned by Bencol. I'm trying to picture how that resolves:

    Sample:

    SELECT t1.col1, t2.col2, t3.col3

    FROM user1a.t1

      JOIN user1b.t2 ON t1.col5 = t2.col6

      JOIN user1c.t3 ON t3.col6 = t3.col6;

     

    i.e. a query that points to tables in 3 schemas, on demand, needing to point to 3 different schemas:

     

    SELECT t1.col1, t2.col2, t3.col3

    FROM user2a.t1

      JOIN user2b.t2 ON t1.col5 = t2.col6

      JOIN user2c.t3 ON t3.col6 = t3.col6;

     

    I'm going to be seriously annoyed at myself if there was something obvious in the link from Bencol and I've missed it.

    Cheers again BluShadow

  • 12. Re: Synonyms on the fly
    Pablolee Journeyer
    Currently Being Moderated

    Hi Hoek, that's the problem with over simplifying my description. Apologies for that.

    My most recent response to BluShadow shows that there is more than one schema used in the proc, which must be 'switched' to different schemas on demand. Therefore I don't think that altering current_schema would work, but I'm open to being shown up for being hard of thinking...

    >>Doing DDL on the fly is generally considered a bad approach.

    Agreed, and something that I tend to tell others too. However, it is a general rule rather than an absolute one.

    I was hoping to avoid Dynamic SQL Primarily because the code is too big to use NDS and dbms_sql is a pain in the A55 but it looks like that is where I'll be going.

    Gents, many many thanks for your input. Have a good one.

  • 13. Re: Synonyms on the fly
    bencol Pro
    Currently Being Moderated

    If schemas user1a.t1, user1b.t2 and user1c.t3 all reprensent user1a's data then, for example user1a can have access to all this data and have private synonyms pointing to these users, and the procedure can still run as CURRENT_USER. These private synonyms (a referencing b & c ) will be permanent (in schemas user1a and user 2a), so what is to stop user1a and user 2a running the procedure as CURRENT_USER, refering to their own objects and synonyms?

  • 14. Re: Synonyms on the fly
    BluShadow Guru Moderator
    Currently Being Moderated

    The authid current_user is useful if the procedure is being called by each of the schemas.  the procedure itself wouldn't specify the schema names in the query, it would just query the tables, and the authid current_user would ensure that it's the calling users schema tables that are used.  We have packages and procedures that do this in one of our ETL applications where each of the source schemas calls a centralized package to process it's own change data capture tables.  Effectively, the centralized package doesn't care who's calling it, just so long as they have the same tables and are expecting the same processing.  It scales too because multiple schemas can call the same code and only have it effect their own tables.

     

    Of course it depends if it's being called from the individual schemas or from the central place.  If the latter, then probably Hoek's suggestion of changing the sessions schema on-the-fly may be more suitable.  It's not something I've done a lot of, but if I understand it correctly it should scale as the changing of schema is within the session calling the procedure, so multiple sessions can be run at the same time, each 'switching' to seperate schemas.  No doubt it has some pitfalls though.

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points