Hi,

I'm trying to resolve a problem. I currently have a java application
utilising an oracle instance called orany1. The developers made the
mistake of hard-coding all the sql references to the tables with the
instance name i. table_name@orany1. The application connects to the
instance as the user that owns the application schema.

However, I now need to move the application schema to another instance
called orany2. This will be a short-term measure as the application
will shorlty be replaced. The developers are apprehensive about
changing the @orany1 references in the java code and have asked me to
look into a workaround.

I need to try and find a way to connect to the orany2 instance and for
the table references to orany1 to actually read from the orany2 tables.
I've tried using synonyms but his was unsuccessful.

What about changing the service name in tnsnames.ora on the application
web server? The server name will be orany1 and the sid will be orany2.
I guess I can test this when I return to work.

Thanks for any assistance in advance.

regards
John

Hi John,

My assumption is they are using the OCI driver. If this is the case,
then changing the tnsnames file will fix the issue, as it uses the
tnanmes file for resolution. If they are using the thin driver, then
it won't, as the connection string contains the hostort:sid, not an
alias as OCI uses.

How did you do the synonyms? As ugly as this sounds, you should be
able to use a database link with a snynoym. If you are connecting to
the user in orany1 who owns the tables, you will probably have to drop
all of the tables in orany1, create the database link and synonyms to
orany2, and you should be OK...and then you should shoot your
developers, or at least point them in the direction of JNDI or a simple
properties file.

Regards,

Steve

Johne_uk wrote:
> Hi,
>
> I'm trying to resolve a problem. I currently have a java application
> utilising an oracle instance called orany1. The developers made the
> mistake of hard-coding all the sql references to the tables with the
> instance name i. table_name@orany1. The application connects to the
> instance as the user that owns the application schema.
>
> However, I now need to move the application schema to another instance
> called orany2. This will be a short-term measure as the application
> will shorlty be replaced. The developers are apprehensive about
> changing the @orany1 references in the java code and have asked me to
> look into a workaround.
>
> I need to try and find a way to connect to the orany2 instance and for
> the table references to orany1 to actually read from the orany2 tables.
> I've tried using synonyms but his was unsuccessful.
>
> What about changing the service name in tnsnames.ora on the application
> web server? The server name will be orany1 and the sid will be orany2.
> I guess I can test this when I return to work.
>
> Thanks for any assistance in advance.
>
> regards
> John

I'd make the developers change the reference to one that is not
hard-coded to teach them a lesson about bad behaviour having
consequences. This will not be the only application they do this
in and not the only time you will have to move an app.

BTW: Exactly how was this moved from test to prod?
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

DA Morgan wrote:
> Johne_uk wrote:
> > Hi,
> >
> > I'm trying to resolve a problem. I currently have a java application
> > utilising an oracle instance called orany1. The developers made the
> > mistake of hard-coding all the sql references to the tables with the
> > instance name i. table_name@orany1. The application connects to the
> > instance as the user that owns the application schema.
> >
> > However, I now need to move the application schema to another instance
> > called orany2. This will be a short-term measure as the application
> > will shorlty be replaced. The developers are apprehensive about
> > changing the @orany1 references in the java code and have asked me to
> > look into a workaround.
> >
> > I need to try and find a way to connect to the orany2 instance and for
> > the table references to orany1 to actually read from the orany2 tables.
> > I've tried using synonyms but his was unsuccessful.
> >
> > What about changing the service name in tnsnames.ora on the application
> > web server? The server name will be orany1 and the sid will be orany2.
> > I guess I can test this when I return to work.
> >
> > Thanks for any assistance in advance.
> >
> > regards
> > John
>
> I'd make the developers change the reference to one that is not
> hard-coded to teach them a lesson about bad behaviour having
> consequences. This will not be the only application they do this
> in and not the only time you will have to move an app.
>
> BTW: Exactly how was this moved from test to prod?
> --
> Daniel A. Morgan
> http://www.psoug.org
> damorgan@x.washington.edu
> (replace x with u to respond)

Maybe they were too Agile for a test system.

Thanks for the replies guys.

I've just found out that the app uses a connection string (no tnsnames)
so that rules out using tnsnames.The orany1 database is going to be
scrapped and rebuilt again in the future so the option of connecting to
orany2 and using db link / synonyms to point to orany1 cannot be used
(good idea though). Our development is done offshore and we would be
charged for these changes which is not worth it for the short time we
need to relocate the app. I agree its bad practise and I believe none
of other others apps have hard-coded references.

I did trying connecting directly to orany2 and creating synonyms that
reference table@orany2 for any reference to table@orany1 in the code
but it refuses to work.

Thanx again
John

"Johne_uk" <edgarj@tiscali.co.uk> wrote in message
news:1145294842.841056.47990@i39g2000cwa.googlegro ups.com...
> Thanks for the replies guys.
>
> I've just found out that the app uses a connection string (no tnsnames)
> so that rules out using tnsnames.The orany1 database is going to be
> scrapped and rebuilt again in the future so the option of connecting to
> orany2 and using db link / synonyms to point to orany1 cannot be used
> (good idea though). Our development is done offshore and we would be
> charged for these changes which is not worth it for the short time we
> need to relocate the app. I agree its bad practise and I believe none
> of other others apps have hard-coded references.
>
> I did trying connecting directly to orany2 and creating synonyms that
> reference table@orany2 for any reference to table@orany1 in the code
> but it refuses to work.
>
> Thanx again
> John
>
Which gives a good reason why off shoring is not a bright idea. But I am
sure you already knew that. Clearly these aren't talented or seasoned
application developers. All that money that was "saved" by off shoring
comes back to haunt one.
Jim

On Mon, 17 Apr 2006 18:48:04 -0700, Jim Kennedy wrote:

>>
> Which gives a good reason why off shoring is not a bright idea. But I am
> sure you already knew that. Clearly these aren't talented or seasoned
> application developers. All that money that was "saved" by off shoring
> comes back to haunt one.

With all due respect - one can get the same lack of talent without going
offshore.

Or worse - one can get seasoned talent in other environments who know
even less in the target environment than the off shore groups.

I can't count the times I've followed 'SQL experts' who were actually
'SQLServer' experts with no knowledge of Oracle. The code, while probably
excellent for SQL Server, often has [many and significant] opportunities
for improvement in Oracle.

--
Hans Forbrich
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com
*** Top posting [replies] guarantees I won't respond. ***

"HansF" <News.Hans@telus.net> wrote in message
newsan.2006.04.18.01.37.20.699481@telus.net...
> On Mon, 17 Apr 2006 18:48:04 -0700, Jim Kennedy wrote:
>
> >>
> > Which gives a good reason why off shoring is not a bright idea. But I
am
> > sure you already knew that. Clearly these aren't talented or seasoned
> > application developers. All that money that was "saved" by off shoring
> > comes back to haunt one.
>
> With all due respect - one can get the same lack of talent without going
> offshore.
>
> Or worse - one can get seasoned talent in other environments who know
> even less in the target environment than the off shore groups.
>
> I can't count the times I've followed 'SQL experts' who were actually
> 'SQLServer' experts with no knowledge of Oracle. The code, while probably
> excellent for SQL Server, often has [many and significant] opportunities
> for improvement in Oracle.
>
> --
> Hans Forbrich
> Canada-wide Oracle training and consulting
> mailto: Fuzzy.GreyBeard_at_gmail.com
> *** Top posting [replies] guarantees I won't respond. ***

Absolutely true. I've just been hearing a lot of off shore complaints vis a
vis this type of thing. But you are very correct.
Jim

HansF wrote:
> I can't count the times I've followed 'SQL experts' who were actually
> 'SQLServer' experts with no knowledge of Oracle. The code, while probably
> excellent for SQL Server, often has [many and significant] opportunities
> for improvement in Oracle.

Is the emphasis on "while probably"?

Johne_uk wrote:
> Thanks for the replies guys.
>
> I've just found out that the app uses a connection string (no tnsnames)
> so that rules out using tnsnames.The orany1 database is going to be
> scrapped and rebuilt again in the future so the option of connecting to
> orany2 and using db link / synonyms to point to orany1 cannot be used
> (good idea though). Our development is done offshore and we would be
> charged for these changes which is not worth it for the short time we
> need to relocate the app. I agree its bad practise and I believe none
> of other others apps have hard-coded references.
>
> I did trying connecting directly to orany2 and creating synonyms that
> reference table@orany2 for any reference to table@orany1 in the code
> but it refuses to work.
>
> Thanx again
> John

Charged? I would tell the off-shore hackers that they delivered a
defective product and that if they don't make good there are plenty
of other two-bit hackers to choose from. In short ... fix it now and
fix it for free or you are fired. They'll fix it. Then find another
someone else to work with in the future. One can only wonder at the
number of other bad-practices they wrote into their app.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)