Contoh kalau pakai external table:
Sbg SYSTEM atau SYS
SQL> create or replace directory text_file_dir as '/home/text_file_dir';
SQL> grant read, write on directory text_file_dir to nama_user;
Sbg nama_user, buat 3 buah external tables sbb:
create table ext_onnet (
total NUMBER,
nomor NUMBER
)
organization external (
type oracle_loader
default directory text_file_dir
access parameters (
records delimited by newline
fields terminated by ' '
missing field values are null
)
location ('onnet.txt')
)
reject limit unlimited;
create table ext_offnet (
total NUMBER,
nomor NUMBER
)
organization external (
type oracle_loader
default directory text_file_dir
access parameters (
records delimited by newline
fields terminated by ' '
missing field values are null
)
location ('offnet.txt')
)
reject limit unlimited;
create table ext_international (
total NUMBER,
nomor NUMBER
)
organization external (
type oracle_loader
default directory text_file_dir
access parameters (
records delimited by newline
fields terminated by ' '
missing field values are null
)
location ('international.txt')
)
reject limit unlimited;
select nomor, sum(ONNET) as onnet, sum(OFFNET) as offnet, sum(int) as
international
from
(select nomor, TOTAL AS ONNET, null as offnet, null as int from EXT_ONNET
union
select nomor, null as onnet,TOTAL AS OFFNET, null as int from EXT_OFFNET
union
select nomor, null as onnet,null as offnet, TOTAL AS INT from EXT_INTERNATIONAL
)
group by nomor;
Bowo
--- In [email protected], romy <romy.hua...@...> wrote:
>
> Saat ini akhirnya pk text importernya PL SQL...
>
> Cuman datanya ada 500rb baris per 1 file...
> Lumayan lama juga, itupun 200 baris terakhir terkadang gagal di masukkan.
>
> Saat ini bisanya pake PL SQL.
>
> Ada yang bisa bantu ?
>
> Terima kasih
>
> Romy
>
> izzudin.hanafie wrote:
> >
> >
> > Kenapa ngga di load saja dulu ke dalam suatu table di database, baru
> > setelah itu dilakukan query pak..
> >
> > Salam,
> >
> > IH
> >
>
