Solved

Sending SQLPLUS output to stdout

Posted on 2004-04-22
7
4,156 Views
Last Modified: 2012-08-13
I am using Crystal Enterprise 10 to schedule a run of SQLPLUS. As a parameter string I pass in
 "stquery/yadayada@myob @d:\mike\runsql.sql". It works great. The problem is, Crystal Enterprise always says it ran great - even if the database is down. This is understandable considering that SQLPlus actually did run great even if some of the commands it was trying to execute failed. Unfortunately, nothing of what appeared on the screen (or would have appeared on the screen if it were being run by a user at a terminal) is returned back to Crystal Enterprise.
If instead of running SQLPLUS, I run a .bat file, the commands from the .bat file ARE returned to Crystal Enterprise. Obviously, a .bat file and SQLPLUS are sending their outputs to different places. I am guessing that a .bat file sends to STDOUT ( or the Windows equivalent ). So, here is my question. How can I run SQLPLUS in a way that will echo output from commands back in the same manner that a .bat file does?
Thanks,
Mike
0
Comment
Question by:mmoore
  • 3
  • 3
7 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 10891684
I'm not familiar with Crystal Enterprise 10  but have create bat files that run sqlplus.  By default SQL*Plus sends it's output back to the screen.  You can turn this off the the 'set term off' command.  What, if any, 'set' commands are in the sql file.  Can you post the sql and bat files you are working with?
0
 
LVL 3

Author Comment

by:mmoore
ID: 10892522
Hi, I just read some Crystal Enterprise documentation that says it will receive Standard Output and Standard Error from any job it runs. I guess that SQLPLUS is not sending output to either of those channels.
The .bat file is not really related to the SQLPLUS job, I just used it as an example of something that works (it returns output back to Crystal Enterprise)
Here is my SQLPLUS script (runsql.sql):

------
set serveroutput on
select sysdate from dual;
exit
-------

Here is my .bat file:

-------
move d:\mike\logon.xxx d:\mike\logon.csp
-------

which results in the following job output being rturned to Crystal enterprise :

--------
C:\Program Files\Crystal Decisions\Enterprise 10\Data\procSched\BCPRS1.programjobserver\procprograma9435afd9d9950>move d:\mike\logon.xxx d:\mike\logon.csp
The system cannot find the file specified.
-------

Please ignore the fact that the file was not found -- the relevant point is that the message was returned to Crystal Enterprise.

So, I think that if I could figure out how to get SQLPLUS to write to Standard Output, I would get the results I am looking for.





0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 10892643
just for grins, try this bat file from both the command line and crystal enterprise and let me know the outcome:

@echo off
(
echo stquery/yadayada@myob
echo set cmdsep on
echo set cmdsep '"'; --"
echo "select sysdate from dual;"
) | sqlplus -s
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Author Comment

by:mmoore
ID: 10893859
Nope. It is as though sqlplus does not accept stdin.
HOWEVER, THIS does work:
@echo off
(
echo connect stquery/yadda@myob
echo set cmdsep on
echo set cmdsep '"'; --"
echo "select sysdate from dual;"
) | d:\orant\bin\plus80.exe /nolog

Apparently plus80 is not not the same when you execute it directly. I think we are on to something here. Further experimentation to follow.
0
 
LVL 8

Expert Comment

by:annamalai77
ID: 10895791
my dear friend

well sqlplus can only throw the output either to the screen or to a output file.
for ur example what u have mentioned

/** set serveroutput on  **/
used in procedures to throw the output generated while executing a procedure or a pl/sql block

what u can do is spool the output to a file and read the contents of the output file thru ur crystal application.

spool d:\anna.txt
select sysdate from dual;
spool off
exit

i think the above method can help solve ur problem.

regards
annamalai
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 10898884
Try removing the /nolog
0
 
LVL 3

Author Comment

by:mmoore
ID: 10901427
slightwv - It works with the /nolog. You put some time and thought in on this so I am giving you the points. Thanks!

anamala - I understand spooling but thanks anyway.

Anyway, I solved my problem. For any future readers, I will document my findings. Understand, that this is what I have discovered by trial and error, so it might not be the absolute truth.

The solution is to execute PLUS80.EXE; not PLUS80W.EXE or SQLPLUS.EXE. PLUS80.EXE reads from STDIN and writes to STDOUT. This means you can use pipes.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle SQL syntax check  without executing 6 104
use lov values 2 71
Oracle collections 15 37
ER Diagram 3 40
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question