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

LVL 1
ank5Asked:
Who is Participating?
 
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
 
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.