CODEWITHSUNDEEP
sqloracle-databaseshell
itsh
itsh

Reputation: 1123

shell script to connect to DB and run some queries

I am trying to write a script where I can connect to the db as dba user and count the number of users. Eventually I want to update my script to check if users exist; kill their sessions; and then drop users. But at present I want to start with counting the users, just to make sure that my script is running.

But looks like it is not connecting to DB. 

    echo
    echo "3> Resetting databases..."

    read -p "Enter Database Server Hostname: " db_host
    read -p "Enter Database SID: " db_sid
    read -p "Enter DBA User: " dba_usr
    read -p "Enter DBA password: " dba_pwd

    read -p "Enter Schema1 owner: " usr1
    read -p "Enter alternate user1 " usr2
    read -p "Enter alternate user2 " usr3

    sqlplus -s $dba_usr/$dba_pwd@$db_sid << EOF

            set timing off
            set feedback off
            set serveroutput off

            DECLARE
                    usr1 varchar2(10) := '$usr1';
                    usr2 varchar2(10) := '$usr2';
                    usr3 varchar2(10) := '$usr3';
                    total INTEGER;

                    TYPE user_arr IS VARRAY(3) OF VARCHAR2(20);
                    users user_arr;

            BEGIN
                    dbms_output.put_line('Entered inside this block...');

                    users := user_arr( upper(usr1),
                                       upper(usr2),
                                       upper(usr3) );

                    total := users.count;

                    FOR i in 1 .. total LOOP
                            dbms_output.put_line('Usernames are: '|| users(i));
                    END LOOP;

            END;
/
EOF

Output looks like:

3> Resetting databases...
Enter Database Server Hostname: HOSTNAME
Enter Database SID: SID
Enter DBA User: DBA
Enter DBA password: pwd
Enter Schema1 owner: user1
Enter alternate user1 user2
Enter alternate user2 user3

After taking the input values, it does not do anything. I don't know what went wrong. Tried to fix it but could not. Can anyone figure out the problem? Thank you!

Upvotes: 1

Views: 26570

Answers (1)

Devon_C_Miller
Devon_C_Miller

Reputation: 16518

Simply removed the set serveroutput off.

You are instructing SQL*Plus to not output anything.

Upvotes: 2

Related Questions