Send query from pl/sql to text file

I want to execute a procedure on a daily basis that checks the health of data guard.

So far the procedure executes a couple of queries and checks the value of a column.  If the value reflects something I should investigate, then the procedure is to run a series of queries against various tables and email the output (in one email please) to me so that I:
(1) know there might be a problem
(2) can review the output in the email to see where my problem might be.

I've got everything working OK except the part where the queries get executed.

I'm a bit of a hack in writing pl/sql and I've only ever written procedures that specifically write each column from a cursor query to a text file.  
i.e. utl_file.put_line(v_output_file1,'Database ID = '||v_database.dbid);                                        

I don't want to have to tell oracle to write each column but instead give me the entire output from the query (including headings) in one lump.

This is on a Unix server and I have no problem incorporating this process into a unix script if it's best that way.  Likely I will be scheduling this through the cron so that if the database is unavailable, I'll get notified of that as well.

Here are two of the many queries I plan to run:

select force_logging,remote_archive,supplemental_log_data_pk,supplemental_log_data_ui, switchover_status,dataguard_broker from v$database;        
select dest_id "ID",destination,status,target,archiver,schedule,process,mountid from v$archive_dest;        

Have attached the code with notations of where I think the code I'm looking for should go.
Thank you.

v_output_file1 utl_file.file_type;                                  
fHandle utl_file.file_type;                                         
vTextOut varchar2(32000);                                           
text varchar2(32000);       
vOutput varchar2(4000);           
v_error_flag number;           
v_count number;     

CURSOR c_protection IS                               
select name,database_role,controlfile_type,protection_mode,protection_level from  v$database;         
v_protection     c_protection%ROWTYPE;                            
v_output_file1 := UTL_FILE.FOPEN('DAILY_OUTPUT','erccms_dg_status.txt','W');        
OPEN c_protection;   
FETCH c_protection INTO v_protection;   
IF v_protection.protection_mode != v_protection.protection_level THEN   
  v_error_flag := 1;   
END IF;   
IF v_status_error.error IS NOT NULL THEN   
  v_error_flag := 1;   
END IF;   
SELECT count(*)    
INTO v_count   
FROM v$dataguard_status    
WHERE severity in ('Error','Fatal');   
IF v_count > 0 THEN   
  v_error_flag := 1;   
END IF;   
IF v_error_flag = 1 THEN   
  utl_file.put_line(v_output_file1,'Problem with Data Guard');        
  -- this part runs multiple queries and places them into a text file. 
  -- Then emails the text file to DBAs.   
   utl_file.put_line(v_output_file1,'Data Guard Looks Good!');        
END IF;    

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
There really isn't a way to get column headings the way you want from PL/SQL.

If you don't care to use a shell script why not just use SQL*Plus as the reporting tool?  You can control the column output and layout using the reporting functionality of sql*plus.

sqlplus -s /nolog <<EOF
connect user/password

set lines 250
column col1 head 'Some output' format a40

spool erccms_dg_status.txt


spool off


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
if you want to keep the PL/SQL procedure and your database server is on a network with a mail server, you can have Oracle email you it's status.

Check out UTL_MAIL.  This is exactly what I do.  You don't get the column headers but I get an email every day for what I deem important to my database.

No email:  Database problem or mail server problem.
vocogovAuthor Commented:
Thanks slightwv.  Suggestions on alternative approaches are appreciated.

I started off doing it from SQL*Plus.  But then decided that I didn't want to be sent any emails of data guard's status unless there was something specific in the output from one of the queries.  So since that required an "if" statement and value check of the output from one of the queries, I figured it'd be best to put it all into a PL/SQL.
I suppose I could have the Unix execute the PL/SQL which checks the value of one of the queries, turns on a flag if it's suspicious, passes the flag back to the unix and then, if the flag is on, execute all those queries I want, spool them to a text file and email me with it.  (whew!)
But I don't exactly know (yet) how to pass a value from a pl/sql procedure to a unix script.  so that opens another question for me.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

vocogovAuthor Commented:
I'm good on the UTL_MAIL process.  It just wasn't included in my code.   I love UTL_MAIL and use it for lots of status stuff.
slightwv (䄆 Netminder) Commented:
>>required an "if" statement and value check

I do similar things in my script using case statements.

You can execute the important selects based on a table count something like:
select * from tab1 where 'Had error' = (select case when count(*) = 0 then 'No error' else 'Had error' end from dual where 1=1);
vocogovAuthor Commented:
I'm not really grasping what you are saying in your last post.  Here is the unix script that connects to each of the four databases I have running on the unix server.   It reads the /etc/oratab file, connects to each database, and queries data guard stuff.

This script simply runs the queries and emails me the output.  Then I decided I wanted to check the values of two queries and, based on the values, email or not email me wih the full output of those queries (and others).

Where I hit a wall on this script is checking the value of columns from one query and also check the record count of another.   I just couldn't wrap my head around the IF..THEN needed on a SQL query inside a unix script.

I hope that makes sense.
queryinst() {
# Called programs use same database ID

SQLUSER=`cat /dbscripts/pass/sqlsystem$ORACLE_SID`
sqlplus -s system/$SQLUSER <<-EOF
set echo on
set feedback on
set verify on
spool /dbscripts/logs/DG_status$ORACLE_SID.spo;

prompt Report of Data Guard Status for $ORACLE_SID
set lines 200
set pagesize 235
set trim on
set trims on
set feedback on
set echo on
column host_name format a20 tru
column version format a9 tru
select instance_name,host_name,version,archiver,log_switch_wait from v\$instance;

column ROLE format a7 tru
select name,database_role,controlfile_type,protection_mode,protection_level from v\$database;

spool off
mailx -s "DG_Diag Daily Report" < /dbscripts/logs/DG_status$ORACLE_SID.spo


cat $ORATAB | while read LINE
case $LINE in
  \#*)                ;;        #comment-line in oratab
  ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
  if [ "$ORACLE_SID" = '*' ] ; then
    # same as NULL SID - ignore this entry
  # Proceed only if last field is 'Y'.
  if [ "`echo $LINE | awk -F: '{print $NF}' -`" = "Y" ] ; then
    INST="Database instance"
    ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
    # Called scripts use same home directory
    export ORACLE_HOME
    # file for logging script's output

Open in new window

slightwv (䄆 Netminder) Commented:
first off line 8:  You take the time and trouble to hid that password and put in on the command line so anyone doing a 'ps -ef' while it is running will see the password.

I suggest:
sqlplus -s /nolog <<EOF
connect system/$SQLUSER
set echo on

Back to point:
Can you point out one of the queries from the original comment:
-- this part runs multiple queries and places them into a text file.
-- Then emails the text file to DBAs.  

Here's what I was thinking
--return OK or problem with select
select case count(*) when 0 then 'Dataguard OK' else 'Problem with dataguard' end from v$dataguard_status where severity in ('Error','Fatal');

--you can then run the multiple queries by changing the above query slightly:

select something from some_table where 1 =
	(select case count(*) when 0 then 0 else 1 end from v$dataguard_status where severity in ('Error','Fatal'));

select something_else from some_other_table where 1 =
	(select case count(*) when 0 then 0 else 1 end from v$dataguard_status where severity in ('Error','Fatal'));

Open in new window

vocogovAuthor Commented:
To answer your question, I've pasted 4 queries I would want sent to me if there was an issue.

>>Back to point:
>>Can you point out one of the queries from the original comment:
>>-- this part runs multiple queries and places them into a text file.
>>-- Then emails the text file to DBAs.
select instance_name,host_name,version,archiver,log_switch_wait from v$instance;
select name,database_role,controlfile_type,protection_mode,protection_level from v$database;
select force_logging,remote_archive,supplemental_log_data_pk,supplemental_log_data_ui, switchover_status,dataguard_broker from v$database;        
select message, timestamp from v$dataguard_status where severity in ('Error','Fatal') order by timestamp;

I see that your example code checks the Count returned from a query.  I will give it a try.  There was another query in addition where I was wanting to compare two column values from the same table.  If the values are not the same, I want to be notified via email with results from the queries above.

select name,database_role,controlfile_type,protection_mode,protection_level from  v$database;        
IF v_protection.protection_mode != v_protection.protection_level THEN...NOTIFY ME.

Thank you.
vocogovAuthor Commented:
I'm having trouble figuring out where to put the code you gave me.  In the unix script or the PL/SQL?
Either way, I'm not getting it.  It seems I need you to show me more explicitly "what goes where".
slightwv (䄆 Netminder) Commented:
>>I'm having trouble figuring out where to put the code you gave me.

in the UNIX script since you want nice formatting and column headings.  If you stick with the PL/SQL you will need to write a lot of additional code to get formatting and headings.

in PL/SQL you can hardcode the messages and use to_char/lpad/rpad/??? to create formatted lines then write it out.  I would look at using a CLOB.

Choice is yours.

The problem here is I don't have access to UNIX or dataguard.  I have the views but never any rows so I really can't put everything together for you.

I'll have to provide general advice and you'll have to integrate everything.

From what I get you want an email regardless.  If there are problems, you want more detailed info.

>>IF v_protection.protection_mode != v_protection.protection_level THEN...NOTIFY ME

So if they don't match run the select otherwise nothing(we can work on getting rid of "no rows seleected" later?

select name,database_role,controlfile_type,protection_mode,protection_level from  v$database
where 1 = (
select case when protection_mode = protection_level then 0 else 1 end from v$database      

vocogovAuthor Commented:
Sorry this is taking so long.  I'm still trying to make it work and have also gotten sidetracked with other assignments.
Will update soon. I think that I'm not properly conveying what I want this query to do so I'll try to recap and expound on the next post.
vocogovAuthor Commented:
I'm giving up in trying to get what I want done in this question. Seems I cannot
 just execute several selects through a stored procedure and have the output sent
 to a text file without specifying each column in the utl_mail.put_line.  It's a
 lot of queries I plan to do and a lot of columns.  No way on all that code! I'm having
 a hard time accepting that it can't be done.

If it is impossible to accomplish the above through a stored procedure,then I must
 change my approach.  I hope someone can help me with it.  I suspect this will need
 to be a new question.

From unix, connect to the database and run a stored procedure that executes two
  queries.  Based on the output from the  two queries, an error_flag gets set to either "1" or "0"
  and passed back to the calling unix script.
  If the value passed from the stored procedure is equal to "1" then run
  several queries and email their output to me. Otherwise, do nothing.

This now changes my question to:  
1. How do I pass a value from a stored procedure to a unix script?  
2. Should I open a new question or can anyone assist me here?
slightwv (䄆 Netminder) Commented:
>>Seems I cannot  just execute several selects through a stored procedure

That is correct.  I've provided what I believe is an acceptable work-around if you absolutely must have column headers.

Although this will also require hard-coding the ouptut you can use a union trick:

select col1, col2 from
select 1 myOrder, 'Col1' col1,'Col2' col2 from dual
select 3, col1, col2 from tab1
order by myOrder;

>>I'm having  a hard time accepting that it can't be done.

Google around.  In 11g there is a feature to do this but not before.

>>How do I pass a value from a stored procedure to a unix script?  

You can set up a sql*plus variable and use that as an out parameter.  Then you'll need to grep/awk/??? for it.

>>can anyone assist me here?

I believe I have.  You can post this question again if you wish but the answer will continue to be the same.
vocogovAuthor Commented:
OK. I give up.  It seems you did answer the original question that I posed and the answer is that I can't just sent the output of a query in a stored procedure to a text file without specifying each column in the utl_mail.put_line command.  
No other possible way to send output to a text file from a stored procedure.  Got it.  Well, at least that's understood.
Closing question and awarding points.  Will open another question addressing the question posed in my last post.  Hopefully I'll get full code instead of snippets that I can't figure out where to place in a script.
vocogovAuthor Commented:
The solution is that the desired function is not possible.
slightwv (䄆 Netminder) Commented:
Can you please explain the 'C' grade?

Please review:

The correct answer to some questions is "You can't do that."
vocogovAuthor Commented:
Yes, I will explain.
EE says:
"an answer is worth an A, unless it doesn't resolve your issue. If it requires you to do a little more research, or figure out one more piece of code, then it's worth a B. If you think it's not worth a B, the custom is to offer the Experts an opportunity to earn a better grade."

I understand that some solutions are "you can't do it".  However, this question should have received a "you can't do it that way but here's what you CAN do" sort of answer which would certainly deserve an 'A'.  You seemed to want to do that however all you gave me were code snippets that I never could figure out how to use.  And boy did I try.
I asked you several times to help me know where to place the code and even gave you code to adjust.  But you just kept coming back with more snippets that I didn't know how to incorporate into a script.

For example:
There were two queries I needed to test against (1) one that test the count (2) the other tested the value of two columns.  You only gave code snippets to handle the count query.  Your snippet did not apply to the 2nd one.  I asked for help with it and you never addressed it.  I'm dead in the water if I can't test both of them.  No solution.

Union?  How does that fit into what I've already coded?  Set up an SQL*Plus variable and use it as an out parameter?  What? How?
Snippets and comments I can't use.  Code I can use.  Full code.   Is it so wrong to need you to throw in a full script? Maybe modify the one I already had?  That would deserve an 'A'.  

As I said, I am a hack at this and since you seemed to want to help, I kept trying to work with what you were giving me.  I asked you questions and spent hours of trying to incorporate your snippets and not getting full answers from you.  Finally, I was so iritated with the spoon feeding I was getting I chose to close the question and award points for the "you can't do it" answer but couldn't bring myself to award either an 'A' or a 'B'.

Whatever the moderators choose to do is fine by me if you want to submit it to them.
slightwv (䄆 Netminder) Commented:
>>"you can't do it that way but here's what you CAN do"

I believe that is what I did.  I offered three alternatives.

I understand your frustration however, I can't write your code for you.  I don't have your environment.

I offered functional code where I could.  It is up to you to learn the concepts I was offering so you could apply them in your environment.

Sorry if that wasn't good enough.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.