Renaming files from inside PL/SQL Oracle Tips by Burleson Consulting

 

Question: How can I rename a file or table through PL/SQL?

Answer:  You have several choices for renaming from inside PL/SQL.

• - If you want to re-name a table, you can run DDL within PL/SQL with the "execute immediate" clause.
   

• If you want to shell-out to the OS to rename a flat file, you can do that too.
   

• Use the utl_file.frename procedure

The most obvious tool for renaming a file from within PL/SQL us the UTL_FILE procedure called FRENAME.  Oracle docs have details on using FRENAME:

"The UTL_FILE.FRENAME procedure renames an existing file to a new name. You will rename the file userdata.txt to userdata2.txt in the USER_DIR directory. Execute the following SQL in SQL*Plus:

@frename

connect hr/hr@orcl.worldset serveroutput on

BEGIN

UTL_FILE.FRENAME('USER_DIR', 'userdata.txt', 'USER_DIR', 'userdata2.txt', TRUE);

END;/"

For further reading on executing operating system commands from inside Oracle, I recommend Dr. Hall's book "Oracle PL/SQL Tuning".