Link to home
Start Free TrialLog in
Avatar of M THIAM
M THIAM

asked on

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);
Avatar of Alex [***Alex140181***]
Alex [***Alex140181***]
Flag of Germany image

Sorry, but where's the question?!?
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.
Avatar of M THIAM
M THIAM

ASKER

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
And that is what the QUERY parameter does.  Allows you to specify a where clause.  Did you look at the documentation?
Avatar of M THIAM

ASKER

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'')');
ASKER CERTIFIED SOLUTION
Avatar of johnsone
johnsone
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of M THIAM

ASKER

Thanks  a lot. As i am new on oracle is not evident to migrate from sqlserver to oracle
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.