• Uncategorized

About linux : how-to-passing-variable-from-bash-to-sqlplus

Question Detail

I have below code in bash:

#/!bin/bash


USERS=tom1,tom2,tom3

and I want to drop all users from my variable “USERS” via sqlplus

#/!bin/bash

USERS=tom1,tom2,tom3

sqlplus -s system/*** <<EOF
*some code*
DROP USER tom1 CASCADE;
DROP USER tom2 CASCADE;
DROP USER tom2 CASCADE;
EOF

pls help

Question Answer

Splitting in database using sql / plsql by using the USERS variable is complex and requires understanding of REGEXP functions and I will not recommend that to you. Moreover, it would also require Dynamic Sql to execute a drop query.

I would suggest you to split it within the shell script,create a script and execute it in sqlplus

USERS="tom1,tom2,tom3"
    echo ${USERS} | tr ',' '\n' | while read word; do
        echo "drop user $word;"
    done >drop_users.sql

sqlplus -s system/pwd <<EOF
#some code
@drop_users.sql
EOF

Your code is OK except for DDL commands for Oracle DB which are derived from USERS parameter of the script file, call userDrop.sh. To make suitable relation with USERS parameter and DB, an extra file (drpUsr.sql) should be produced, and invoked inside EOF tags with an @ sign prefixing.

Let’s edit by vi command :

$ vi userDrop.sh

#!/bin/bash
USERS=tom1,tom2,tom3

IFS=',' read -ra usr <<< "$USERS" #--here IFS(internal field separator) is ','
for i in "${usr[@]}"; do
  echo "drop user "$i" cascade;"
done > drpUsr.sql

#--to drop a user a privileged user sys or system needed.    
sqlplus / as sysdba <<EOF
@drpUsr.sql;
exit;
EOF

Call as

$ . userDrop.sh

and do not forget to come back to command prompt by an exit command of DB.

You may also like...

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.