pB pL:sql CURSOR

**gdavezac** · 27/02/2009, 09h01

J'ai un script d'extraction de données dans un fichier plat en poisition longueur fixe.
J'ai une erreur que je n'arrive pas à résoudre ....

script :

begin
declare
NOMFIC varchar2(45);
Ret boolean;
ligne varchar2(1024);

-- Declaration des curseurs
-- ------------------------
-- Selection des lignes de details
-- -----
cursor c_det is
SELECT
.... ......

begin
fichier:= 'gda' ||to_char(sysdate,'YYYYMMDD')||'.txt';
fichier := 'gda.txt';
fic := sys.utl_file.fopen(repertoire, fichier,'w');

if sys.utl_file.is_open (fic) then
ligne := null;

-- ecriture des lignes de detail
for r_det in c_det loop
sys.utl_file.put_line (fic, r.det.ligne);
end loop;

end if;
sys.utl_file.fclose (fic);

Erreur :
SQL> sta createfic
for r_det in c_det loop
*
ERREUR à la ligne 139 :
ORA-06550: Ligne 139, colonne 6 :
PLS-00402: alias requis dans liste de curseur SELECT pour éviter noms de col.
en double
ORA-06550: Ligne 139, colonne 6 :
PL/SQL: Statement ignored

**iSylvain** · 27/02/2009, 09h12

Bonjour,

A priori la partie de ton code qui pose problème est celle que tu n'as pas mis

Dans ton select tu dois avoir plusieurs fois une colonne du même nom mais portant sur des tables différentes du coups il faut leur donner un alias.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
SELECT TABLE1.A AS "COL1", 
       TABLE2.A AS "COL1bis"
FROM TABLE1,
      TABLE2
WHERE ...

Sylvain

**gdavezac** · 27/02/2009, 09h27

Je te remercie pour ta réponse.

J'ai vérifié tout le select mais je n'ai rien vu ... tu me diras il est un peu long, j'ai du louper une ligne ....

Scrpt complet :

set serveroutput on size 1000000

-- on positionne les variables SQL
-- afin d'eviter des affichages parasites
set heading off
set verify off
set echo off
set feedback off
set pagesize 0

begin
declare
NOMFIC varchar2(45);
Ret boolean;
ligne varchar2(1024);

-- Declaration des curseurs
-- ------------------------
-- Selection des lignes de details
-- -----
cursor c_det is
SELECT
rpad(nvl(PET.MATRIC,' '),12,' '),
rpad(nvl(PET.ETNOMU,' '),20,' '),
rpad(nvl(PET.ETPREN,' '),20,' '),
rpad(nvl(PET.ETNOJF,' '),20,' '),
rpad(nvl(PAD.ADLIG1,' '),35,' '),
rpad(nvl(PAD.ADLIG2,' '),35,' '),
rpad(nvl(PAD.ADCPOS,' '),5,' '),
rpad(nvl(PAD.ADLCOM,' '),30,' '),
rpad(nvl(PAD.ADTEL1,' '),16,' '),
rpad(nvl(PET.ETCTIT,' '),4,' '),
rpad(nvl(BI.Libel(( TPA.CODHOP ),( TPA.CODDIF ),( TPA.TPANOME ),'NO','PFCCODE',( PFC.FCCODE ),' ',( PFC.DATEFF ),'LIS'),' '),11,' '),
rpad(nvl(PSJ.SJNENF,0),2,' '),
rpad(PET.ETDNAI,10,' '),
rpad(nvl(PET.ETDEPT,' '),3,' '),
rpad(nvl(PET.ETLCOM,' '),30,' '),
rpad(nvl(PET.ETNATI,' '),1,' '),
rpad(nvl(BI.Libel(( TPA.CODHOP ),( TPA.CODDIF ),( TPA.TPANOME ),'NO','PADPAYS',( PET.ETNATI ),' ',sysdate,'LIS'),' '),3,' '),
rpad(nvl(PET.ETSXSS || PET.ETAASS || PET.ETMMSS || PET.ETDPSS || PET.ETCMSS || PET.ETRGSS || PET.ETCLSS,' '),15,' '),
rpad(nvl(PSJ.SJUFON,' '),12,' '),
rpad(PSJ.SJDTAF,10,' '),
/* niveau 2 */
rpad(' ',12,' '),
rpad(' ',10,' '),
/* niveau 3*/
rpad(' ',12,' '),
rpad(' ',10,' '),
/* niveau 4 */
rpad(' ',12,' '),
rpad(' ',10,' '),
/* statut */
rpad(nvl(PSJ.SJSTAT,' '),12,' '),
rpad(to_char(PSJ.SJDTST,'DD/MM/YYYY'),10,' '),
/* mode de travail */
rpad(' ',12,' '),
rpad(' ',10,' '),
/* coe emploi */
rpad(' ',12,' '),
rpad(' ',10,' '),
/* grade */
rpad(nvl(PSJ.SJGRAD,' '),12,' '),
rpad(PSJ.SJDTGR,10,' '),
/* echelon */
rpad(' ',12,' '),
rpad(' ',10,' '),
/* indice */
rpad(' ',4,' '),
rpad(' ',10,' '),
/* categorie */
rpad(' ',3,' '),
rpad(' ',10,' '),
/* regime */
rpad(nvl(PHC.HCTEMP,' '),3,' '),
rpad(PHC.DATEFF,'10',' '),
/* ? */
rpad(' ',12,' '),
rpad(' ',10,' '),
/* date entree */
PSJ.SJDTTP,
PSJ.SJDEEH,
PSJ.SJDTEN,
/* sortie */
PSJ.SJDTSO,
PSJ.SJCDEP,
/* contrat */
rpad(' ',1,' '), /* type contrat */
rpad(' ',3,' '), /* durée contrat */
rpad(' ',10,' '), /* date fin contrat contrat */
/* divers */
rpad('FERIE',8,' '), /* code calendrier */
'N', /* pointage */
rpad(' ',7,' '), /* numéro de badge */
rpad(' ',1,' '), /* version du badge */
rpad(' ',30,' '), /* terminaux accessibles */
'N', /* flag controle acces */
rpad(' ',6,' '), /* profil et acces badge */
'O', /* gestion des temps */
' ', /* gtmode */
'01', /* code ecretage */
'01', /* groupe temps */
PSJ.SJDTTP, /* date effet groupe temps */
rpad(' ',162,' '),
'016 ', /* code agirh hopital */
PSJ.SJDTTP, /* date effet */
'STDDROITS ', /* code legislation */
PSJ.SJDTTP /* date effet */
FROM
PSJ,
TPA,
PFC,
PAD,
PES Sorties_Précédentes,
PET,
PHC,
PES Entrées
WHERE
pet.matric = '262447' and
( PET.CODHOP=PAD.CODHOP AND PET.MATRIC = PAD.MATRIC )
AND ( PSJ.CODHOP=PET.CODHOP AND PSJ.MATRIC = PET.MATRIC )
AND ( PFC.CODHOP=PET.CODHOP and PFC.MATRIC=PET.MATRIC )
AND ( PHC.CODHOP=PET.CODHOP and PHC.MATRIC=PET.MATRIC )
AND ( to_char(PHC.DATFIN,'DD/MM/YYYY') = '31/12/3000')
AND ( PET.CODHOP=Entrées.CODHOP(+) AND PET.MATRIC = Entrées.MATRIC(+) )
AND ( Entrées.CODHOP=Sorties_Précédentes.CODHOP(+) and Entrées.MATRIC=Sorties_Précédentes.MATRIC(+) AND (Sorties_Précédentes.DATEFF(+) = BI.occurence(Entrées.CODHOP,Entrées.MATRIC,Entrées.DATEFF,Entrées.NUMORD,'PES','SOR','1')) )
AND ( TPA.CODHOP=PET.CODHOP AND TPA.FLANNU = 'V' )
AND ( PET.CODHOP = '016' )
AND ( TPA.CODHOP = '016' )
AND ( Entrées.ESMODE != ' ' )
AND ( Sorties_Précédentes.ESMODS != ' ' OR Sorties_Précédentes.ESMODS IS NULL );

-- Declaration fichier extraction et variable
-- ------------------------------------------
fic utl_file.file_type;
repertoire varchar2(50) := 'c:\temp';
fichier varchar2(50) ;

-- Debut creation fichier
-- ---------------------
begin
fichier:= 'gda' ||to_char(sysdate,'YYYYMMDD')||'.txt';
fichier := 'gda.txt';
fic := sys.utl_file.fopen(repertoire, fichier,'w');

if sys.utl_file.is_open (fic) then
ligne := null;

-- ecriture des lignes de detail
--------------------------------

for r_det in c_det loop
sys.utl_file.put_line (fic, r.det.ligne);
end loop;

end if;
sys.utl_file.fclose (fic);

exception
when UTL_FILE.INVALID_PATH then
dbms_output.put_line ('INVALID PATH sur fichier ' || repertoire || '\' || fichier);
when UTL_FILE.INVALID_MODE then
dbms_output.put_line ('INVALID MODE'|| repertoire || '\' || fichier);
when UTL_FILE.INVALID_OPERATION then
dbms_output.put_line ('INVALID OPERATION'|| repertoire || '\' || fichier);
end;
end;

/

**gdavezac** · 27/02/2009, 09h38

Je suis sur une BASE Oracle 8.

Je pense que les parametres UTL.FILE ne marche pas avec cette version

**jsd03** · 27/02/2009, 09h39

Oh my godddd !

Ca c'est de la requête !!

Plusieurs chose à corriger :

- Donner un alias aux colonnes où tu utilises une fonction comme :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

rpad(nvl(PET.ETNOMU,' '),20,' ')

qui pourrait devenir :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

rpad(nvl(PET.ETNOMU,' '),20,' ') AS RPAD_ETNOMU

- Evites les commentaires dans les requêtes ça sera plus clair pour la relecture des requêtes

- donnes des alias à tes noms de tables quand leurs noms sont trop long

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

FROM myTABLE t, myTable2 p

et apelles leurs colonnes comme ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT t.myColonne, p.myColone

- évites les parenthèses superflues :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

AND ( Entrées.ESMODE != ' ' )

peut très bien s'écrire

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

AND Entrées.ESMODE != ' '

- utilises plutôt des USING à la place des WHERE ...AND... pour la jointure des tables

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

PET.CODHOP=PAD.CODHOP AND PET.MATRIC = PAD.MATRIC

Pour conclure, ton problème c'était qu'il fallait donner un alias aux colonnes utilisant une fonction.

PLS-00402: alias requis dans liste de curseur SELECT pour éviter noms de col.
en double

Bonne continuation

**iSylvain** · 27/02/2009, 10h54

Je pense qu'on ne peut pas faire une reponse plus claire et complète que celle de jsd03 !

**gdavezac** · 27/02/2009, 10h55

Pour tes conseils ....

J'ai mis des alias sur toutes les lignes (je sais, c'est long).
ça va un peu mieux .....

mais je vais finir par m'arrcher les cheveux ...

voici la nouvelle erreur :

SQL> sta createfic
sys.utl_file.put_line (fic, r_det);
*
ERREUR à la ligne 142 :
ORA-06550: Ligne 142, colonne 7 :
PLS-00306: numéro ou types d'arguments erronés dans appel à 'PUT_LINE'
ORA-06550: Ligne 142, colonne 7 :
PL/SQL: Statement ignored

Ce qu eje ne comprends pas, c'est que j'ai utilisé cette requete(sur d'autres tables) et je n'ai jamais eu ces erreurs. La requete était aussi plus simple.... il est vrai

**iSylvain** · 27/02/2009, 10h59

utl_file.put_line prend en premier argument un fichier et en second un varchar2.
Dans ton cas tu ne passes pas un varchar2 je crois (?).

http://www.psoug.org/reference/utl_file.html

Sylvain

**iSylvain** · 27/02/2009, 11h07

Essaye qq chose comme ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
OPEN c_det;
LOOP
    FETCH c_det INTO r_det;
    EXIT WHEN c_det%NOTFOUND;
    utl_file.putf(fic,'%s%s%s...\n',r_det.COL1,r_det.COL2,...);
end loop;

dans utl.putf tu mets autant de '%s' que tu as d'arguments à enregistrer dans ton fichier et tu mets dans l'ordre les valeurs associés à ton curseur.

Je ne sais pas si c'est très clair ...

Sylvain

EDIT : Euh par contre putf ne prend que 5 arguments max je crois ...
il vaudrait donc mieux faire

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
utl.put_line(fic,r_det.col1||','||r_det.col2||','||r_det.col3 ...);

**gdavezac** · 27/02/2009, 11h12

Grâce à votre aide.... cela à l'air de fonctionner.

Mon soucis est que je ne lis pas le paramètre UTL_FILE.

Erreur :
SQL> sta createfic
INVALID PATH sur fichier c:\temp\gda20090227.txt

Scripts :
when UTL_FILE.INVALID_PATH then
dbms_output.put_line ('INVALID PATH sur fichier ' || repertoire || '\' || fichier);
when UTL_FILE.INVALID_MODE then

C'est une Base V8 que je ne "manage" pas. Je ne suis pas sur du tout que UTL_FILE_DIR soit déclaré dans l'init.ora et que cette version l'utilise.

**iSylvain** · 27/02/2009, 11h30

Verifie si le directory est bien créé.
Normalement tu n'accèdes pas directement au c:\temp ... mais plutot à un directory au sens Oracle.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
create or replace directory dir_temp as 'c:\temp';
 
declare
  f utl_file.file_type;
begin
  f := utl_file.fopen('DIR_TEMP', 'something.txt', 'w');
  utl_file.put_line(f, 'line one: some text');
  utl_file.put_line(f, 'line two: more text');
  utl_file.fclose(f);
end;
/

Après tu n'as peut etre pas suffisament de droit pour l'utiliser des directory.
Et il faut vérifier que le user oracle a bien les droits d'ecriture sur le repertoire c:\temp (a priori sous windows il n'y a pas trop de soucis).

Sylvain

**jsd03** · 27/02/2009, 11h42

Envoyé par iSylvain

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
create or replace directory dir_temp as 'c:\temp';
/

De plus n'oublies pas de donner les droits d'accés au DIRECTORY :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

GRANT READ, WRITE ON DIRECTORY dir_temp TO PUBLIC ;

Ici je donne l'accès à tout le monde en lecture et écriture mais tu peux indiquer l'utilisateur que tu souhaites.

**gdavezac** · 27/02/2009, 13h11

Je sais, je suis une truffe ...

J'ai mis les parametres indiqués :

CREATE OR REPLACE directory dir_temp AS 'c:\temp';
GRANT READ, WRITE ON DIRECTORY dir_temp TO PUBLIC ;

.
.
.
begin
fichier:= 'gda' ||to_char(sysdate,'YYYYMMDD')||'.txt';

fic := sys.utl_file.fopen('dir_temp', fichier,'w');
.
.
.
sys.utl_file.fclose (fic);

exception
when UTL_FILE.INVALID_PATH then
dbms_output.put_line ('INVALID PATH sur fichier ' || 'dir_temp' || '\' || fichier);
when UTL_FILE.INVALID_MODE then
dbms_output.put_line ('INVALID MODE'|| 'dir_temp' || '\' || fichier);
when UTL_FILE.INVALID_OPERATION then
dbms_output.put_line ('INVALID OPERATION'|| 'dir_temp' || '\' || fichier);
end;

.
.
.

Et voici l'erreur :

SQL> sta createfic
INVALID PATH sur fichier dir_temp\gda20090227.txt