tag:blogger.com,1999:blog-8884584404576003487.post3469722888511644967..comments2024-02-29T09:43:12.251-05:00Comments on ORACLENERD: Create Database Link - Loopback Editionoraclenerdhttp://www.blogger.com/profile/12412013306950057961noreply@blogger.comBlogger9125tag:blogger.com,1999:blog-8884584404576003487.post-10211760231749308382009-06-12T18:40:23.344-04:002009-06-12T18:40:23.344-04:00@gary You ask "why". Here is my answer....@gary You ask "why". Here is my answer. "Being able to change the code later" is not the way I think.<br /><br />The underlying connection information encapsulated by a database link as well as the meta-information of the dblink's name itself are an ENVIRONMENTAL components to your app, analogous to the host name or IP address. Things important to the overall execution of all software in the mix, but they are something to avoid at all costs as a dependency in your code.<br /><br />Philosophically, I am opposed to the very concept of an environmental component changing and it requires my code to be modified and recompiled.<br /><br />I can tell you have a lower threshold of concern for changing application source when in fact you are not changing its functionality in any way. I am very resistant to doing that.Clever Idea Widgetryhttps://www.blogger.com/profile/11224068405843575576noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-25271291560434562862009-06-07T03:29:09.261-04:002009-06-07T03:29:09.261-04:00"It sacrifices flexibility for no legitimate ..."It sacrifices flexibility for no legitimate gain."<br />Sacrifices flexibility ? Why ? You think that it isn't possible to change the code later ?<br />After all, if you do change which DB the object is in, you'll need to test the code set anyway. Changing a reference or two is hardly a chore.<br />Besides, moving schemas between databases is going to be rare enough that I'll never worry about it.SydOraclehttps://www.blogger.com/profile/08828771074492585943noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-41723683944039891412009-06-05T13:08:01.557-04:002009-06-05T13:08:01.557-04:00Why not just add a comment that it uses a synonym ...Why not just add a comment that it uses a synonym or db link or a dblink to a synonym. Try to be informational if you can : )Tomhttps://www.blogger.com/profile/09600876443323337792noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-57425676349139988212009-06-05T10:46:56.931-04:002009-06-05T10:46:56.931-04:00@Gary Using synonyms has no effect on the optimize...@Gary Using synonyms has no effect on the optimizer. Nor does it conceal the dblink dependency in data dictionary queries.<br /><br />My thinking is that the developer should KNOW what the optimizer implications are by explain plans of statements, profiling the code and data dictionary queries. If they the think are KNOWING anything by visually examining the source code of a stored procedure, they are standing in quicksand.<br /><br />Putting dblink syntax into source code is a pattern of failure. It sacrifices flexibility for no legitimate gain.Clever Idea Widgetryhttps://www.blogger.com/profile/11224068405843575576noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-61375577301624992242009-06-04T11:56:48.236-04:002009-06-04T11:56:48.236-04:00This comment has been removed by the author.moleboyhttps://www.blogger.com/profile/02060554309807021391noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-83918811360076956732009-06-03T22:08:29.861-04:002009-06-03T22:08:29.861-04:00I beg to differ on the 'Big Issue'.
Having...I beg to differ on the 'Big Issue'.<br />Having an object (table or view) on a remote DB has implications for the optimizer (and with user defined types). <br />I'd want future developers to KNOW that whenever they look at the relevant SQL statement. I want anyone reviewing the code to see it too.<br />If the synonym is named such that it obviously uses a DB link, or the team and environment are such that everyone knows it anyway, that's fine. Pros and Cons.<br /><br />If, in production, the object is referenced over a DB link, I'd want it to do that in development too. Synonym or no synonym.SydOraclehttps://www.blogger.com/profile/08828771074492585943noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-71460711560532992772009-06-03T17:04:47.835-04:002009-06-03T17:04:47.835-04:00One other thing I ran into with dblinks that may o...One other thing I ran into with dblinks that may or may not be in the documentation and may or may not be relevant to your situation:<br /><br />Avoid using dblink references that reference a synonym on the target database.Clever Idea Widgetryhttps://www.blogger.com/profile/11224068405843575576noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-82295741891537299012009-06-03T15:02:00.990-04:002009-06-03T15:02:00.990-04:00@crisatunity
re: Little One
I've been having...@crisatunity<br /><br />re: Little One<br />I've been having the same conversation with @serge_a_storms the past year. I don't believe that scripts should include the schema qualifier.<br /><br />re: Big One<br />Completely agree. Unfortunately I am not the creator nor do I have the ability to change that...yet. From what I can tell, it's essentially the L (of ETL), just staging the data. An ideal would be either batching it (once a day or whatever) or using something like CDC to do it automagically.oraclenerdhttps://www.blogger.com/profile/12412013306950057961noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-52478328720977806162009-06-03T14:20:21.480-04:002009-06-03T14:20:21.480-04:00Two issues, one big and one little.
First the lit...Two issues, one big and one little.<br /><br />First the little one: When you are creating deployment code, you will want to avoid any schema references as much as possible. Your "create procedure test_user.update_t" creates constricted developer sandbox and production deployment flexibility.<br /><br />Second the bigger one: In general you will not want to develop source code with the dblink syntax in it.<br /><br />If you create a synonym to the same object: <br /><br />create synonym t for t@loopback_link;<br />...<br />create procedure update_t is<br />...<br />insert into t (x,y) values ...<br /><br />This also gives you code with more developer sandbox and production deployment flexibility. This is especially true if you are using the dblink for some component in your development sandbox and no such dblink exists when it comes time to deploy to production.Clever Idea Widgetryhttps://www.blogger.com/profile/11224068405843575576noreply@blogger.com