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.
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)
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)
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)
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)
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)
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 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,
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)
Discussion started by: Aparna.N
10 Replies
LEARN ABOUT OPENSOLARIS
setusershell
getusershell(3C) Standard C Library Functions getusershell(3C)NAME
getusershell, setusershell, endusershell - get legal user shells
SYNOPSIS
#include <unistd.h>
char *getusershell(void);
void setusershell(void);
void endusershell(void);
DESCRIPTION
The getusershell() function returns a pointer to a legal user shell as defined by the system manager in the file /etc/shells. If
/etc/shells does not exist, the following locations of the standard system shells are used in its place:
/bin/bash /bin/csh
/bin/jsh /bin/ksh
/bin/ksh93 /bin/pfcsh
/bin/pfksh /bin/pfsh
/bin/sh /bin/tcsh
/bin/zsh /sbin/jsh
/sbin/pfsh /sbin/sh
/usr/bin/bash /usr/bin/csh
/usr/bin/jsh /usr/bin/ksh
/usr/bin/ksh93 /usr/bin/pfcsh
/usr/bin/pfksh /usr/bin/pfsh
/usr/bin/sh /usr/bin/tcsh
/usr/bin/zsh /usr/sfw/bin/zsh
/usr/xpg4/bin/sh
The getusershell() function opens the file /etc/shells, if it exists, and returns the next entry in the list of shells.
The setusershell() function rewinds the file or the list.
The endusershell() function closes the file, frees any memory used by getusershell() and setusershell(), and rewinds the file /etc/shells.
RETURN VALUES
The getusershell() function returns a null pointer on EOF.
BUGS
All information is contained in memory that may be freed with a call to endusershell(), so it must be copied if it is to be saved.
NOTES
Restricted shells should not be listed in /etc/shells.
SunOS 5.11 1 Nov 2007 getusershell(3C)
12-17-2015
