Sending SQLPLUS output to stdout

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
LVL 3
mmooreAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Try removing the /nolog
0
 
slightwv (䄆 Netminder) Commented:
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
 
mmooreAuthor Commented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
slightwv (䄆 Netminder) Commented:
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
 
mmooreAuthor Commented:
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
 
annamalai77Commented:
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
 
mmooreAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.