1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

dbms_datapump.metadata_filter(h3, 'INCLUDE_PATH_LIST', 'USER');

Discussion in 'Server Administration and Options' started by ioug, Dec 3, 2010.

  1. ioug

    ioug Active Member

    Messages:
    9
    Likes Received:
    2
    Trophy Points:
    65
    Hi !

    The following code works well in 10g but fails with
    ORA-39001: invalid argument value
    in 11g

    dbms_datapump.metadata_filter(h3, 'INCLUDE_PATH_LIST', 'USER');

    Any idea why ?

    Tks
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You should probably write this as:

    dbms_datapump.metadata_filter(handle=>h3, name=>'INCLUDE_PATH_LIST', value=>'USER');

    to ensure you're setting the correct parameter to the desired value.
     
  3. ioug

    ioug Active Member

    Messages:
    9
    Likes Received:
    2
    Trophy Points:
    65
    Hi David,

    Tks for the reply !
    Problem is that I have already tried it and I have the same error !!

    JP
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You need to verify that h3 actually is a valid file handle; my expectation is that it is not.
     
  5. ioug

    ioug Active Member

    Messages:
    9
    Likes Received:
    2
    Trophy Points:
    65
    the following code works well...


    h3 := dbms_datapump.open('SQL_FILE',
    'SCHEMA',
    NULL,
    'cr_sql_file_' || pin_user);

    dbms_datapump.add_file(h3, pin_dump || '_%U.dp', lv_dump_dir);

    dbms_datapump.add_file(h3,
    lv_sql_file,
    lv_log_dir,
    NULL,
    dbms_datapump.ku$_file_type_sql_file);

    -- add filter so we only get info on the user
    -- *************** PROBLEMS IN 11G *****************
    IF to_number(gv_version) < 11 THEN
    dbms_datapump.metadata_filter(handle=>h3,name=>'INCLUDE_PATH_LIST',value=>'USER');
    END IF;

    -- start the job.
    dbms_datapump.start_job(h3);

    If I comment the if and still execute the metadata_filter, I get the error.
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    There appears to be a space in your string:

    dbms_datapump.metadata_filter(handle=>h3,name=>'IN CLUDE_PATH_LIST',value=>'USER');
     
  7. ioug

    ioug Active Member

    Messages:
    9
    Likes Received:
    2
    Trophy Points:
    65
    Ok, Dont know how it got there but tripple check it in the code and there is no space.

    I even re-wrote the line and re-tested it to be sure .

    Have you ever used it successfully in 11g ?
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Searching google.com I found that all parameters need to be passed, even if they're null:

    dbms_datapump.metadata_filter(h3,'INCLUDE_PATH_LIST','USER', NULL);

    Try this and see if it works in 11g.
     
  9. ioug

    ioug Active Member

    Messages:
    9
    Likes Received:
    2
    Trophy Points:
    65
    does not work !!!
    same err

    :(
     
  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Have you verified that the package status is VALID? Which actual release of 11g are you using? 11.1.0.x or 11.2.0.x? It can make a difference. You may also need to contact Oracle support for this issue.
     
  11. ioug

    ioug Active Member

    Messages:
    9
    Likes Received:
    2
    Trophy Points:
    65
    Package is valid
    using 11.2

    about to open a SR
    I ll let you know if I (they) find a solution

    Tks for your help
     
  12. ioug

    ioug Active Member

    Messages:
    9
    Likes Received:
    2
    Trophy Points:
    65
    solution

    DBMS_DATAPUMP.METADATA_FILTER( handle => h3, name => 'INCLUDE_PATH_LIST', value => '''USER''' );

    check the quotes on value !!
    works in 10 and 11
     
    Sadik likes this.