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

Discussion in 'Perl Misc' started by Tim, Dec 12, 2007.

  1. Tim

    Tim Guest

    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.
    Tim, Dec 12, 2007
    #1
    1. Advertising

  2. Tim

    Ben Morrow Guest

    Quoth 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;


    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
    Ben Morrow, Dec 12, 2007
    #2
    1. Advertising

  3. Tim

    Guest

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

    Tim <> 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.
    , Dec 13, 2007
    #3
  4. Tim

    Tim Guest

    On Dec 12, 4:01 pm, Ben Morrow <> wrote:
    > Quoth 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;

    >
    > 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.
    Tim, Dec 14, 2007
    #4
  5. Tim

    Guest

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

    Tim <> wrote:
    > On Dec 12, 4:01 pm, Ben Morrow <> wrote:
    > > Quoth 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;

    > >
    > > 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.
    , Dec 14, 2007
    #5
  6. Tim

    Ron Bergin Guest

    On Dec 13, 4:29 pm, Tim <> wrote:
    > On Dec 12, 4:01 pm, Ben Morrow <> wrote:
    >
    > > Quoth 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;

    >
    > > 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
    Ron Bergin, Dec 14, 2007
    #6
  7. Tim

    Ted Zlatanov Guest

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

    On 13 Dec 2007 18:04:48 GMT wrote:

    x> Tim <> 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
    Ted Zlatanov, Dec 14, 2007
    #7
  8. Tim

    Tim Guest

    On Dec 13, 6:57 pm, Ron Bergin <> wrote:

    > > > Quoth Tim <>:

    >
    > > 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.
    Tim, Dec 15, 2007
    #8
  9. Re: Using the DBI to connect to an Oracle server w/o connecting toa database

    On 2007-12-14 14:46, Ted Zlatanov <> wrote:
    > On 13 Dec 2007 18:04:48 GMT wrote:
    > x> Tim <> 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
    Peter J. Holzer, Dec 15, 2007
    #9
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. ulloa
    Replies:
    1
    Views:
    526
    Juha Laiho
    Jul 22, 2004
  2. Benson, John
    Replies:
    1
    Views:
    2,218
    Aurelio Martin
    Jan 28, 2004
  3. John Benson
    Replies:
    1
    Views:
    271
  4. Peter Bailey
    Replies:
    11
    Views:
    533
    Vetrivel Vetrivel
    Nov 30, 2009
  5. Replies:
    5
    Views:
    259
Loading...

Share This Page