f



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

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.
0
Tim
12/12/2007 10:00:46 PM
comp.lang.perl.misc 33233 articles. 1 followers. brian (1246) is leader. Post Follow

8 Replies
783 Views

Similar Articles

[PageSpeed] 57

Quoth Tim <google@hoodfamily.org>:
> 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

0
Ben
12/12/2007 11:01:53 PM
Tim <google@hoodfamily.org> wrote:
> 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.
0
xhoster
12/13/2007 6:04:48 PM
On Dec 12, 4:01 pm, Ben Morrow <b...@morrow.me.uk> wrote:
> Quoth Tim <goo...@hoodfamily.org>:
>
> > 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

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.
0
Tim
12/14/2007 12:29:18 AM
Tim <google@hoodfamily.org> wrote:
> On Dec 12, 4:01 pm, Ben Morrow <b...@morrow.me.uk> wrote:
> > Quoth Tim <goo...@hoodfamily.org>:
> >
> > > 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
>
> 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.
0
xhoster
12/14/2007 1:09:08 AM
On Dec 13, 4:29 pm, Tim <goo...@hoodfamily.org> wrote:
> On Dec 12, 4:01 pm, Ben Morrow <b...@morrow.me.uk> wrote:
>
> > Quoth Tim <goo...@hoodfamily.org>:
>
> > > 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
>
> 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
0
Ron
12/14/2007 1:57:02 AM
On 13 Dec 2007 18:04:48 GMT xhoster@gmail.com wrote: 

x> Tim <google@hoodfamily.org> wrote:
>> 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;

x> /nolog turns sqlplus into something unusual and highly specific
x> 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.

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
0
Ted
12/14/2007 2:46:56 PM
On Dec 13, 6:57 pm, Ron Bergin <r...@i.frys.com> wrote:

> > > Quoth Tim <goo...@hoodfamily.org>:
>
> > 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

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.
0
Tim
12/15/2007 1:08:00 AM
On 2007-12-14 14:46, Ted Zlatanov <tzz@lifelogs.com> wrote:
> On 13 Dec 2007 18:04:48 GMT xhoster@gmail.com wrote: 
> x> Tim <google@hoodfamily.org> wrote:
>>> 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.
>
> 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
0
Peter
12/15/2007 8:48:48 PM
Reply: