Do i have to use SQLLOADER or is there any other way ?
how to i input the return code of the shell script into the table ?
Veera
Answer: no, sqlldr is not necessary and more clumsy than the following method:
Script 1:
Code:
#! /bin/ksh
...
myscript # Call your script
RC=$? # Get your script's return status
HOST=$(uname -n)
# Call SQL*Plus
sqlplus username/password <<EOF
BEGIN
INSERT INTO status_table VALUES('${HOST}', '${RC}');
COMMIT;
END;
/
EOF
or
sqlplus username/password <<EOF
BEGIN
your_plsql_sp('${HOST}', '${RC}');
END;
/
EOF
Scrpt 2 that monitors the status table:
Code:
#! /bin/ksh
...
# Loop forever
while :
do
{
sqlplus -S username/password <<EOF
set heading off feedback off serveroutput on
DECLARE
STATUS_CODE VARCHAR2(50) := 'none';
BEGIN
-- Attempt to find the status for the specified host
SELECT 'StatusCode:' || 0
INTO STATUS_CODE
FROM status_table
WHERE hostname = '${HOST}';
-- Found it, print the status and drop out of PL/SQL block
DBMS_OUTPUT.PUT_LINE (STATUS_CODE);
EXCEPTION
-- No status yet, drop out with a -1 code
WHEN NO_DATA_FOUND THEN
SELECT 'StatusCode:-1'
INTO STATUS_CODE
FROM dual;
DBMS_OUTPUT.PUT_LINE (STATUS_CODE);
END;
/
EOF
} | while read LINE # Find out what SQL*Plus says
do
# Look for the status message
if print $LINE | /usr/xpg4/bin/grep -iq StatusCode
then
# Parse out the actual status code and break from inner loop
STATUS_CODE=$(print $LINE | awk -F: '{print $2}')
break
fi
done
# If status code is not -1, break out of endless loop
if [ ${STATUS_CODE:=-1} -ge 0 ]
then
break
fi
# Otherwise, continue monitoring the status table
print "Still waiting for status..."
sleep 5
done
if [ ${STATUS_CODE} -eq 0 ]
then
maix -s Success...
else
maix -s Sorry, it failed...
fi
This is one way of accomplishing your task.
If you really want to use a trigger then you are going to have to utilize UTL_SMTP.
Hi,
I would like to know how to update a table in Oracle database, if a command in one shell script either successfully completes or it fails.(like Y if its success or N if its a failure)
While the command is running,I am able to view the log file created in the Unix machine.After the command... (2 Replies)
Hi friends,
i am having a variable declared in .profile.i am changing its value in a shell script and then i am connecting to oracle and then from there i am calling a .sql called update.sql
STATUS is the variable declared in the .profile
===============================
if
sqlplus <<END... (3 Replies)
Hi all,
I have a interresting problem. My application is as follows:
From a shell script i will conn to a oracle database and fetch few rows using a select statement.
Then i want to sort these rows and return a column (collection of values of a column from the selected results) as array... (3 Replies)
I am using the following code in my shell script
list=`sqlplus -s $user/$pwd@$dbms<<EOF
WHENEVER SQLERROR EXIT SQL.SQLCODE
set pagesize 0 feedback off verify off heading off echo off
select * from control_tbl
where src_nm=$3
and extrct_nm=$4;
exit SQL.SQLCODE;
EOF`
ERROR=$?... (1 Reply)
Hi
I'm writing a shell script that connects to oracle database and fires query to check the availability of data in a table. In case of no data found then what will be the return code and how to handle in that in variable. Kindly provide with an example for better understanding... Thanks... (1 Reply)
hi
i am calling a oracle stored procedure(in the database) from unix shell scripting (a.sh).
the called stored procedure returns some values through OUT variables
i want to assign the return values of stored procedure in to unix shell script variable.
can you provide me the code.
... (1 Reply)
Hi, unix gurnis
I need help for following requirement for writing a shell scritp.
log in to oracle database, query one table total records (select count(*) from table1), pass the return value to a file.
Thanks in advance (2 Replies)
Hi,
I want to write a shell script which will export data from oracle table .
I don't want to save that data . I want the queries .
Right now i am right clicking on the table and clicking on export as to my desktop .
Please let me know if any one have any idea . (2 Replies)
Hello Folks,
I'm working on a requirement to automate the process of generating report(csv file) using metadata info stored in an Oracle table and E-mail it to respective people.
Meta data table:
Report_ID,Report_SUB_ID,Report_DB,Report_SQL,Report_to_email_Id
1,1,DEV,'select * From... (2 Replies)
Discussion started by: venkat_reddy
2 Replies
LEARN ABOUT FREEBSD
atf-sh
ATF-SH(1) BSD General Commands Manual ATF-SH(1)NAME
atf-sh [-s shell] -- interpreter for shell-based test programs
SYNOPSIS
atf-sh script
DESCRIPTION
atf-sh is an interpreter that runs the test program given in script after loading the atf-sh(3) library.
atf-sh is not a real interpreter though: it is just a wrapper around the system-wide shell defined by ATF_SHELL. atf-sh executes the inter-
preter, loads the atf-sh(3) library and then runs the script. You must consider atf-sh to be a POSIX shell by default and thus should not
use any non-standard extensions.
The following options are available:
-s shell Specifies the shell to use instead of the value provided by ATF_SHELL.
ENVIRONMENT
ATF_LIBEXECDIR Overrides the builtin directory where atf-sh is located. Should not be overridden other than for testing purposes.
ATF_PKGDATADIR Overrides the builtin directory where libatf-sh.subr is located. Should not be overridden other than for testing purposes.
ATF_SHELL Path to the system shell to be used in the generated scripts. Scripts must not rely on this variable being set to select a
specific interpreter.
EXAMPLES
Scripts using atf-sh(3) should start with:
#! /usr/bin/env atf-sh
Alternatively, if you want to explicitly choose a shell interpreter, you cannot rely on env(1) to find atf-sh. Instead, you have to hardcode
the path to atf-sh in the script and then use the -s option afterwards as a single parameter:
#! /path/to/bin/atf-sh -s/bin/bash
ENVIRONMENT
ATF_SHELL Path to the system shell to be used in the generated scripts.
SEE ALSO atf-sh(3)BSD September 27, 2014 BSD