You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Fabio Insaccanebbia <fi...@gmail.com> on 2005/08/31 15:15:07 UTC
Re: Oracle scheme
In Oracle "user" and "schema" are almost synonyms..
Configure iBatis to connect with VIBSCANNER user and you won't need to
prefix the tables with the user name.
If you don't want to connect with VIBSCANNER, you could create
synonyms for the schema of the user you connect with.
A better approach could be to create a "login trigger" for the user
you connect with. The trigger should then change the session to use
another schema (VIBSCANNER)
Hope it helps,
Fabio
2005/8/31, MRaedle@t-online.de <MR...@t-online.de>:
> Hello all,
>
> I use iBATIS to access ORACLE, MS SQL and other databases (with the same SQL
> maps).
>
> To access ORACLE databases I have following connection URL:
>
> Connection URL: jdbc:oracle:thin:@cm_ora_test:1521:webtest
>
> "webtest" indicates the SID of the ORACLE database.
>
> The problem is that under "webtest" more than one databases could managed.
> So for each db query I must put the database name in front of the table name
> such like this:
>
> "select * from VIBSCANNER.T_ALARM_BAND ..." (VIBSCANNER is the database
> name).
>
> For MS Access, MY SQL, ... I couldn't take the same SQL maps and that is not
> nice.
>
> Can you tell me, how I could resolve this problem, e.g. indicate also a
> scheme in the connection URL
> (jdbc:oracle:thin:@cm_ora_test:1521:webtest</scheme>)?
>
> Regards
>
> Manuel
>
>
>
>
Re: Oracle scheme
Posted by Jan Vissers <Ja...@cumquat.nl>.
Ok, I think my knowledge of Oracle is getting in the way here. How is
creating a synonym any harder than alter session statements. More than
that, by using appropriate granting/synonyms structures in Oracle - you
can have more secure data paths.
You can even use Oracle's own dictionary to have the 'create synonyms'
statements generated for you, like (in SQL*Plus):
SQL> select 'create synonym '||table_name||' for USER01.'||table_name||';'
2 from user_tab_privs_recd
3 where grantor = 'USER01';
'CREATESYNONYM'||TABLE_NAME||'FORUSER01.'||TABLE_NAME||';'
--------------------------------------------------------------------------------
create synonym T_ALARM_BELL for USER01.T_ALARM_BELL;
create synonym T_ALARM_BAND for USER01.T_ALARM_BAND;
create synonym T_ALARM_SIGN for USER01.T_ALARM_SIGN;
Copy & Paste results
SQL> create synonym T_ALARM_BELL for USER01.T_ALARM_BELL;
Synonym created.
SQL> create synonym T_ALARM_BAND for USER01.T_ALARM_BAND;
Synonym created.
SQL> create synonym T_ALARM_SIGN for USER01.T_ALARM_SIGN;
Synonym created.
IMO: Not only a matter of taste.
Provided of course that user01 has granted rights to user02
Koka wrote:
> I have used kind of 'login trigger' approach - in fact I extended
> datasource I were using to add execution of
> alter session set current_schema = "USER"
> statement to getConnection method.
> Found it easier then creating sysnonyms
>
> >I guess it's a matter of taste..
> Agree.
>
>
>
--
Cumquat Information Technology
De Dreef 19
3706 BR Zeist
T +31 (0)30 - 6940490
F +31 (0)10 - 6940499
http://www.cumquat.nl
Jan.Vissers@cumquat.nl
M +31 6 5 11 169 556
Re: Oracle scheme
Posted by Koka <22...@gmail.com>.
I have used kind of 'login trigger' approach - in fact I extended datasource
I were using to add execution of
alter session set current_schema = "USER"
statement to getConnection method.
Found it easier then creating sysnonyms
>I guess it's a matter of taste..
Agree.
Re: Oracle scheme
Posted by Fabio Insaccanebbia <fi...@gmail.com>.
The login trigger is executed once for connection and most of the
cases it means it is rarely used (example: connection pool).
If the number of objects you want to access is large (50+) you'd
probably prefer to create a single trigger rather than having to
create and "mantain" 50+ synonyms..
I guess it's a matter of taste.. I should have added IMHO :-)
Disclaimer: probably there are better ways to achieve this effect in
iBatis.. I was talking only as an Oracle/JDBC guy
Bye,
Fabio
2005/8/31, Jan Vissers <Ja...@cumquat.nl>:
> "A better approach could be to create a "login trigger" for the user you
> connect with. The trigger should then change the session to use another
> schema (VIBSCANNER)"
>
> This looks pretty ugly and rather "expensive". Why would you want to do
> this?
>
> Fabio Insaccanebbia wrote:
>
> >In Oracle "user" and "schema" are almost synonyms..
> >
> >Configure iBatis to connect with VIBSCANNER user and you won't need to
> >prefix the tables with the user name.
> >
> >If you don't want to connect with VIBSCANNER, you could create
> >synonyms for the schema of the user you connect with.
> >
> >A better approach could be to create a "login trigger" for the user
> >you connect with. The trigger should then change the session to use
> >another schema (VIBSCANNER)
> >
> >Hope it helps,
> >Fabio
> >
> >2005/8/31, MRaedle@t-online.de <MR...@t-online.de>:
> >
> >
> >>Hello all,
> >>
> >>I use iBATIS to access ORACLE, MS SQL and other databases (with the same SQL
> >>maps).
> >>
> >>To access ORACLE databases I have following connection URL:
> >>
> >>Connection URL: jdbc:oracle:thin:@cm_ora_test:1521:webtest
> >>
> >>"webtest" indicates the SID of the ORACLE database.
> >>
> >>The problem is that under "webtest" more than one databases could managed.
> >>So for each db query I must put the database name in front of the table name
> >>such like this:
> >>
> >>"select * from VIBSCANNER.T_ALARM_BAND ..." (VIBSCANNER is the database
> >>name).
> >>
> >>For MS Access, MY SQL, ... I couldn't take the same SQL maps and that is not
> >>nice.
> >>
> >>Can you tell me, how I could resolve this problem, e.g. indicate also a
> >>scheme in the connection URL
> >>(jdbc:oracle:thin:@cm_ora_test:1521:webtest</scheme>)?
> >>
> >>Regards
> >>
> >>Manuel
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
> >
>
> --
> Cumquat Information Technology
> De Dreef 19
> 3706 BR Zeist
> T +31 (0)30 - 6940490
> F +31 (0)10 - 6940499
> http://www.cumquat.nl
>
> Jan.Vissers@cumquat.nl
> M +31 6 5 11 169 556
>
>
>
Re: Oracle scheme
Posted by Jan Vissers <Ja...@cumquat.nl>.
"A better approach could be to create a "login trigger" for the user you
connect with. The trigger should then change the session to use another
schema (VIBSCANNER)"
This looks pretty ugly and rather "expensive". Why would you want to do
this?
Fabio Insaccanebbia wrote:
>In Oracle "user" and "schema" are almost synonyms..
>
>Configure iBatis to connect with VIBSCANNER user and you won't need to
>prefix the tables with the user name.
>
>If you don't want to connect with VIBSCANNER, you could create
>synonyms for the schema of the user you connect with.
>
>A better approach could be to create a "login trigger" for the user
>you connect with. The trigger should then change the session to use
>another schema (VIBSCANNER)
>
>Hope it helps,
>Fabio
>
>2005/8/31, MRaedle@t-online.de <MR...@t-online.de>:
>
>
>>Hello all,
>>
>>I use iBATIS to access ORACLE, MS SQL and other databases (with the same SQL
>>maps).
>>
>>To access ORACLE databases I have following connection URL:
>>
>>Connection URL: jdbc:oracle:thin:@cm_ora_test:1521:webtest
>>
>>"webtest" indicates the SID of the ORACLE database.
>>
>>The problem is that under "webtest" more than one databases could managed.
>>So for each db query I must put the database name in front of the table name
>>such like this:
>>
>>"select * from VIBSCANNER.T_ALARM_BAND ..." (VIBSCANNER is the database
>>name).
>>
>>For MS Access, MY SQL, ... I couldn't take the same SQL maps and that is not
>>nice.
>>
>>Can you tell me, how I could resolve this problem, e.g. indicate also a
>>scheme in the connection URL
>>(jdbc:oracle:thin:@cm_ora_test:1521:webtest</scheme>)?
>>
>>Regards
>>
>>Manuel
>>
>>
>>
>>
>>
>>
>
>
>
--
Cumquat Information Technology
De Dreef 19
3706 BR Zeist
T +31 (0)30 - 6940490
F +31 (0)10 - 6940499
http://www.cumquat.nl
Jan.Vissers@cumquat.nl
M +31 6 5 11 169 556