Howto capture interactive SQL*Plus session info on Win32?

Discussion in 'Perl Misc' started by James, Jun 14, 2004.

  1. James

    James Guest

    Hi All,
    I am trying to capture the output of an Oracle SQL*Plus session into
    an array but can't find any examples; specifically o retrieve a list of
    tablespaces. I can get it to log on and run the script but capturing it is
    a different game entirely. I realise Perl has its own native Oracle DBD
    module but that won't compile with Perl2exe(at least I can't work out how
    to). I can have Perl call SQL*Plus with an external script but I'm trying
    to do without loads of temp sql files. Is this even possible or is a major
    rethink in order?

    Thanks in advance for any hints
    James
    James, Jun 14, 2004
    #1
    1. Advertising

  2. James wrote:

    > Hi All,
    > I am trying to capture the output of an Oracle SQL*Plus session
    > into
    > an array but can't find any examples; specifically o retrieve a list of
    > tablespaces. I can get it to log on and run the script but capturing it
    > is
    > a different game entirely. I realise Perl has its own native Oracle DBD
    > module but that won't compile with Perl2exe(at least I can't work out how
    > to). I can have Perl call SQL*Plus with an external script but I'm trying
    > to do without loads of temp sql files. Is this even possible or is a
    > major rethink in order?
    >
    > Thanks in advance for any hints
    > James


    Sqlplus just writes to stdout so you can try something like (untested):

    open(ORA, "cat 'select * from tableapace;'|sqlplus |")
    while (<ORA>) {
    #processing
    }
    close(ORA)


    But DBI is by far the preferred solution.

    gtoomey
    Gregory Toomey, Jun 15, 2004
    #2
    1. Advertising

  3. James

    Ben Morrow Guest

    Quoth Gregory Toomey <>:
    > James wrote:
    >
    > > Hi All,
    > > I am trying to capture the output of an Oracle SQL*Plus session
    > > into
    > > an array but can't find any examples; specifically o retrieve a list of
    > > tablespaces. I can get it to log on and run the script but capturing it
    > > is
    > > a different game entirely. I realise Perl has its own native Oracle DBD
    > > module but that won't compile with Perl2exe(at least I can't work out how
    > > to). I can have Perl call SQL*Plus with an external script but I'm trying
    > > to do without loads of temp sql files. Is this even possible or is a
    > > major rethink in order?
    > >
    > > Thanks in advance for any hints
    > > James

    >
    > Sqlplus just writes to stdout so you can try something like (untested):
    >
    > open(ORA, "cat 'select * from tableapace;'|sqlplus |")


    You mean echo, not cat.

    You could also use IPC::Open2.

    Ben

    --
    "If a book is worth reading when you are six, *
    it is worth reading when you are sixty." - C.S.Lewis
    Ben Morrow, Jun 15, 2004
    #3
  4. James

    James Guest

    Thanks Kevin, I tried that today and it worked!
    Cheers
    James

    "Kevin Michael Vail" <> wrote in message
    news:...
    > In article <mAozc.97273$>,
    > "James" <> wrote:
    >
    > > [...] I realise Perl has its own native Oracle DBD
    > > module but that won't compile with Perl2exe(at least I can't work out

    how
    > > to). [...]

    >
    > I've been doing this for literally years. You need to explicitly add
    > the following line in your program somewhere:
    >
    > use DBD::Oracle;
    >
    > so that the Perl2Exe program knows to include that module in the
    > compiled executable. Other than that there should be no problem.
    > --
    > Kevin Michael Vail | a billion stars go spinning through the night,
    > | blazing high above your head.
    > . . . . . . . . . | But _in_ you is the presence that
    > . . . . . . . . | will be, when all the stars are dead.
    > . . . . . . . . . | (Rainer Maria Rilke)
    James, Jun 15, 2004
    #4
  5. James

    James Guest

    Hi,
    This seemed an interesting solution but I think I may have misunderstood:
    ----------------------------------------------------------------------------
    ---------------------------------------
    #!C:\Perl\bin\perl.exe

    open(ORA, "echo 'select tablespace_name from tablespace;'|sqlplus
    system/manager\@ORAC.WORLD|") ;

    while (<ORA>)
    {
    print "$_\n";
    }
    close(ORA) ;
    ----------------------------------------------------------------------------
    ---------------------------------------

    C:\Perl>ora2.pl


    SQL*Plus: Release 9.2.0.1.0 - Production on Tue Jun 15 20:42:52 2004



    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.





    Connected to:

    Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production

    With the Partitioning option

    JServer Release 8.1.7.4.0 - Production



    SQL> SP2-0734: unknown command beginning "'select ta..." - rest of line
    ignored.

    SQL> Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 -
    Production

    With the Partitioning option

    JServer Release 8.1.7.4.0 - Production


    C:\Perl>
    ----------------------------------------------------------------------------
    ---------------------------------------

    Have I forgotten something obvious?











    "Gregory Toomey" <> wrote in message
    news:10072023.ASMkjmpKEe@GMT-hosting-and-pickle-farming...
    > James wrote:
    >
    > > Hi All,
    > > I am trying to capture the output of an Oracle SQL*Plus session
    > > into
    > > an array but can't find any examples; specifically o retrieve a list of
    > > tablespaces. I can get it to log on and run the script but capturing it
    > > is
    > > a different game entirely. I realise Perl has its own native Oracle

    DBD
    > > module but that won't compile with Perl2exe(at least I can't work out

    how
    > > to). I can have Perl call SQL*Plus with an external script but I'm

    trying
    > > to do without loads of temp sql files. Is this even possible or is a
    > > major rethink in order?
    > >
    > > Thanks in advance for any hints
    > > James

    >
    > Sqlplus just writes to stdout so you can try something like (untested):
    >
    > open(ORA, "cat 'select * from tableapace;'|sqlplus |")
    > while (<ORA>) {
    > #processing
    > }
    > close(ORA)
    >
    >
    > But DBI is by far the preferred solution.
    >
    > gtoomey
    James, Jun 15, 2004
    #5
    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. WJ
    Replies:
    2
    Views:
    484
  2. The LoxFather

    C plus plus vs C Sharp

    The LoxFather, Aug 9, 2003, in forum: C++
    Replies:
    23
    Views:
    9,201
  3. The LoxFather

    C plus plus vs C Sharp

    The LoxFather, Aug 9, 2003, in forum: C Programming
    Replies:
    23
    Views:
    1,384
  4. Rahul
    Replies:
    9
    Views:
    419
    Andrey Tarasevich
    Mar 25, 2008
  5. furqan shaikh

    c plus plus code comparator

    furqan shaikh, Nov 7, 2008, in forum: C++
    Replies:
    6
    Views:
    816
    red floyd
    Nov 12, 2008
Loading...

Share This Page