Rem
Rem $Header: rdbms/admin/dbfs_drop_filesystem.sql /main/2 2009/07/01 21:38:41 kkunchit Exp $
Rem
Rem dbfs_drop_filesystem.sql
Rem
Rem Copyright (c) 2009, Oracle and/or its affiliates. All rights reserved. 
Rem
Rem    NAME
Rem      dbfs_drop_filesystem.sql - DBFS drop filesystem
Rem
Rem    DESCRIPTION
Rem      DBFS drop filesystem script
Rem      Usage: sqlplus <dbfs_user> @dbfs_drop_filesystem.sql <fs_name>
Rem
Rem    NOTES
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    weizhang    06/12/09 - Package name change
Rem    weizhang    04/06/09 - Created
Rem

SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
SET SERVEROUTPUT ON

define fs_name = &1

--------------------------------------------------
-- Drop DBFS file system helper procedure
--------------------------------------------------

create or replace procedure fsDrop (
  volName       in  varchar2
  )
authid current_user
IS
  fsname  varchar2(100);
  tabname varchar2(100);
  mntdir  varchar2(100);
  stmt    varchar2(32000);
BEGIN
  fsname  := upper('FS_' || to_char(volName));
  tabname := upper('T_'  || to_char(volName));
  mntdir  := volName;

  -- unmount the store
  stmt := 'begin dbms_dbfs_content.unmountStore(' ||
          'store_name=>''' || fsname ||
          ''', store_mount=>''' || mntdir || '''); end;';
  dbms_output.put_line('unmount stmt: ' || stmt);
  execute immediate stmt;

  -- unregister the store
  stmt := 'begin dbms_dbfs_content.unregisterStore(' ||
          'store_name=> ''' || fsname || '''); end;';
  dbms_output.put_line('unregister stmt: ' || stmt);
  execute immediate stmt;

  -- drop file system
  stmt := 'begin dbms_dbfs_sfs.dropFilesystem(' ||
          'store_name => ''' || fsname || '''); end;' ;
  dbms_output.put_line('drop stmt: ' || stmt);
  execute immediate stmt;

  commit;
END;
/
show errors;

--------------------------------------------------
-- Main entry
--------------------------------------------------

begin
  fsDrop('&fs_name');
exception
  when others then
    rollback;
    dbms_output.put_line('ERROR: ' || sqlcode || ' msg: ' || sqlerrm);
    raise;
end;
/
show errors;

drop procedure fsDrop;

undefine fs_name

exit;