Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Oracle function invoked from shell script doubt
Top Forums Shell Programming and Scripting Oracle function invoked from shell script doubt Post 302950881 by arun888 on Thursday 30th of July 2015 05:26:58 AM
Old 07-30-2015
hi vgersh,
Thanks a lot for your help.

It went through fine

---------- Post updated 07-30-15 at 04:26 AM ---------- Previous update was 07-29-15 at 03:22 PM ----------

hi all,

My code went through fine. But I would like to check whether I can make this efficiently or in a better way to code it. any help would be appreciated.

Code:
function_out()
{
function=$1
location_id=$2
sales=$3
echo $function
sqlplus -S xxxx/yyyy@xxxxdbt <<EOF
        SET head off
        SET serveroutput on
        spool chkbits.txt
        select PUT_UNIX.$function('$location_id',$sales) from dual;
        spool off
exit
EOF
}
sqlplus -S xxxx/yyyy@xxxxdbt <<EOF
set echo off
set heading off
set feedback off
set pagesize 0
spool queryout.txt
SELECT PROCESS_CODE FROM scheduled_process_queue where PROCESS_CODE like '%INFO.SDCNT%';
spool off
exit
EOF
while IFS='[, ]' read job location_id sales; do
count=`sqlplus -S xxxx/yyyy@xxxxdbt <<EOF
set echo off
set heading off
set feedback off
Set pagesize 0
SET head off
spool output.txt
select count(*)  from total_count where location_id='$location_id' and sales=$sales;
spool off
exit
EOF`
count=`cat output.txt`
if [[ $count > 0 ]];
then
sqlplus -S xxxx/yyyy@xxxxdbt <<EOF
delete from total_count where location_id='$location_id' and sales=$sales;
commit;
exit
EOF
echo "count is not  gretaehr than zero"
else
echo "count is zero"
fi
case $location_id in
DRUG|GROC)
function_out PUT_GROC_DRUG_COUNT $location_id $sales;;
CLUB|MASS|DECA|LIQ|PETS)
function_out PUT_OTHER_location_COUNT $location_id $sales;;
CONV)
function_out PUT_OTHER_location_COUNTi $location_id $sales;;
*)echo "$location_id location_id is not in the database";;
esac
out=`cat chkbits.txt`
if [ $out = 0 ]
then
echo "Check TRC & lenap Function "
sqlplus -S xxxx/yyyy@xxxxdbt <<EOF
SET head off
SET serveroutput on
spool bits1.txt
select PUT_UNIX.PUT_UNIX.PUT_TRC_COUNT('$location_id',$sales) from dual;
spool off
spool bits2.txt
select PUT_UNIX.PUT_UNIX.PUT_lenap_COUNT('$location_id',$sales) from dual;
spool off
exit
EOF
else
echo "failire"
fi
if [[ $out = 0 && $bits1 = 0 && $bits2 = 0 ]]
then
mailx -s "Alert Email for KMDS JOBS Suceess for location_id $location_id $sales" arunkumar@gmail.com  < /dev/null
else
mailx -s "Alert Email for KMDS JOBS Failed for location_id $location_id $sales"  arunkumar@gmail.com < /dev/null
fi
rm output.txt
rm bits1.txt
rm bits2.txt
rm chkbits.txt
done < out.txt
rm out.txt

 

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Unix + oracle doubt....involving shell script

....does the dbms_output.put_line work inside unix shell script? i mean this is to be inside the sqlplus connection as follows!! sqlplus -s $UP <<EOJ .. .. .. dbms_output.put_line ('Insertion procedure failed for UPC BC : ' || wk_key_value || ' Sqlcode: ' || SQLCODE || ' Error... (2 Replies)
Discussion started by: mexx_freedom
2 Replies

2. Shell Programming and Scripting

no shell invoked in crontab

Hi all, I am trying to run a script in crontab but I receive the email below. Note the empty variable SHELL. Apparently cron doesn't invoke a shell. That explains the errors in the script it is trying to execute. Anyone know what maybe wrong? Subject: Output from cron job... (3 Replies)
Discussion started by: ivanushka
3 Replies

3. Shell Programming and Scripting

i want to call a oracle function in my shell script

i want to call a oracle function in my shell script (4 Replies)
Discussion started by: dineshr85
4 Replies

4. Shell Programming and Scripting

shell script doubt

Hi, While reading a shell script ,i have come accross the following statements. script_name_full=$0 ***script_name=${script_name_full##*\} ***script_name_noexst=${script_name%%\.ksh} host_name=`hostname` ***host_name_short=${host_name%%\.*} can anybody tell me what is the purpose of marked... (5 Replies)
Discussion started by: ravi raj kumar
5 Replies

5. Shell Programming and Scripting

Doubt in running shell script

Hi, I'm a newbie in shell script. I have a problem in running my a.out in a bash shell script named vetri . The following is the code. #!bash/bin ./a.out abc.xyz where ./a.out is a c++ bin file and abc.xyz is an argument. My ./a.out expects an input (cin >> temp). How can I give the... (5 Replies)
Discussion started by: lchokka
5 Replies

6. Shell Programming and Scripting

shell script basic doubt

hi, I am new script learner, so my basic doubt is , how to store value of any command in a variable example $ ls | wc -l i want to stote the output of this in a variable c. so that i can use c in if else loop. and when do we use " ` " symbol in script.. can anyone also tell for... (5 Replies)
Discussion started by: hi2_t
5 Replies

7. Shell Programming and Scripting

SHELL SCRIPT Function Calling Another Function Please Help...

This is my function which is creating three variables based on counter & writing these variable to database by calling another function writeRecord but only one record is getting wrote in DB.... Please advise ASAP...:confused: function InsertFtg { FTGSTR="" echo "Saurabh is GREAT $#" let... (2 Replies)
Discussion started by: omkar.sonawane
2 Replies

8. Shell Programming and Scripting

Pass a variable string in To_Date Oracle function in shell script

Hello, I am trying to execute an SQL query from shell script. A part of script is something like this: fromDate=`echo $(date +"%F%T") | sed "s/-//g" | sed "s/://g"` $ORACLE_HOME/sqlplus -s /nolog <<EOD1 connect $COSDBUID/$COSDBPWD@$COSDBSID spool... (4 Replies)
Discussion started by: sanketpatel.86
4 Replies

9. Shell Programming and Scripting

How to call Oracle function with multiple arguments from shell script?

Dear All, I want to know how can i call oracle function from shell script code . My oracle function have around 5 input parameters and one return value. for name in *.csv; do echo "connecting to DB and start processing '$name' file at " echo "csv file name=$x" sqlplus -s scoot/tiger <!... (2 Replies)
Discussion started by: Balraj
2 Replies

10. Shell Programming and Scripting

Calling Oracle function from script

Hi I need to call a function in database and update the return value of that function with a value in csv file. test.csv 1,2,3,,5,,,8,9,10 1,2,3,4,5,,,8,9,10 1,2,3,,,,,8,9,10In the above file I want to replace column 2 with a value extracted from database like (select student_id from... (3 Replies)
Discussion started by: kev94
3 Replies

LEARN ABOUT CENTOS

idmap_tdb2

IDMAP_TDB2(8)						    System Administration tools 					     IDMAP_TDB2(8)

NAME

       idmap_tdb2 - Samba's idmap_tdb2 Backend for Winbind

DESCRIPTION

       The idmap_tdb2 plugin is a substitute for the default idmap_tdb backend used by winbindd for storing SID/uid/gid mapping tables in
       clustered environments with Samba and CTDB.

       In contrast to read only backends like idmap_rid, it is an allocating backend: This means that it needs to allocate new user and group IDs
       in order to create new mappings.

IDMAP OPTIONS

       range = low - high
	   Defines the available matching uid and gid range for which the backend is authoritative.

       script
	   This option can be used to configure an external program for performing id mappings instead of using the tdb counter. The mappings are
	   then stored int tdb2 idmap database. For details see the section on IDMAP SCRIPT below.

IDMAP SCRIPT

       The tdb2 idmap backend supports an external program for performing id mappings through the smb.conf option idmap config * : script or its
       deprecated legacy form idmap : script.

       The mappings obtained by the script are then stored in the idmap tdb2 database instead of mappings created by the incrementing id counters.
       It is therefore important that the script covers the complete range of SIDs that can be passed in for SID to Unix ID mapping, since
       otherwise SIDs unmapped by the script might get mapped to IDs that had previously been mapped by the script.

       The script should accept the following command line options.

		SIDTOID S-1-xxxx
		IDTOSID UID xxxx
		IDTOSID GID xxxx

       And it should return one of the following responses as a single line of text.

		UID:yyyy
		GID:yyyy
		SID:yyyy
		ERR:yyyy

EXAMPLES

       This example shows how tdb2 is used as a the default idmap backend.

		[global]
		idmap config * : backend = tdb2
		idmap config * : range = 1000000-2000000

       This example shows how tdb2 is used as a the default idmap backend using an external program via the script parameter:

		[global]
		idmap config * : backend = tdb2
		idmap config * : range = 1000000-2000000
		idmap config * : script = /usr/local/samba/bin/idmap_script.sh

AUTHOR

       The original Samba software and related utilities were created by Andrew Tridgell. Samba is now developed by the Samba Team as an Open
       Source project similar to the way the Linux kernel is developed.

Samba 4.0							    06/17/2014							     IDMAP_TDB2(8)
All times are GMT -4. The time now is 10:22 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy