Solved

Sending SQLPLUS output to stdout

Posted on 2004-04-22
7
4,180 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
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

Industry Leaders: 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!

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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.
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

739 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