Setting Oracle Schema


G

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
 
Ad

Advertisements

F

Frans Bouma [C# MVP]

Jonathan said:
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

--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top