Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Need help in updating the tables inside shell script

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Need help in updating the tables inside shell script
# 8  
Old 03-22-2012
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.
# 9  
Old 03-23-2012
Please try the below code
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

# 10  
Old 03-26-2012
Quote:
Originally Posted by guruprasadpr
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 Smilie.

---------- 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:
Code:
echo ${stmt} >> ./$DirectoryName/SELECTQuery_$TimeStamp.sql

# 11  
Old 03-26-2012
Try this...
There were a couple of syntax errors in the code I gave before. The below code has those misses marked in red.

Can you also check whether the file
Quote:
${DirectoryName}/Query.sql
is created?

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
done

# 12  
Old 03-26-2012
Quote:
Originally Posted by arun.ijhs.klm
There were a couple of syntax errors in the code I gave before. The below code has those misses marked in red.

Can you also check whether the file is created?

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
done

I have executed it but the output sql files are not as expected.
SELECTQuery_$TimeStamp.sql is created with 0 bytes and
Query.sql ,UPDATEQuery_$TimeStamp.sql are filled with sql usage.
Code:
more UPDATEQuery_20120326081256.sql
SQL*Plus:
Release
10.2.0.4.0
-
Production
Copyright
(c)
1982,
2007,
Oracle.
All
Rights
Reserved.
Usage
1:
sqlplus
-H
|
-V

# 13  
Old 03-26-2012
Hi
The 'commit' statement is missing. Put the 'commit' statement just before the line containing the /
# 14  
Old 03-26-2012
Quote:
Originally Posted by guruprasadpr
Hi
The 'commit' statement is missing. Put the 'commit' statement just before the line containing the /
I have already tried it,but the problem is still the sameSmilie

Code:
END IF;
END;
EXIT;
commit;
/
EOF`
done
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Shell script automation using cron which query's MySQL Tables

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

2. HP-UX

Unable to send attachment with html tables in UNIX shell script

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)
Discussion started by: Harsha Vardhan
2 Replies

3. Shell Programming and Scripting

Arranging the output of a shell script into tables

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)
Discussion started by: Pandera
3 Replies

4. Shell Programming and Scripting

How to use V$tables in UNIX shell script?

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)
Discussion started by: kalidoss
2 Replies

5. Shell Programming and Scripting

Shell script for comparing data of tables

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)
Discussion started by: nessj
1 Replies

6. Shell Programming and Scripting

Tables to query to find users for database from shell script

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)
Discussion started by: pinnacle
0 Replies

7. Shell Programming and Scripting

compare two tables using shell script

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)
Discussion started by: dtidke
1 Replies

8. Shell Programming and Scripting

To find the count of records from tables present inside a file.

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)
Discussion started by: navojit dutta
1 Replies

9. Shell Programming and Scripting

updating a column in oracle table using shell script

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)
Discussion started by: sveera
3 Replies
Login or Register to Ask a Question