Create table from file ? [Résolu]

[Reivilo35]

Bonjour,
Je cherche à créer une table à partir d'un fichier texte.
Je souhaiterais utiliser une requête du style :

Code :

1
2
 
CREATE TABLE nom_table (colonne_1 char(13),colonne_2 char(10)) FROM file

J'ai recherché ce qui pourrait ressembler à ce que je veux faire, mais je ne trouve pas exactement ce que je veux.
Je ne cherche peut-être pas au bon endroit.

Le fichier source qui contient les données à insérer dans ma table contient 950000 lignes et est structuré comme ceci :
- une première donnée sur 13 caractères
- et une deuxième sur 10 caractères.
Il ressemble à ce qui suit :

000000101P028DIR029893A
000000101P124DIR029986A
000010000R003TRL313504A
000010000R004TRL315280A
000010000R005TRL315281A
000010000R006TRL315282A
000010000R008TRL313505A
000010202R001TRL260637A
000010202R002TRL260638A
000010202R003TRL260639A
000010202R004TRL260640A
000020002R054TRL031713A
000020004P060AHD000038A
000020004P065AHD000040A
000020004R164TRL216507A
000020004R165TRL216508A
000020004R166TRL226665A
000020006R001TRL024416A
000020006R002TRL024417A
000020006R007TRL035005A
000020006R008TRL035006A
000020006R013TRL035007A
000020006R017TRL138338A
000020006R018TRL138339A
000020006R026TRL121170A
000020006R029TRL116631A
000020006R045TRL116646A
000020006R047TRL210413A
000020006R048TRL245679A

Si quelqu'un voit ce que je veux dire et sait comment y parvenir, je le remercie par avance.

Bonne journée.

Cdlt,
Olivier CHRETIEN

[Waldar]

Vous ne pouvez pas le faire exactement comme vous l'entendez, mais si vous êtes en 10g+ vous pouvez utiliser une table externe.

[mnitu]

Les tables externes sont arrivées avec Oracle 9 et oui utilisez une table externe pour ça.

[Reivilo35]

OK merci.
Je suis en train de regarder dans cette direction et j'ai réalisé un script qui tourne mais à la suite duquel il est impossible de consulter les enregistrements de la table (qui a bien été créée).

Voici mon script :

Code :

1
2
3
4
5
6
7
8
9
10
 
-- Valorisation de la variable 'ext'
CREATE OR REPLACE DIRECTORY ext AS '/home/';
 
-- Creation de la table temporaire REF_FACTURE_TMP a partir du fichier texte FFAA77
CREATE TABLE REF_FACTURE_TMP (ANCNUMFEU VARCHAR2(13),REF_FAC VARCHAR2(10)) ORGANIZATION EXTERNAL (TYPE oracle_loader DEFAULT DIRECTORY ext ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ',' MISSING FIELD VALUES ARE NULL) LOCATION ('FFAA77')) REJECT LIMIT unlimited;
 
COMMIT;
 
QUIT

Et voici une partie de mon fichier texte où j'ai rajouté un séparateur de champ (,) :

000000101P028,DIR029893A
000000101P124,DIR029986A
000010000R003,TRL313504A
000010000R004,TRL315280A
000010000R005,TRL315281A
000010000R006,TRL315282A
000010000R008,TRL313505A
000010202R001,TRL260637A
000010202R002,TRL260638A
000010202R003,TRL260639A

Les erreurs Oracle que j'ai en voulant consulter la table REF_FACTURE_TMP sont dans la pièce jointe.

Je n'y comprends pas grand chose.
Merci pour votre aide.

Cdlt,
Olivier CHRETIEN

[mnitu]

Le fichier est test.txt dans le sous-répertoire c:\temp
Faite attention à la casse dans le nom du directory (TMP) et en fonction du système d'exploitation, au nom du fichier.

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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
 
Connected TO Oracle DATABASE 10g Express Edition Release 10.2.0.1.0 
Connected AS mni
 
SQL> 
SQL> CREATE OR REPLACE directory TMP AS 'c:\temp'
  2  /
 
Directory created
SQL> GRANT READ, WRITE ON directory TMP TO hr
  2  /
 
GRANT succeeded
SQL> connect hr/hr
Connected TO Oracle DATABASE 10g Express Edition Release 10.2.0.1.0 
Connected AS hr
SQL> CREATE TABLE ext_table (
  2    col1       varchar2(30),
  3    col2       varchar2(30)
  4  )
  5  organization external
  6  (type oracle_loader
  7   DEFAULT directory TMP
  8   access parameters
  9   (records delimited BY newline
 10    nobadfile
 11    nologfile
 12    FIELDS terminated BY ','
 13    (col1 char(30),
 14     col2 char(30)
 15    )
 16   )
 17   location ('test.txt')
 18  )
 19  reject LIMIT unlimited
 20  /
 
TABLE created
SQL> SELECT *
  2    FROM ext_table
  3  /
 
COL1                           COL2
------------------------------ ------------------------------
000000101P028                  DIR029893A
000000101P124                  DIR029986A
000010000R003                  TRL313504A
000010000R004                  TRL315280A
000010000R005                  TRL315281A
000010000R006                  TRL315282A
000010000R008                  TRL313505A
000010202R001                  TRL260637A
000010202R002                  TRL260638A
000010202R003                  TRL260639A
 
10 rows selected
 
SQL>

[Reivilo35]

Merci Mnitu.
Je vais modifier mon script.

Cependant, j'ai deux questions:
- à quoi sert la directory TMP ?
- et dans le cas d'un script appelé par un programme, où dois-je mettre le fichier 'test.txt' ? Dans mon cas, je l'ai mis à la même racine que le script PL/SQL qui l'appelle. Est-ce correct ?

Cldt,
Olivier CHRETIEN

[mnitu]

Un directory est un nom logique dans la base des données pour un sous-répertoire physique du système d’exploitation. TMP indique où Oracle va chercher le fichier. Le fichier devrait se trouver dans ce répertoire et Oracle doit disposer des droits appropriés pour y accéder.
Normalement le script PL/SQL devrait se trouves dans un répertoire de type script ou sql, etc. et votre fichier test.txt dans un répertoire de stockage des fichiers à traiter : temp ou interface ou trv ? etc.

[Reivilo35]

Bonjour,
Merci pour les réponses.
Je suis en train de tester.

Par contre, est-ce que la partie 'FIELDS terminated BY ','' est indispensable ?
Peut-on se passer du séparateur de champ ?

Dans mon cas, le fichier d'entrée fait plus de 950000 lignes, et ça ferait gagner du temps si on n'avait pas à formater ce fichier avec un séparateur de champ.
Format du fichier d'entrée :

000000101P028DIR029893A
000000101P124DIR029986A
000010000R003TRL313504A
000010000R004TRL315280A
000010000R005TRL315281A
000010000R006TRL315282A
000010000R008TRL313505A
000010202R001TRL260637A
000010202R002TRL260638A
000010202R003TRL260639A
000010202R004TRL260640A

Merci.
Cdlt,
Olivier CHRETIEN

[Reivilo35]

Bonjour,
Merci pour vos aides et conseils : ça fonctionne.

J'ai une dernière question avant de passer cette discussion en "résolue".

La seule contrainte qu'il me reste à gérer c'est le formatage du fichier de données en entrée.
Je n'ai pas eu de réponse sur la nécessité ou non du séparateur de champ, alors je voudrais savoir quel moyen assez rapide permettrait de convertir le fichier suivant :

000000101P028DIR029893A
000000101P124DIR029986A
000010000R003TRL313504A
000010000R004TRL315280A
000010000R005TRL315281A
000010000R006TRL315282A
000010000R008TRL313505A
000010202R001TRL260637A
000010202R002TRL260638A
000010202R003TRL260639A
000010202R004TRL260640A

en :

000000101P028,DIR029893A
000000101P124,DIR029986A
000010000R003,TRL313504A
000010000R004,TRL315280A
000010000R005,TRL315281A
000010000R006,TRL315282A
000010000R008,TRL313505A
000010202R001,TRL260637A
000010202R002,TRL260638A
000010202R003,TRL260639A
000010202R004,TRL260640A

en sachant que ce fichier en entrée peut atteindre presque le million de lignes.

J'ai essayé avec l'éditeur de texte PSPad avec des expressions régulières, mais l'éditeur plante.

Merci pour votre aide.
Cdlt,
Olivier CHRETIEN

[Waldar]

Ne modifiez pas votre fichier en entrée, s'il n'a pas de séparateur c'est donc un fichier à longueur fixe.
Je n'ai pas d'Oracle sous la main mais soit en définissant précisément vos colonnes soit avec un substr vous devriez vous en sortir.

[mnitu]

Ne chagez pas le fichier changez la table

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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
 
Connected TO Oracle DATABASE 10g Express Edition Release 10.2.0.1.0 
Connected AS hr
 
SQL> 
SQL> DROP TABLE ext_table
  2  /
 
TABLE dropped
SQL> CREATE TABLE ext_table (
  2      col1       varchar2(13),
  3      col2       varchar2(10)
  4    )
  5  organization external
  6  (
  7    type oracle_loader
  8    DEFAULT directory TMP
  9    access parameters
 10    (
 11      records delimited BY newline
 12      nobadfile
 13      nologfile
 14      FIELDS
 15      (col1 (1:13) char,
 16       col2 (14:23) char
 17      )
 18    )
 19    location ('test.txt')
 20  )
 21  reject LIMIT unlimited
 22  /
 
TABLE created
SQL> SELECT *
  2  FROM ext_table
  3  /
 
COL1          COL2
------------- ----------
000000101P028 DIR029893A
000000101P124 DIR029986A
000010000R003 TRL313504A
000010000R004 TRL315280A
000010000R005 TRL315281A
000010000R006 TRL315282A
000010000R008 TRL313505A
000010202R001 TRL260637A
000010202R002 TRL260638A
000010202R003 TRL260639A
000010202R004 TRL260640A
 
11 rows selected
 
SQL>

[Reivilo35]

Super, c'est exactement ce que je voulais faire.
Merci beaucoup.
Cdlt,
Olivier CHRETIEN