?
Solved

Sending SQLPLUS output to stdout

Posted on 2004-04-22
7
Medium Priority
?
4,257 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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 77

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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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
This video shows how to recover a database from a user managed backup
Suggested Courses

765 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