Home » SQL & PL/SQL » SQL & PL/SQL » problem with DBMS_DATAPUMP (oracle 10 g)
problem with DBMS_DATAPUMP [message #419328] Fri, 21 August 2009 05:11 Go to next message
Draco
Messages: 6
Registered: March 2006
Location: Germany/Duesseldorf
Junior Member
hi all,

I have a problem with DBMS_DATAPUMP.metadata_filter.
I found some examples in books how to use metadata_filter and for the value e.g.
'IN ('xyz')' or '= xyz'

but always I try it , I get a ora-39001 - what's wrong ?

create or replace procedure export_schema_sdok
is
v_user varchar2(100) := 'xyz';
act_job_name varchar2(100);
act_date_char varchar2(100);
act_muster varchar2(100);
v_dph		number;
v_filename varchar2(100);
v_logname varchar2(100);
BEGIN
act_job_name  := v_user || '_' || to_char(sysdate, 'ddmonyyyy_hh24miss');
v_dph := DBMS_DATAPUMP.open(operation=>'EXPORT',job_mode=>'SCHEMA',job_name=> act_job_name);
v_filename := v_schema || '_EXP_' || to_char(sysdate, 'dd-mon-yyyy-hh24-mi') || '.DMP';
 DBMS_DATAPUMP.add_file(handle=>v_dph,filename=> v_filename,directory => v_directory,filetype=>1);
v_logname := v_schema || '_LOG_' || to_char(sysdate, 'dd-mon-yyyy-hh24-mi') || '.LOG';
 DBMS_DATAPUMP.add_file(handle=>v_dph,filename=> v_logname,directory => v_directory,filetype=>3);

act_muster := '=' || v_user || '';
DBMS_DATAPUMP.metadata_filter(handle => v_dph,name=>'SCHEMA_EXPR',value=> act_muster);

DBMS_DATAPUMP.start_job(v_dph);
DBMS_DATAPUMP.detach(v_dph);
END;
Re: problem with DBMS_DATAPUMP [message #419330 is a reply to message #419328] Fri, 21 August 2009 05:20 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-39001: invalid argument value
 *Cause:  The user specified API parameters were of the wrong type or
          value range.  Subsequent messages supplied by
          DBMS_DATAPUMP.GET_STATUS will further describe the error.
 *Action: Correct the bad argument and retry the API.

I think
act_muster := '=' || v_user || '';
should be
act_muster := '=''' || v_user || '''';

Also take of name case.

Regards
Michel

Re: problem with DBMS_DATAPUMP [message #419332 is a reply to message #419328] Fri, 21 August 2009 05:49 Go to previous messageGo to next message
Draco
Messages: 6
Registered: March 2006
Location: Germany/Duesseldorf
Junior Member
Hi Michel,

thanks for the reply.
I changed
act_muster := '=' || v_user || '';

to
act_muster := '=''' || v_user || '''';


but I get the same exeption.

Greetings

Draco
Re: problem with DBMS_DATAPUMP [message #419340 is a reply to message #419332] Fri, 21 August 2009 06:20 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy the whole session: procedure creation and execution.

Regards
Michel
Re: problem with DBMS_DATAPUMP [message #419343 is a reply to message #419330] Fri, 21 August 2009 06:40 Go to previous messageGo to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
Did You create the DIRECTORY correct ? Your code works fine for me after:

CREATE DIRECTORY V_DIRECTORY AS 'C:\TEMP';
GRANT READ, WRITE ON DIRECTORY V_DIRECTORY TO <user>;

You can try this with:
 dbms_datapump.add_file(handle => v_dph, filename => v_filename, DIRECTORY => NULL , filetype=>1);

Re: problem with DBMS_DATAPUMP [message #420632 is a reply to message #419328] Tue, 01 September 2009 01:38 Go to previous message
Draco
Messages: 6
Registered: March 2006
Location: Germany/Duesseldorf
Junior Member
Thanks for the reply┬┤s. The problem was that i used the wrong schema Sad
Previous Topic: Stored procedure date type compare ORA-01839
Next Topic: ORA-01847: day of month must be between 1 and last day of month error
Goto Forum:
  


Current Time: Mon Sep 26 14:31:55 CDT 2016

Total time taken to generate the page: 0.29484 seconds