Dynamic SQL statement to replace the path of one or more Oracle Directories || #directory #oracle #sql

drop-tables.sql

--
SELECT 'DROP TABLE ' || TABLE_NAME || ';'
FROM USER_TABLES u
WHERE TABLE_NAME LIKE '%%'
ORDER BY 1

replace-directory.sql

-- https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5007.htm
-- Specify OR REPLACE to re-create the directory database object if it already exists. 
-- You can use this clause to change the definition of an existing directory without dropping, 
-- re-creating, and regranting database object privileges previously granted on the directory.
-- Users who had previously been granted privileges on a redefined directory can still access 
-- the directory without being regranted the privileges.

SELECT 
  'CREATE OR REPLACE DIRECTORY ' || directory_name || ' AS ' || '''' ||
    REPLACE(directory_path, '/old', '/new') || '''' || ';' AS sqlstmt,  
  directory_name, 
  directory_path,
  REPLACE(directory_path, '/old', '/new') AS replaced
FROM  dba_directories 
WHERE directory_name IN ()

Sql-utils

.