Home » RDBMS Server » Server Utilities » Possible to use ...%type in external table field declaration?
Possible to use ...%type in external table field declaration? [message #203252] Tue, 14 November 2006 08:35 Go to next message
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 #203274 is a reply to message #203252] Tue, 14 November 2006 09:54 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi.

%type, or %rowtype is plsql datatype. so i think you cann't use in external tables.

waiting for others opinion.

hope this helps
Mohammad Taj.
Re: Possible to use ...%type in external table field declaration? [message #203280 is a reply to message #203274] Tue, 14 November 2006 10:13 Go to previous message
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
Previous Topic: IMP-00023 import from 7.3.4 to 8.1.7
Next Topic: To avoid duplicate rows in oracle 8i
Goto Forum:
  


Current Time: Thu Dec 08 16:09:54 CST 2016

Total time taken to generate the page: 0.14980 seconds