Using the DBI to connect to an Oracle server w/o connecting to adatabase

[Tim]

I've searched for the answer to this problem and have not seen it
(which suggests what the answer might be ;-) ). Is it possible to use
DBI (and DBD::Oracle) to connect to an Oracle server without
connecting to a database? This would be the DBI equivalent of:

sqlplus /nolog
SQL> connect user/passwd@database;

or, more similarly:

sqlplus /nolog @my.sql

where my.sql might be:

connect user/passwd@database;
select ...
etc.

I'm thinking this is not possible, but I hope it is. Thanks.

 

[Ben Morrow]

I've searched for the answer to this problem and have not seen it
(which suggests what the answer might be ;-) ). Is it possible to use
DBI (and DBD::Oracle) to connect to an Oracle server without
connecting to a database? This would be the DBI equivalent of:

sqlplus /nolog
SQL> connect user/passwd@database;

Is this not connecting to a database? I think you will have to explain a
bit more about what you are actually trying to acheive.

Ben

 

[xhoster]

I've searched for the answer to this problem and have not seen it
(which suggests what the answer might be ;-) ). Is it possible to use
DBI (and DBD::Oracle) to connect to an Oracle server without
connecting to a database? This would be the DBI equivalent of:

sqlplus /nolog
SQL> connect user/passwd@database;

/nolog turns sqlplus into something unusual and highly specific
to Oracle. I doubt DBI has an interest in trying to reproduce that.

or, more similarly:

sqlplus /nolog @my.sql

where my.sql might be:

connect user/passwd@database;
select ...
etc.

I'm thinking this is not possible, but I hope it is. Thanks.

Why would you want that? The point of DBI is that it provides a perl
interface for interactively communicating with Oracle. If you just
want to feed a text file through sqlplus with no interaction, then why
bother trying to do it with DBI?

Xho

--
-------------------- http://NewsReader.Com/ --------------------
The costs of publication of this article were defrayed in part by the
payment of page charges. This article must therefore be hereby marked
advertisement in accordance with 18 U.S.C. Section 1734 solely to indicate
this fact.

 

[Tim]

Is this not connecting to a database? I think you will have to explain a
bit more about what you are actually trying to acheive.

Ben

Yes, this connects to the database, just not from the command-line.
Functionally, in perl what I wanted to do was the DBI->connect... to
connect to the server and then in a separate call, connect to the
database itself.

The reason boils down to the fact that Sybase doesn't allow connecting
to a database from the connect call, but requires a separate
subsequent call. I was hoping to write some perl code portable enough
that when the time comes to switch this particular database from
Sybase to Oracle I wouldn't have to modify perl code.

 

[xhoster]

Yes, this connects to the database, just not from the command-line.
Functionally, in perl what I wanted to do was the DBI->connect... to
connect to the server and then in a separate call, connect to the
database itself.

The reason boils down to the fact that Sybase doesn't allow connecting
to a database from the connect call, but requires a separate
subsequent call.

In DBI? Can you show the code you use to connect to Sybase using DBI?


Xho

--
-------------------- http://NewsReader.Com/ --------------------
The costs of publication of this article were defrayed in part by the
payment of page charges. This article must therefore be hereby marked
advertisement in accordance with 18 U.S.C. Section 1734 solely to indicate
this fact.

 

[Ron Bergin]

Yes, this connects to the database, just not from the command-line.
Functionally, in perl what I wanted to do was the DBI->connect... to
connect to the server and then in a separate call, connect to the
database itself.

The reason boils down to the fact that Sybase doesn't allow connecting
to a database from the connect call, but requires a separate
subsequent call. I was hoping to write some perl code portable enough
that when the time comes to switch this particular database from
Sybase to Oracle I wouldn't have to modify perl code.

You, apparently, haven't tried using the DBI (with DBD::Sybase) to
connect to your database.

http://search.cpan.org/~mewp/DBD-Sybase-1.08/Sybase.pm

 

[Ted Zlatanov]

SQL> connect user/passwd@database;

x> /nolog turns sqlplus into something unusual and highly specific
x> to Oracle. I doubt DBI has an interest in trying to reproduce that.

x> Why would you want that? The point of DBI is that it provides a perl
x> interface for interactively communicating with Oracle. If you just
x> want to feed a text file through sqlplus with no interaction, then why
x> bother trying to do it with DBI?

Well this *is* a valid question: how to mix DBI connect calls with DBI
SQL statements? See in his input how he connected to a particular
database through *user input* and not code? I don't think you can do
that with SQL statements passed to DBI, you need to do a new connect().

One answer, specifically for DB connections, is to parse the input on
the fly, something like:

my $dbh;
while (my $line = <>)
{
if ($line =~ m/^connect\s/i)
{
# switch the $dbh handle
$dbh = ... connect call with parameters parsed from $line ...
}
elsif (defined $dbh)
{
... use $line on $dbh ...
}
else
{
warn "Input [$line] came before a 'connect ...' call in the input, so no database connection was available";
}
}

This is generically useful, not just for Oracle interaction.

Ted

 

[Tim]

You, apparently, haven't tried using the DBI (with DBD::Sybase) to
connect to your database.

http://search.cpan.org/~mewp/DBD-Sybase-1.08/Sybase.pm

Well, I have, but my problem was that I read Sybase documentation that
said to "use database" and when I read the DBD::Sybase perldoc:

"...Specify the database within the server that should be made the
default database (via "use $database")." that only reinforced my
misunderstanding. I now understand that statement to mean that the
connect will automatically perform a "use $database" for me if I put
the :database= in the connect string.

 

[Peter J. Holzer]

x> Why would you want that? The point of DBI is that it provides a perl
x> interface for interactively communicating with Oracle. If you just
x> want to feed a text file through sqlplus with no interaction, then why
x> bother trying to do it with DBI?

Well this *is* a valid question: how to mix DBI connect calls with DBI
SQL statements? See in his input how he connected to a particular
database through *user input* and not code?

That "user input" is a script. The scripting language is sqlplus, not
perl. You can do the same thing in a perl script:

$dbh = DBI->connect("dbi:Oracle:database", "user", "passwd");
my $result = $dbh->selectall_arrayref("select ..."):
for (@$result) {
...
}

I don't think you can do
that with SQL statements passed to DBI, you need to do a new connect().

Yes. Just as sqlplus does.

sqlplus handles two types of commands: SQL queries are passed on to the
database and sqlplus then displays the result. Sqlplus commands are
handled by sqlplus itself: These commands are for things like connecting
to databases, formatting the output, spooling to files, etc. They are
syntactically different from sql queries: They are terminated by a
newline, not a ; or / character.

If you use DBI, you have the same distinction: You have DBI functions,
which are processed by the local perl interpreter, and you have SQL
queries which are processed by the remote database.

The syntax difference is greater because perl has evolved from C, awk,
etc., and not from SQL, but it's the same principle.

hp