Operating System - HP-UX
1748288 Members
3327 Online
108761 Solutions
New Discussion юеВ

Unix script to read from oracle table

 
SOLVED
Go to solution
Jesse Delk
Frequent Advisor

Unix script to read from oracle table

Can I get a little (or alot) help with a unix script?

I need a script to read a column from an oracle table and then email it from unix.

such as

sqlplus username/password@sid
select columnname from table where rownum <2; **pulling first row only**
host mailx -s Batch "columnname" user@somewhere.com
delete from table where columnname = 'columnname';

Would like it not to send mail if the table is empty.


17 REPLIES 17
Oviwan
Honored Contributor

Re: Unix script to read from oracle table

Hey

You could write a sendmail procedure in pl/sql and do the whole thing in pl/sql. you will find a lot of example with google about sendmail procedures...

Regards
Yogeeraj_1
Honored Contributor
Solution

Re: Unix script to read from oracle table

hi Jesse,

Method1:
========
Create a shell script as follows:
#!/bin/ksh
export ORACLE_HOME=
export PATH=$PATH:$ORACLE_HOME/bin
export emailadd1=user@somewhere.com
#
sqlplus /nolog << EOF
connect username/password@sid
set echo off;
set termout on;
set linesize 100;
set pagesize 60;
set newpage 0;
set heading off;
set feedback off;
spool /tmp/output.lis;
select columnname from table where rownum <2;
spool off;
EOF
/usr/bin/uuencode /tmp/output.lis "FileYouNeed.txt"|mailx -m -s "Subject" $emailadd1
#End-of-Script

Method2: (oracle 10g)
========
Create a procedure/package to do the same:
create or replace procedure readtab as
mcolumname table%type;
BEGIN
begin
select columnname
into :mcolumname
from table where rownum <2;
begin
UTL_MAIL.send(
sender => 'me@domain.com',
recipients => 'user@somewhere.com',
cc => 'user@somewhere.com',
bcc => 'boss@somewhere.com',
subject => 'Subject',
message => 'This is the value of columnname:'||:mcolumnname);
end;
exception
when no_data_found then
null;
end;
END;

NB. Method 2 is more efficient and will NOT send the mail if the table is empty... If you choose method 1, you should add additional logic to the code so that it detects that no records exist in the table.

hope this helps!
kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Jesse Delk
Frequent Advisor

Re: Unix script to read from oracle table

Alot of info I left out.

Running Oracle 8i. Unable to get utl_mail to work due to low memory issues. HP-Ux 11i.

This is what I have come up with so far but seems ugly...hoping someone has something better.


$ cat batch.sh

#!/usr/bin/ksh
export VAR=0
export VAR=`$ORACLE_HOME/bin/sqlplus -s user/password@sid<whenever sqlerror exit 1
set escape off
set head off
set verify off
select batch from ashdrum where rownum < 2;
EOF
`
if [ $VAR -gt 0 ]; then
mailx -s "Batch $VAR" user@somewhere.com sqlplus user/password@sid @batch.sql;
else
exit
fi


$ cat batch.sql
declare
var number;
Begin
select batch into var from ashdrum where rownum < 2;
delete from ashdrum where batch = var;
end;
/
commit;
exit;
Jonathan Fife
Honored Contributor

Re: Unix script to read from oracle table

I would be careful about separating the emailed select from the select / delete. There is no guarantee that Oracle will return the same top result each time, and thus no guarantee that you are deleting the same row that was emailed.

Also, I assume that you have a unique index on columnname, or you could possibly delete more than one row.

Here is a modified version of your script:
#!/usr/bin/ksh
export VAR=0

echo $($ORACLE_HOME/bin/sqlplus -s /nolog <conn user/pass
whenever sqlerror exit 1
set escape off
set head off
set verify off
set termout off
set serveroutput on
declare var number;
begin
select column into var from table where rownum < 2;
dbms_output.put_line (var);
delete from table where column = var;
end;
/
quit;
EOF
) | read blah VAR blah

if [ -n $VAR ]; then
mailx -s "$VAR has been deleted" user@email.com
else
exit 1
fi
Decay is inherent in all compounded things. Strive on with diligence
Jesse Delk
Frequent Advisor

Re: Unix script to read from oracle table

Fife,

The only thing is that I need the Unix variable $VAR set to the item being deleted. Unix $VAR=oracle var. The oracle piece you sent within the unix script is working fine.

This way in my email I can say Batch 12345(the $VAR) has been deleted.

Right now when I run the code it brings me back diferent things for $VAR like 'declare','PLSQL', or just var.

Thanks for all the help.
Jonathan Fife
Honored Contributor

Re: Unix script to read from oracle table

Just realized that my script above would send something regardless of whether the table was empty, so here is one that works:

$cat batch.ksh
#!/usr/bin/ksh
VAR='NO_RESULTS'
export ORACLE_SID=test

echo $($ORACLE_HOME/bin/sqlplus -s /nolog <<^D
@batch.sql
^D
) | read blah VAR blah


if [ $VAR != 'NO_RESULTS' ]; then
echo $VAR
else
exit 1
fi

$cat batch.sql
conn / as sysdba
set escape off
set head off
set verify off
set serveroutput on
declare var number;
begin
select column into var from table where rownum < 2;
delete from table where column = var;
dbms_output.put_line(var);
EXCEPTION
WHEN OTHERS THEN
IF SQL%NOTFOUND THEN -- check for 'no data found'
var := 'NO_RESULTS';
dbms_output.put_line(var);
END IF;
end;
/

Decay is inherent in all compounded things. Strive on with diligence
Jonathan Fife
Honored Contributor

Re: Unix script to read from oracle table

Just realized that my script above would send something regardless of whether the table was empty, so here is one that works:

$cat batch.ksh
#!/usr/bin/ksh
VAR='NO_RESULTS'
export ORACLE_SID=test

echo $($ORACLE_HOME/bin/sqlplus -s /nolog <<^D
@batch.sql
^D
) | read blah VAR blah


if [ $VAR != 'NO_RESULTS' ]; then
echo $VAR
else
exit 1
fi

$cat batch.sql
conn / as sysdba
set escape off
set head off
set verify off
set serveroutput on
declare var number;
begin
select column into var from table where rownum < 2;
delete from table where column = var;
dbms_output.put_line(var);
EXCEPTION
WHEN OTHERS THEN
IF SQL%NOTFOUND THEN -- check for 'no data found'
var := 'NO_RESULTS';
dbms_output.put_line(var);
END IF;
end;
/

Decay is inherent in all compounded things. Strive on with diligence
Jesse Delk
Frequent Advisor

Re: Unix script to read from oracle table

Fife,

Using your latest one, it gives me PL/SQL for the unix $VAR. Trying to find if I'm getting an error and its putting that into the $VAR.
Jonathan Fife
Honored Contributor

Re: Unix script to read from oracle table

To troubleshoot just comment out the
"| read blah VAR blah"

The first word of output should go to the blah variable, the second to VAR (so the second word is what's important), and the rest to blah.
Decay is inherent in all compounded things. Strive on with diligence