?
Solved

Sending SQLPLUS output to stdout

Posted on 2004-04-22
7
Medium Priority
?
4,406 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 78

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 78

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 750 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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

571 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