| Possible to use ...%type in external table field declaration? [message #203252] |
Tue, 14 November 2006 08:35  |
bobo69
Messages: 9
Registered: November 2005
|
Junior Member |
|
|
Hi all,
I would like to import/export data using external files, with exactly the same type/size for fields in the internal as in the external table.
Imagine for an import a file 'foo_external.txt' containing 2 lines:
1,AAAAA,
2,BBBBB,
So instead of having:
set serveroutput on echo on termout on
DROP TABLE foo;
CREATE TABLE foo
(
nb number(5),
vc varchar2(5)
);
CREATE OR REPLACE DIRECTORY TEMPO AS '/PROJET/AESLAB/bin/kdb_genTEST/ext/tempo';
DROP TABLE foo_external;
CREATE TABLE foo_external
(
nb_ext number(5),
vc_ext varchar2(5)
)
ORGANIZATION EXTERNAL
(
TYPE oracle_loader
DEFAULT DIRECTORY TEMPO
ACCESS PARAMETERS
(
RECORDS DELIMITED BY newline
NOBADFILE
NODISCARDFILE
NOLOGFILE
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
)
LOCATION ('foo_external.txt')
)
REJECT LIMIT UNLIMITED;
--select * from foo_external;
--insert into foo select * from foo_external;
--select * from foo;
I would prefer to indicate somehting like:
...
(
nb_ext foo.nb%type,
vc_ext foo.vc%type
)
...
But if I do so, I get an error:
nb_ext foo.nb%type,
*
ERREUR à la ligne 3 :
ORA-00911: invalid character
Ever done before ? Even possible? That would minimize duplicate information and simplify maintenance...
Thanks for any hint!
|
|
|
|
|
|
| Re: Possible to use ...%type in external table field declaration? [message #203280 is a reply to message #203274] |
Tue, 14 November 2006 10:13  |
bobo69
Messages: 9
Registered: November 2005
|
Junior Member |
|
|
Thank you Mohammad for responding!
Yes, I came up with the same conclusion a couple of minutes ago, and swithced then to try adapting the 'create table as select' syntax to my case, but with no more success ...
I might consider doing something more complex with plsql, but not sure!
Thanks anyway for that...
Stephane
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
