Emailing results of a pl sql procedure from UNIX shell script
Hello All,
I am writing the below unix script to email the result of a small pl sql procedure:
Code:
#!/bin/bash
ORACLE_HOME=/opt/oracle/orcts/product/9.2.0; export ORACLE_HOME
SQLPLUS=$ORACLE_HOME/bin/sqlplus
sqlplus -s user/pass@Db_instance<<EOF
set echo off
set feedback off
set pages 0
VAR id number;
begin
select empid into :id from employee where employee_name='XYZ';
END;
EOF
echo "this is a test email for employee ID= $id" | mailx -r xyz@gmail.com -s "test email" abc@gmail.com
When i run the script, i am just getting the email without the id value.
Please assist.
Moderator's Comments:
edit by bakunin: as Yoda already said, CODE-tags please! It is entirely possible to edit oneselfs posts to put these tags in, btw., like i did for you right now.
The scope of that PL/SQL variable value is within the PL/SQL code.
By the way you don't need PL/SQL, you can use SQL instead:-
Code:
#!/bin/bash
ORACLE_HOME=/opt/oracle/orcts/product/9.2.0
export ORACLE_HOME
id=`${ORACLE_HOME}/bin/sqlplus -s user/pass@Db_instance << EOF
set echo off head off feed off pagesize 0 trimspool on linesize 1000 num 20
select empid from employee where employee_name='XYZ';
EOF`
echo "this is a test email for employee ID= $id" | mailx -r xyz@gmail.com -s "test email" abc@gmail.com
Thanks for the prompt response Jedi Master
This code i tried initially and it worked. But my requirement is to email more than one value after applying some logic through pl sql procedure. So, i was thinking to embed a pl sql procedure in the script.
For example i want to return the employee_id, sal, and department_name by joining the employee table and the department table in the query, how to i accomplish this?
This is just an example, my actual requirement is bigger though.
For example i want to return the employee_id, sal, and department_name by joining the employee table and the department table in the query, how to i accomplish this?
This is just an example, my actual requirement is bigger though.
Instead of capturing the output to a variable you could spool out to a temporary file, then use the file to create the mails body (here is just a sketch):
Code:
#!/bin/bash
export ORACLE_HOME="/opt/oracle/orcts/product/9.2.0"
${ORACLE_HOME}/bin/sqlplus -s user/pass@Db_instance > /some/file << EOF
set echo off head off feed off pagesize 0 trimspool on linesize 1000 num 20
select empid from employee where employee_name='XYZ';
EOF
cat /path/to/mail_header /some/file /path/to/mail_footer | mailx -r xyz@gmail.com -s "test email" abc@gmail.com
exit 0
"mail_header" will contain some adressing, like
Code:
Dear Someone,
find below the data you asked for:
-----------[Begin of Data]---------------------
and "mail_footer" something like:
Code:
----------[End of Data]-----------------
kind regards
whoever
Well , let me post my real requirement.
the script will select all the records from a table with the condition timestamp=(sysdate-1 hr). once the records are selected, it will check the flag value of each record and if the record matches a particular flag value, it will send out an email.
i want to accomplish this task in the shell script itself without using any additional files.
is this feasible?
i am a newbee to unix and doesn't have much knowlegde.
---------- Post updated at 05:22 PM ---------- Previous update was at 04:36 PM ----------
here is my sample script
Code:
#!/bin/bash
ORACLE_HOME=/opt/oracle/orcts/product/9.2.0; export ORACLE_HOME
SQLPLUS=$ORACLE_HOME/bin/sqlplus
sqlplus -s user/pass@db_inst <<EOF
set echo off head off feed off pagesize 0 trimspool on linesize 1000
spool result.csv
select rownum,emp_id,emp_name from employees where dept_no=2;
spool off
exit;
EOF
a=1
b=2
while read f1 f2 f3
do
if $f1 -eq $a
then
echo "this is first test email for employee id= $f2 with name =$f3" | mailx -r abc@gmail.com -s "test email 1" xyz@gmail.com
elif $f1 -eq $b
then
echo "this is second test email for employee id= $f2 with name =$f3" | mailx -r abc@gmail.com -s "test email 1" xyz@gmail.com
fi
done < result.csv
getting the following errors on executing
Code:
test.sh[17]: 1: Execute permission denied.
test.sh[21]: 1: Execute permission denied.
test.sh[17]: 2: not found.
test.sh[21]: 2: not found.
it runs perfect without the if else decision making. and there are only 2 records in the result of my sql query.
Hi,
We have a procedure e.g. prc_synonym created in Oracle 12c Database. I want to do small change in procedure through Unix. I have that changed procedure (prc_synonym) in proc.sql file. Want to recompile that procedure through Unix so that changes should reflect in existing procedure in... (10 Replies)
Hi everyone,
I am new to unix and bash and in need of some help.
I am writing a script that will execute a SQL query. The script runs and the SQl query runs, but I cannot figure out how to save the results as a file that can be emailed to a user. Here is my scripts thus far:
#!/bin/sh
SID=$1... (2 Replies)
hi,
Could anyone tell me how to pass the output values of the PL/SQL procedure to Shell script and how to store that values in a shell script variable...
Thanks in advance... (5 Replies)
Hi Dudes :)
I want a unix shell script to pass value to SQL stored procedure.
Below is the procedure
declare
res varchar2(10);
begin
odm_load_check('PRE_SANITY',res);
dbms_output.put_line(res);
end;
select * from error_log;
truncate table error_log;
select * from test; (1 Reply)
Here's a shell script snippet.....
cd $ORACLE_HOME/bin
Retval=`sqlplus -s <<eof
$TPDB_USER/april@$TPD_DBCONN
whenever SQLERROR exit 2 rollback
whenever OSERROR exit 3 rollback
set serveroutput on
set pages 999
var status_desc char(200)
var status_code... (1 Reply)
hai,
can anybody say how to call or to execute an oracle stored procedure in oracle from unix...
thanks in advance.... for ur reply....
by,
leo (2 Replies)
Hi
I want to get the a field from a SQL query into unix shell script variable. the whole situation is like this.
1. Opened a cursor to a table in DB2 databse.
2. Fetching individual rows with the help of cursor.
3. Each row has 4 fields.
I want each of the field in individual shell... (1 Reply)
Hi
i'm calling a pl/sql procedure which is returning one variable.
i'm trying to assing this value to variable in shell script
the code i wrote is
** in shell script**
var= 'sqlplus user/pass @ret.sql'
echo $var
**
and
variable dum_var number
exec rt_test(:DUM_VAR);
exit;
in... (4 Replies)
12-17-2015
