pour le lancer, se connecter sur le serveur, uploader le script sql, et taper :
sqlplus login/password @extractionDbms.sql >> monExtraction.csv
ou sinon, se connecter en sqlplus, et appeler directement la procédure stockée ... attention à ne pas oublié le backslash \ en fin de procédure ou sous sqlplus ...
rem ****************************************************
rem MISE A JOUR DU SCHEMA
rem script : maj_donnees_test_curseur.sql
rem auteur : Capgemini
rem societe : Capgemini
rem ****************************************************
set serveroutput on
spool extraction_test_curseur.csv
declare
CURSOR ListeWagon (pNumeroCmdBoc number,pVersBoc number) IS
select mat.NO_MAT
from tableWagon mat
where mat.no_cmd_boc = pNumeroCmdBoc
and mat.vers_boc = pVersBoc
and rownum <= 5;
/** declaration du curseur*/
CURSOR ListeResultat IS
SELECT
cbov.no_cmd_boc,
cbov.vers_boc,
cbov.etat,
cbov.no_cmd_def,
(SELECT def.etat FROM tableVersionCommande def WHERE def.no_cmd_def = cbov.no_cmd_def and def.vers_def = cbov.vers_def and def.ind_vcour = 1 and cbov.ind_vcour = 1) as etatDef,
cbov.cmc_lv,
cbov.dh_accept,
cbov.dep_ets_ori,
(select LR2_ETS_FER
FROM vueRef referentiel
WHERE referentiel.ETS_FER = cbov.DEP_ETS_ORI
AND referentiel.RES_FER = cbov.DEP_RES_ORI
AND referentiel.D_DEB_ETS_FER <= (TO_DATE(cbov.DH_ACCEPT, 'DD/MM/YY') + 1)
AND ((referentiel.D_FIN_ETS_FER + 1) >=
(TO_DATE(cbov.DH_ACCEPT, 'DD/MM/YY') + 1)
OR referentiel.D_FIN_ETS_FER IS NULL)
AND ROWNUM = 1) as libelleOri,
cbov.dep_ets_dest,
(select LR2_ETS_FER
FROM vueRef referentiel
WHERE referentiel.ETS_FER = cbov.DEP_ETS_DEST
AND referentiel.RES_FER = cbov.DEP_RES_DEST
AND referentiel.D_DEB_ETS_FER <= (TO_DATE(cbov.DH_ACCEPT, 'DD/MM/YY') + 1)
AND ((referentiel.D_FIN_ETS_FER + 1) >=
(TO_DATE(cbov.DH_ACCEPT, 'DD/MM/YY') + 1)
OR referentiel.D_FIN_ETS_FER IS NULL)
AND ROWNUM = 1) as libelleDest,
cbov.ccl_ref_cli,
cbov.aff_cli_pay_fa,
(SELECT cli.l_cli FROM tableClient cli WHERE cli.cli = cbov.aff_cli_pay_fa AND D_FIN_CLI IS NULL) as libelleFA,
cbov.aff_cli_pay_fna,
(SELECT cli.l_cli FROM tableClient cli WHERE cli.cli = cbov.aff_cli_pay_fna AND D_FIN_CLI IS NULL) as libelleFNA,
(SELECT m.lib_mot_valo FROM tableMoteur m WHERE cbov.mot_valo = m.code_mot_valo) as mot_valo,
cbov.cmc_tarif_app
from tableOrcVersion cbov
where cbov.dh_accept >= TO_DATE('01/01/2009')
and cbov.dh_accept <= TO_DATE(sysdate, 'dd/MM/yy')
and cbov.cmc_lv <= 10000
UNION
SELECT
cbov.no_cmd_boc,
cbov.vers_boc,
cbov.etat,
cbov.no_cmd_def,
NULL,
cbov.cmc_lv,
cbov.dh_accept,
cbov.dep_ets_ori,
(select LR2_ETS_FER
FROM vueRef referentiel
WHERE referentiel.ETS_FER = cbov.DEP_ETS_ORI
AND referentiel.RES_FER = cbov.DEP_RES_ORI
AND referentiel.D_DEB_ETS_FER <= (TO_DATE(cbov.DH_ACCEPT, 'DD/MM/YY') + 1)
AND ((referentiel.D_FIN_ETS_FER + 1) >=
(TO_DATE(cbov.DH_ACCEPT, 'DD/MM/YY') + 1)
OR referentiel.D_FIN_ETS_FER IS NULL)
AND ROWNUM = 1) as libelleOri,
cbov.dep_ets_dest,
(select LR2_ETS_FER
FROM vueRef referentiel
WHERE referentiel.ETS_FER = cbov.DEP_ETS_DEST
AND referentiel.RES_FER = cbov.DEP_RES_DEST
AND referentiel.D_DEB_ETS_FER <= (TO_DATE(cbov.DH_ACCEPT, 'DD/MM/YY') + 1)
AND ((referentiel.D_FIN_ETS_FER + 1) >=
(TO_DATE(cbov.DH_ACCEPT, 'DD/MM/YY') + 1)
OR referentiel.D_FIN_ETS_FER IS NULL)
AND ROWNUM = 1) as libelleDest,
cbov.ccl_ref_cli,
cbov.aff_cli_pay_fa,
(SELECT cli.l_cli FROM tableClient cli WHERE cli.cli = cbov.aff_cli_pay_fa AND D_FIN_CLI IS NULL) as libelleFA,
cbov.aff_cli_pay_fna,
(SELECT cli.l_cli FROM tableClient cli WHERE cli.cli = cbov.aff_cli_pay_fna AND D_FIN_CLI IS NULL) as libelleFNA,
(SELECT m.lib_mot_valo FROM tableMoteur m WHERE cbov.mot_valo = m.code_mot_valo) as mot_valo,
cbov.cmc_tarif_app
from tableHC cbov
where cbov.dh_accept >= TO_DATE('01/01/2009')
and cbov.dh_accept <= TO_DATE(sysdate, 'dd/MM/yy')
and cbov.cmc_lv <= 10000;
/** declaration des variables */
numeroCmdBoc number(8);
versBoc number(2);
etatCmdBoc number(4);
numeroCmdDef number(8);
etatCmdDef number(4);
numeroExpedition number(6);
dateAcceptation date;
etsOri number(6);
libEtsOri varchar2(12);
etsDest number(6);
libEtsDest varchar2(12);
referenceClient varchar2(35);
codePayeurFA number(7);
libellePayeurFA varchar2(32);
codePayeurFNA number(7);
libellePayeurFNA varchar2(32);
numeroMateriel number(12);
moteurValorisation varchar(25);
tarifApplique number(7);
/** liste des wagons */
buffer VARCHAR2(255);
begin
DBMS_OUTPUT.ENABLE(1000000);
dbms_output.put_line('debut extraction');
/** creer des libelles de colonnes */
buffer := 'cmdBOC' || ';' || 'etat' || ';' || 'cmdDEF' || ';' || 'etatCmdDef' || ';' || 'numExp' || ';' || 'dateAccept' || ';' || 'codeEtsOri' || ';' || 'libEtsOri' || ';' || 'codeEtsDest' || ';' || 'libEtsDest' || ';' || 'refClient' || ';' || 'codeCliPFA' || ';' || 'libCliPFA' || ';' || 'codeCliPNFA' || ';' || 'libCliPNFA' || ';' || 'numMat' || ';' || 'motValo' || ';' || 'tarApp' || ';';
dbms_output.put_line(buffer);
/** ouverture du curseur */
Open ListeResultat;
Loop
-- Pour chaque jour sélectionné
Fetch ListeResultat Into numeroCmdBoc,versBoc,etatCmdBoc,numeroCmdDef,etatCmdDef,numeroExpedition,dateAcceptation,etsOri,libEtsOri,etsDest,libEtsDest,referenceClient,codePayeurFA,libellePayeurFA,codePayeurFNA,libellePayeurFNA,moteurValorisation,tarifApplique;
Exit When ListeResultat%NOTFOUND;
-- creer les lignes
Open ListeWagon (numeroCmdBoc,versBoc);
Loop
Fetch ListeWagon Into numeroMateriel;
Exit When ListeWagon%NOTFOUND;
buffer := numeroCmdBoc || ';' || etatCmdBoc || ';' || numeroCmdDef || ';' || etatCmdDef || ';' || numeroExpedition || ';' || dateAcceptation || ';' || etsOri || ';' || libEtsOri || ';' || etsDest || ';' || libEtsDest || ';' || referenceClient || ';' || codePayeurFA || ';' || libellePayeurFA || ';' || codePayeurFNA || ';' || libellePayeurFNA || ';' || numeroMateriel || ';' || moteurValorisation || ';' || tarifApplique || ';';
dbms_output.put_line(buffer);
End Loop;
Close ListeWagon;
End loop ;
/** liberation ressources */
Close ListeResultat ;
dbms_output.put_line('fin extraction');
end;
/
spool off;
/
Un second exemple utilisant une ecriture directe dans un fichier ... Attention, il faut noter qu'il est nécessaire de disposer d'un "directory oracle" configuré par le DBA ... Le fichier se trouvera dans le directory ... (ici nommé EXTRACT et correspond à un chemin sur le filesystem du serveur).
SET SERVEROUTPUT ON;
/** declaration methode extraction */
create or replace procedure extraction is
/** declaration du curseur*/
CURSOR ListeResultat IS
select
vcmd.no_cmd_boc,
vcmd.etat,
vcmd.no_cmd_def,
vcmd.cmc_lv,
vcmd.dh_accept,
vcmd.aff_cli_pay_fa,
(SELECT L_CLI
FROM CLIENT client
where client.cli = vcmd.aff_cli_pay_fa
and client.d_deb_cli <= TO_DATE(sysdate, 'dd/MM/yy')
and (client.d_fin_cli is null or client.d_fin_cli >= TO_DATE(sysdate, 'dd/MM/yy')
) and rownum=1
) as libelleFA,
vcmd.aff_cli_pay_fna,
(SELECT L_CLI
FROM CLIENT client
where client.cli = vcmd.aff_cli_pay_fna
and client.d_deb_cli <= TO_DATE(sysdate, 'dd/MM/yy')
and (client.d_fin_cli is null or client.d_fin_cli >= TO_DATE(sysdate, 'dd/MM/yy')
) and rownum=1
) as libelleFNA,
vcmd.ccl_ref_cli,
wagons.no_mat,
vcmd.mot_valo,
vcmd.cmc_tarif_app,
vcmd.cmc_typ_valo_tarif
from VERSIONCMD vcmd, WAGON wagons
where vcmd.dh_accept >= TO_DATE('01/01/2009')
and vcmd.dh_accept <= TO_DATE(sysdate, 'dd/MM/yy')
and vcmd.cmc_lv <= 10000
and wagons.no_cmd_boc = vcmd.no_cmd_boc and wagons.vers_boc = vcmd.vers_boc;
/** declaration des variables */
numeroCmdBoc number(8);
etatCmdBoc number(4);
numeroCmdDef number(8);
numeroExpedition number(6);
dateAcceptation date;
codePayeurFA number(7);
libellePayeurFA varchar2(32);
codePayeurFNA number(7);
libellePayeurFNA varchar2(32);
referenceClient varchar2(35);
numeroMateriel number(12);
moteurValorisation number(1);
tarifApplique number(7);
typeTarif number(1);
/** fichier de sortie */
file utl_file.file_type;
buffer VARCHAR2(1024);
/** traitement */
begin
/** ouverture fichier dasn le directory */
file := utl_file.fopen('EXTRACT','extraction.csv','W');
/** creer des libelles de colonnes */
buffer := 'n° de commande BOC' || ';' || 'etat de la commande BOC' || ';' || 'n° de commande DEF' || ';' || 'n° d’expédition' || ';' || 'date acceptation' || ';' || 'code client PFA' || ';' || 'libelle client PFA' || ';' || 'code client PNFA' || ';' || 'libelle client PFA' || ';' || 'reference client' || ';' || 'numero materiel' || ';' || 'moteur de valorisation' || ';' || 'tarif applique' || ';' || 'type de tarif';
utl_file.put_line(file,buffer);
/** ouverture du curseur */
Open ListeResultat;
Loop
-- Pour chaque jour sélectionné
Fetch ListeResultat Into numeroCmdBoc,etatCmdBoc,numeroCmdDef,numeroExpedition,dateAcceptation,codePayeurFA,libellePayeurFA,codePayeurFNA,libellePayeurFNA,referenceClient,numeroMateriel,moteurValorisation,tarifApplique,typeTarif;
Exit When ListeResultat%NOTFOUND;
-- creer les lignes
buffer := numeroCmdBoc || ';' || etatCmdBoc || ';' || numeroCmdDef || ';' || numeroExpedition || ';' || dateAcceptation || ';' || codePayeurFA || ';' || libellePayeurFA || ';' || codePayeurFNA || ';' || libellePayeurFNA || ';' || referenceClient || ';' || numeroMateriel || ';' || moteurValorisation || ';' || tarifApplique || ';' || typeTarif;
utl_file.put_line(file,buffer);
End loop ;
/** liberation ressources */
Close ListeResultat ;
utl_file.fclose(file);
end extraction;
Aucun commentaire:
Enregistrer un commentaire