Home » Open Source » Programming Interfaces » Connect from perl directly to a schema (Oracle, 10g, Unix)
Connect from perl directly to a schema [message #464419] Thu, 08 July 2010 01:20 Go to next message
chris0101
Messages: 3
Registered: July 2010
Junior Member
Hi there,

first I want to mention, that I googled for the answer of my question, but I was not successful. So if the answer is very simple, I think I searched for the wrong thing...

I am using DBI to connect to an oracle db, and it is working fine.
But in every statement I have to put my schema in front of the table name.

e.g.:
SELECT row1, row2 FROM schema.table

which is very annoying, as I never change the schema.

While surfing in the internet, I always find statments which are looking like, that perl is directly connected to a certain schema.

I was wondering (if it is possible at all), if I have to change the Oracle listener.ora, tnsnames.ora or the per connection string?

I am using the following:

Perl:
my $db = DBI->connect( "DBI:Oracle:orcl", "user", "pass" );

tnsnames.ora:
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
(CONNECT_DATA =
(SID = PLSExtProc)
(SERVICE_NAME = orcl)
)
)

Maybe somebody can help me out?

Thanks and regards,
Chris
Re: Connect from perl directly to a schema [message #464422 is a reply to message #464419] Thu, 08 July 2010 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You are connected to the schema with same name as the account you gave to connect.
If you don't want to name the schema when accessing objects you don't own you have to create (public) synonym.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7001.htm#CJAJCDDF

Regards
Michel

[Updated on: Thu, 08 July 2010 02:52]

Report message to a moderator

Re: Connect from perl directly to a schema [message #464448 is a reply to message #464422] Thu, 08 July 2010 02:13 Go to previous messageGo to next message
chris0101
Messages: 3
Registered: July 2010
Junior Member
Hi Michel,

thank you for your reply.
As the tables in the schema are altered dynamically, the solution with the synonyms will not work for me.

As I am not the "owner" of the table, and cannot log in as the owner... I have to put the schema on every statement I write.
What a pity Smile

But thanks again for your quick reply,
Christian
Re: Connect from perl directly to a schema [message #476372 is a reply to message #464448] Wed, 22 September 2010 18:47 Go to previous messageGo to next message
andrew again
Messages: 2574
Registered: March 2000
Senior Member
there are 2 other options you can investigate.
The DBA can create a logon trigger for your user, to set the default schema to the table owner.
CREATE OR REPLACE TRIGGER FRED.CHANGE2SCOTT_SCHEMA
AFTER LOGON ON FRED.SCHEMA
begin
  execute immediate 'alter session set current_schema = SCOTT';
end;
/


You can also try executing the alter session yourself when you connect. That should set the default scehma for the rest of that session.
Re: Connect from perl directly to a schema [message #476395 is a reply to message #464419] Thu, 23 September 2010 01:28 Go to previous message
chris0101
Messages: 3
Registered: July 2010
Junior Member
Hi Andrew,

thank you very much.
This is exact what I was looking for.

Regards,
Christian
Previous Topic: access a SQL Server database through ODBC
Next Topic: How can I connect Borland C++ builder 6 and oracle 10g??
Goto Forum:
  


Current Time: Thu Oct 23 15:55:49 CDT 2014

Total time taken to generate the page: 0.12510 seconds