topic Re: Unix script to read from oracle table in Operating System - HP-UX

Unix script to read from oracle table

Jesse Delk — Wed, 19 Sep 2007 08:05:56 GMT

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.

Re: Unix script to read from oracle table

Oviwan — Wed, 19 Sep 2007 08:18:18 GMT

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

Re: Unix script to read from oracle table

Yogeeraj_1 — Thu, 20 Sep 2007 00:52:06 GMT

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

Re: Unix script to read from oracle table

Jesse Delk — Thu, 20 Sep 2007 06:34:33 GMT

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;

Re: Unix script to read from oracle table

Jonathan Fife — Thu, 20 Sep 2007 08:07:03 GMT

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

Re: Unix script to read from oracle table

Jesse Delk — Thu, 20 Sep 2007 08:58:59 GMT

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.

Re: Unix script to read from oracle table

Jonathan Fife — Thu, 20 Sep 2007 09:08:39 GMT

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;
/

Re: Unix script to read from oracle table

Jonathan Fife — Thu, 20 Sep 2007 09:09:35 GMT

Re: Unix script to read from oracle table

Jesse Delk — Thu, 20 Sep 2007 11:32:35 GMT

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.

Re: Unix script to read from oracle table

Jonathan Fife — Thu, 20 Sep 2007 12:02:35 GMT

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.

Re: Unix script to read from oracle table

Jesse Delk — Thu, 20 Sep 2007 13:00:36 GMT

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.

Re: Unix script to read from oracle table

Jesse Delk — Thu, 20 Sep 2007 13:24:29 GMT

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?

Re: Unix script to read from oracle table

Jesse Delk — Thu, 20 Sep 2007 13:37:01 GMT

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.

Re: Unix script to read from oracle table

Jonathan Fife — Thu, 20 Sep 2007 13:39:11 GMT

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?

Re: Unix script to read from oracle table

Jesse Delk — Thu, 20 Sep 2007 13:44:49 GMT

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

Re: Unix script to read from oracle table

Jonathan Fife — Thu, 20 Sep 2007 14:28:59 GMT

Oh, try declaring var as a char(20). I was setting it to 'NO_RESULT', which isn't a numeric :)

Re: Unix script to read from oracle table

Jesse Delk — Thu, 20 Sep 2007 14:46:33 GMT

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

Thanks for all the help.

Re: Unix script to read from oracle table

Jesse Delk — Mon, 24 Sep 2007 08:33:10 GMT

Thanks for all the help