GNU basename in PL/SQL

Sep 17, 2008 / By Don Seiler


In the process of scripting a database migration, I was in need of something akin to the GNU basename utility that I know and love on Linux. basename is most famous for taking a full file path string and stripping away the leading path component, returning just the name of the file. This can be emulated in PL/SQL with calls to SUBSTR and INSTR, like this:


(Thanks to Ian Cary, who shared this logic on oracle-l)

As you can see, this simply finds the last occurence of /, which is our directory separator on *nix and Solaris operating systems. On Windows, it would be \. It then returns a substring beginning one character after that last separator until the end of the string. Voila, a basic basename routine!

Upon reading the basename man page again, I found that basename also takes an optional parameter, a suffix string. If this suffix string is provided, basename will also truncate that string from the end. For example:

$ basename /home/seiler/bookmarks.html 
$ basename /home/seiler/bookmarks.html .html

I decided that this would be handy to have, and set out to create a compatible basename function in PL/SQL. Here is what I came up with:

                                        v_suffix IN VARCHAR2 DEFAULT NULL,
                                        v_separator IN CHAR DEFAULT '/')
                v_basename VARCHAR2(256);
                v_basename := SUBSTR(v_full_path, INSTR(v_full_path,v_separator,-1)+1);
                IF v_suffix IS NOT NULL THEN
                        v_basename := SUBSTR(v_basename, 1, INSTR(v_basename, v_suffix, -1)-1);
                END IF;

                RETURN v_basename;

I’ve also added an optional third parameter to specify a directory separator other than the default. It would probably be rarely useful, but not hard to remove if you don’t like it. As you can see, I’ve used similar SUBSTR/INSTR logic to identify the suffix index and prune it out.

Here it is in action:

SQL> COLUMN file_name FORMAT a45;
SQL> COLUMN basename FORMAT a15;
SQL> COLUMN no_suffix FORMAT a12;
SQL> SELECT file_name
  2          , basename(file_name) as basename
  3          , basename(file_name, '.dbf') as no_suffix
  4  FROM dba_data_files;

FILE_NAME                                     BASENAME        NO_SUFFIX
--------------------------------------------- --------------- ------------
/u01/app/oracle/oradata/orcl/users01.dbf      users01.dbf     users01
/u01/app/oracle/oradata/orcl/sysaux01.dbf     sysaux01.dbf    sysaux01
/u01/app/oracle/oradata/orcl/undotbs01.dbf    undotbs01.dbf   undotbs01
/u01/app/oracle/oradata/orcl/system01.dbf     system01.dbf    system01
/u01/app/oracle/oradata/orcl/example01.dbf    example01.dbf   example01

I hope this makes your work just a little bit easier, as it has mine.

Share this article

5 Responses to “GNU basename in PL/SQL”

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>