Setting Oracle Schema

[Guest]

At present, when I write Oracle SQL in .net, I have to embed the schema name
as a prefix for all of the table names so I end up with code like "select *
from " + GetSchemaName() + ".mytable". It would be much neater if I could
change the schema for the connection. I could add a "alter session set
current_schema="+GetSchemaName() every time a connection is opened, however
as we open a new connection for almost every statement (and rely on
connection pooling), this would double the number of db round trips.

Is there any other way of setting the current schema without a round trip or
only only running the "alter session" command when the connection is a new
connection rather than one in the pool (I presume this would work)?

Thanks,

Jonathan

 

[Frans Bouma [C# MVP]]

At present, when I write Oracle SQL in .net, I have to embed the
schema name as a prefix for all of the table names so I end up with
code like "select * from " + GetSchemaName() + ".mytable". It would
be much neater if I could change the schema for the connection. I
could add a "alter session set current_schema="+GetSchemaName() every
time a connection is opened, however as we open a new connection for
almost every statement (and rely on connection pooling), this would
double the number of db round trips.

Is there any other way of setting the current schema without a round
trip or only only running the "alter session" command when the
connection is a new connection rather than one in the pool (I presume
this would work)?

Isn't it so that if you connect with a user which can see only its own
schema, you don't have to prefix table names with the schema name?

FB

--