[13:34:30] <progreSSive> hi everybody!
[13:34:42] <progreSSive> have a problem here, and would appreciate any help!
[13:35:07] <progreSSive> i've been using sybase for about 2 years, but now i have a need to migrate to oracle
[13:36:10] <progreSSive> but i have this problem... in sybase, when you log into a database, you can choose a database to you, and the dafault schema, so that you dont have to write the schema name before the each table you refer to
[13:37:33] <progreSSive> but in oracle, i couldnt find this option, and each time have to show the alias, i mean, the schema name to use
[13:38:37] <progreSSive> for instance, if i need to use a table, that belongs to a schema called "billing", and i log in under a different user, each time i need to specify "select * from billing.books where ...."
[13:38:42] <progreSSive> any suggestions??
[13:38:50] <progreSSive> or.. anybody alive in here? :)
[13:42:31] <Clone> hehe.
[13:42:34] <Vovka> hi
[13:44:53] <Clone> progreSSive: I dont know of any other way to address a table from a different schema.
[13:45:03] <Clone> but im no guru.
[13:45:19] <Vovka> synonym
[13:45:51] <Vovka> progreSSive: synonym helps you
[13:51:48] <progreSSive> Vovka, oh, thanks
[13:52:21] <progreSSive> Vovka, but do i need to create synonyms for each table??
[13:52:35] <progreSSive> Vovka, it's just, there are about 100s of tables
[13:52:59] miken_ [i=miken@outbound.silenceisdefeat.org] зашел на #oracle
[14:00:06] <Vovka> progreSSive: you can do it dynamicly with SQL
[14:01:33] <progreSSive> Vovka, hmm.. could you please tell me, how? :)
[14:02:01] <Vovka> select 'create synonym '||table_name||' for '||owner||'.'||table_name||' ; ' from dba_tables where blablabla
[14:02:27] <Vovka> this SQL makes script that creates synonyms
[14:03:02] <progreSSive> hm
[14:03:04] <Vovka> for all tables wich selects
[14:03:04] <progreSSive> i see
[14:03:16] <progreSSive> and how about "alter session"?
[14:03:22] <progreSSive> heard about it?
[14:03:25] <Vovka> current_schema ?
[14:03:32] <progreSSive> yeah
[14:03:39] <Vovka> up to you
[14:04:23] <Vovka> synonyms can be public and no need ot use alter sesion for every user
[14:04:24] <progreSSive> which one is better and easier to realize?
[14:04:36] <progreSSive> well.. the problem here is..
[14:04:49] <progreSSive> i have several modules, that used to work with sybase
[14:05:32] <progreSSive> and there are hundreds of places and queries i would have to re-write, if i had to change the select, insert, update statements
[14:05:57] <progreSSive> in sybase, in the beginning of your connect to the database, you say "use dbname"
[14:06:11] <progreSSive> and under any user, you can you the default schema
[14:06:52] <progreSSive> but here, the structure is a little different, oracle has users, and each user is assigned a role, and each user can use his own schema
[14:07:04] <progreSSive> but no single default schema
[14:07:10] <progreSSive> as far as i got it, right?
[14:07:36] <Vovka> user ownes schema
[14:07:44] <progreSSive> or is it possible to assign a schema as a default schema to a group of users??
[14:07:54] <Vovka> no
[14:08:28] <progreSSive> so a user cannot own a schema of a different user?
[14:08:59] <Vovka> user owns schema, names resolution is schema.table if own schema - just table
[14:09:17] <Vovka> in oracle user = schema ))
[14:09:40] <progreSSive> so what i mean is, in my module, all the tables are referred to as just the table name
[14:09:45] <progreSSive> no schema used
[14:09:52] <progreSSive> and all the users could access it
[14:10:22] <progreSSive> and now, i'll have to add the schema name to the tables
[14:10:29] <Vovka> I think public synonyms and grants on tables will help
[14:10:33] <progreSSive> but i dont really want to do that :)
[14:11:59] <progreSSive> what if i do this..
[14:12:10] <progreSSive> when the user connects to use the module
[14:12:35] <progreSSive> the first query would be "alter session current_schema=billing"
[14:12:45] <progreSSive> and use the tables as they are
[14:12:56] <progreSSive> not changing the prefixes
[14:13:03] <progreSSive> would that be okay?
[14:13:20] <Vovka> yea
[14:13:27] <Vovka> it should )
[14:13:43] <progreSSive> nice
[14:14:06] <progreSSive> i'll try, and if it doesnt work, i'll try the synonym stuff )
[14:14:11] <progreSSive> thanks a lot, man
[14:14:14] <progreSSive> appreciate! :)
[14:14:25] <Vovka> you are wellcome
[14:14:32] <progreSSive> кстати, ты откуда? :)
[14:14:35] <Vovka> Бля
[14:14:39] <progreSSive> ))))