Visit Our UNIX and Linux User Community


Error with script interaction


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Error with script interaction
# 8  
Old 08-11-2009
Now it's getting quite messy!

How does the EMAIL procecure get the output from the while loop?

I mean, you could try this:
Code:
done | D:/user-applications/oracle/ora92/bin/sqlplus.exe -s $HKLOGIN/${HKPWD}@$HKSVC << !
begin
EMAIL_PROCEDURE;
end;
/
!

or this...
Code:
done | {
cat << !
begin
EMAIL_PROCEDURE;
end;
/
!
} | D:/user-applications/oracle/ora92/bin/sqlplus.exe -s $HKLOGIN/${HKPWD}@$HKSVC

If you show me the usage of the EMAIL procedure (which arguments it takes) then it would help.
# 9  
Old 08-11-2009
Hi Scott,

The email_procedure has no parameters.
./hkstats.ksh does some processes which ultimately inserts the current size of various databases (which the loop accesses) into a table. Some of them fail to collect (incorrect details in tsnnames.ora or other issues) which are then listed in an email. I'd like to continue to use this stored procedure rather than sending it some other way because its long & works. The email_procedure iterates over some emails in a table and then sends the email to each user. The list is achieved by listing all the databases and then subtracting (minus) those that have inserts associated with them on that particular day.

Code:
CREATE OR REPLACE PROCEDURE EMAIL_PROCEDURE
IS
    v_From      VARCHAR2(80) := 'HKADM.Weekly';   
    v_Subject   VARCHAR2(80) := 'HKADM C3 Stats Fail - ' || to_char(sysdate,'DD') || ' ' || 
          rtrim(to_char(sysdate,'Month'), ' ') || ' ' || to_char(sysdate, 'YYYY');
    v_Mail_Host VARCHAR2(30) := 'smtp.XXX.com';
    v_Mail_Conn utl_smtp.Connection;
    crlf        VARCHAR2(2)  := chr(13)||chr(10);
    
    -- All live C3 databases to have not updated TODAY
    cursor c1 is select distinct hkadm.tbstat.tbs_svc_name
          from hkadm.tbstat, hkadm.srvc 
          where  hkadm.tbstat.tbs_svc_name = hkadm.srvc.svc_name 
          and hkadm.srvc.svc_app_name = 'c3' and hkadm.srvc.svc_app_type = 'L' 
          minus
          select distinct hkadm.tbstat.tbs_svc_name
          from hkadm.tbstat, hkadm.srvc 
          where hkadm.tbstat.tbs_date = TO_CHAR(SYSDATE, 'DD-MON-YY') and hkadm.tbstat.tbs_svc_name = hkadm.srvc.svc_name 
          and hkadm.srvc.svc_app_name = 'c3' and hkadm.srvc.svc_app_type = 'L'
          order by 1;
          
    -- Read emails from table
    cursor emails is select email from tbl_email;
    
    -- Place keeper for failed database collections
    db_fail VARCHAR2(4000);
        
    -- Name in introductory message generated from email
    recip VARCHAR2(20);
BEGIN
    -- Fill variable db_fail with the failed database names
    for val in c1 loop
       db_fail := db_fail || val.tbs_svc_name || crlf;
    end loop;
   
    -- Sends the email to all listed
    for email in emails loop
    -- Name in introductory message generated from email
    recip := UPPER(substr(SUBSTR(email.email, 1 ,INSTR(email.email, '.', 1, 1)-1),1,1)) || LOWER(substr(SUBSTR(email.email, 1 ,INSTR(email.email, '.', 1, 1)-1),2,19));
    v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
    utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
    utl_smtp.Mail(v_Mail_Conn, v_From);
    utl_smtp.Rcpt(v_Mail_Conn, email.email);   
    utl_smtp.Data(v_Mail_Conn,
       'Date: '   || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
       'From: '   || v_From || crlf ||
       'Subject: '|| v_Subject || crlf ||
       'To: '     || email.email || crlf ||
       crlf ||
       'Hi ' || recip || ',
       
The following databases failed to commit:'|| crlf ||	-- Message body
        db_fail ||
'
KR,
_______________________
Mike');
    
     utl_smtp.Quit(v_mail_conn);
     end loop;
EXCEPTION
     WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
        raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
END;
/


Last edited by Dird; 08-11-2009 at 11:05 AM..
# 10  
Old 08-11-2009
In that case, I would just do this after the while loop

Code:
D:/user-applications/oracle/ora92/bin/sqlplus.exe -s $HKLOGIN/${HKPWD}@$HKSVC << !
begin
EMAIL_PROCEDURE;
end;
/
!

So you have to re-comment, but I can't imagine that's so expensive. Do you have to call the procedure in the same session as the select?
# 11  
Old 08-12-2009
Hi scott,

No, I don't have to it's just that I read somewhere about keeping connections open because of the time it takes to repeatedly connect. So are you saying?:
Code:
cat << !
set heading off
set feedback off
set pagesize 0
set serveroutput on
select distinct tbstat.tbs_svc_name
          from tbstat, srvc
          where  tbstat.tbs_svc_name = srvc.svc_name
          and srvc.svc_app_name = 'c3' and srvc.svc_app_type = 'L';
!
} | D:/user-applications/oracle/ora92/bin/sqlplus.exe -s $HKLOGIN/${HKPWD}@$HKSVC | while read line; do
echo "$line collecting..."
./hkstats.ksh all $line
echo "$line completed..."
done | D:/user-applications/oracle/ora92/bin/sqlplus.exe -s $HKLOGIN/${HKPWD}@$HKSVC << !
begin
EMAIL_PROCEDURE;
end;
/
!

Wouldn't I be able to do it with 1 sqlplus login?

Edit: Scott? Smilie

---------- Post updated 08-12-09 at 03:11 AM ---------- Previous update was 08-11-09 at 08:23 AM ----------

Or does the "done" cancel the connection?

Last edited by Dird; 08-11-2009 at 12:55 PM.. Reason: I've lost Scotty
# 12  
Old 08-12-2009
Hi!

The connection would be "cancelled" after the last bit of data is sent through the pipe to the while loop.

Do you need to run EMAIL_PROCEDURE for every record returned by the select, or only once?

If for every record (why?) then I would suggest changing your initial SQL input PL/SQL using a cursor, and then calling the EMAIL_PROCEDURE within the cursor loop.
# 13  
Old 08-12-2009
Hi Scott,

No the email is sent at the end. The loop goes to 24 databases and collects bits of data from them. The email_procedure then sends an email to various people listing those databases which failed to collect.

Is my last guess the correct way it would be done then or would it be on a separate line instead of joined together with the | ?
# 14  
Old 08-12-2009
Hi.

If the email is sent at the VERY end, then you put

Code:
D:/user-applications/oracle/ora92/bin/sqlplus.exe -s $HKLOGIN/${HKPWD}@$HKSVC << !
begin
EMAIL_PROCEDURE;
end;
/
!

outside the while loop (not with a pipe, after "done").
(but I see nothing so far that indicates which databases have "failed")

What started out as a question is now turning into an application Smilie

Previous Thread | Next Thread
Test Your Knowledge in Computers #135
Difficulty: Easy
As of 2006, approximately five percent of the Linux kernel was written by Linus Torvalds himself
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Avoid interaction into script

Sorry for my english! I'm using Debian squeeze and for an assignement, i have create 2 virtual pdf cups printers. Both are working very well. To test the different administion command; i try to disable one of the printer and move his queue file to the second one. I'm able to do it easily. ... (4 Replies)
Discussion started by: lyapma
4 Replies

2. UNIX for Advanced & Expert Users

PAM and vsftp interaction

If I use to PAM to validate a vsftp user using a remote service to do the validation and the user does not exist on the system locally, then what becomes the home directory for the user logging in via vsftp? Assuming that chroot_user_list is enable to limit all users to their home directory. (0 Replies)
Discussion started by: ChrisC
0 Replies

3. Shell Programming and Scripting

Expect help/interaction with switch..

Hi All, I have been trying to get an Expect program to communicate with a Remote Power Switch which will eventually log in, and then turn on/off certain outlets. For some reason, I can't get through the log in procedure with this Switch. When you first spawn the telnet, you get the Switch... (1 Reply)
Discussion started by: mrjgs70
1 Replies

4. Shell Programming and Scripting

textbox and user interaction

I'm wanting to get user interaction...textbox or checkboxes would be great. I want to allow someone to enter data into textbox. (this is the time I want to grab a photo from) Then I want to be able to check the different sizes I want for the image. Say 4 options. Is this possible using the... (0 Replies)
Discussion started by: mainegate
0 Replies

5. Programming

Needed help in a ruby script with user interaction involved!

Hi all, I am struck at this point and needed some help in ruby I wanted to write a script that accepts the no of VNICs the user wants to create and then ask for the name of each Vnic he wants then use these names to create the corresponding Vnics Eg: suppose that there are 2 Vnics to be... (0 Replies)
Discussion started by: wrapster
0 Replies

6. Shell Programming and Scripting

ksh and awk interaction

in a ksh script, i want to process some string variables using awk, and then i want to go on using this variables in the same ksh (out of awk lines) can anybody send me a very simple example about this? (0 Replies)
Discussion started by: gfhgfnhhn
0 Replies

7. HP-UX

Boot interaction

Hi All, I am the new boy on the Block with HP-UX. To get to the point, I have a Hp-ux (C200) workstation running on Hp-ux 11.x Operating System. A week ago when practicing on file access permission, I have set the permission on a directory (perhaps a file I am not sure) to 0544 and did... (19 Replies)
Discussion started by: mhossien
19 Replies

8. Programming

client server interaction? anyone know

hi there, i need help :( im trying to produce a program that would do the following, but i dont know were to begin, any help/guide lines, please im need help asap, One program is the main program, which fork/execs one referee process and three x,y,z processes repectvley. It then waits until... (0 Replies)
Discussion started by: zmanultra
0 Replies

9. Shell Programming and Scripting

bash and Perl interaction questions

hi. im working in bash and am trying to create a Perl daemon that controls bashs behavior. this is actually in preparation for a later project ill be working on. basically, im looking for a way to have the Perl daemon tell bash what to do. i already have a small daemon that simply prints... (2 Replies)
Discussion started by: deryk
2 Replies

10. UNIX for Dummies Questions & Answers

Help in setting up password without Interaction.

Can anybody tell me how can I create an account without having to enter the password after editing the vipw file. I mean the system should automatically take care of the password encryption in the shadow file and I don't to enter the password again and the Account should be ready to go. ... (3 Replies)
Discussion started by: syedifti
3 Replies

Featured Tech Videos