A simple and practical shell script

 　　There is a very useful shell script to handle something for utl_file, utl_http and utl_smtp. The following code will create a directory for using utl_file to operate file and grant execute on utl_file to useraccount you have created, we should log in oracle by user sys at first. About utl_http and utl_smtp, it will do the same as the above. Also, if we want to use the function of utl_file in normal, we should set the related configuration in acl(access control list). Maybe you have noticed the end of below code, it will make some configuration for acl. Use this script, you're gonna be more efficient during your work.

 

#!/bin/bash
sqlplus=sqlplus64
username=$1
psswd=$2
filedir=$3
[[ $filedir ]] || filedir="/sandbox/EDF_API_REPO_DIR"
[[ -d $filedir ]] || { echo "mkdir $filedir"; mkdir $filedir; chmod 777 $filedir; }
[[ -d $filedir ]] || { echo "cannot create oracle directory"; exit 1; }   
[[ $username ]] || { echo "Where is user name?" ; exit 1; } 
[[ $psswd ]] || psswd=$username 
echo "create user $username identified by $psswd"; 
sqlcode="
connect sys/111111@localhost:1522/xe as sysdba; \n
set serveroutput on; \n
create user $username identified by $psswd; \n
grant all privileges to $username; \n
grant execute on utl_http to $username;  \n
grant execute on utl_file to $username;  \n
grant execute on utl_smtp to $username;  \n
CREATE OR REPLACE DIRECTORY EDF_API_REPO_DIR as '$filedir'; \n
declare \n
  uzer varchar2(100) := '$username'; \n
BEGIN\n
  uzer := upper(uzer);  \n
  begin \n
     dbms_network_acl_admin.drop_acl(\n
       'utl_http.xml'\n
     );\n
  exception when others then 
    dbms_output.put_line(sqlerrm);
  end;
  dbms_network_acl_admin.create_acl (\n
    acl         => 'utl_http.xml',\n
    description => 'HTTP Access',\n
    principal   => uzer ,\n
    is_grant    => TRUE,\n
    privilege   => 'connect'\n
  );\n
  dbms_network_acl_admin.add_privilege (\n
    acl        => 'utl_http.xml',\n
    principal  => uzer,  \n\n
    is_grant   => TRUE,\n
    privilege  => 'resolve'\n
  );\n
  dbms_network_acl_admin.assign_acl (\n
    acl        => 'utl_http.xml',\n
    host       => '*'\n
  );\n
  dbms_output.put_line('acl created' ); \n
END; \n
/\n
show errors;"

echo -e $sqlcode | $sqlplus /nolog