Operating System - HP-UX
1827853 Members
1621 Online
109969 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
Jesse Delk
Frequent Advisor

Re: Unix script to read from oracle table

I think I get the blah thing now. Instead of read blah VAR blah....I needed "read VAR blah blah"..... It seems as my batch number is the first num/word.
Jesse Delk
Frequent Advisor

Re: Unix script to read from oracle table

OK,
on this line

) | read blah VAR blah

I changed to ) | read VAR blah blah

It started picking up the right number. The problem now is that when the table is empty..I get a memoryfault error in unix. Any last changes to clean this up?
Jesse Delk
Frequent Advisor

Re: Unix script to read from oracle table

Actually........I changed the line back to
) | read blah VAR blah

and I still get the memory fault core error when the table is empty.
Jonathan Fife
Honored Contributor

Re: Unix script to read from oracle table

Hmmm, that is odd. I tested my script on 9.2.0.8, so that might be a difference between our versions.

Can you run just the sqlplus command, and if so does it crash, or is it something else crashing?
Decay is inherent in all compounded things. Strive on with diligence
Jesse Delk
Frequent Advisor

Re: Unix script to read from oracle table

running just the sql I get this error


set feedback off
set escape off
set head off
set verify off
set serveroutput on
declare var number;
begin
select batch into var from ashdrum where rownum < 2;
delete from ashdrum where batch = 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;
/


ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 9
ORA-01403: no data found
Jonathan Fife
Honored Contributor

Re: Unix script to read from oracle table

Oh, try declaring var as a char(20). I was setting it to 'NO_RESULT', which isn't a numeric :)
Decay is inherent in all compounded things. Strive on with diligence
Jesse Delk
Frequent Advisor

Re: Unix script to read from oracle table

Got it. I believe I'm golden now. Everything looks good. Table empty or not.

Thanks for all the help.
Jesse Delk
Frequent Advisor

Re: Unix script to read from oracle table

Thanks for all the help