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(operati on=>'EXPOR T',job_mod e=>'TABLE' ,job_name= >'PORTABLE _EXPORT');
-- FICHIER D'EXPORT
DBMS_DATAPUMP.add_file(han dle=>tache _de_dump,f ilename=>' HP_EXPORT. dmp',direc tory=> dir_name, filetype=>1, reusefile=>1);
-- FICHIER DE LOG
DBMS_DATAPUMP.add_file(han dle=>tache _de_dump,f ilename=>' HP_EXPORT. log',direc tory=> dir_name, filetype=>3, reusefile=>1);
-- META_FILTER
DBMS_DATAPUMP.metadata_fil ter(handle => tache_de_dump, name =>'NAME_EXPR', value => 'IN (' || in_liste_des_tables || ')', object_type=> 'TABLE');
DBMS_DATAPUMP.metadata_fil ter(handle => tache_de_dump,name =>'EXCLUDE_PATH_EXPR', value =>'IN (''COMMENT'',''INDEX'',''C ONSTRAINT' ',''REF_CO NSTRAINT'' ,''TRIGGER '',''STATI STICS'','' GRANT'')') ;
-- DEMARRAGE DE LA TACHE
DBMS_DATAPUMP.start_job(ta che_de_dum p);
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
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(operati
-- FICHIER D'EXPORT
DBMS_DATAPUMP.add_file(han
-- FICHIER DE LOG
DBMS_DATAPUMP.add_file(han
-- META_FILTER
DBMS_DATAPUMP.metadata_fil
DBMS_DATAPUMP.metadata_fil
-- DEMARRAGE DE LA TACHE
DBMS_DATAPUMP.start_job(ta
Sorry, but where's the question?!?
I believe this is the question:
Not totally sure about the question, but I believe you are looking for the QUERY parameter. Documentation has a description and some examples.
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.
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_fil ter(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
in fact when i cal the store proc i pass a parameter which have the list of tables like this follow :
DBMS_DATAPUMP.metadata_fil
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?
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_fil ter(handle => tache_de_dump, name =>'NAME_EXPR', value => 'IN (' || in_liste_des_tables || ')', object_type=> 'TABLE');
--DBMS_DATAPUMP.metadata_f ilter(hand le => tache_de_dump, name =>'NAME_EXPR', value => 'IN (''MESSAGE_ENM'',''BIT'')' , object_type=> 'TABLE');
DBMS_DATAPUMP.metadata_fil ter(handle => tache_de_dump,name =>'EXCLUDE_PATH_EXPR', value =>'IN (''COMMENT'',''INDEX'',''C ONSTRAINT' ',''REF_CO NSTRAINT'' ,''TRIGGER '',''STATI STICS'','' GRANT'')') ;
-- META_FILTER
DBMS_DATAPUMP.metadata_fil
--DBMS_DATAPUMP.metadata_f
DBMS_DATAPUMP.metadata_fil
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.