Forum Stats

  • 3,767,857 Users
  • 2,252,726 Discussions
  • 7,874,366 Comments

Discussions

How to get around dbms_datapump max filter size in api

RLOG
RLOG Member Posts: 30 Red Ribbon

In datapump to get around the 4000 character limit of exclude / include lists you can put something like this in your par file on the command line -

EXCLUDE=TABLE:"NOT IN (select table_name from my_list)"

Does anyone know the equivalent from the plsql api?

I have tried using the following, where l_tbl_list is a clob, but get an invalid argument value (it works when the list is small). I also tried doing a select query (this would be my preferred option if it's possible) as above but that was invalid as well.

   dbms_datapump.metadata_filter(

     handle   => l_datapump_handler

    ,name    => 'NAME_EXPR'

    ,value    => 'IN ('||l_tbl_list||')'

    ,object_type => 'TABLE');

Thanks in advance.

Tagged:

Answers

  • Paulzip
    Paulzip Member Posts: 8,489 Blue Diamond

    What error do you get when you run this :

    dbms_datapump.metadata_filter(
      handle => l_datapump_handler
    , name => 'NAME_EXPR'
    , value => 'NOT IN (select table_name from my_list)'
    , object_type => 'TABLE'
    );
    
  • RLOG
    RLOG Member Posts: 30 Red Ribbon
    edited Feb 26, 2021 10:04PM

    I was getting invalid argval error. I actually realised in the end it was because I was trying to select from a table in the target database (this is import over dblink) and it worked if I created the table in the source schema.

    Is there anyway to have the select statement evaluate on the target side?

  • Paulzip
    Paulzip Member Posts: 8,489 Blue Diamond

    You could try to create a view or mview based on that query and select from that.

    Alternatively 'NOT IN (select table_name from [email protected]')