Importer des données access sous Oracle
Fermé
p6
Messages postés
4
Date d'inscription
vendredi 19 mars 2004
Statut
Membre
Dernière intervention
22 mars 2004
-
19 mars 2004 à 15:37
oaks - 19 mars 2004 à 17:37
oaks - 19 mars 2004 à 17:37
A voir également:
- Importer des données access sous Oracle
- Importer contact - Guide
- Importer favoris chrome - Guide
- Validation des données excel - Guide
- Importer favoris firefox - Guide
- Effacer les données de navigation - Guide
2 réponses
DaNot
Messages postés
221
Date d'inscription
mardi 30 septembre 2003
Statut
Membre
Dernière intervention
4 novembre 2005
163
19 mars 2004 à 15:40
19 mars 2004 à 15:40
Salut,
De mémoire, il existe un outils livré avec ORACLE et qui s'appelle SQL*Loader.
DaNot
un Libre ouvert à la source...
De mémoire, il existe un outils livré avec ORACLE et qui s'appelle SQL*Loader.
DaNot
un Libre ouvert à la source...
Bonjour,
petit exemple :
aide en ligne de sqlloader :
============================
>sqlldr help=yes
userid ORACLE username/password
control Control file name
log Log file name
bad Bad file name
data Data file name
discard Discard file name
discardmax Number of discards to allow
skip Number of logical records to skip
load Number of logical records to load
errors Number of errors to allow
rows Number of rows in conventional path bind array or between direct path data saves
bindsize Size of conventional path bind array in bytes
silent Suppress messages during run (header,feedback,errors,discards,partitions)
direct use direct path
_synchro internal testing
parfile parameter file: name of file that contains parameter specifications
parallel do parallel load
file File to allocate extents from
skip_unusable_indexes disallow/allow unusable indexes or index partitions
skip_index_maintenance do not maintain indexes, mark affected indexes as unusable
commit_discontinued commit loaded rows when load is discontinued
_display_exitcode Display exit code for SQL*Loader execution
readsize Size of Read buffer
exemple :
==========
creation de la table cible sous ORACLE :
---------------------------------------
DROP TABLE UPTABLE CASCADE CONSTRAINTS ;
CREATE TABLE UPTABLE (
NOMTABLE VARCHAR2 (20) NOT NULL,
ID1 VARCHAR2 (20) DEFAULT null,
ID2 VARCHAR2 (20) DEFAULT null,
ID3 VARCHAR2 (20) DEFAULT null,
ID4 VARCHAR2 (20) DEFAULT null,
ID5 VARCHAR2 (20) DEFAULT null,
ID6 VARCHAR2 (20) DEFAULT null)
-- TABLESPACE DATA1
-- PCTFREE 10
-- PCTUSED 40
-- INITRANS 1
-- MAXTRANS 255
-- STORAGE (
-- INITIAL 100000
-- NEXT 8192
-- PCTINCREASE 0
-- MINEXTENTS 1
-- MAXEXTENTS 100
-- FREELISTS 1 FREELIST GROUPS 1 )
-- NOCACHE;
COMMENT ON TABLE UPTABLE IS 'table temporaire de recensement des tables demandant une MAJ';
COMMENT ON COLUMN UPTABLE.ID1 IS 'field1';
COMMENT ON COLUMN UPTABLE.ID2 IS 'field2';
COMMENT ON COLUMN UPTABLE.ID3 IS 'field3';
COMMENT ON COLUMN UPTABLE.ID4 IS 'field4';
COMMENT ON COLUMN UPTABLE.ID5 IS 'field5';
COMMENT ON COLUMN UPTABLE.ID6 IS 'field6';
COMMENT ON COLUMN UPTABLE.NOMTABLE IS 'table name';
fichier de controle (basique, beaucoup de possibilités comme l'utilisation de DECODE, SYSDATE,....) :
-----------------------------------------------------------------------------------------------------
LOAD DATA
INFILE 'C:\temp\struct\data\UPTABLE2.csv' -- fichier source, separateur ;
BADFILE 'C:\temp\struct\data\UPTABLE2.bad' -- fichier ou seront stocké les enregs en erreur
DISCARDFILE 'C:\temp\struct\data\UPTABLE2.dsc'
INTO TABLE "UPTABLE"
INSERT
TRAILING NULLCOLS
(NOMTABLE TERMINATED BY ';'
,
ID1 TERMINATED BY ';'
,
ID2 TERMINATED BY ';'
,
ID3 TERMINATED BY ';'
,
ID4 TERMINATED BY ';'
,
ID5 TERMINATED BY ';'
,
ID6 TERMINATED BY ';'
)
ligne de commande :
sqlldr SCOTT/TIGER control=./data/uptable.ctl (compte ORACLE SCOTT, password TIGER)
LOG :
SQL*Loader: Release 8.1.6.0.0 - Production on Fri Feb 15 16:09:05 2002
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Control File: C:\temp\struct\data\uptable.ctl
Data File: C:\temp\struct\data\UPTABLE2.csv
Bad File: C:\temp\struct\data\UPTABLE2.bad
Discard File: C:\temp\struct\data\UPTABLE2.dsc
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 65536 bytes
Continuation: none specified
Path used: Conventional
Table "UPTABLE", loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
NOMTABLE FIRST * ; CHARACTER
ID1 NEXT * ; CHARACTER
ID2 NEXT * ; CHARACTER
ID3 NEXT * ; CHARACTER
ID4 NEXT * ; CHARACTER
ID5 NEXT * ; CHARACTER
ID6 NEXT * ; CHARACTER
Table "UPTABLE":
98 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 65016 bytes(36 rows)
Space allocated for memory besides bind array: 0 bytes
Total logical records skipped: 0
Total logical records read: 98
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Fri Feb 15 16:09:05 2002
Run ended on Fri Feb 15 16:09:06 2002
Elapsed time was: 00:00:01.29
CPU time was: 00:00:00.11
Oaks
petit exemple :
aide en ligne de sqlloader :
============================
>sqlldr help=yes
userid ORACLE username/password
control Control file name
log Log file name
bad Bad file name
data Data file name
discard Discard file name
discardmax Number of discards to allow
skip Number of logical records to skip
load Number of logical records to load
errors Number of errors to allow
rows Number of rows in conventional path bind array or between direct path data saves
bindsize Size of conventional path bind array in bytes
silent Suppress messages during run (header,feedback,errors,discards,partitions)
direct use direct path
_synchro internal testing
parfile parameter file: name of file that contains parameter specifications
parallel do parallel load
file File to allocate extents from
skip_unusable_indexes disallow/allow unusable indexes or index partitions
skip_index_maintenance do not maintain indexes, mark affected indexes as unusable
commit_discontinued commit loaded rows when load is discontinued
_display_exitcode Display exit code for SQL*Loader execution
readsize Size of Read buffer
exemple :
==========
creation de la table cible sous ORACLE :
---------------------------------------
DROP TABLE UPTABLE CASCADE CONSTRAINTS ;
CREATE TABLE UPTABLE (
NOMTABLE VARCHAR2 (20) NOT NULL,
ID1 VARCHAR2 (20) DEFAULT null,
ID2 VARCHAR2 (20) DEFAULT null,
ID3 VARCHAR2 (20) DEFAULT null,
ID4 VARCHAR2 (20) DEFAULT null,
ID5 VARCHAR2 (20) DEFAULT null,
ID6 VARCHAR2 (20) DEFAULT null)
-- TABLESPACE DATA1
-- PCTFREE 10
-- PCTUSED 40
-- INITRANS 1
-- MAXTRANS 255
-- STORAGE (
-- INITIAL 100000
-- NEXT 8192
-- PCTINCREASE 0
-- MINEXTENTS 1
-- MAXEXTENTS 100
-- FREELISTS 1 FREELIST GROUPS 1 )
-- NOCACHE;
COMMENT ON TABLE UPTABLE IS 'table temporaire de recensement des tables demandant une MAJ';
COMMENT ON COLUMN UPTABLE.ID1 IS 'field1';
COMMENT ON COLUMN UPTABLE.ID2 IS 'field2';
COMMENT ON COLUMN UPTABLE.ID3 IS 'field3';
COMMENT ON COLUMN UPTABLE.ID4 IS 'field4';
COMMENT ON COLUMN UPTABLE.ID5 IS 'field5';
COMMENT ON COLUMN UPTABLE.ID6 IS 'field6';
COMMENT ON COLUMN UPTABLE.NOMTABLE IS 'table name';
fichier de controle (basique, beaucoup de possibilités comme l'utilisation de DECODE, SYSDATE,....) :
-----------------------------------------------------------------------------------------------------
LOAD DATA
INFILE 'C:\temp\struct\data\UPTABLE2.csv' -- fichier source, separateur ;
BADFILE 'C:\temp\struct\data\UPTABLE2.bad' -- fichier ou seront stocké les enregs en erreur
DISCARDFILE 'C:\temp\struct\data\UPTABLE2.dsc'
INTO TABLE "UPTABLE"
INSERT
TRAILING NULLCOLS
(NOMTABLE TERMINATED BY ';'
,
ID1 TERMINATED BY ';'
,
ID2 TERMINATED BY ';'
,
ID3 TERMINATED BY ';'
,
ID4 TERMINATED BY ';'
,
ID5 TERMINATED BY ';'
,
ID6 TERMINATED BY ';'
)
ligne de commande :
sqlldr SCOTT/TIGER control=./data/uptable.ctl (compte ORACLE SCOTT, password TIGER)
LOG :
SQL*Loader: Release 8.1.6.0.0 - Production on Fri Feb 15 16:09:05 2002
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Control File: C:\temp\struct\data\uptable.ctl
Data File: C:\temp\struct\data\UPTABLE2.csv
Bad File: C:\temp\struct\data\UPTABLE2.bad
Discard File: C:\temp\struct\data\UPTABLE2.dsc
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 65536 bytes
Continuation: none specified
Path used: Conventional
Table "UPTABLE", loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
NOMTABLE FIRST * ; CHARACTER
ID1 NEXT * ; CHARACTER
ID2 NEXT * ; CHARACTER
ID3 NEXT * ; CHARACTER
ID4 NEXT * ; CHARACTER
ID5 NEXT * ; CHARACTER
ID6 NEXT * ; CHARACTER
Table "UPTABLE":
98 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 65016 bytes(36 rows)
Space allocated for memory besides bind array: 0 bytes
Total logical records skipped: 0
Total logical records read: 98
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Fri Feb 15 16:09:05 2002
Run ended on Fri Feb 15 16:09:06 2002
Elapsed time was: 00:00:01.29
CPU time was: 00:00:00.11
Oaks
