DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Problem running anonymous plsql blocks from shell script

  1. #1
    Join Date
    Oct 2002
    Location
    london
    Posts
    41

    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

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    What's the error?
    Jeff Hunter

  3. #3
    Join Date
    Oct 2002
    Location
    london
    Posts
    41
    It doesnt execute the anonymous block. it doesnt even execute the SELECT sysdate as endtime from dual;
    statement at the end of the script. However it does execute the
    "SELECT sysdate as starttime from dual" statement at the beginning of the script which proves that sqlplus is recognozing commands from within the here document.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width