We help IT Professionals succeed at work.

Usiing several tables and several filter (like sql Select on some condition) with Oracle Datapump Export API

Hi, i want to use DBMS Datapump Api to export some tables with some filter on the data.
in my .NET application i cal a store proc with a parameters name : in_liste_des_tables . i got somehing like this follow  : 'NAME_EXPR', value => 'IN (''MESSAGE_ENM'',''BIT'')'
i want to use in meta filter some option to do for exemple : select * from Message_Enm where Message_id = 100, select * from Bit where BIT_ID = 47

Here is this store proc i use and i want to update. Regards
.
PROCEDURE SP_PORTABLE_EXPORT_DUMP(in_liste_des_tables IN varchar2, in_dossier_reseau IN varchar2) IS

  tache_de_dump number;
  dir_name varchar2(200);
  etat_de_la_tache varchar2(30);
 
BEGIN

--  INITIALISATION DU DOSSIER DE SAUVEGARDE --  
    dir_name := in_dossier_reseau ;

--  INITIALISATION DE LA TACHE
    tache_de_dump := DBMS_DATAPUMP.open(operation=>'EXPORT',job_mode=>'TABLE',job_name=>'PORTABLE_EXPORT');

--  FICHIER D'EXPORT
    DBMS_DATAPUMP.add_file(handle=>tache_de_dump,filename=>'HP_EXPORT.dmp',directory=> dir_name, filetype=>1, reusefile=>1);

--  FICHIER DE LOG
    DBMS_DATAPUMP.add_file(handle=>tache_de_dump,filename=>'HP_EXPORT.log',directory=> dir_name, filetype=>3, reusefile=>1);
   
--  META_FILTER
    DBMS_DATAPUMP.metadata_filter(handle => tache_de_dump, name =>'NAME_EXPR', value => 'IN (' || in_liste_des_tables || ')', object_type=> 'TABLE');
    DBMS_DATAPUMP.metadata_filter(handle => tache_de_dump,name =>'EXCLUDE_PATH_EXPR', value =>'IN (''COMMENT'',''INDEX'',''CONSTRAINT'',''REF_CONSTRAINT'',''TRIGGER'',''STATISTICS'',''GRANT'')');

--  DEMARRAGE DE LA TACHE
    DBMS_DATAPUMP.start_job(tache_de_dump);
Comment
Watch Question

Alex [***Alex140181***]Software Developer
CERTIFIED EXPERT

Commented:
Sorry, but where's the question?!?
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
I believe this is the question:
i want to use in meta filter some option to do for exemple : select * from Message_Enm where Message_id = 100, select * from Bit where BIT_ID = 47

Not totally sure about the question, but I believe you are looking for the QUERY parameter.  Documentation has a description and some examples.
M THIAMDEVELOPPER

Author

Commented:
yes Johnsonne, i want to use in meta filter with DBMS_DATAPUMP API to do for exemple : select * from Message_Enm where Message_id = 100, select * from Bit where BIT_ID = 47 ;
in fact when i cal the store proc i pass a parameter which have the list of tables like this follow :

DBMS_DATAPUMP.metadata_filter(handle => tache_de_dump, name =>'NAME_EXPR', value => 'IN (''MESSAGE_ENM'',''BIT'')', object_type=> 'TABLE');

so i want to filter the data into the tables base on a condition on the value of one or several fields
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
And that is what the QUERY parameter does.  Allows you to specify a where clause.  Did you look at the documentation?
M THIAMDEVELOPPER

Author

Commented:
yes but in the example, they use the expdb impdb .exe tools, but i want to do the same direct with the API here

--  META_FILTER
    DBMS_DATAPUMP.metadata_filter(handle => tache_de_dump, name =>'NAME_EXPR', value => 'IN (' || in_liste_des_tables || ')', object_type=> 'TABLE');
    --DBMS_DATAPUMP.metadata_filter(handle => tache_de_dump, name =>'NAME_EXPR', value => 'IN (''MESSAGE_ENM'',''BIT'')', object_type=> 'TABLE');
    DBMS_DATAPUMP.metadata_filter(handle => tache_de_dump,name =>'EXCLUDE_PATH_EXPR', value =>'IN (''COMMENT'',''INDEX'',''CONSTRAINT'',''REF_CONSTRAINT'',''TRIGGER'',''STATISTICS'',''GRANT'')');
Senior Oracle DBA
CERTIFIED EXPERT
Commented:
That is not a METADATA_FILTER.  It is a DATA_FILTER.  You are filtering data, not objects.

See documentation here.  Specifically, you are looking at a SUBQUERY filter.
M THIAMDEVELOPPER

Author

Commented:
Thanks  a lot. As i am new on oracle is not evident to migrate from sqlserver to oracle
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
Honestly, the documentation is your friend.  The Oracle documentation is very good and has lots of examples.  Start with http://docs.oracle.com, all the documentation is there.