execute immediate avec utl_file

Version imprimable

04/06/2017, 22h21
moracles

execute immediate avec utl_file
Bonjour,
Dans ma procédure stockée, j'initialise :
- un fichier trace myfic := UTL_FILE.FOPEN
- une variable stmt pour un sql dynamique.
Code:

1 2 3 4 5 6 7 8 stmt := ' BEGIN for cursor as ... LOOP traitement de type insert/update /* ici je voudrais faire un appel à UTL_FILE.PUT(myfic, 'mon message'); */ END LOOP END; '
Ensuite, j'écris dans le fichier de trace je lance 'execute immediate stmt' cf. stmt ci-dessus
Par contre je n'arrive pas à enrichir ce traitement dynamique par un appel à UTL_FILE pour chaque itération du curseur.
Comment passer le pointeur myfic (UTL_FILE.FILE_TYPE) à sql dynamique ?
Ce handler UTL_FILE.FILE_TYPE étant une structure :
Code:

1 2 3 4 TYPE file_type IS RECORD ( id BINARY_INTEGER, datatype BINARY_INTEGER, byte_mode BOOLEAN);
je n'arrive pas à l'inclure l'appel à UTL_FILE.PUT dans 'execute immediate'.
Auriez-vous des solutions ?

Cdlt
06/06/2017, 11h49
mnitu

Il est fort probable que vous concevez mal votre traitement. Néanmoins, essayez de poster un jeu de test simplifié de ce que vous voulez accomplir.
06/06/2017, 12h09
moracles

Question simplifiée

Bonjour,
pour simplifier voici ma question :
est-il possible d'exécuter avec 'execute immediate' une procédure PLSQL ayant en paramètre 'IN' un pointeur sur le fichier de type UTL_FILE.FILE_TYPE
ouvert au préalable dans la procédure appelante ?

Ceci dans le but de générer un fichier log global du traitement.

Cdlt
06/06/2017, 15h48
mnitu

Non le type de binding ne passe pas il est inconnu pour le moteur SQL.
Par contre tout le monde pourrait écrire dans une table temporaire à vider dans un fichier en fin de traitement.
06/06/2017, 17h19
moracles

Citation:

Envoyé par mnitu

Non le type de binding ne passe pas il est inconnu pour le moteur SQL.
Par contre tout le monde pourrait écrire dans une table temporaire à vider dans un fichier en fin de traitement.

Bonjour mnitu,
je vais effectivement me rabattre sur cette solution de table journal, bien qu'elle ne permette pas de suivre l'avancement en temps réel et reste dépendante du commit final donc en cas d'un plantage ...
Merci en tout cas de ton avis éclairé.
Cdlt
06/06/2017, 18h08
skuatamad

Pour des logs vous pouvez utiliser une transaction autonome
06/06/2017, 18h14
moracles

Citation:

Envoyé par skuatamad

Pour des logs vous pouvez utiliser une transaction autonome

Merci skuatamad,
je vais étudier cette possibilité pour commiter ma table log de façon indépendante.
Cdlt
07/06/2017, 11h21
mnitu

Si disons, le nom du fichier et le répertoire sont connus via un contexte Oracle il est possible d'écrire une procédure simple qui ouvre le fichier, écrit ce qu'elle reçoit comme valeur d'un hypothétique paramètre et le ferme à chaque appel. C'est terriblement inefficace quand la volumétrie est forte mais tout dépende de ce qu'on veut faire.

effectivement ...

Bonjour mnitou,
ça aurait été possible techniquement, mais je suis d'accord avec toi sur la lourdeur de cette solution en terme de performance.

J'ai adopté pour le moment la solution suivante :
- déclaration d'une variable log_tab de type 'associative array' dans le package, donc partagée entre la proc appelante et le SQL dynamique
- à chaque itération de curseur dans mon bloc de 'exécute immediate', je stocke dans ce tableau mes infos et les restitue dans le fichier trace à la fin de trt dynamique en boucle.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 create or replace PACKAGE PCK_IGOITF AS -- constantes -- types TYPE LOG_REC_TYP IS RECORD ( ID_ITF varchar2(50) := '', ACTION varchar2(50) := '', NBR_ENR varchar2(50) := '', ERR_CODE varchar2(50) := null, ERR_MSG varchar2(250) := null); TYPE LOG_TAB_TYP IS TABLE OF LOG_REC_TYP INDEX BY BINARY_INTEGER; -- associative array. -- variables log_tab LOG_TAB_TYP; -- tableau pour la trace du trt
L'avantage de cette solution est que je n'ai pas besoin de comiter le contenu de la table journal à chaque itération du LOOP.
Ensuite, voici l'extrait du code correspondant à la restitution des erreurs :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 /* *************** DEB TRT ERREURS ********** */ -- FOR j in pck_igoitf.log_tab.FIRST..pck_igoitf.log_tab.LAST LOOP DBMS_Output.PUT_LINE(LPAD(j,3,0)||' log_tab : ID_ITF|ACTION|NBR_ENR|ERR_CODE|ERR_MSG =' ||pck_igoitf.log_tab(j).ID_ITF ||'|' ||pck_igoitf.log_tab(j).ACTION ||'|' ||pck_igoitf.log_tab(j).NBR_ENR ||'|' ||NVL(pck_igoitf.log_tab(j).ERR_CODE,'(null)')||'|' ||pck_igoitf.log_tab(j).ERR_MSG ); utl_file.putf(pck_igoitf.p_file,'%s log_tab : ID_ITF|ACTION|NBR_ENR|ERR_CODE|ERR_MSG =%s ', to_char(systimestamp, 'DD/MM/YYYY HH24:MI:SS,FF3'), pck_igoitf.log_tab(j).ID_ITF ||'|'|| pck_igoitf.log_tab(j).ACTION ||'|'|| pck_igoitf.log_tab(j).NBR_ENR ||'|'|| pck_igoitf.log_tab(j).ERR_CODE||'|'|| pck_igoitf.log_tab(j).ERR_MSG); END LOOP; -- --FORALL j in pck_igoitf.log_tab.FIRST..pck_igoitf.log_tab.LAST FORALL j IN INDICES OF pck_igoitf.log_tab UPDATE IMMOSIS.UT SET ERR_CODE=pck_igoitf.log_tab(j).ERR_CODE, ERR_MSG=pck_igoitf.log_tab(j).ERR_MSG WHERE IMMOSIS.UT.CODE_UT=pck_igoitf.log_tab(j).ID_ITF -- AND pck_igoitf.log_tab(j).ERR_CODE is not null ; -- DBMS_OUTPUT.PUT_LINE ('REPORT ERREURS pour ='||sql%rowcount||' enr.'); COMMIT;
Je rencontre juste un souci avec un update final à partir de mon tableau tab_log contenant 4 lignes dont 2 avec les erreurs.
Je tente reporter ces erreurs vers la table des données et tout se passe bien sans la condition sur ERR_CODE du tableau : "AND pck_igoitf.log_tab(j).ERR_CODE is not null".
Dans ce cas (commentaire dans WHERE clause ) les 4 lignes de la table UT sont maj.

Citation:

001 log_tab : ID_ITF|ACTION|NBR_ENR|ERR_CODE|ERR_MSG =000002S|MAJ|1|(null)|
002 log_tab : ID_ITF|ACTION|NBR_ENR|ERR_CODE|ERR_MSG =000003M|MAJ|1|(null)|
003 log_tab : ID_ITF|ACTION|NBR_ENR|ERR_CODE|ERR_MSG =000004H|MAJ|1|-2291|ORA-02291: violation de contrainte d'intégrité (COSWIN.FK_EREQ_FUNCTION) - clé parent introuvable
004 log_tab : ID_ITF|ACTION|NBR_ENR|ERR_CODE|ERR_MSG =000005D|MAJ|1|-2291|ORA-02291: violation de contrainte d'intégrité (COSWIN.FK_EREQ_FUNCTION) - clé parent introuvable
REPORT ERREURS pour =4 enr.
** FIN P_ITF_TRT_ROW **

Par contre avec la condition "AND pck_igoitf.log_tab(j).ERR_CODE is not null", aucune ligne n'est trouvée pour la maj dans la table UT. Et j'aimerais bien comprendre pourquoi ...
Normalement, les 2 lignes UT doivent être maj avec ERR_CODE=-02291

Merci d'avance de votre attention
Cdlt

09/06/2017, 13h34
mnitu

Faut voir ce qu'il y a dans pck_igoitf.log_tab(j).ERR_CODE
09/06/2017, 14h43
moracles

Bonjour
cf. l'affichage du tableau :

Citation:

001 log_tab : ID_ITF|ACTION|NBR_ENR|ERR_CODE|ERR_MSG =000002S|MAJ|1|(null)|
002 log_tab : ID_ITF|ACTION|NBR_ENR|ERR_CODE|ERR_MSG =000003M|MAJ|1|(null)|
003 log_tab : ID_ITF|ACTION|NBR_ENR|ERR_CODE|ERR_MSG =000004H|MAJ|1|-2291|ORA-02291: violation de contrainte d'intégrité (COSWIN.FK_EREQ_FUNCTION) - clé parent introuvable
004 log_tab : ID_ITF|ACTION|NBR_ENR|ERR_CODE|ERR_MSG =000005D|MAJ|1|-2291|ORA-02291: violation de contrainte d'intégrité (COSWIN.FK_EREQ_FUNCTION) - clé parent introuvable
REPORT ERREURS pour =4 enr.

C'est l'avant dernière donnée sur chacune de quatre ligne on a : (null), (null),-2291, -2291

Le string '(null)' est issu de NVL(ERR_CODE).

Donc, je ne n'explique pas le résultat obtenu via la WHERE clause du UPDATE

Merci d'avance de vos lumières.
09/06/2017, 17h12
mnitu

Seule chose qu'on peut dire c'est err_code était nul.

D'autre part si le but de tout ça c'est d'insérer/modifier des enregistrements dans N tables et récupérer les anomalies tout en gardant les enregistrements sans anomalies il existe deux autres solutions offertes par Oracle: une avec du SQL et une autre avec du pl/sql
12/06/2017, 10h14
moracles

Explication sur le contexte du traitement

Bonjour,
c'est un peu compliqué de présenter de façon succincte le but du traitement : il s'agit de répercuter sur la BD le contenu des fichiers plats (insert/update).
On doit traiter différents fichiers/tables et le format de fichiers peut évoluer (traitement journalier).
J'ai décidé donc d'écrire une procédure avec PLSQL dynamique qui s'appuie sur une table de paramétrage (format du fichier, mapping colonnes, jointures etc).
J'utilise SQLLDR pour charger le fichier dans une table de travail, et ensuite je déroule une action sur la BD à partir de cette table (ligne par ligne).
Le 'Update' cité ci-dessus a pour but de récupérer le type d'opération (inert/update) et event. code erreur oracle dans ma table de travail.
J'aurais aimé le limiter aux lignes en erreur au lieu d'update (inutile) de tous les enregistrements.
pourquoi ce n'est pas possible (à partir de mon tableau PLSQL) ?
Je dois par la suite restituer, pour la correction, la partie du fichier d'origine sous son format d'origine avec l'explication sur erreur rencontrée.
Pour cela je prévois une table de traduction de messages d'erreurs.

En espérant avoir été suffisamment claire dans mon explication.
Je vous remercie tous de votre intérêt et de vos participations.

Cdlt