Problem running anonymous plsql blocks from shell script
Im trying to run an anonymous plsql block from a shell script using sqlplus -s but the command doesnt seem to get run. Does anyone know if there are there any known issues with doing this?
Here is te shell script im trying to run against an oracle 8.1.6 database.
#!/usr/bin/sh
#######################################################################
# Script to create privileged database user
# usage: CreateSchema.sh [USERNAME] [PASSWORD]
#
# WARNING: THIS SCRIPT WILL DROP THE EXISTING USER IF IT ALREADY EXISTS
#
#
# Mark Blakey 21/11/02
#######################################################################
USERNAME=$1
USERPASSWORD=$2
if [ -n "$2" ]
then
echo creating user $1
else
echo Insufficient number of arguments- please supply more arguments
echo usage: CreateSchema.sh [USERNAME] [PASSWORD]
echo WARNING: THIS SCRIPT WILL DROP THE EXISTING USER IF IT ALREADY EXISTS
exit
fi
sqlplus -s /nolog << EOF
connect / as sysdba
whenever sqlerror exit sql.sqlcode;
set serveroutput on
set echo on
SELECT sysdate as starttime from dual;
spool userCreate.log
DECLARE
UserCount integer;
UserName dba_users.username%TYPE;
cid integer;
userCheckSql varchar2(800);
userDropSql varchar2(800);
userCreateSql varchar2(800);
userTablespace dba_tablespaces.tablespace_name%type := 'USERS';
tempTablespace dba_tablespaces.tablespace_name%type := 'TEMP01';
BEGIN
UserName := upper('$USERNAME');
UserPassword := upper('$PASSWORD');
userCheckSql := 'SELECT count(1)FROM dba_users WHERE username = :s' ;
userDropSql := 'DROP user ' || userName || ' cascade';
userCreateSql := 'Create user ' || userName || ' identified by ' || UserPassword
|| ' default tablespace ' || userTablespace
|| ' temporary tablespace ' || tempTablespace;
EXECUTE IMMEDIATE userCheckSql INTO UserCount USING UserName;
IF (UserCount = 1)
EXECUTE IMMEDIATE userDropSql;
dbms_output.put_line('User ' || UserName || ' dropped');
ELSE
dbms_output.put_line('creating user ' || userName );
END IF;
EXECUTE IMMEDIATE userCreateSql;
dbms_output.put_line('User ' || UserName || ' created ');
EXCEPTION
WHEN others THEN
raise_application_error(-20112,' CREATE USER: ' ||
userName || ' failed ' ||substr(sqlerrm,1,64))
END;
/
show errors
GRANT dba TO $USERNAME;
GRANT select any table TO $USERNAME;
GRANT execute on sys.dbms_sql TO $USERNAME;
GRANT execute on sys.DBMS_SYS_SQL TO $USERNAME;
GRANT create any sequence TO $USERNAME;
SELECT sysdate as endtime from dual;
spool off
exit sql.sqlcode;
EOF
LOG=$?
if [ $LOG != 0 ]
then
echo unexpected error
exit
else
echo user creation success
fi
exit
