Just open a same sql session like you had one for the select statement and so something like this:
Code:
sqlplus -s xxxx/xxxx@$ORACLE_SID $a $b $c <<EOF
SET HEADING OFF
SET SERVEROUTPUT ON
SET FEEDBACK OFF
DECLARE
CNT NUMBER(5);
BCNT NUMBER(5);
BEGIN
SELECT COUNT(*) INTO CNT FROM USG_ERROR WHERE USAGE_ID='&1';IF ( CNT != &3) THEN -- Fetch the intrl_drp from usage_id and decrement it by &2. -Write the update query.END IF;
END;
/
exit;
EOF
This is a kind of something which you would like to have. I hope you can develop it from here. Keep in mind, the variables you passed to sql script, will be contained in the form of bind variables &1, &2...
I have tried in this way but getting some unexpected results can you please check if the script is fine
Code:
nawk '{{print $1,$2,$3}}' ./$DirectoryName/AuditGroupTableTableData_$TimeStamp.txt | while read a b c
do
echo $a
echo $b
echo $c
ret=`sqlplus -s $db_user/$db_pwd@$db_sid $a $b $c <<EOF
SET HEADING OFF
SET SERVEROUTPUT ON
SET FEEDBACK OFF
DECLARE
CNT NUMBER(5);
DROPCNT NUMBER(5);
ERRORCNT NUMBER(5);
BEGIN
echo "SELECT COUNT(*) INTO CNT FROM USAGE_ INTRNL_ERROR WHERE AUDIT_GROUP_ID='&1'";
IF ( CNT != &3) THEN
echo "SELECT INTRL_DROP_QTY,INTRL_ERROR_QTY INTO DROPCNT,ERRORCNT FROM USAGE_AUDIT_GROUP WHERE AUDIT_GROUP_ID='&1';">> ./$DirectoryName/SELECTQuery_$TimeStamp.sql
SET DROPCNT=DROPCNT-CNT;
echo "UPDATE USAGE_AUDIT_GROUP SET INTRL_DROP_QTY='&DROPCNT' ,INTRL_ERROR_QTY='&CNT' WHERE USAGE_AUDIT_GROUP WHERE AUDIT_GROUP_ID='&1' ;" >>./$DirectoryName/UPDATEQuery_$TimeStamp.sql
END IF;
END;
EXIT;
EOF`
done
It didnt throw any error msgs and also no .sql files created in the directories
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 gurus,
I am having a file containing a list of tables.i want to find the count of records inside thes tables.
for this i have to connect into database and i have to put the count for all the tables inside another file i used the following loop once all the tablenames are inside the file.
... (1 Reply)
Hi,
I want to compare two tables fieldwise using shell script. Can anyone help me regarding the same.
The approach which i tried is to first move the two tables in simple txt file where each field is now seperated by space. But i can't retrive each field with "space" as a seperator b'coz there... (1 Reply)
I am coding shell script.
I need to connect to different databases like DB2, Oracle and Sybase.
I would then need to query tables where it has all the groups, users for that database.
I would also need who has what kind of permissions.
EG: I know for DB2 some TABAUTH table needs to be... (0 Replies)
Hi,
I have two databases with same tables on different servers.I need to check the data content in each table and if something is missing, should print that.
I have a tool which takes the snapshot the table structure,index so on and compares with the other server tables snapshot.
Now i need... (1 Reply)
Hi All,
In my script I have used the below code to retrieve the instance name
V_INSTANCE_NAME=`sqlplus -s ${APPS_USR_PSWD} <<+
set pagesize 0 linesize 256 feedback off verify off head off echo off
set serveroutput off
select... (2 Replies)
Hi guys.
I am new to this forum so cheers :)
I have a question.
I have created a shell script that puts all the output into 1 file.
The out put is like this:
-----IP------
Data
Data
Data
-----IP------
Data
Data
Data
How can i arrange this to be like this:
IP | Data |... (3 Replies)
Heyy,
any help would be grateful....
LOOKING FOR THE WAYS TO SEND AN EMAIL WITH ATTACHMENT & HTML TABLES IN BODY THROUGH SHELL SCRIPT (LINUX)..NOT SURE, IF WE HAVE ANY INBUILT HTML TAG OR UNIX COMMAND TO SEND THE ATTACHMENTS. KINDLY HELP
below is small script posted for our understanding..... (2 Replies)
What I have:
I have a input.sh (script which basically connect to mysql-db and query's multiple tables to write back the output to output1.out file in a directory)
note: I need to pass an integer (unique_id = anything b/w 1- 1000) next to the script everytime I run the script which generates... (3 Replies)
Discussion started by: kkpand
3 Replies
LEARN ABOUT DEBIAN
cvm-sql
cvm-sql(7) Miscellaneous Information Manual cvm-sql(7)NAME
cvm-sql - CVM SQL Framework
QUERY SUBSTITUTION
$VAR and ${VAR} are replaced with the quoted value of the environment variable named VAR. Use the second form when VAR contains anything
other than alpha-numeric or underscore (_) characters. Do not include quotes in the query. The variables account and domain contain the
account and domain names given by the CVM client. $$ is replaced with a single dollar sign.
QUERY RESULTS
The query must produce the following fields exactly in order. All required fields must not be null or empty.
1. Password, encrypted with crypt(3) (required)
2. Actual account name (required)
3. User ID (integer) (required)
4. Group ID (integer) (required)
5. Directory (required)
6. Real name (optional)
7. Login shell (optional)
8. Group name (optional)
9. Domain name (optional)
10. System account name (optional)
11. System account directory (optional)
12. Mailbox path (optional)
DEFAULT QUERY
SELECT password, username, userid, groupid, directory, realname, shell, groupname, domain, sys_username, sys_directory FROM accounts WHERE
username=$account AND domain=$domain
SEE ALSO cvm-mysql(8), cvm-pgsql(8), cvm-pwfile(8), cvm-qmail(8), cvm-unix(8), cvm-vmailmgr(8), cvm-benchclient(8), cvm-checkpassword(8), cvm-test-
client(8)
http://untroubled.org/cvm/cvm.html
cvm-sql(7)
03-22-2012
