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
# 15  
Old 03-26-2012
Run PROC directly to check
Can you please execute the PL/SQL proc in some Oracle DEV Tool like Toad or SQL tools and check the output.

Code:
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;
/

# 16  
Old 03-26-2012
Quote:
Originally Posted by arun.ijhs.klm
Can you please execute the PL/SQL proc in some Oracle DEV Tool like Toad or SQL tools and check the output.

Code:
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;
/

I cannot use those tools here Smilie
# 17  
Old 03-26-2012
Can you give the whole content of the UPDate.sql
# 18  
Old 03-26-2012
Quote:
Originally Posted by arun.ijhs.klm
Can you give the whole content of the UPDate.sql
Code:
SQL*Plus:
Release
10.2.0.4.0
-
Production
Copyright
(c)
1982,
2007,
Oracle.
All
Rights
Reserved.
Usage
1:
sqlplus
-H
|
-V
-H
Displays
the
SQL*Plus
version
and
the
usage
help.
-V
Displays
the
SQL*Plus
version.
Usage
2:
sqlplus
[
[<option>]
[<logon>]
[<start>]
]
<option>
is:
[-C
<version>]
[-L]
[-M
"<options>"]
[-R
<level>]
[-S]
-C
<version>
Sets
the
compatibility
of
affected
commands
to
the
version
specified
by
<version>.
The
version
has
the
form
"x.y[.z]".
For
example,
-C
10.2.0
-L
Attempts
to
log
on
just
once,
instead
of
reprompting
on
error.
-M
"<options>"
Sets
automatic
HTML
markup
of
output.
The
options
have
the
form:
HTML
[ON|OFF]
[HEAD
text]
[BODY
text]
[TABLE
text]
[ENTMAP
{ON|OFF}]
[SPOOL
{ON|OFF}]
[PRE[FORMAT]
{ON|OFF}]
-R
<level>
Sets
restricted
mode
to
disable
SQL*Plus
commands
that
interact
with
the
file
system.
The
level
can
be
1,
2
or
3.
The
most
restrictive
is
-R
3
which
disables
all
user
commands
interacting
with
the
file
system.
-S
Sets
silent
mode
which
suppresses
the
display
of
the
SQL*Plus
banner,
prompts,
and
echoing
of
commands.
<logon>
is:
(<username>[/<password>][@<connect_identifier>]
|
/)
[AS
SYSDBA
|
AS
SYSOPER]
|
/NOLOG
Specifies
the
database
account
username,
password
and
connect
identifier
for
the
database
connection.
Without
a
connect
identifier,
SQL*Plus
connects
to
the
default
database.
The
AS
SYSDBA
and
AS
SYSOPER
options
are
database
administration
privileges.
The
/NOLOG
option
starts
SQL*Plus
without
connecting
to
a
database.
<start>
is:
@<URL>|<filename>[.<ext>]
[<parameter>
...]
Runs
the
specified
SQL*Plus
script
from
a
web
server
(URL)
or
the
local
file
system
(filename.ext)
with
specified
parameters
that
will
be
assigned
to
substitution
variables
in
the
script.
When
SQL*Plus
starts,
and
after
CONNECT
commands,
the
site
profile
(e.g.
$ORACLE_HOME/sqlplus/admin/glogin.sql)
and
the
user
profile
(e.g.
login.sql
in
the
working
directory)
are
run.
The
files
may
contain
SQL*Plus
commands.
Refer
to
the
SQL*Plus
User's
Guide
and
Reference
for
more
information.
SQL*Plus:
Release
10.2.0.4.0
-
Production
Copyright
(c)
1982,
2007,
Oracle.
All
Rights
Reserved.
Usage
1:
sqlplus
-H
|
-V
-H
Displays
the
SQL*Plus
version
and
the
usage
help.
-V
Displays
the
SQL*Plus
version.
Usage
2:
sqlplus
[
[<option>]
[<logon>]
[<start>]
]
<option>
is:
[-C
<version>]
[-L]
[-M
"<options>"]
[-R
<level>]
[-S]
-C
<version>
Sets
the
compatibility
of
affected
commands
to
the
version
specified
by
<version>.
The
version
has
the
form
"x.y[.z]".
For
example,
-C
10.2.0
-L
Attempts
to
log
on
just
once,
instead
of
reprompting
on
error.
-M
"<options>"
Sets
automatic
HTML
markup
of
output.
The
options
have
the
form:
HTML
[ON|OFF]
[HEAD
text]
[BODY
text]
[TABLE
text]
[ENTMAP
{ON|OFF}]
[SPOOL
{ON|OFF}]
[PRE[FORMAT]
{ON|OFF}]
-R
<level>
Sets
restricted
mode
to
disable
SQL*Plus
commands
that
interact
with
the
file
system.
The
level
can
be
1,
2
or
3.
The
most
restrictive
is
-R
3
which
disables
all
user
commands
interacting
with
the
file
system.
-S
Sets
silent
mode
which
suppresses
the
display
of
the
SQL*Plus
banner,
prompts,
and
echoing
of
commands.
<logon>
is:
(<username>[/<password>][@<connect_identifier>]
|
/)
[AS
SYSDBA
|
AS
SYSOPER]
|
/NOLOG
Specifies
the
database
account
username,
password
and
connect
identifier
for
the
database
connection.
Without
a
connect
identifier,
SQL*Plus
connects
to
the
default
database.
The
AS
SYSDBA
and
AS
SYSOPER
options
are
database
administration
privileges.
The
/NOLOG
option
starts
SQL*Plus
without
connecting
to
a
database.
<start>
is:
@<URL>|<filename>[.<ext>]
[<parameter>
...]
Runs
the
specified
SQL*Plus
script
from
a
web
server
(URL)
or
the
local
file
system
(filename.ext)
with
specified
parameters
that
will
be
assigned
to
substitution
variables
in
the
script.
When
SQL*Plus
starts,
and
after
CONNECT
commands,
the
site
profile
(e.g.
$ORACLE_HOME/sqlplus/admin/glogin.sql)
and
the
user
profile
(e.g.
login.sql
in
the
working
directory)
are
run.
The
files
may
contain
SQL*Plus
commands.
Refer
to
the
SQL*Plus
User's
Guide
and
Reference
for
more
information.
SQL*Plus:
Release
10.2.0.4.0
-
Production
Copyright
(c)
1982,
2007,
Oracle.
All
Rights
Reserved.
Usage
1:
sqlplus
-H
|
-V
-H
Displays
the
SQL*Plus
version
and
the
usage
help.
-V
Displays
the
SQL*Plus
version.
Usage
2:
sqlplus
[
[<option>]
[<logon>]
[<start>]
]
<option>
is:
[-C
<version>]
[-L]
[-M
"<options>"]
[-R
<level>]
[-S]
-C
<version>
Sets
the
compatibility
of
affected
commands
to
the
version
specified
by
<version>.
The
version
has
the
form
"x.y[.z]".
For
example,
-C
10.2.0
-L
Attempts
to
log
on
just
once,
instead
of
reprompting
on
error.
-M
"<options>"
Sets
automatic
HTML
markup
of
output.
The
options
have
the
form:
HTML
[ON|OFF]
[HEAD
text]
[BODY
text]
[TABLE
text]
[ENTMAP
{ON|OFF}]
[SPOOL
{ON|OFF}]
[PRE[FORMAT]
{ON|OFF}]
-R
<level>
Sets
restricted
mode
to
disable
SQL*Plus
commands
that
interact
with
the
file
system.
The
level
can
be
1,
2
or
3.
The
most
restrictive
is
-R
3
which
disables
all
user
commands
interacting
with
the
file
system.
-S
Sets
silent
mode
which
suppresses
the
display
of
the
SQL*Plus
banner,
prompts,
and
echoing
of
commands.
<logon>
is:
(<username>[/<password>][@<connect_identifier>]
|
/)
[AS
SYSDBA
|
AS
SYSOPER]
|
/NOLOG
Specifies
the
database
account
username,
password
and
connect
identifier
for
the
database
connection.
Without
a
connect
identifier,
SQL*Plus
connects
to
the
default
database.
The
AS
SYSDBA
and
AS
SYSOPER
options
are
database
administration
privileges.
The
/NOLOG
option
starts
SQL*Plus
without
connecting
to
a
database.
<start>
is:
@<URL>|<filename>[.<ext>]
[<parameter>
...]
Runs
the
specified
SQL*Plus
script
from
a
web
server
(URL)
or
the
local
file
system
(filename.ext)
with
specified
parameters
that
will
be
assigned
to
substitution
variables
in
the
script.
When
SQL*Plus
starts,
and
after
CONNECT
commands,
the
site
profile
(e.g.
$ORACLE_HOME/sqlplus/admin/glogin.sql)
and
the
user
profile
(e.g.
login.sql
in
the
working
directory)
are
run.
The
files
may
contain
SQL*Plus
commands.
Refer
to
the
SQL*Plus
User's
Guide
and
Reference
for
more
information.

---------- Post updated at 09:53 AM ---------- Previous update was at 09:36 AM ----------

Quote:
Originally Posted by arun.ijhs.klm
Can you please execute the PL/SQL proc in some Oracle DEV Tool like Toad or SQL tools and check the output.

Code:
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;
/

and also could you please check if these statements are correct or not
Code:
 IF ( CNT != &3) THEN
    Dbms_Output.put_line('SELECT INTRL_DROP_QTY,INTRL_ERROR_QTY INTO DROPCNT,ERRORCNT FROM USAGE_AUDIT_GROUP WHERE AUDIT_GROUP_NBR=''&1'';';
    SELECT INTRL_ERROR_QTY INTO DROPCNT FROM USAGE_AUDIT_GROUP WHERE AUDIT_GROUP_NBR = '&1';
    SET DROPCNT=DROPCNT-CNT;
    Dbms_Output.put_line('UPDATE USAGE_AUDIT_GROUP SET INTRL_DROP_QTY='||DROPCNT||' ,INTRL_ERROR_QTY='||CNT||'  WHERE WHERE AUDIT_GROUP_NBR=''&1'';'
  END IF;

are those statements in dbms_output correct :-/

---------- Post updated at 10:43 AM ---------- Previous update was at 09:53 AM ----------

Quote:
Originally Posted by arun.ijhs.klm
Can you give the whole content of the UPDate.sql
can we use spool code lyk:
Code:
ret=`sqlplus -s  /nolog << EOF
connect $db_user/$db_pwd@$db_sid;
SPOOL  ./$DirectoryName/AuditGroupTableTableData_$TimeStamp.txt;

inplace of this :
Code:
 ret=`sqlplus -s  $db_user/$db_pwd@$db_sid  $a $b $c <<EOF > ./${DirectoryName}/Query.sql

If yes can you tell me how
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