• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 434
  • Last Modified:

executing a shell script

I have created the below shell script to disable triggers on a particualr schema.

This works fine in one environemnt. It disables triggers and also shows the output on the putty console, using which I am connecting to the Solaris box. But on another environment it just returns without giving any output. Can someone please help and let me know what could be the reason.

I even tried putting incorrect user/password in the script, hoping it would give an error but it just returns without giving any output.
#!/bin/sh
echo "select 'ALTER TABLE' , table_name,  'DISABLE ALL TRIGGERS;' from user_tables;" | sqlplus -S user/passowrd460@dbserver | \
grep "ALTER TABLE" | sqlplus -S user/password@dbserver

Open in new window

0
ank5
Asked:
ank5
  • 4
  • 3
  • 3
2 Solutions
 
slightwv (䄆 Netminder) Commented:
Not really an answer to the question but why are you calling sqlplus twice?
-------------------------
#!/bin/sh
sqlplus -S user/password@dbserver <<EOF

begin
for i in (select table_name from user_tables) loop
    execute immediate 'ALTER TABLE ' ||  table_name || ' DISABLE ALL TRIGGERS';
end loop;
end;
/

EOF
0
 
johnsoneSenior Oracle DBACommented:
With slightwv's post you would not see any output either, but by design.  Not sure if that is what you want.  If not, you can add output lines to his post.

I want to add a word of caution to doing things this way, and only because I got burned by this once.  If you have the intention of enabling all the triggers at some point, are you sure you want to re-enable all the triggers?  What if there was a disabled trigger before you started?  You would enable that without necessarily realizing it.
0
 
ank5Author Commented:
Thanks, I do not intend to re-enable the triggers

the above script seems to get stuck. I added the echo statement as well, but it never gets printed

#!/bin/sh
sqlplus -S schemaname/pwd@dbname

begin
for i in (select table_name from user_tables) loop
    execute immediate 'ALTER TABLE ' ||  table_name || ' DISABLE ALL TRIGGERS';
echo "Altered "
end loop;
end;
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
johnsoneSenior Oracle DBACommented:
You cannot use echo there.  echo is a shell command and you are inside SQL*Plus.

Try this:

#!/bin/sh
sqlplus -S schemaname/pwd@dbname << END

begin
for i in (select table_name from user_tables) loop
    execute immediate 'ALTER TABLE ' ||  table_name || ' DISABLE ALL TRIGGERS';
    dbms_output.put_line(table_name || ' altered');
end loop;
end;
END
0
 
ank5Author Commented:
thanks, I tried this script but it returns without printing anything.
0
 
slightwv (䄆 Netminder) Commented:
One tweak:

#!/bin/sh
sqlplus -S schemaname/pwd@dbname << END
set server output on

begin
...



That said, how are you executing this script that is different form the one through putty?
0
 
ank5Author Commented:
Yes, I am executing this through putty. I have named it dt.sh and executing as below

sh ./dt.sh
0
 
slightwv (䄆 Netminder) Commented:
So both scripts are executed through putty?

I'm not a putty person but if both are executed from the same client using the same tools, then the issue must be on the remote machine.
0
 
johnsoneSenior Oracle DBACommented:
Isn't "server output" one word?

It should be

set serveroutput on

not

set server output on

That could be your issue.
0
 
slightwv (䄆 Netminder) Commented:
oops.  My mistake.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now