Capturing Oracle sqlplus output

[Duane]

Hello. I am a user & customer (three years?) having a license of my own (Duane McCollum) and now one at work. Love the product.

I am using the Tools menue to launch compile & run of java and C programs but I am having difficulty with capturing output from an Oracle SQLPLUS file.

I created a simple sql file, foo.sql

select user from dual;
--exit

then i added a new "Command" to the tools menu such that the command line says
C:\Oraclient817\BIN\SQLPLUS
parameters: user/password @schema @$File

instead of the output getting piped back to Textpad, it is sent back to the cmd shell. If i remove the comments from the --exit, then i don't get anything back.

What am i missing? Is it possible to get the output back into Textpad

[Andreas]

Have you checked the "Capture Output" box on the tool's settings?

[Sam]

Have toyed with a similar idea and your problem/question has motivated me to get it to work on my box. As noted above, make sure the "Capture Output" is checked.

Make sure you are running the DOS version of SQLPLUS and not the Windows version or else Windows will intercept your output before TextPad. Try looking for other SQLPlus executables that opens a cmd box instead of a window.

Here's mine:
($File: select user from dual;)
command: C:\ORANT\BIN\PLUS33.EXE
parameters: [user]/[password]@p42a @$File

SQL*Plus: Release 3.3.4.0.0 - Production on Mon Apr 01 08:41:10 2002

Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
With the Partitioning option
JServer Release 8.1.7.2.0 - Production


USER
------------------------------
[user]

SQL> Disconnected from Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
With the Partitioning option
JServer Release 8.1.7.2.0 - Production

Tool completed successfully

[Duane]

Thanks sam. I got it to work if i copied my TNSNAMES.ORA file to the working directory. I suppose there must be something i could tweak in my environment variables so that i can keep the file in one place and not have to move it around.

but, i got the results i wanted...

[Duane]

I could have just as easily used the WSH and pipe the results back to textpad! The output is much better since the if the # and length of columns exceed a certain amount/length, sqlplus tends to choke.

Dim oConn
Dim oRs
Dim Index

' Create ADO Connection Component to connect

Set oConn = CreateObject("ADODB.Connection")
sConn = "DRIVER={Microsoft ODBC for Oracle}; SERVER=foobar; UID=scott; PWD=tiger"

oConn.Open sConn


' Execute a SQL query and store the results
' within recordset
strSQL = "Select * from user_tables"
Set oRs = oConn.Execute(strSQL)
'get the column headings first
For Index=0 to (oRs.fields.count-1)
sPrint = sPrint & oRs(Index).name & chr(9)
Next

wscript.echo sPrint
sPrint =""
'get the records, one at a time
Do while (Not oRs.eof)
For Index=0 to (oRs.fields.count-1)
sPrint = sPrint & oRs(Index) & chr(9)
Next
wscript.echo sPRint
sPrint =""
oRs.MoveNext
Loop

'clean up
oRs.close
oConn.close