Hi
Inside the sql session, you cannot put shell scripting codes. I mean the place between BEGIN and END should contain only PL/SQL codes, not shell codes like echo and set. Something like this:
Code:
SET HEADING OFF
SET SERVEROUTPUT ON
SET FEEDBACK OFF
DECLARE
CNT NUMBER(5);
DROPCNT NUMBER(5);
ERRORCNT NUMBER(5);
BEGIN
SELECT COUNT(*) INTO CNT FROM USAGE_ INTRNL_ERROR WHERE AUDIT_GROUP_ID='&1';
IF ( CNT != &3) THEN
SELECT INTRL_DROP_QTY,INTRL_ERROR_QTY INTO DROPCNT,ERRORCNT
FROM USAGE_AUDIT_GROUP WHERE AUDIT_GROUP_ID='&1';
DROPCNT := GREATEST(DROPCNT-CNT, 0);
UPDATE USAGE_AUDIT_GROUP
SET INTRL_DROP_QTY=DROPCNT ,INTRL_ERROR_QTY='&CNT'
WHERE USAGE_AUDIT_GROUP WHERE AUDIT_GROUP_ID='&1' ;
END IF;
END;
/
EXIT;
Guru.
Hi I have executed the above one the script ran with out any errors but the tables values are not updated .
---------- Post updated at 04:42 AM ---------- Previous update was at 04:37 AM ----------
Quote:
Originally Posted by arun.ijhs.klm
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 > ./${DirectoryName}/Query.sql
SET HEADING OFF
SET SERVEROUTPUT ON
SET FEEDBACK OFF
DECLARE
CNT NUMBER(5);
DROPCNT NUMBER(5);
ERRORCNT NUMBER(5);
BEGIN
SELECT COUNT(*) INTO CNT FROM USAGE_INTRNL_ERROR WHERE AUDIT_GROUP_ID='&1';
IF ( CNT != &3) THEN
Dbms_Output.put_line('SELECT INTRL_DROP_QTY,INTRL_ERROR_QTY, ERRORCNT FROM USAGE_AUDIT_GROUP WHERE AUDIT_GROUP_ID=''&1'';';
SELECT INTRL_ERROR_QTY INTO DROPCNT FROM USAGE_AUDIT_GROUP WHERE AUDIT_GROUP_ID = '&1';
SET DROPCNT=DROPCNT-CNT;
Dbms_Output.put_line('UPDATE USAGE_AUDIT_GROUP SET INTRL_DROP_QTY='||DROPCNT||' ,INTRL_ERROR_QTY='||CNT||' WHERE USAGE_AUDIT_GROUP WHERE AUDIT_GROUP_ID=''&1'';'
END IF;
END;
/
EXIT;
EOF`
touch ./$DirectoryName/SELECTQuery_$TimeStamp.sql
touch ./$DirectoryName/UPDATEQuery_$TimeStamp.sql
for stmt in `cat ./${DirectoryName}/Query.sql`
do
is_select=`echo ${stmt} | grep "^SELECT" | wc -l`
if [ ${is_select -ne 0 ]
then
echo ${stmt} >> ./$DirectoryName/SELECTQuery_$TimeStamp.sql
else
echo ${stmt} >> ./$DirectoryName/UPDATEQuery_$TimeStamp.sql
fi
done
Tried this one ..but getting some syntax error :
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 > ./${DirectoryName}/Query.sql
SET HEADING OFF
SET SERVEROUTPUT ON
SET FEEDBACK OFF
DECLARE
CNT NUMBER(5);
DROPCNT NUMBER(5);
ERRORCNT NUMBER(5);
BEGIN
SELECT COUNT(*) INTO CNT FROM USAGE_INTRNL_ERROR WHERE AUDIT_GROUP_ID='&1';
IF ( CNT != &3) THEN
Dbms_Output.put_line('SELECT INTRL_DROP_QTY,INTRL_ERROR_QTY, ERRORCNT FROM USAGE_AUDIT_GROUP WHERE AUDIT_GROUP_ID=''&1'';';
SELECT INTRL_ERROR_QTY INTO DROPCNT FROM USAGE_AUDIT_GROUP WHERE AUDIT_GROUP_ID = '&1';
SET DROPCNT=DROPCNT-CNT;
Dbms_Output.put_line('UPDATE USAGE_AUDIT_GROUP SET INTRL_DROP_QTY='||DROPCNT||' ,INTRL_ERROR_QTY='||CNT||' WHERE USAGE_AUDIT_GROUP WHERE AUDIT_GROUP_ID=''&1'';'
END IF;
END;
/
EXIT;
EOF`
touch ./$DirectoryName/SELECTQuery_$TimeStamp.sql
touch ./$DirectoryName/UPDATEQuery_$TimeStamp.sql
for stmt in `cat ./${DirectoryName}/Query.sql`
do
is_select=`echo ${stmt} | grep "^SELECT" | wc -l`
if [ ${is_select -ne 0 ]
then
echo ${stmt} >> ./$DirectoryName/SELECTQuery_$TimeStamp.sql
else
echo ${stmt} >> ./$DirectoryName/UPDATEQuery_$TimeStamp.sql
fi
done
syntax error : `>' unexpected
Am getting this error at this line:
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)