Shell Scripting - Anonymous Block

Question

I have a procedure that does a certain task and exits. The procedure has to be replicated to around 40 databases, that will run this procedure. My concern is that if there is a change all across, every procedure has to be changed.

How can I create a shell script, that takes input username, password and SID and runs an anonymous block (the same procedure is converted as an anonymous block and put on server) and it runs it.

What have you done so far?

Nitish
– Nitish

2017-02-16 11:08:17 +00:00
Commented Feb 16, 2017 at 11:08 — Nitish
– Nitish, Commented Feb 16, 2017 at 11:08

J. Chomel · Accepted Answer · 2017-02-16 15:00:55Z

1

Here is the code I sometimes use:

dbs_list="
DB1.USR1
DB2.USR2
"
for x in ${dbs_list}
do
  DB_=` echo ${x}|cut -f "1" -d . `
  USR=` echo ${x}|cut -f "2" -d . `
  echo "################################               #### "
  echo "#processinb ${USR} @ ${DB_} --   ...#"
  echo "enter password:"
  read -s PWD
  sqlplus ${USR}/${PWD}@${DB_} << _EOF
    set serveroutput on
    prompt HERE is my anonymous code block
    begin
      dbms_output.put_line('do my things');
    end;
    /
_EOF
done

Note the importance:

to have this / thing at the end of the PLSQL code block
to have this last _EOF thing at the very beginning of the line after the sqlplus pseudo-file block (or so called famous Here-document )

Hope this helps.

edited Feb 16, 2017 at 15:00

answered Feb 16, 2017 at 14:49

J. Chomel

8,44215 gold badges44 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Shell Scripting - Anonymous Block

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related