Keep your Database Tidy – making sure a file exists before DBMS_DATAPUMP makes a mess

There are times when I wonder whether DBMS_DATAPUMP isn’t modelled on your average teenager’s bedroom floor.
If you’ve ever tried to start an import by specifying a file that doesn’t exist ( or that DBMS_DATAPUMP can’t see) you’ll know what I mean.
The job fails, which is fair enough. However, DBMS_DATAPUMP then goes into a huff and refuses to “clean up it’s room”.
Deb has suggested that this sort of thing is also applicable to husbands.
Not that I have any idea of whose husband she’s talking about.
Anyway, you may consider it preferable to check that the export file you want to import from actually exists in the appropriate directory before risking the wrath of the temperamental datapump API.
This apparently simple check can get a bit interesting, especially if you’re on a Linux server…

For what follows, I’ll be using the DATA_PUMP_DIR directory object. To check where this is pointing to…

select directory_path
from dba_directories
where directory_name = 'DATA_PUMP_DIR'
/

DIRECTORY_PATH
--------------------------------------------------------------------------------
/u01/app/oracle/admin/XE/dpdump/

The owner of the functions I’ll be creating will need to have READ privileges granted directly to them on this directory…

select privilege
from user_tab_privs
where table_name = 'DATA_PUMP_DIR'
/

PRIVILEGE
----------------------------------------
READ

SQL> 

If the user does not have this privilege then you can grant it ( connecting as sysdba) with the following :

grant read on directory data_pump_dir to user
/

…where user is the name of the schema in which you are going to create the function.

Now to create a file in this directory so that we can test for it’s existence…

sudo su oracle
[sudo] password for mike: 

touch /u01/app/oracle/admin/XE/dpdump/test.txt
ls -l  /u01/app/oracle/admin/XE/dpdump/test.txt
-rw-r--r-- 1 oracle dba 0 Jul  3 18:08 /u01/app/oracle/admin/XE/dpdump/test.txt

In order to check for the existence of this file from within PL/SQL, we have a couple of options…

UTL_FILE

The UTL_FILE.FGETATTR procedure retrieves details of a file, including whether or not it exists…

set serveroutput on size unlimited
declare

    l_filename varchar2(4000) := 'test.txt';
    l_exists boolean;
    l_length number;
    l_bsize number;
begin
    utl_file.fgetattr
    (
        location => 'DATA_PUMP_DIR',
        filename => l_filename, 
        fexists => l_exists,
        file_length => l_length,
        block_size => l_bsize
    );
    if l_exists then
        dbms_output.put_line( l_filename ||' exists in DATA_PUMP_DIR : ');
        dbms_output.put_line( 'Length : '||l_length);
        dbms_output.put_line( 'Block Size : '||l_bsize);
    else
        dbms_output.put_line('File does not exist in DATA_PUMP_DIR');
    end if;
end;
/

Run this and we get :

test.txt exists in DATA_PUMP_DIR :
Length : 0
Block Size : 4096

PL/SQL procedure successfully completed.

That’s handy. Let’s put it into a function…

create or replace function file_exists_fn
(
    i_dir in all_directories.directory_name%type,
    i_filename in varchar2
)
    return varchar2
is

    l_exists boolean;
    l_length number;
    l_block_size number;
    
    l_return varchar2(4000);
    
begin
    utl_file.fgetattr
    (
        location => upper(i_dir),
        filename => i_filename,
        fexists => l_exists,
        file_length => l_length,
        block_size => l_block_size
    );
    if l_exists then
        l_return := i_filename||' in '||upper(i_dir)||' - Length : '||l_length||' - Block Size : '||l_block_size;
    else
        l_return := i_filename||' does not exist in '||upper(i_dir);
    end if;
    
    return l_return;
end;
/

Now let’s see what happens with a Symbolic Link…

touch /home/mike/symlink.txt

sudo su oracle
[sudo] password for mike: 

ln -s /home/mike/symlink.txt /u01/app/oracle/admin/XE/dpdump/symlink.txt
ls -l /u01/app/oracle/admin/XE/dpdump/symlink.txt
lrwxrwxrwx 1 oracle dba 22 Jul  3 18:29 /u01/app/oracle/admin/XE/dpdump/symlink.txt -> /home/mike/symlink.txt

If we now call our function to fine symlink.txt in DATA_PUMP_DIR…

select file_exists_fn('DATA_PUMP_DIR', 'symlink.txt')
from dual
/

FILE_EXISTS_FN('DATA_PUMP_DIR','SYMLINK.TXT')
--------------------------------------------------------------------------------
symlink.txt does not exist in DATA_PUMP_DIR

SQL> 

It is at this point that I realise that I really should have read the manual, which states that, for UTL_FILE : “neither hard nor symbolic links are supported.”

So, if we’re to handle links, a different approach is required…

The DBMS_LOB approach

The DBMS_LOB has a FILEEXISTS function which looks like it could come in handy here…

set serveroutput on size unlimited
declare

    l_filename varchar2(4000) := 'symlink.txt';
    l_loc bfile;
begin
    l_loc := bfilename('DATA_PUMP_DIR', l_filename);
    if dbms_lob.fileexists(l_loc) = 1 then
        dbms_output.put_line( l_filename||' exists');
    else
        dbms_output.put_l1ine('File not found');
    end if;
end;
/

symlink.txt exists 

PL/SQL procedure successfully completed.

That’s better. After amending the function…

create or replace function file_exists_fn
(
    i_dir in all_directories.directory_name%type,
    i_filename in varchar2
)
    return varchar2
is

    l_loc bfile;
    l_return varchar2(4000);

begin
    l_loc := bfilename(upper(i_dir), i_filename);
    if dbms_lob.fileexists(l_loc) = 1 then
        l_return :=  i_filename||' exists in '||upper(i_dir);
    else
        l_return := 'File '||i_filename||' not found';
    end if;
    return l_return;
end;
/

…we can see that this also works just fine for conventional files…

select file_exists_fn('DATA_PUMP_DIR', 'test.txt')
from dual
/

FILE_EXISTS_FN('DATA_PUMP_DIR','TEST.TXT')
--------------------------------------------------------------------------------
test.txt exists in DATA_PUMP_DIR

SQL> 

Let’s check that is works for hard links as well…

touch /home/mike/hardlink.txt
chmod a+rw /home/mike/hardlink.txt
sudo su oracle
[sudo] password for mike: 

cd /u01/app/oracle/admin/XE/dpdump/
ln /home/mike/hardlink.txt hardlink.txt
ls -l hardlink.txt
-rw-rw-rw- 2 mike mike 0 Jul  3 18:50 hardlink.txt

And the test….

select file_exists_fn('DATA_PUMP_DIR', 'hardlink.txt')
from dual
/

FILE_EXISTS_FN('DATA_PUMP_DIR','HARDLINK.TXT')
--------------------------------------------------------------------------------
hardlink.txt exists in DATA_PUMP_DIR

SQL> 

So, if you want to minimise the prospect of muttering “I’m not your mother, you know!” to your database, then the DBMS_LOB approach would seem to be the way to go.

Advertisements

3 thoughts on “Keep your Database Tidy – making sure a file exists before DBMS_DATAPUMP makes a mess

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s