asked on

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

slightwv (䄆 Netminder)

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

johnsone

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.

ank5

ASKER

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;

ASKER CERTIFIED SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ank5

ASKER

thanks, I tried this script but it returns without printing anything.

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ank5

ASKER

Yes, I am executing this through putty. I have named it dt.sh and executing as below

sh ./dt.sh

slightwv (䄆 Netminder)

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.

johnsone

Isn't "server output" one word?

It should be

set serveroutput on

not

set server output on

That could be your issue.

slightwv (䄆 Netminder)

oops. My mistake.