Howto capture interactive SQL*Plus session info on Win32?

J

James

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
 
G

Gregory Toomey

James said:
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
 
B

Ben Morrow

Quoth Gregory Toomey said:
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
 
J

James

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

Kevin Michael Vail said:
James said:
[...] 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,
(e-mail address removed) | blazing high above your head.
. . . . . . . . . | But _in_ you is the presence that
. . . . . . . . | will be, when all the stars are dead.
. . . . . . . . . | (Rainer Maria Rilke)
 
J

James

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?
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,900
Latest member
Nell636132

Latest Threads

Top