Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, nikhil.

Asked: November 25, 2002 - 9:17 am UTC

Last updated: March 18, 2018 - 3:40 am UTC

Version: 9.0.1

Viewed 50K+ times! This question is

You Asked

hi Tom,

Is there any advantage of sql* Loader over the new 9i feature external table. Which of them is faster. sql* loader direct path load or direct path load from the external table.

thanx in advance

Nikhil
India

and Tom said...

well, lets do a simple example and see what we see:

ops$tkyte@ORA920.US.ORACLE.COM> create or replace directory data_dir as '/tmp/'
2 /
Directory created.

ops$tkyte@ORA920.US.ORACLE.COM> create table external_table
2 (EMPNO NUMBER(4) ,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2)
10 )
11 ORGANIZATION EXTERNAL
12 ( type oracle_loader
13 default directory data_dir
14 access parameters
15 ( fields terminated by ',' )
16 location ('emp.dat')
17 )
18 /
Table created.

ops$tkyte@ORA920.US.ORACLE.COM> create or replace view emp_view
2 as
3 select * from scott.emp, all_objects;
View created.

ops$tkyte@ORA920.US.ORACLE.COM> host flat / emp_view > /tmp/emp.dat

see
</code> http://asktom.oracle.com/~tkyte/flat/index.html <code>
for 'flat'




ops$tkyte@ORA920.US.ORACLE.COM> !wc /tmp/emp.dat
410340 568568 57946304 /tmp/emp.dat

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> select count(*) from external_table
2 /

COUNT(*)
----------
410340



ops$tkyte@ORA920.US.ORACLE.COM> set timing on
ops$tkyte@ORA920.US.ORACLE.COM> create table emp as select * from external_table;

Table created.

Elapsed: 00:00:03.13


ops$tkyte@ORA920.US.ORACLE.COM> insert /*+ append */ into emp select * from external_table;

410340 rows created.

Elapsed: 00:00:07.69


ops$tkyte@ORA920.US.ORACLE.COM> truncate table emp;

Table truncated.


versus

[tkyte@tkyte-pc sqlstuff]$ time sqlldr / /tmp/emp.ctl direct=true

SQL*Loader: Release 9.2.0.1.0 - Production on Mon Nov 25 09:35:34 2002

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Load completed - logical record count 410340.

real 0m8.747s
user 0m2.422s
sys 0m0.588s
[tkyte@tkyte-pc sqlstuff]$



totally non-scientific but here is my opinion

External tables are so much easier the sqlldr -- insert /*+ append */ is showing about the same performance, CTAS even better.

You can use SQL to filter and transform the data much easier. True, sqlldr in direct path can now use sql functions -- but the filtering is really powerful

multi-table inserts, merge's -- can't be beat.


I really dig external tables.

Rating

  (154 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Very good example.

Leo, November 25, 2002 - 10:56 am UTC


external tables

A reader, May 11, 2003 - 7:54 pm UTC

My understanding is that external tables are read only and cannot permit any DML. We cannot create any indexes or constraints. How external tables will be useful (only for queries that too without any indexes)? If I used sqlldr, I can load the data into a table that I can use for further DML in my database. am i missing anything?

apologize for my ignorance. Could you please clarify? thanks.

Tom Kyte
May 11, 2003 - 8:05 pm UTC

you are using external tables *instead of* sqlldr.

with external tables you can

o merge a flat file with an existing table in one statement.
o sort a flat file on the way into a table you want compressed nicely.
o do a parallel direct path load -- without splitting up the input file, writing
umpteen scripts and so on
o run sqlldr in effect from a stored procedure or trigger (insert is not sqlldr)
o do multi-table inserts
o flow the data through a pipelined plsql function for cleansing/transformation

and so on. they are *instead of* sqlldr -- to get data into the database without having to use sqlldr in the first place.

You would not normally query them day to day in an operational system, you use them to load data.

Thanks a lot!

A reader, May 11, 2003 - 8:49 pm UTC


My 2.5 cents...

kashif, May 11, 2003 - 10:22 pm UTC

Hi Tom,

I've noticed that timings on both SQL Loader and External Table loads (using insert /*+ append */) are roughly comparable, but the real boost I get is when I need to update data post-load, from different tables. For example, recently I modified a process which was as follows:

Use sqlldr to upload file to table A.
Update table A using codes from table B and table C.

Now, I simply do:
Rename file to name of external table source (e.g. 123.txt).
Insert into table A from external table. Use previous updates as embedded selects in insert statements.

The real difference I get is when I can simply select into the target table's column values I would otherwise have updated after the load, using selects. This way, I have one insert statement, with all the updates I need to do as part of the same statement, in the form of selects. To give you some timing numbers, the sqlldr and updates took almost an hour and a half. With the external tables and the embedded selects the process finishes in about 15 minutes. And it is so much easier to use, compared to sqlldr.

Kashif

Sqlldr vs Temprary Table

Reaz/Dhaka, May 12, 2003 - 4:37 am UTC

I am using it in a real time project which is underway. Frankly , this new feature of 9i has made our ETL process lot simpler then it was.

For example, the whole of filtering/cleansing could now be done in flat file without loading it in database.

This is definetley a powerful feature.

Excellent Example

Abubaker Khered, May 12, 2003 - 7:49 am UTC


Some difficulties in doing the example

Abubaker, May 13, 2003 - 4:38 am UTC

Hi Tom,

I liked the example above very much, because it is going to make my live a lot easier. But when I am about to do it I was stuck-up at:

SQL> !wc /tmp/emp.dat
SP2-0734: unknown command beginning "!wc /tmp/e..." - rest of line ignored.

Also is there anyway to check the result of the following command:
SQL> host flat / emp_view > /tmp/emp.dat 


Although I have copied the files you mentioned in the above link (flat, sqldr_exp) to the working directory where I run the sqlplus from. 

I am having oracle 92 on NT server.


Would you please explain what I was doing wrong.


Regards,
Abubaker
 

Tom Kyte
May 13, 2003 - 7:24 am UTC

windoze does not do "wc", windows does not do !, you must use HOST.


I do have flat and sqlldr_exp for windows here:
</code> http://asktom.oracle.com/~tkyte/flat/index.html <code>

you would of course use windoze file names, not /tmp/emp.dat

External tables and zoned data

Bob Rich, June 25, 2003 - 3:33 pm UTC

Tom,
We have an existing application that uses sqlloader to load zoned data from a mainframe. There seems to be a bug with 9.2 (bug 3 3022089) that is delaying an upgrade. Can an external table be used for this application?

Tom Kyte
June 25, 2003 - 8:36 pm UTC

did you try? i have no "zoned data" with which to do so myself.

bug with external tables and ltrim (or lrtrim)

Freek, June 26, 2003 - 4:45 am UTC

Tom,

When I use the lrtrim or the ltrim keyword in an external table definition,then I get the field too long for datatype error when the value of the field are spaces.
When I use no trim or rtrim then I don't get this error.

Is this a bug or am I doing something wrong?

thanks

Freek


system@dev01> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

system@dev01> create directory external_table_dir as 'd:\arch\debug';

Directory created.

system@dev01> grant read, write on directory external_table_dir to freek;

Grant succeeded.

system@dev01> @connect freek@dev01

freek@dev01> create table test
2 (
3 test1 varchar2(50),
4 test2 varchar2(50)
5 )
6 organization external
7 (
8 type oracle_loader
9 default directory external_table_dir
10 access parameters
11 (
12 records delimited by newline
13 skip 1
14 fields terminated by 0x'09'
15 lrtrim
16 missing field values are null
17 (
18 test1 char(50),
19 test2 char(50)
20 )
21 )
22 location ('test.txt')
23 )
24 /

Table created.

freek@dev01> select * from test;
select * from test
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1


freek@dev01> drop table test;

Table dropped.

freek@dev01> create table test
2 (
3 test1 varchar2(50),
4 test2 varchar2(50)
5 )
6 organization external
7 (
8 type oracle_loader
9 default directory external_table_dir
10 access parameters
11 (
12 records delimited by newline
13 skip 1
14 fields terminated by 0x'09'
15 rtrim
16 missing field values are null
17 (
18 test1 char(50),
19 test2 char(50)
20 )
21 )
22 location ('test.txt')
23 )
24 /

Table created.

freek@dev01> column test1 format a30
freek@dev01> column test2 format a30
freek@dev01> select * from test;

TEST1 TEST2
------------------------------ ------------------------------
test test
test



content test.txt
----------------

test1 test2
test test
test

output from log file
--------------------


LOG file opened at 06/24/03 16:57:29

Field Definitions for table TEST
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted

Fields in Data Source:

TEST1 CHAR (50)
Terminated by "09"
Trim whitespace from left and right
TEST2 CHAR (50)
Terminated by "09"
Trim whitespace from left and right
KUP-04021: field formatting error for field TEST1
KUP-04026: field too long for datatype
KUP-04101: record 3 rejected in file d:\arch\debug\test.txt


LOG file opened at 06/24/03 16:58:49

Field Definitions for table TEST
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted

Fields in Data Source:

TEST1 CHAR (50)
Terminated by "09"
Trim whitespace from right
TEST2 CHAR (50)
Terminated by "09"
Trim whitespace from right



content of bad file:
--------------------

test


Tom Kyte
June 26, 2003 - 9:30 am UTC

appears to be an issue in 9201, cannot reproduce it in 9203. it is caused by the null field apparently.

suggestion until you get to 9203:

o use a view

organization external
(
type oracle_loader
default directory external_table_dir
access parameters
(
records delimited by newline
skip 1
fields terminated by 0x'09'
missing field values are null
(
test1 char(50),
test2 char(50)
)
)
location ('test.txt')
)
/
create or replace view v
as
select trim( test1 ) test1, trim( test2 ) test2 from test;


to trim.....




Not bad, but there's better ways of doing this

Nigel Heffernan, June 30, 2003 - 12:06 pm UTC

The lowly MS-Access programmers have a slower, but much, much easier method. The Oracle target table and the source file can both be declared as 'Linked' tables - definitions are created, but no data is imported in an Access database - and then its just a case of writing (or visually building) a single JET-SQL 'INSERT...' query.

MS-Access was designed for botched, bodged and cobbled-together data operations. Oracle wasn't, and it's unreasonable to expect a 'professional' RDBMS to do anything more than simple flat file imports.

The 9i external table is cool, because data in external files is now part of your data 'world' and accessible to SQL sorting and searching - but you're never going to get the Oracle database engine performance until its in a real Oracle table.

For large-scale operations - a million records and upwards - some kind of GREP or VB File I/O and string-handling should be used to concatenate 'INSERT INTO' statements. These can be sent directly to the Database using an OracleInProcServer object, and you'll find it's an order of magnitude faster than the Oracle 8 Loader.

Here's an example:

Option Explicit

Public Function TransferCSV( _
CsvFileName As String, _
HostName As String, _
DbName As String, _
DbPassword As String, _
DestinationTable As String, _
ParamArray DestinationFields()) _
As Long



'Loads comma-separated data to an Oracle table.
'Works faster than JET text ISAM into an ODBC-Linked table
'And can load more than the Excel 65535 row limit

'Timed at 1200 records per second

'SAMPLE USAGE:

'lngRet = TransferCSV( _
' "D:\Program Files\Develop\LoadData.csv", _
' "**HOST**", "**USER**", "**PASSWORD**", _
' "tblPrice", _
' "PriceID", "StockID", "TradingDay", "ClosingPrice", "logChangeOnDay" _
' )

' DestinationFields can be a VBA array, a parameter set, or the boolean value
' TRUE denoting that the first line of the output file specifies the field names

'Author: Nigel Heffernan, Credit Lyonnais Securities Feb 2003
' Heffernann@YAH00.com

'Thanks to Denis Duchene for Oracle OO40 sample VBA code


Dim SQL As String
Dim SQLstub As String
Dim SQLdata As String

Dim iField As Integer
Dim lngProgress As Long
Dim lngFile As Long

Dim strData As String



Const ORADB_NO_REFETCH As Long = &H4&


TransferCSV = False


'Early binding code for performance: requires VBE reference to \Orant\Bin\OIP.tlb
Dim oraSession As OracleInProcServer.OraSessionClass
Dim oraDatabase As OracleInProcServer.oraDatabase
Set oraSession = New OracleInProcServer.OraSessionClass

'Late-Binding code for compatibility across all Oracle clients:
'Dim oraSession As Object
'Dim oraDatabase As Object
'Set oraSession = CreateObject("OracleInProcServer.XOraSession")





'VBA File I/O

lngFile = FreeFile
Open CsvFileName For Input As #lngFile

'SQL stub String-handling: the target is this type of SQL statement:
'SQL = "INSERT INTO tblPrice ( PriceID, StockID, TradingDay, ClosingPrice, logChangeOnDay) Values ( -2147483641, 1194, 36795, 310, -0.009631 )"

SQLstub = ""
SQLstub = SQLstub & "INSERT INTO " & DestinationTable & " ("

If InStr(TypeName(DestinationFields(0)), "(") > 0 Then
'Its an array of field Names

For iField = LBound(DestinationFields(0)) To UBound(DestinationFields(0))
SQLstub = SQLstub & " " & DestinationFields(0)(iField) & ","
Next iField

ElseIf IsNumeric(DestinationFields(0)) Then
'Use the first row of the file

If DestinationFields(0) = True Then

strData = ""
Line Input #lngFile, strData
strData = ReplaceString(strData, Chr(34), "")
strData = ReplaceString(strData, Chr(39), "")
strData = strData & ","
SQLstub = SQLstub & strData

End If

Else
'It's an element parameter list array

For iField = LBound(DestinationFields) To UBound(DestinationFields)
SQLstub = SQLstub & " " & DestinationFields(iField) & ","
Next iField

End If

'Trim trailing comma
SQLstub = Left(SQLstub, Len(SQLstub) - 1)
SQLstub = SQLstub & " ) Values ( "


Set oraDatabase = oraSession.OpenDatabase(HostName, DbName & "/" & DbPassword, ORADB_NO_REFETCH)
oraSession.BeginTrans

Do While Not EOF(lngFile)

SQL = ""
strData = ""

Line Input #lngFile, strData

SQL = SQLstub & strData & " )"

oraDatabase.ExecuteSQL SQL


lngProgress = lngProgress + 1

If lngProgress Mod 1000 = 0 Then

oraSession.CommitTrans
oraSession.BeginTrans

'***** Comment this out if your VB host app doesn't support .Statusbar
Application.StatusBar = "Loading to Oracle... " & lngProgress & " records"
'*****

End If

Loop 'EOF(lngFile)


ExitFunction:

Close #lngFile
oraSession.CommitTrans
oraDatabase.Close

Set oraSession = Nothing

TransferCSV = lngProgress


'**** Comment this out if your VB host app doesn't support .Statusbar
Application.StatusBar = "Ready ...Loaded " & lngProgress & " records to Oracle."
'**** MS-Access 97 code is syscmd acsyscmdsetstatus, "Ready ...Loaded " & lngProgress & " records to Oracle."


End Function


Public Function ReplaceString(InputString, SearchString, InsertString) As String
On Error Resume Next

'Replaces all instances of search string in input string with an insert string
'Regardless of relative lengths of search and input string

Dim i As Integer
Dim iPosition As Integer

Dim strTemp As String
Dim strOutput As String

strOutput = ""
strTemp = ""
iPosition = 1

If SearchString = InputString Then
ReplaceString = InsertString
Exit Function
End If

If InStr(InputString, SearchString) < 1 Then
ReplaceString = InputString
Exit Function
End If

If SearchString = "" Then
ReplaceString = InputString
Exit Function
End If

Do Until iPosition > VBA.Len(InputString)
i = InStr(iPosition, InputString, SearchString)
If i < 1 Then
strOutput = strOutput & VBA.Right(InputString, 1 + VBA.Len(InputString) - iPosition)
Exit Do
End If

strOutput = strOutput & VBA.Mid(InputString, iPosition, i - iPosition) & InsertString
iPosition = i + VBA.Len(SearchString)

Loop

If VBA.Len(strOutput) < 1 Then
strOutput = InputString
End If

ReplaceString = strOutput


End Function




Tom Kyte
June 30, 2003 - 1:33 pm UTC

i have to believe (hope anyway) that you are being tongue in cheek sarcastic?

Not that I can benchmark your code as it just refused to be compiled on open systems...

But, I see not a bind variable anywhere. Very scary, I wouldn't let that code near my database.

How this is easier then

Insert into T select * from external_table;


I just cannot fathom.

Nigel Heffernan - Benchmarks to prove it?

Kamal Kishore, June 30, 2003 - 2:42 pm UTC

<quote>
and you'll find it's an order of magnitude faster
than the Oracle 8 Loader.
</quote>


But, I do NOT see any benchmark test(s) to prove that this load mechanism is "magnitude" faster???

We would like to hear more from the poster on this statement before this can be taken any seriously or it should be kept aside as a slower and bulky alternative (I do not think this can be categorized as an alternative, but, for the sake of argument...).


To Nigel...pls take your VB stuff.....

Robert, June 30, 2003 - 2:42 pm UTC


>> Not bad, but there's better ways of doing this

Wa..ha..ha..ha...And then there you go entertaining us
with your bit of VB "magic" (got another word for it).

Well, chap you're not entirely hopeless, at least you're doing OO4O - but do it via st. procedure and use BV for crying out loud.

Tom is too nice a bloke to say this. So I will be bold & say: VB/Acce$$ stuff don't belong here.
Take it to the M$ newsgroups or put it where the sun don't shine.

We dig open source here.




Tom Kyte
June 30, 2003 - 2:52 pm UTC

I dig open systems.




Yeah, that's what i mean......sorry

Robert, June 30, 2003 - 3:37 pm UTC

>..I dig open systems.

Got a bit brainwashed by this GNU/FSF/"Open whatever"/"Peace on Earth"
these days...they all began to mean the same thing to me...



an order of magnitude faster

Nigel Heffernan, July 01, 2003 - 7:39 am UTC

Hmmm... I post an easy fix and I get grief from the 'open source' guys. Me, I think ease-of-use is more valuable than ideological purity. It's also the reason this board exists: nobody comes here because of problems with Oracle performance.

Anyway, in answer to the more serious questions on speed, here's the file, fields delimited by comma, records separated by Chr(10):

"PriceID","StockID","TradingDay","ClosingPrice","logChangeOnDay"
-2147483581,1027,33379,375.90,0.01
-2147483189,1834,35384,10.65,-.00292356
-2147480993,1299,36780,968.00,-0.01
-2147480914,505,36928,80.00,0.03
-2147480587,1021,36879,823.01,0.00120362

You'll need another 3,500,000 records to replicate the live operation and I have no plans to post them.

PriceID is a primary key, long integer; StockID is a foreign key, indexed, long int; TradingDay is long int, indexed; the other two fields are single-precision floats.

I had to do this four times: the Oracle 8 loader, when I finally got it working, took 3:35 then 4:10 hours to load it all. Two VB runs took 44 and 46 minutes.

...On NT 4.0. I suspect it would be faster still on any UNIX system, as the file i/o functions are better than anything available in VB and NT.

Viewed purely from an Oracle perspective, maybe my code doesn't belong here. Viewed from the perspective of a developer who works with a mix of RDBMS systems, and is constantly asked to deal with file imports from legacy systems, the answer is: what works, works.

People come here for easy fixes, not for lectures on the evils of MS applications.

I've uploaded similar files to an IBM mainframe, a proprietary trading system and NT SQL Server databases. I used the same VB script (albeit with ADO, which is slower and less stable than OO40) and it ran faster than the native text-file import drivers two of those databases, too.

Given the headaches I get dealing with all those proprietary systems, I might have a complete sense-of-humour failure when told how much people in academia and single-vendor environments dig 'Open Source' solutions. The real world is locked into a balkanised mess, all of us lowly programmers would like the authority and the budget to replace it, and you can't get there from here, even by digging.


Tom Kyte
July 01, 2003 - 9:12 am UTC

people don't come here for performance? kidding right.

anyway - an order of magnitude is 10x, but the math using your numbers is 5x - NOT that sqlldr would ever take that long -- not even close.

I will say this -- you messed something up hugely in your test. I've loaded millions of records using sqlldr (in a similar format) and it takes MINUTES, not hours.

Minutes -- not hours. Really, minutes.

To me, the 44minute VB run is way too slow (about 2 orders of magnitude too slow). That should not take more then a couple of minutes to load up. (and since vb doesn't run on unix, it would be hard to "test")


You did something seriously big time *wrong*, I would find 44minutes to be about 42 minutes too long. Here we are -- on a small machine (my desktop PC -- intel based, IDE drives, running RedHat Linux). I created a flat file:


tkyte@tkyte-pc Desktop]$ head test.dat
1,17602,17602,17602,17602
2,7516,7516,7516,7516
3,23098,23098,23098,23098
4,13926,13926,13926,13926
5,22663,22663,22663,22663
6,10130,10130,10130,10130

loaded it:

[tkyte@tkyte-pc Desktop]$ sqlldr / test data=test.dat direct=y


and the timing is

Table V:
3817192 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.

Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 3817192
Total logical records rejected: 0
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 799
Total stream buffers loaded by SQL*Loader load thread: 0

Run began on Tue Jul 01 08:27:37 2003
Run ended on Tue Jul 01 08:30:00 2003

Elapsed time was: 00:02:22.17
CPU time was: 00:00:10.19

The table V was:

ps$tkyte@ORA920> @desc v
Name Null? Type
------------------------------------ -------- -------------------------
C1 NOT NULL NUMBER
C2 NUMBER
C3 NUMBER
C4 NUMBER
C5 NUMBER

Indexes on v

Index Is
Name Unique COLUMNS
------------------------------ ------ --------------------------------
V_IDX1 No C3
V_IDX2 No C5
SYS_C005924 Yes C1


So, very similar to your setup (since numbers are numbers are numbers in Oracle -- everything is a NUMBER)

4 hours -- not a chance, not a chance at all. Even if I go with the slower conventional path load (which one would not really do in the real world), the times are no where near 45 minutes.



and it is about open SYSTEMS, not source really. Your VB implementation is something I would avoid like the plague as I cannot use it anywhere (really -- I could not)


I live in the real world -- but in my real world, that sqlldr would take 4 hours to load a measely 3.5 million records would be a huge red flag that "I've done something horribly wrong" -- not motivation to write code that can only be slower.


I'm not even sure your machine could parse 3.5 million UNIQUE sql statements in 44minutes -- and if it did, 99% of your run time was spent KILLING the server -- forcing it to hard parse like that.

Single row inserts
No binds
Ugh

Other part of the load?

Kamal Kishore, July 01, 2003 - 9:23 am UTC

Maybe he can post for all of us to see as to how the alternative load approach (that took 4 Hours!!!) was actually performed.

We would like to see that approach as an example to be able to take the posted run times differences seriously.


I like to see the DBA's face...really....

Robert, July 01, 2003 - 5:13 pm UTC

>>......3.5 Mil records
>> Single row inserts
>> No binds
>> Ugh

Nigel, does (any of) your DBA has ANY CLUE that you're doing this ??? Very curious...reall
Maybe you should show & impress him/her like you're trying to convince us "unbelivers".
Go knock her socks off ! :)




Humble pie...

Nigel Heffernan, July 02, 2003 - 8:55 am UTC

> but in my real world, that sqlldr would take 4 hours
to load a measely 3.5 million records would be a huge red flag that "I've done something horribly wrong"

I work to a different benchmark: it works and its faster than MS-Access, so I must be doing something right.

(I look forward to reading the responses to that)

On a more serious note, what are the most common errors in importing data into Oracle?

All replies appreciated


Nigel Heffernan



And finally, a sour note:

> I'm not even sure your machine could parse 3.5 million UNIQUE sql statements in 44minutes -

I doubt it even raised a sweat. Companies nowadays - especially banks - spend a fortune on high-performance hardware because its cheaper than efficient code. Until it eventually breaks down...

...Which is why there are so many MS-Access systems out there, fine for the dozen users they were designed for, until until you're told the extra memory they bought isn't working and discover 50 to 100 users on three continents generating data volumes in the millions, rather than thousands of records that were originally anticipated.

And that is a matter of commercial interest to Oracle Corporation.

Nowadays more systems are upsized than designed from scratch, and M$ made it almost a single-click operation to move the back end of an Access system up to SQL Server. Which is unfortunate, because a well-designed Access system can act as the client to any server and performs significantly better with Oracle.

Let's face it, the task isn't made easy for us. Years ago I found Sybase a lot more helpful. The learning curve is very steep and every VB or Access developer who do tries to work out how to do it is left wondering whether anybody, anywhere, has ever upsized to Oracle before.

Most database administrators have even less knowledge of client design than us front-end developers have of Oracle coding. The constant complaint is: "Oracle people live in the server room" and you're lucky if they'll build the tables for you. I've worked in two companies where the DBAs flatly deny that 'parameter queries' - functions returning a cursor - can be implemented in Oracle and I suspect that those who *do* know how its done are unaware of the importance of parameter queries in small desktop databases.

We'll ignore the vocal minority who are resolutely 'open source' and disdain all effluvia from Redmond - which goes down a treat when most developers have to write the client apps in VB and run it on NT.



Why complain?

I hate reinventing the wheel. I particularly hate discovering, months later, that square wheels worked great for sliding things downhill but now reveal fundamental conceptual flaws. It's all very well saying: "read the introductory manuals again, bozo" but I am beginning to suspect that there are no part-time Oracle developers, only dedicated experts.

I'll be spending most of next year separating out the back-end and front-end operations of Access databases, turning them into proper client-server applications. Plus a set of Excel spreadsheets that act as front ends to MS-Access 'servers' that are approaching the golden gigabyte that marks complete collapse.

The current situation is that I can get dedicated servers but not an Oracle developer or a training budget: hardware 'fixes' really are that cheap. Luckily the benchmark is that if it works and runs faster than Access, nobody's going to complain. Least of all the DBA, given the workload they get.

Lucky too, that Oracle's a lot more stable, even under adverse conditions, than its next-biggest rival.

Lucky for you, too, that you won't read many gripes like this: most VB developers just give up on Oracle and choose an intellectually-undemanding solution.


creating external sequence not right?

David, July 09, 2003 - 12:35 pm UTC

Hi Tom,

Following your example given, I was trying to practice this by myself and there are some problems that I encountered. I am not sure if your answer really was what you did.

1. creating external_table before creating emp_view will not load emp.dat data into the external table.

2. The host flat / emp_view > /tmp/emp.dat generated file is a tab diliminated file rather than a comma diliminated file as it showed in your create external_table syntax. So when create external_table the data will not be loaded because the flat generate data file is not compabible as your FIELDS DILIMINATED BY ',' syntax

It took me quite a bit time to figure out the correct sequence and get the correct results. So here is my question

Was that the exact sequence and syntax in your responds for the question?

Best Regards,

Tom Kyte
July 09, 2003 - 1:39 pm UTC

that was a straight cut and paste from a sqlplus session, it is exactly what I did.

1) that is correct but so? I didn't query the external table until after I filled up the file.

2) the flat I used myself:

[tkyte@tkyte-pc Desktop]$ flat scott/tiger emp
7369,SMITH,CLERK,7902,17-DEC-80,800,,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10
7788,SCOTT,ANALYST,7566,09-DEC-82,3000,,20
7839,KING,PRESIDENT,,17-NOV-81,5000,,10
7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30
7876,ADAMS,CLERK,7788,12-JAN-83,1100,,20
7900,JAMES,CLERK,7698,03-DEC-81,950,,30
7902,FORD,ANALYST,7566,03-DEC-81,3000,,20
7934,MILLER,CLERK,7782,23-JAN-82,1300,,10


uses commas. the one on the download site does use tabs, you are correct -- sorry about that.



All you needed was a ',' delimited file. sorry about that, the sequence of steps however, is correct.

Thanks Tom

David, July 09, 2003 - 5:43 pm UTC

Thanks Tom for clarifying my concern. Now I understand that we don't have to have the emp.dat data ready when we create external_table.

External tables and EBCDIC

Adrian, July 10, 2003 - 5:11 am UTC

Tom

Is there any way of using external tables with EBCDIC files? Our database is ASCII-7. Furthermore, the files are littered with COMP-3 (packed decimal) fields. So far I've tried the "dd" UNIX command to convert to ASCII but the COMP-3's get screwed. Our source system is outsourced so to get them to batch to ASCII rather than EBCDIC is a non-starter (just getting them to make a coffee would cost us hundreds of thousands, be 9 months late and be wrong anyway).

Any assistance you could offer on this would be invaluable...

Tom Kyte
July 10, 2003 - 9:57 am UTC

it might look something like this:

CREATE table....
(
X NUMBER,
Y VARCHAR2(25),
Z DATE
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY ....
ACCESS PARAMETERS
(
RECORDS FIXED 80 CHARACTERSET WE8EBCDIC500
READSIZE 1048576
FIELDS LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
....
)
)
location
(
'test.dat'
)
)

if you have that character set installed...

Couldn't find that WE8EBCDIC500 anywhere...

Adrian Billington, July 10, 2003 - 12:05 pm UTC

Tom thanks. Used that WE8EBCDIC500 in SQL*Loader all over the place in my last job but couldn't find the charset name anywhere on the web or in the docs at tahiti/otn. I knew it wasn't simply "EBCDIC" but just couldn't crack it. Thanks again.

External Table Load - From a file on a different Host

HB, September 09, 2003 - 9:57 am UTC

Hi Tom,
Your explanation was super. I have one simple question, - can we use ET to load data from a file that is on different machine? If answer is "no" can this is done in future Oracle release.
We currently use SQL*Loader to load data from a VMS machine to Oracle server on Windows 2000 and would like to use ET?
FTping the files would not be an option as files are big and have do every time there is data change in the base file.
Thanks


Tom Kyte
September 09, 2003 - 11:53 am UTC

the file sort of needs to be ACCESIBLE to the server -- think about it, how can Oracle reach out and grab a file from "just anywhere"???

the files need to be moved over the network regardless right -- (yes, rhetorical question). whether you do that via FTP or via an nfs mount or a samba share -- doesn't matter. they'll be going over the network regardless.

Why isn't it working for me ?

Sonali, October 24, 2003 - 11:32 am UTC

Do I need any init.ora parameters ? Am I missing something ?
I have a tab delimited text file and here is the script I wrote after reading this ...

Connect system @emerald;
create directory external_table_dir as 'd:\dumps\test';
grant read, write on directory external_table_dir to sak;
connect sak@emerald;

Create table test_mweborg
(
ORG_ID NUMBER ,
ORG_ITEM_CODE VARCHAR2(15),
ORG_DESCRIPTION VARCHAR2(80),
ORG_LEVEL NUMBER,
ORG_COMPANY_NUMBER NUMBER,
ORG_PAR1 NUMBER,
ORG_PAR2 NUMBER,
ORG_PAR3 NUMBER,
ORG_PAR4 NUMBER,
ORG_PAR5 NUMBER,
ORG_PAR6 NUMBER,
ORG_PAR7 NUMBER,
ORG_PAR8 NUMBER,
ORG_PAR9 NUMBER,
ORG_PAR_ITEM_CODE1 VARCHAR2(15),
ORG_PAR_ITEM_CODE2 VARCHAR2(15),
ORG_PAR_ITEM_CODE3 VARCHAR2(15),
ORG_PAR_ITEM_CODE4 VARCHAR2(15),
ORG_PAR_ITEM_CODE5 VARCHAR2(15),
ORG_PAR_ITEM_CODE6 VARCHAR2(15),
ORG_PAR_ITEM_CODE7 VARCHAR2(15),
ORG_PAR_ITEM_CODE8 VARCHAR2(15),
ORG_PAR_ITEM_CODE9 VARCHAR2(15),
ORG_STATUS NUMBER,
ORG_CREATE_DATE DATE,
ORG_EFFECTIVE_DATE DATE,
ORG_MODIFY_DATE DATE,
ORG_MANAGER NUMBER,
ORG_HISTORY_ID NUMBER
)
organization external
(
type oracle_loader
default directory external_table_dir
access parameters
(
records delimited by newline
skip 1
fields terminated by 0x'09'
lrtrim

)
location ('test_revised_tab.txt')
)
/
I get this error why ?
SQLWKS> select count(*) from test_mweborg
2>
COUNT(*)
----------
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file TEST_MWEBORG_1376_3900.log
OS error The data is invalid.
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1

Thanks

Tom Kyte
October 24, 2003 - 11:38 am UTC

is d:\dumps\test

a) on the windows server the database runs on
b) accessible to oracle itself

at the very least, it seems that the directory is not writable at the OS level for Oracle.

forgot to mention we have 9.2.0.1.0 version

sonali, October 24, 2003 - 11:33 am UTC


Now I get this error

sonali, October 24, 2003 - 11:43 am UTC

SQLWKS> create or replace view v
2> as
3> select * from test_mweborg;
Statement processed.
SQLWKS>
SQLWKS> select * from v where rownuM<2;
ORG_ID ORG_ITEM_CODE ORG_DESCRIPTION ORG_LEVEL ORG_COMPAN ORG_PAR1 ORG_PAR2 ORG_PAR3 ORG_PAR4 ORG_PAR5 ORG_PAR6 ORG_PAR7 ORG_PAR8 ORG_PAR9 ORG_PAR_ITEM_CO ORG_PAR_ITEM_CO ORG_PAR_ITEM_CO ORG_PAR_ITEM_CO ORG_PAR_ITEM_CO ORG_PAR_ITEM_CO ORG_PAR_ITEM_CO ORG_PAR_ITEM_CO ORG_PAR_ITEM_CO ORG_STATUS ORG_CREATE_DATE ORG_EFFECTIVE_DATE ORG_MODIFY_DATE ORG_MANAGE ORG_HISTOR
---------- --------------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- ---------- -------------------- -------------------- -------------------- ---------- ----------
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1

I figured out the other error, but then I hit
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1
this error, so I created view like you said, but still can't select from that view...Sorry about the ignorence, but trying this for the 1st time.


Tom Kyte
October 24, 2003 - 12:27 pm UTC

check out your logs -- by default they are generated into the same directory.

you probably have some bad dates or numbers in there.

I like to start with all VARCHAR2 columns just to see whats where and then add the "real" types in after the fact.

Thanks..

sonali, October 24, 2003 - 12:50 pm UTC

I think you are right, it has to with data, the thing is I can't figure out what it is ??

drop table test_mweborg;
Create table test_mweborg
(
ORG_ID varchar2(10) ,
ORG_ITEM_CODE VARCHAR2(15),
ORG_DESCRIPTION VARCHAR2(80),
ORG_LEVEL varchar2(10),
ORG_COMPANY_NUMBER varchar2(10),
ORG_PAR1 varchar2(10),
ORG_PAR2 varchar2(10),
ORG_PAR3 varchar2(10),
ORG_PAR4 varchar2(10),
ORG_PAR5 varchar2(10),
ORG_PAR6 varchar2(10),
ORG_PAR7 varchar2(10),
ORG_PAR8 varchar2(10),
ORG_PAR9 varchar2(10),
ORG_PAR_ITEM_CODE1 VARCHAR2(15),
ORG_PAR_ITEM_CODE2 VARCHAR2(15),
ORG_PAR_ITEM_CODE3 VARCHAR2(15),
ORG_PAR_ITEM_CODE5 VARCHAR2(15),
ORG_PAR_ITEM_CODE6 VARCHAR2(15),
ORG_PAR_ITEM_CODE7 VARCHAR2(15),
ORG_PAR_ITEM_CODE8 VARCHAR2(15),
ORG_PAR_ITEM_CODE9 VARCHAR2(15),
ORG_STATUS varchar2(10),
ORG_MANAGER varchar2(10),
ORG_HISTORY_ID varchar2(10)
)
organization external
(
type oracle_loader
default directory external_table_dir
access parameters
(
records delimited by newline
skip 1
fields terminated by 0x'09'
missing field values are null
(ORG_ITEM_CODE VARCHAR2(15),
ORG_DESCRIPTION VARCHAR2(80),
ORG_LEVEL varchar2(10),
ORG_COMPANY_NUMBER varchar2(10),
ORG_PAR1 varchar2(10),
ORG_PAR2 varchar2(10),
ORG_PAR3 varchar2(10),
ORG_PAR4 varchar2(10),
ORG_PAR5 varchar2(10),
ORG_PAR6 varchar2(10),
ORG_PAR7 varchar2(10),
ORG_PAR8 varchar2(10),
ORG_PAR9 varchar2(10),
ORG_PAR_ITEM_CODE1 VARCHAR2(15),
ORG_PAR_ITEM_CODE2 VARCHAR2(15),
ORG_PAR_ITEM_CODE3 VARCHAR2(15),
ORG_PAR_ITEM_CODE5 VARCHAR2(15),
ORG_PAR_ITEM_CODE6 VARCHAR2(15),
ORG_PAR_ITEM_CODE7 VARCHAR2(15),
ORG_PAR_ITEM_CODE8 VARCHAR2(15),
ORG_PAR_ITEM_CODE9 VARCHAR2(15),
ORG_STATUS varchar2(10),
ORG_MANAGER varchar2(10),
ORG_HISTORY_ID varchar2(10)
)


)
location ('test_revised_tab.txt')
)
/


I changed all the numbers to varchar as you suggested.. I keep getting this error now, which does not generate logs anymore
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "identifier": expecting one of: "comma, char, date, defaultif, decimal, double, float, integer, (, nullif, oracle_date, oracle_number, position, raw, recnum, ), unsigned, varrawc, varchar, varraw, varcharc, zoned"
KUP-01008: the bad identifier was: varchar2
KUP-01007: at line 6 column 35
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1


It was generating the logs before, till I changed all NULL ( spelled out words) in the text file to nothing doing find and replace, because number datatype did not like NULL (word)..
How do I make it work ? What am I missing now ?

Thanks
Sonali

Tom Kyte
October 24, 2003 - 12:59 pm UTC

don't use varchar2 down at the bottom -- in fact, don't use anything, just list the column names.

There is a very easy way to use External Table--Read Tom Book

Ajeet, October 24, 2003 - 9:40 pm UTC

Guys,
If you have used SQLLOADER earlier --Tom has made External table very simple for you --in his latest book , he has told how to convert sqlloader script to external table..as we all have been using sqlloader since long--just use that commnad...
it is something like
sqlldr userid/password name.ctl external_table=generate_only...
and you are done..it tells you allmost all you need to know.Also you don;t have to worry about data types etc..

Just a thought..however.I felt it alot easir than writing a script for External tables specially if you have file having alot of fields in it...

Thanks

Very good example to help us to understand

Pauline, October 25, 2003 - 12:05 am UTC

Tom,
In order to follow your sample to practice create external
table, I use one of our 9i database to test. Also in order 
to create external user, I edit the pfile and then shutdown
db, create spfile to pfile.

The exact things I did :
 <dbdev1:dev3:/appl/oracle/product/9.2.0.1.0/dbs> export ORACLE_SID=gp9i
 <dbdev1:gp9i:/appl/oracle/product/9.2.0.1.0/dbs> sqlplus '/ as sysdba'
 
 SQL*Plus: Release 9.2.0.4.0 - Production on Fri Oct 24 15:29:50 2003
 
 Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
 Connected to an idle instance.
 
 SQL> startup
 ORACLE instance started.
 
 Total System Global Area  320309648 bytes
 Fixed Size                   731536 bytes
 Variable Size             285212672 bytes
 Database Buffers           33554432 bytes
 Redo Buffers                 811008 bytes
 Database mounted.
 Database opened.
 SQL> select name from v$database;
 
 NAME
 ---------
 GP9I
 
SQL> create or replace directory data_dir as '/tmp/';
 Directory created.
 
 SQL> create table external_table
      (EMPNO NUMBER(4) ,
      ENAME VARCHAR2(10),
      JOB VARCHAR2(9),
      MGR NUMBER(4),
      HIREDATE DATE,
      SAL NUMBER(7, 2),
      COMM NUMBER(7, 2),
      DEPTNO NUMBER(2)
    )
    ORGANIZATION EXTERNAL
    ( type oracle_loader
      default directory data_dir
      access parameters
      ( fields terminated by ',' )
      location ('emp.dat')
    );
    
 
 Table created.
 
 
 SQL> sho parameter os_authent_prefix
 
 NAME                                 TYPE        VALUE
 ------------------------------------ -----------
 ------------------------------
 os_authent_prefix                    string      ops$
 
 Now I want to change the parameter for os_authent_prefix and then create
 an ORACLE external user, so I do
 
 SQL> shutdown immediate;
 Database closed.
 Database dismounted.
 ORACLE instance shut down.
 
 At this moment, I see:
 
 <dbdev1:dev3:/appl/oracle/product/9.2.0.1.0/dbs> ls -l *gp9i*
 -rw-r--r--   1 oracle   dba         1024 Sep 12 10:40 initgp9i.ora
 -rwSr-----   1 oracle   dba         1536 Oct 24 15:33 orapwgp9i
 -rw-r-----   1 oracle   dba         2560 Jul 16 19:08 spfilegp9i.ora
 
 Then I edit  initgp9i.ora to add one more line below.

 <dbdev1:dev3:/appl/oracle/product/9.2.0.1.0/dbs> vi initgp9i.ora
 
 *.os_authent_prefix=""
 
 SQL> create spfile from pfile;
 
 File created.
 
 <dbdev1:dev3:/appl/oracle/product/9.2.0.1.0/dbs> ls -l *gp9i*
 -rw-r--r--   1 oracle   dba         1047 Oct 24 16:11 initgp9i.ora
 -rwSr-----   1 oracle   dba         1536 Oct 24 15:33 orapwgp9i
 -rw-r-----   1 oracle   dba         3584 Oct 24 16:12 spfilegp9i.ora
 
SQL> startup
 ORACLE instance started.
 ORA-24324: service handle not initialized
 ORA-24323: value not allowed
 ORA-03113: end-of-file on communication channel
 
SQL> shutdown abort
 ORA-24324: service handle not initialized
 ORA-01041: internal error. hostdef extension doesn't exist
 
 SQL> create pfile from spfile;
 create pfile from spfile
 *
 ERROR at line 1:
 ORA-03114: not connected to ORACLE
 

 SQL> conn / as sysdba
 ERROR:
 ORA-03113: end-of-file on communication channel

Now I run into big problem. I can't conn sys to start or shutdown database. The db is generating gp9i_pmon_N.trc (800k size) every 3-4 minutes and core dump is generating every 5 minutes.

The content for gp9i_pmon_N.trc is something like:

Fri Oct 24 16:38:47 2003
Errors in file /appl/oracle/admin/gp9i/bdump/gp9i_pmon_25647.trc:
ORA-00445: background process "QMN0" did not start after 120 seconds
Fri Oct 24 16:41:56 2003
Restarting dead background process QMN0


By read oracle trace file and see:

/appl/oracle/admin/gp9i/udump/gp9i_ora_6276.trc
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
ORACLE_HOME = /appl/oracle/product/9.2.0.1.0
System name:    SunOS
Node name:      dbdev1
Release:        5.8
Version:        Generic_108528-13
Machine:        sun4u
Instance name: gp9i
Redo thread mounted by this instance: 0 <none>
Oracle process number: 0
6276

Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0x64, PC: [0x1027b25b0, 00000001027B25B0]
*** 2003-10-24 17:39:25.526
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [00000001027B25B0] [SIGSEGV] [Address not mapped to object] [0x000000064] [] []
Current SQL information unavailable - no session.

Now I only can guess the another DBA created this instance from 9.2.0.1.0. But she moved all other instances to 9.2.0.4.0 and this instance uses spfilegp9i.ora in same $ORACLE_HOME/dbs with other instance and same environment variables for 9.2.0.4.0 software. So the instance first starup without problem. Once create spfile from pfile,
the problem happened.

Please help how to stop this instance running. Now I could
not connect sys as sysdba to shutdown. So many trace and core dump files will fill up file system soon.

As always, thanks for your help.

Pauline
 

Tom Kyte
October 25, 2003 - 8:09 am UTC

just exit sqlplus, go back in, shutdown abort, exit sqlplus and startup.

it'll be fine.


you did do this "the hard way"

ops$tkyte@ORA920> show parameter os
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_cost_adj             integer     100
os_authent_prefix                    string      ops$
....

   
ops$tkyte@ORA920> alter system set os_authent_prefix='' scope=spfile;
System altered.

sys@ORA920> startup force
ORACLE instance started.
 
Total System Global Area  190386792 bytes
Fixed Size                   451176 bytes
Variable Size             117440512 bytes
Database Buffers           67108864 bytes
Redo Buffers                5386240 bytes
Database mounted.
Database opened.
sys@ORA920>

 

Very good example to help us to understand

Pauline, October 25, 2003 - 12:24 am UTC

Tom,
In order to follow your sample to practice create external
table, I use one of our 9i database to test. Also in order 
to create external user, I edit the pfile and then shutdown
db, create spfile to pfile.

The exact things I did :
 <dbdev1:dev3:/appl/oracle/product/9.2.0.1.0/dbs> export ORACLE_SID=gp9i
 <dbdev1:gp9i:/appl/oracle/product/9.2.0.1.0/dbs> sqlplus '/ as sysdba'
 
 SQL*Plus: Release 9.2.0.4.0 - Production on Fri Oct 24 15:29:50 2003
 
 Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
 Connected to an idle instance.
 
 SQL> startup
 ORACLE instance started.
 
 Total System Global Area  320309648 bytes
 Fixed Size                   731536 bytes
 Variable Size             285212672 bytes
 Database Buffers           33554432 bytes
 Redo Buffers                 811008 bytes
 Database mounted.
 Database opened.
 SQL> select name from v$database;
 
 NAME
 ---------
 GP9I
 
SQL> create or replace directory data_dir as '/tmp/';
 Directory created.
 
 SQL> create table external_table
      (EMPNO NUMBER(4) ,
      ENAME VARCHAR2(10),
      JOB VARCHAR2(9),
      MGR NUMBER(4),
      HIREDATE DATE,
      SAL NUMBER(7, 2),
      COMM NUMBER(7, 2),
      DEPTNO NUMBER(2)
    )
    ORGANIZATION EXTERNAL
    ( type oracle_loader
      default directory data_dir
      access parameters
      ( fields terminated by ',' )
      location ('emp.dat')
    );
    
 
 Table created.
 
 
 SQL> sho parameter os_authent_prefix
 
 NAME                                 TYPE        VALUE
 ------------------------------------ -----------
 ------------------------------
 os_authent_prefix                    string      ops$
 
 Now I want to change the parameter for os_authent_prefix and then create
 an ORACLE external user, so I do
 
 SQL> shutdown immediate;
 Database closed.
 Database dismounted.
 ORACLE instance shut down.
 
 At this moment, I see:
 
 <dbdev1:dev3:/appl/oracle/product/9.2.0.1.0/dbs> ls -l *gp9i*
 -rw-r--r--   1 oracle   dba         1024 Sep 12 10:40 initgp9i.ora
 -rwSr-----   1 oracle   dba         1536 Oct 24 15:33 orapwgp9i
 -rw-r-----   1 oracle   dba         2560 Jul 16 19:08 spfilegp9i.ora
 
 Then I edit  initgp9i.ora to add one more line below.

 <dbdev1:dev3:/appl/oracle/product/9.2.0.1.0/dbs> vi initgp9i.ora
 
 *.os_authent_prefix=""
 
 SQL> create spfile from pfile;
 
 File created.
 
 <dbdev1:dev3:/appl/oracle/product/9.2.0.1.0/dbs> ls -l *gp9i*
 -rw-r--r--   1 oracle   dba         1047 Oct 24 16:11 initgp9i.ora
 -rwSr-----   1 oracle   dba         1536 Oct 24 15:33 orapwgp9i
 -rw-r-----   1 oracle   dba         3584 Oct 24 16:12 spfilegp9i.ora
 
SQL> startup
 ORACLE instance started.
 ORA-24324: service handle not initialized
 ORA-24323: value not allowed
 ORA-03113: end-of-file on communication channel
 
SQL> shutdown abort
 ORA-24324: service handle not initialized
 ORA-01041: internal error. hostdef extension doesn't exist
 
 SQL> create pfile from spfile;
 create pfile from spfile
 *
 ERROR at line 1:
 ORA-03114: not connected to ORACLE
 

 SQL> conn / as sysdba
 ERROR:
 ORA-03113: end-of-file on communication channel

Now I run into big problem. I can't conn sys to start or shutdown database. The db is generating gp9i_pmon_N.trc (800k size) every 3-4 minutes and core dump is generating every 5 minutes.

The content for gp9i_pmon_N.trc is something like:

Fri Oct 24 16:38:47 2003
Errors in file /appl/oracle/admin/gp9i/bdump/gp9i_pmon_25647.trc:
ORA-00445: background process "QMN0" did not start after 120 seconds
Fri Oct 24 16:41:56 2003
Restarting dead background process QMN0


By read oracle trace file and see:

/appl/oracle/admin/gp9i/udump/gp9i_ora_6276.trc
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
ORACLE_HOME = /appl/oracle/product/9.2.0.1.0
System name:    SunOS
Node name:      dbdev1
Release:        5.8
Version:        Generic_108528-13
Machine:        sun4u
Instance name: gp9i
Redo thread mounted by this instance: 0 <none>
Oracle process number: 0
6276

Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0x64, PC: [0x1027b25b0, 00000001027B25B0]
*** 2003-10-24 17:39:25.526
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [00000001027B25B0] [SIGSEGV] [Address not mapped to object] [0x000000064] [] []
Current SQL information unavailable - no session.

Now I only can guess the another DBA created this instance from 9.2.0.1.0. But she moved all other instances to 9.2.0.4.0 and this instance uses spfilegp9i.ora in same $ORACLE_HOME/dbs with other instance and same environment variables for 9.2.0.4.0 software. So the instance first starup without problem. Once create spfile from pfile,
the problem happened.

Please help how to stop this instance running. Now I could
not connect sys as sysdba to shutdown. So many trace and core dump files will fill up file system soon.

As always, thanks for your help.

Pauline
 

Pauline, October 25, 2003 - 9:54 am UTC

Tom,
Thanks very much for the response.
Now if I can conn sys in sqlplus then I can do what you said . But now the problem is I can't connect sys in sqlplus.
<dbdev1:dev3:/appl/oracle/admin/gp9i/bdump> export ORACLE_SID=gp9i
<dbdev1:gp9i:/appl/oracle/admin/gp9i/bdump> sqlplus /nolog

SQL*Plus: Release 9.2.0.4.0 - Production on Sat Oct 25 09:44:14 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> sho user
USER is ""
SQL> conn / as sysdba
ERROR:
ORA-01041: internal error. hostdef extension doesn't exist

This instance generate gp9i_qmn0_N.trc per 5 minutes with 800k and core dump per 5 minutes.

I have no way to stop this instance inside oracle. Can I kill all processes for this instance on O/S level? We may recreate it later.

Please help me to stop it.

Thank you again.
 

Tom Kyte
October 25, 2003 - 10:08 am UTC

i see a mixture of 9204 and 9201 here -- whats going on with that? are you using the RIGHT software here? I'm thinking "no" maybe.

verify your path, make sure your ORACLE_HOME is set to the 9204 code tree. Make sure your PATH references that as well.

Yes, you can kill it -- but you've done something wacky with your environments here -- I believe you are mixing 9201 and 9204 code together with bad results.

Tablespace for External Tables

A reader, October 25, 2003 - 10:45 am UTC

Tom:
I have seen that external tables are being created in system tablespace.Can we specify a tablespace for external tables?

Thanks
Ajeet

Tom Kyte
October 25, 2003 - 11:47 am UTC

umm, they consume no space IN the database whatsoever (they are external)

They don't "have" a tablespace really. The have no storage inside at all.

But no, you cannot -- but it is 1000% OK since they do not really consume any storage, anywhere inside of the database

Tom's insight is always helpful

Pauline, October 25, 2003 - 9:11 pm UTC

Tom,
Thanks for your continuing response over the weekend. The another DBA created this instance when install 9.2.0.1.0.
The $ORACLE_HOME is /appl/oracle/product/9.2.0.1.0/dbs.
Then used same $ORACLE_HOME to install 9.2.0.4.0. and upgraded other instances from 8i to 9.2.0.4.0. except left this one with 9.2.0.1.0 software. So its spfile, pfile all sitting in /appl/oracle/product/9.2.0.1.0/dbs which is really pointing to 9.2.0.4.0 software. That is why it used mixture code from 9.2.0.1 and 9.2.0.4 and once we touched
spfile, we ran into trouble. Now I only can stop it by kill -9 for all processes of this instance.

Thanks very much for your help.

One BIG difference is missed

Olesksandr Alesinskyy, October 26, 2003 - 9:17 am UTC

Ther IS one really big difference between SQL*Loader abd external tables - with SQL*Loader files with data may be loaded to a DB directly from client but to be used as exteranal tables they have to reside on the server (or on network share, mounted on the server).

Import Just Data

Fred, November 04, 2003 - 8:39 pm UTC

As merely a data loader, how does import stack up against external tables? Or is this comparing apples and oranges?

Tom Kyte
November 05, 2003 - 8:06 am UTC

apples to toaster ovens.


import can only process a dmp file created by export.

external tables can handle regular old fashioned flat files (but not an export dmp file in 9i and before)

External tables and EBCDIC

David Piazza, December 18, 2003 - 5:40 pm UTC

I have a file transferred from a db2 db on the mainframe.  The transfer was selected as ASCII.  The data file is as follows: 
212^ASTANDARD
I created an external table with:
drop table test_load;
CREATE TABLE test_load
 (ZIP3_CODE          VARCHAR2(3)
 ,FAC_ID             INTEGER
 ,BOX_TYPE_CODE      VARCHAR2(10))
  ORGANIZATION EXTERNAL 
  (
    TYPE ORACLE_LOADER 
    DEFAULT DIRECTORY test_etl
    ACCESS PARAMETERS 
   ( records delimited by newline 
     CHARACTERSET WE8EBCDIC500
     FIELDS LDRTRIM
     missing field values are null
 (ZIP3_CODE          CHAR(3)
 ,FAC_ID             CHAR(1)
 ,BOX_TYPE_CODE      CHAR(10)))
                          LOCATION ('TEST.DAT')) nologging;

The table created sucessfully, but when I select from it, I get:
SQL> select * from test_load;
select * from test_load
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1

The log file says:
error processing column FAC_ID in row 1 for datafile /data/TEST.DAT ORA-01722: invalid number

What did I do wrong??
 

Tom Kyte
December 18, 2003 - 6:25 pm UTC

the data isn't in ebcdic but your control file says it is?

also "^" doesn't seem to be a number?

Loading Numbers

Martin Guillen, January 15, 2004 - 2:53 pm UTC

Tom:
I'm trying to load some data in the db from a flat file.
I've done some tests:

Test 1:
ordbas01 > connect load_test/load_test;
Connected.
ordbas01 > create table t (a number);

Table created.

oracle@martin:/opt/oracle/dba/load_test> cat data.dat
3,640

Here ',' is the decimal separator (the character used in spanish languages)

oracle@martin:/opt/oracle/dba/load_test> cat load_test.ctl
load data
infile '/opt/oracle/dba/data.dat'
into table t
fields terminated by whitespace
(
a
)

oracle@martin:/opt/oracle/dba/load_test> sqlldr load_test/load_test control=load_test.ctl

SQL*Loader: Release 9.2.0.1.0 - Production on Thu Jan 15 12:44:55 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Commit point reached - logical record count 1

And from the sqlldr log:

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
A FIRST * WHT CHARACTER

Record 1: Rejected - Error on table T, column A.
ORA-01722: invalid number


Table T:
0 Rows successfully loaded.
1 Row 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.

So:

oracle@martin:/opt/oracle/dba/load_test> export NLS_NUMERIC_CHARACTERS=',.'
oracle@martin:/opt/oracle/dba/load_test> sqlldr load_test/load_test control=load_test.ctl

SQL*Loader: Release 9.2.0.1.0 - Production on Thu Jan 15 12:45:48 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Commit point reached - logical record count 1

And now the log says:

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
A FIRST * WHT CHARACTER


Table T:
1 Row 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.

ordbas01 > select * from t;

A
----------
3,64


Test 2:

ordbas01 > l
1 create table t_ext
2 (
3 a number
4 )
5 organization external
6 (
7 type oracle_loader
8 default directory load_test_data
9 access parameters
10 (
11 records delimited by newline
12 badfile load_test_data:'load_test_ext.bad'
13 logfile load_test_data:'load_test_ext.log'
14 fields terminated by whitespace
15 missing field values are null
16 (
17 a
18 )
19 )
20 location ('data.dat')
21 )
22* reject limit unlimited
ordbas01 > /

Table created.

ordbas01 > select * from t_ext;

no rows selected

And load_test_ext.log says:
A CHAR (255)
Terminated by whitespace
Trim whitespace same as SQL Loader
error processing column A in row 1 for datafile /opt/oracle/dba/load_test/data.dat
ORA-01722: invalid number

ordbas01 > select * from nls_session_parameters where parameter='NLS_NUMERIC_CHARACTERS';

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_NUMERIC_CHARACTERS ,.

1* select * from nls_instance_parameters where parameter='NLS_NUMERIC_CHARACTERS'
ordbas01 > /

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_NUMERIC_CHARACTERS ,.

Whats wrong with the external table?

I think that the value of NLS_NUMERIC_CHARACTERS being ',.' in instance/session should be fine but look:

1* select * from nls_database_parameters where parameter='NLS_NUMERIC_CHARACTERS'
ordbas01 > /

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_NUMERIC_CHARACTERS .,

Could the value of NLS_NUMERIC_CHARACTERS in nls_database_parameters be the problem?
From the docs (Oracle 9i Globalization Support Guide Page 3-9):

"NLS Data Dictionary Views
(...)
NLS_DATABASE_PARAMETERS shows the values of the NLS parameters that were used when the database was *created*."

So the only way to change the values in nls_database_parameters is recreating the db?

Thank you tom for your time,
Martin.

PS: My system is UnitedLinux 1.0 (SuSE Linux Enterprise Server) with Oracle 9i Release 2 (9.2.0.1).

Tom Kyte
January 16, 2004 - 12:51 am UTC

the docs for external tables do say:

.....
Default Character Sets and Date Masks

For fields in a datafile, it is the client s NLS environment that determines the default character set and date masks. For fields in external tables, it is the server s NLS environment that determines the default character set and date masks.
......

one option would be to use a varchar2(n) field instead of number and using "to_number()" in the select list.

Loading dates using External tables

Duncan, March 15, 2004 - 3:56 am UTC

Tom. Thanks for all the advice on the new 9i external table feature.

I just ahve one quick question regarding the loading of dates.

In my control file (.csv) the date field exists in the format of dd/mm/yy. I set up my external table as follows:


CREATE TABLE External_Table_HR
(
S_NUMBER VARCHAR2(8 BYTE),
NAME VARCHAR2(100 BYTE),
SEX VARCHAR2(2 BYTE),
DEPTID VARCHAR2(6 BYTE),
DEPT_DESCR VARCHAR2(100 BYTE),
GEO_LOCATION VARCHAR2(100 BYTE),
DESCR VARCHAR2(100 BYTE),
FULL_PART VARCHAR2(1 BYTE),
STD_HRS_WK NUMBER,
GRADE VARCHAR2(30 BYTE),
GRADE_DESCR VARCHAR2(30 BYTE),
DATA_DATE DATE
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY DATA_DIR
ACCESS PARAMETERS
( records delimited by newline
NOLOGFILE
BADFILE DATA_DIR:'EXTHR.bad'
skip 1
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
)
LOCATION (DATA_DIR:'EXTHR.csv')
)
REJECT LIMIT 0
LOGGING
NOCACHE
NOPARALLEL;

With the date in the control file in the format it is, it will not load into the external table and an exception is generated. When i change the date in the control file to an Oracle standard format it works fine.

The question i have is this. When using SQl loader you could specify the date mask to be used which would solve my problem. I am having a bit of difficulty getting a similar thing to work using external tables. could you possibly provide me with an example of how I could set a date mask to get round this problem I am having.

Thanks for any help






Tom Kyte
March 15, 2004 - 7:25 am UTC

Say you had this ctl file t.ctl:

load data
INFILE x.dat
INTO TABLE t
APPEND
FIELDS TERMINATED BY ',' trailing nullcols
(a date "dd/mm/yy",
b,
c,
d,
ID "s.nextval")


and you wanted "external table" from it.. just run

$ sqlldr u/p t.ctl external_table=generate_only

you'll find in t.log:

CREATE TABLE "SYS_SQLLDR_X_EXT_T"
(
A DATE,
B NUMBER(38),
C NUMBER(38),
D NUMBER(38),
ID VARCHAR(255)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8ISO8859P1
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'x.bad'
LOGFILE 't.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
A CHAR(255)
TERMINATED BY ","
DATE_FORMAT DATE MASK "dd/mm/yy",

B CHAR(255)
TERMINATED BY ",",
C CHAR(255)
TERMINATED BY ",",
D CHAR(255)
TERMINATED BY ",",
ID CHAR(255)
TERMINATED BY ","
)
)
location
(
'x.dat'
)
)REJECT LIMIT UNLIMITED


which also shows you how to do what you ask. I find that using external_table with sqlldr is an easy way to take working ctl files and make them into external table creates...

Nice Topic

Srinivas, March 31, 2004 - 5:08 am UTC

Hi Tom,

Can you Please tell me how to load Data in Excel which is spread across different sheets into oracle tables.

For Example Sheet1 needed to be loaded into emp,sheet2 needed to be Loaded into Dept.

Please Help me and Thanks in Advance.



Tom Kyte
March 31, 2004 - 8:46 am UTC

sorry - i know next to nothing about excel, specially when it comes to multiple sheets.

External tables in 10g

A reader, April 19, 2004 - 11:58 pm UTC

Tom,
Is there anything new to know for external tables in 10g?

Thanks


Tom Kyte
April 20, 2004 - 8:31 am UTC

sure, they go the other way:

ops$tkyte@ORA10G> create or replace directory tmp as '/tmp'
  2  /
 
Directory created.
 
ops$tkyte@ORA10G> pause
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> create table all_objects_unload
  2  organization external
  3  ( type oracle_datapump
  4    default directory TMP
  5    location( 'allobjects.dat' )
  6  )
  7  as
  8  select * from all_objects
  9  /
 
Table created.
 
ops$tkyte@ORA10G> pause
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> !head /tmp/allobjects.dat
c&#65533;&#65533;!g&#65533;&#65533;&#65533;~&#1897;&#65533;&#65533;&#65533;&#65533;0BA&#65533; F&#65533;p"".""Linuxi386/Linux-2.0.34-8.1.0WE8ISO8859P1LBB EMB GHC JWD SD EBE WMF DDG JG SJH SRH JGK CL EGM BJM RAP RLP RP KR PAR MS MRS JLS CET HLT&#65533;&#65533;$$
&#65533;F&#65533;&#65533;!F&#65533;h<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <STRMTABLE_T>
   <VERS_MAJOR>1</VERS_MAJOR>
   <VERS_MINOR>0 </VERS_MINOR>
   <VERS_DPAPI>3</VERS_DPAPI>
   <ENDIANNESS>0</ENDIANNESS>
   <CHARSET>WE8ISO8859P1</CHARSET>
 
ops$tkyte@ORA10G> pause
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select count(*)
  2    from all_objects_unload
  3  /
 
  COUNT(*)
----------
     47304
 


you can pick up that file and plop it down on another system and there you go.  the defining query can be pretty much anything (joins, aggregates, etc). 

External table placed in another machine

RK, April 21, 2004 - 1:17 am UTC

Tom,

If oracle runs on a unix server, it is possible to create a directory which resides in a windows machine in the same network?

To be clear once again, should the external table be in the database server?

If need not be, how will you make the oracle server to know that the external table is in another server in a unix database server + windows file server in the same network?


Tom Kyte
April 21, 2004 - 7:49 pm UTC

if the windows filesystem is mounted to the unix box sure.

the external table, read by the server, needs to be VISIBLE to the server..

mount the windows disk on the unix machine.

ET from different Host

A reader, June 03, 2004 - 4:54 pm UTC

Hi Tom,
Can we use ET to extract data from a differnt Host for an example let say the database is on Host1(nt) and feeder file to load is on Host2 (VMS)

I tried to use NFS but without luck?

Also heard that in 10g ET can do write besides read ?



Tom Kyte
June 03, 2004 - 7:14 pm UTC

windows makes it really really really hard to access network disks -- utterly hard. It is not simple like Unix is.


see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:241814624807 <code>


why do i get the error

Rahul, June 14, 2004 - 11:43 am UTC

Hi Tom,
I was trying the external table feature.
I first created the directorys and granted the appropriate read/write permissions on the dir to the user. Then i created the table it gives me a reply table created but when i try to query the table i get the following error.


The following error has occurred:

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "badfile": expecting one of: "exit, fields, records"
KUP-01007: at line 1 column 1
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1


my create table stabe ment is as follows.

create table load_recon
( oid number(16),
ticket_number varchar2(10),
pnr_number varchar2(10))
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY DATA_DIR
ACCESS PARAMETERS
(
badfile bad_dir:'loader.bad'
logfile log_dir:'loader.log'
records delimited by newline character set us7ascii
fileds terminated by ' '
missing filed values are null
(
oid,ticket_number,pnr_number
)
)
LOCATION ('data.dat')
)
PARALLEL
REJECT LIMIT UNLIMITED;


I have checked the previous responces and i have not made any of the mistakes which were listed. Now my question is if the table is created then why do i get this error only while querying it.
Is it not able to detect the end of records in the file? if so why should i do?

Please help.

Thank you.
Rahul.


Tom Kyte
June 14, 2004 - 12:05 pm UTC

there are quite a few obvious typos in there.  

fileds, filed.

character set is to be a single word characterset

records delimited by comes before badfile

OID is a reserved word to sqlldr....


so, after fixing that:

ops$tkyte@ORA9IR2> create table load_recon
  2  ( oid number(16),
  3    ticket_number varchar2(10),
  4    pnr_number varchar2(10)
  5  )
  6  ORGANIZATION EXTERNAL
  7  (TYPE ORACLE_LOADER
  8   DEFAULT DIRECTORY DATA_DIR
  9   ACCESS PARAMETERS
 10   (
 11    records delimited by newline characterset us7ascii
 12    badfile bad_dir:'loader.bad'
 13    logfile log_dir:'loader.log'
 14    fields terminated by '   '
 15    missing field values are null
 16    (
 17        "OID",
 18        ticket_number,
 19        pnr_number
 20    )
 21   )
 22   LOCATION ('data.dat')
 23  )
 24  PARALLEL
 25  REJECT LIMIT UNLIMITED;
 
Table created.
  
ops$tkyte@ORA9IR2> select * from load_recon;
 
no rows selected
 

 

Thanks a lot

rahul, June 14, 2004 - 1:01 pm UTC

What to say i made some awesome mistakes but simple typos but thanks for showing it.
But i didn't knew i have to give the 'records delimited ' before badfile and why do we have to give oid in double quotes? is it because its a number field.
Thank you very much for your fast and accurate responce.

Rahul.

Tom Kyte
June 14, 2004 - 1:22 pm UTC

OID is a reserved word is why, you have to quote it to say "ignore the reserved word, this is my column name"




Why do CTAS is FAST

Js, July 21, 2004 - 12:42 pm UTC

hi,

Sir, Why do CTAS is Fast than /*+APPEND*/ in first
post by you ? My assumption is /*+APPEND*/ should
same [ in noarchive ] and fast in [ archive mode if
CTAS using with logging ].


Thanks,
Js



Tom Kyte
July 21, 2004 - 2:12 pm UTC

append doesn't affect logging really -- only if the TABLE itself were nologging.

in archivelog mode, both CTAS and insert /*+ append */ generate redo normally.

it is UNDO they skip.

CTAS, not having a pre-existing table to deal with, worry about, work with, can take a short cut or two that insert /*+ append */ does not. realize -- this is a very very small example here -- you'd have to scale this way up to see if "CTAS is 2x as fast as append" or "CTAS saves a second or two over append". Ultimately, you'll find them to be "about the same"

give it a go, the entire test case is read to run and easily scaled up.

Re:

A reader, July 22, 2004 - 5:17 am UTC

Thanks for your reply. Does it mean .. There is no
Diff. in CTAS and APPEND in any case.


Thanks,
Js

Tom Kyte
July 22, 2004 - 7:28 am UTC

there are subtle differences you cannot see but as far as undo and redo generation go - they are pretty much the same.

Thanks Sir...

Js, July 22, 2004 - 12:12 pm UTC

:)

SQL functions

A reader, July 22, 2004 - 3:33 pm UTC

I currently have SQL functions applied to my columns to say divide a number by 1000 or something. This way my staging table gets the data I want.

How can I do this with external tables? Load everything as varchar2 and do all the conversions in the SQL that selects from this table?

Ideally, I want to keep these trivial transformations as part of my external table DDL itself so that no one else has to know about them.

For example, one of my legacy input files sends us float numbers without the decimal point, typical Cobol stuff. In my controlfile I have a ":field/10000" function to convert it on the way into the staging table

When I move to using external tables, is there a way I can put this field/10000 division right in the external table DDL?

Thanks

Tom Kyte
July 23, 2004 - 8:16 am UTC

think "views"

create your external table.
create a view that performs f(x) on anything you want.

use the view.

Thanks for all your help on external tables!!

Duncan, July 23, 2004 - 7:11 am UTC

Tom

firstly i would like to thank you for all your help so far on external tables!

We use them pretty extensivly on a day to day basis and the problem i am having is one of performance.
One procedure we have is:

insert into table 1 (select * from ext_tab)

which is a very common practice but we perform a lot of data transformation on the data we select from the external tables. We are using several DECODES on concatanated columns and joins(+) to stored tables etc.

Some of these queries can take a while to run and i was just curious if you had and rules of thumb when performing a lot of data transformation from external tables before loading it into a table in the database?

Thanks for any help



Tom Kyte
July 23, 2004 - 9:01 am UTC

use them as a table that will be full scanned. use generous hash area sizes if you are doing lots of joins, use dbms_stats to tell oracle about how large the external table is (or use dbms_stats to actually analyze the external table!)

Direct path

A reader, August 01, 2004 - 11:27 pm UTC

In earlier versions, direct path load using sqlldr was the fastest way to load data into a table, but the restriction was that SQL functions couldnt be applied to the columns in the controlfile.

This was because direct path load created pre-formatted data blocks and wrote them directly to the datafile, bypassing the SQL layer.

This is no longer a restriction, both insert /*+ append */ and external tables perform as fast or faster than the old direct path load and can do SQL functions.

What changed to allow this? If direct path load now invokes the SQL engine, how is it still faster than non-direct path load?

Thanks

Tom Kyte
August 02, 2004 - 7:41 am UTC

insert /*+ append */ was always a direct path load running in the sql engine. until external tables -- insert append could only go table to table so the only way to LOAD data using direct path operations was -- well -- sqlldr.

so, the addition of external tables opened up the ability to use SQL to load data, and SQL could always direct path -- so you get a direct path load using sql and all of it's functionality.




Direct path load

A reader, August 02, 2004 - 10:29 am UTC

Didnt think about it that way, you are right of course.

But my question was really, what makes direct path load (via /*+ append */, or sqlldr direct=y or external tables, whatever) faster than non-direct path load?

If direct path load is always faster than non-direct path and there are no restrictions/limitations of the former over the latter, why isnt direct-path load the default mechanism?

If both of them go thru the SQL engine, what makes the direct path faster than the regular path? [Earlier, the direct path load skipped the SQL engine and wrote pre-formatted blocks, yadda, yadda]

Thanks

Tom Kyte
August 02, 2004 - 10:56 am UTC

direct path is faster because:

o you bypass the buffer cache
o we format database blocks directly
o we bypass traditional space mgmt and do it faster
o we bypass undo generation
o it can bypass redo

direct path loads don't use existing free space (that is one major reason they are not the "default")

direct path loads don't let you read the table until you commit (another major reason). since we don't have undo, the consistent read mechanism is somewhat "defeated"

direct path loads can skip redo generation as well, that is *bad* by default.




access to oracle

A reader, August 03, 2004 - 4:36 pm UTC

Hi Tom,

can we use external tables for exporting an access database to oracle? can u pls explain with an example?

Thanks.

Tom Kyte
August 03, 2004 - 6:01 pm UTC

you would have to create a csv file from access to use an external table. this is probably easier:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4406709207206#18830681837358 <code>



Staging tables

A reader, August 06, 2004 - 11:30 pm UTC

Currently, we use sqlldr to load files into a staging table, validate them, upsert into the main table and log errors into a error table. The error table has a FK pointing back to the staging table so that the full context of the error can be seen.

Since the staging area is purged with some frequency, say 10 days, I have access to all the good/bad records, errors, for 10 days.

If I conver this to use external tables and get rid of my persistent staging tables, then I lose the ability to query previously loaded files for troubleshooting. I only have access to the file/table that is there right now.

Any way to have my cake and eat it too?!

Tom Kyte
August 07, 2004 - 10:09 am UTC

purge your file area with the same frequency i suppose?

not sure why this is any different?

Let me take this paragraph and change words:


If I conver this to use REAL tables and get rid of my persistent staging
FILES, then I lose the ability to query previously loaded TABLES for
troubleshooting. I only have access to the table/file that is there right now.

Not quite

A reader, August 07, 2004 - 11:15 am UTC

"If I conver this to use REAL tables and get rid of my persistent staging FILES, then I lose the ability to query previously loaded TABLES for troubleshooting. I only have access to the table/file that is there right now. "

Thats not true, if I use REAL tables, I DONT lose the ability to query previously loaded tables, everything is right there in the tables.

But instead if I keep my files lying around on the filesystem, everytime I need to generate an error report, I would be implicitly loading the file again, just for the silly error report.

Also, sometimes the staging record errors out because it has a "as yet unknown account" for example. Since the staging table is persistent, I just mark that record as 'processed=N' and the next run (possibly with a different file) picks that up too. I CANNOT do this with external tables since my "staging" table only points to the current file.

Thanks

Tom Kyte
August 07, 2004 - 1:43 pm UTC

and by cracky -- everything can be in your files. seems to be a "management" issue here.

seems your error report is "data", and as such would be stored in a database table?

but at the end of the day -- you do what "makes sense". the is the only rule here -- common sense prevails, you do what makes the most sense in your environment. if you don't *want* to use external tables (although they do make loading of your "stage" easier perhaps) and have good reasons (or hey, just don't want to) - you don't have to.

do what makes sense, what makes your life easiest, what gets the job done.

Loading data into oracle (9.x)

Abhivyakti, September 02, 2004 - 6:06 am UTC

Hi Tom,
Your answers/suggestions/alternatives are always good to read and in fact a technical joy for a learner like me.

I wud like to ask u regarding loading data in oracle, i mean, how wud u compare sqlldr , external tables and a function (in Pro C) that loads - INSERTs the data into oracle??

which one of these wud be the fastest??

Abhi

Tom Kyte
September 02, 2004 - 9:03 am UTC

u?
wud?
keyboard failing? it is losing vowels -- and even some other characters....


I would use external tables first and foremost, in all cases. If I had to (was forced to) load over the network, i would use sqlldr. Using Pro*C would be my LAST choice, if even considered.

Loading data into oracle

Abhivyakti, September 03, 2004 - 1:38 am UTC

Hi Tom,
Thanks for the reply, Well sorry for "u" and "wud" as i am much used to use that.

Well...In case of Pro*C function - Why would you choose it last? for what reasons?? I mean - in what ways its going to be "costly" than ET or sqlldr??

Sorry for ignorance...
regards
Abhi

Tom Kyte
September 03, 2004 - 9:57 am UTC

(i know, lots of people are starting to treat IM'ism or SMS messaging acronyms as words, as written language -- in email, in writing, in everything. I think they are appropriate in IM or SMS, they are not appropriate in written media. So, I just point it out when I see it)


Lets see, write a program, compile program, debug program, maintain program, enhance program, upgrade program, .......

or

insert into table select * from external_table;


It is an ease of use thing, I write code if and only if I *have* to write code. Writing an application to do something that can be done in a single line of SQL is something I abhore.

Call NotePAD data by the SQL query

Tanweer, September 05, 2004 - 5:17 am UTC

Call NotePAD data by the SQL query 
How I Display notePAD contains into SQL> Prompt 

Is it possible to display notepad contains directly into the SQL> Prompt 

7369|MR. SMITH|CLERK|7902|17-12-80|800|null|20 
7499|Mr. ALLEN John|SALESMAN|7698|20-02-81|1600|300|30 
7521|WARD|SALESMAN|7698|22-02-81|1250|500|null|30 

etc......... 

Thanks 

Tom Kyte
September 05, 2004 - 10:36 am UTC

what the heck is "notepad" data. not sure at all what you are asking there.

external table error

A reader, March 31, 2005 - 11:58 am UTC

Hi Tom,

I am trying to use external table to load data into a table. I have done the following steps:

1. The source data is in SQL Server and I exported it into a dump file on the Oracle Server (c:\exp\data.out)
2. In the Oracle Server created an external table as

create table external_table
(
id number(10),
lastname char(20),
firstname char(20),
env char(1)
)
ORGANIZATION EXTERNAL
( type oracle_loader
default directory data_dir
access parameters
( fields terminated by ',(')
location ('data.out')
)
/

where data_dir is
create or replace directory data_dir as 'c:\exp';

When I do a select count(*) from external_table;

I am getting

select count(*) from external_table
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
KUP-04020: found record longer than buffer size supported, 524288, in
c:\exp\data.out
ORA-06512: at "SYS.ORACLE_LOADER", line 52
ORA-06512: at line 1

I checked the log file and it says

Fields in Data Source:

ID CHAR (255)
Terminated by ",("
Trim whitespace same as SQL Loader
LASTNAME CHAR (255)
Terminated by ",("
Trim whitespace same as SQL Loader
FIRSTNAME CHAR (255)
Terminated by ",("
Trim whitespace same as SQL Loader
ENV CHAR (255)
Terminated by ",("
Trim whitespace same as SQL Loader
KUP-04020: found record longer than buffer size supported, 524288, in c:\exp\data.out
KUP-04053: record number 1

Pls help.

Thanks.

Tom Kyte
March 31, 2005 - 12:06 pm UTC

are there newlines in this file? how big is the biggest record?

A reader, March 31, 2005 - 12:14 pm UTC

There should be newlines in the file. I used SQL Server's bcp out to export the data. I did not explicitly specify any row terminator. The env is only a one char value for all records.

Thanks.

Tom Kyte
March 31, 2005 - 12:26 pm UTC

can you edit it and verify?

Anshul Deo, May 05, 2005 - 5:57 am UTC

Hi Tom,

I am rewriting some scripts we currently have that use SQL Loader to use external tables instead. The one I am currently looking at has three different types of records in the data file to be imported. These records are position
delimited rather than comma delimited and each of the three record types have different columns. The record types are distinguished by the first three characters which make up the first column for each record.
The SQL Loader script uses the WHEN clause, one of the SQL Loader scripts is as follows:
LOAD DATA
REPLACE
--
-- Agency Header Record
--
INTO TABLE GESB_IMPORT_HDR_TMP
WHEN record_identifier = 'AHD'
(
record_identifier position(001:003) char,
remitting_group position(004:009) char,
effective_date position(010:019) date "DD/MM/YYYY"
)
How can I do something similar in external tables so that I can reference the same flat file in three separate create external table statements but only look at the records I want in each case?
I have rewritten the above example to get the following:
create table GESB_LOAD_TEST1 (RECORD_IDENTIFIER VARCHAR2(3),
REMITTING_GROUP VARCHAR2(6),
EFFECTIVE_DATE DATE)
ORGANIZATION EXTERNAL (TYPE oracle_loader
DEFAULT DIRECTORY STAGING_EXTERNAL_LOAD
ACCESS PARAMETERS(RECORDS FIXED 19
LOGFILE 'GESB_LOAD_TEST1.log'
BADFILE 'GESB_LOAD_TEST1.bad'
FIELDS(RECORD_IDENTIFIER CHAR(3),
REMITTING_GROUP CHAR(6),
EFFECTIVE_DATE CHAR(10)
DATE_FORMAT DATE MASK "DD/MM/YYYY"))
LOCATION ('GESB_TEST1.lis')) REJECT LIMIT UNLIMITED;

Thanks,

Anshul.


Tom Kyte
May 05, 2005 - 7:31 am UTC

I took your ctl file, ran

$ sqlldr / t.ctl external_table=generate_only

and sqlldr says.....

CREATE TABLE "SYS_SQLLDR_X_EXT_GESB_IMPORT_H"
(
  "RECORD_IDENTIFIER" VARCHAR2(3),
  "REMITTING_GROUP" VARCHAR2(10),
  "EFFECTIVE_DATE" DATE
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8ISO8859P1
    LOAD WHEN ("RECORD_IDENTIFIER" = "AHD")
    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'t.bad'
    LOGFILE 't.log_xt'
    READSIZE 1048576
    FIELDS LDRTRIM
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "RECORD_IDENTIFIER" (1:3) CHAR(3),
      "REMITTING_GROUP" (4:9) CHAR(6),
      "EFFECTIVE_DATE" (10:19) CHAR(10)
        DATE_FORMAT DATE MASK "DD/MM/YYYY"
    )
  )
  location
  (
    't.dat'
  )
)REJECT LIMIT UNLIMITED



Edit that up a little bit and:


ops$tkyte@ORA10G> create or replace directory dir as '/tmp'
  2  /
 
Directory created.
 
ops$tkyte@ORA10G> !echo AHD45678901/01/2001 > /tmp/t.dat
 
ops$tkyte@ORA10G> !echo AxD45678901/01/2001 >> /tmp/t.dat
 
ops$tkyte@ORA10G> drop table t;
 
Table dropped.
 
ops$tkyte@ORA10G> CREATE TABLE "T"
  2  (
  3    "RECORD_IDENTIFIER" VARCHAR2(3),
  4    "REMITTING_GROUP" VARCHAR2(10),
  5    "EFFECTIVE_DATE" DATE
  6  )
  7  ORGANIZATION external
  8  (
  9    TYPE oracle_loader
 10    DEFAULT DIRECTORY DIR
 11    ACCESS PARAMETERS
 12    (
 13      RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8ISO8859P1
 14      LOAD WHEN ("RECORD_IDENTIFIER" = "AHD")
 15      BADFILE 't.bad'
 16      LOGFILE 't.log_xt'
 17      READSIZE 1048576
 18      FIELDS LDRTRIM
 19      REJECT ROWS WITH ALL NULL FIELDS
 20      (
 21        "RECORD_IDENTIFIER" (1:3) CHAR(3),
 22        "REMITTING_GROUP" (4:9) CHAR(6),
 23        "EFFECTIVE_DATE" (10:19) CHAR(10)
 24          DATE_FORMAT DATE MASK "DD/MM/YYYY"
 25      )
 26    )
 27    location
 28    (
 29      't.dat'
 30    )
 31  )REJECT LIMIT UNLIMITED
 32  /
 
Table created.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select * from t;
 
REC REMITTING_ EFFECTIVE
--- ---------- ---------
AHD 456789     01-JAN-01
 
 

Mark, May 10, 2005 - 4:56 pm UTC

Hi Tom,

We have a client who supplies us with a spreadsheet, containing a date column in the format YYY/MM/DD. To get the year indicated by YYY, we add 1900 to it. For example, if they give us 104/03/01, we have to convert that to 03/01/2004 (mm/dd/yyyy). 99/08/20 becomes 08/20/1999 (mm/dd/yyyy). I suspect this was their quick-fix around the Y2K problem, so they must not be using a very good database (very big company, too).

I see we can transform a char to a date by specifying the format mask. Is there any neat and elegant way to define the transformation I need in the external table definition?

Thanks,
Mark



Tom Kyte
May 10, 2005 - 11:26 pm UTC

I see substr, to_number and a little math plus a to_date in your future.


to_number(substr( C, 1, instr(C,'/')-1 ))

should get you 104

1900+<that> || substr( c, instr(C,'/') )

will get you 2004/03/01

to_date of that....

Mark, May 11, 2005 - 10:35 am UTC

Thanks for the response. I am actually familiar with these functions... Guess I wasn't clear about the crux of my question. I'll try to explain better.

I am going to be loading data like: CSV -> External Table -> Live Table. I would like to catch as much bad data as possible in the badfile. The client sometimes makes mistakes in their dates, so it wouldn't be unheard of to receive '101/0314' for example (they forgot a slash). I would like to do the converting at the external table level -- not when I am inserting into the live table.

Looking two posts up, we have:

23 "EFFECTIVE_DATE" (10:19) CHAR(10)
24 DATE_FORMAT DATE MASK "DD/MM/YYYY"

So in this example, if I understand correctly, having a date string '12/30/1980' (reversed DD and MM) would cause a bad row to be logged in the badfile. I would like to do something similar, but rather than have a simple date format mask I would need to call to_number, substr, etc.

Is this possible? Thanks.

Tom Kyte
May 11, 2005 - 12:19 pm UTC

sorry, if you want SQL, you'll have to do it in SQL - during the SELECT.

can you use a multi-table insert? have the function reeturn a "good date" or a "known bad date (01-01-0001 for example) and insert the good rows into one table, bad rows into another?

Mark, May 11, 2005 - 1:43 pm UTC

Yes I think that will do. Thanks.

What is

Dave, May 11, 2005 - 5:00 pm UTC

<quote>flow the data through a pipelined plsql function for cleansing/transformation<quote>

What is a *pipelined plsql function* and how to use it for cleansing/transformation? any example? Thnaks.


Tom Kyte
May 11, 2005 - 7:29 pm UTC

search for pipelined, tons of examples in this site.

A reader, May 17, 2005 - 11:47 pm UTC

Dear Tom,

I was exploring the possibility of using external tables instead of sqlldr in our DW environment. The main issue I am faced with is that with external tables, the processing driver has to shift from the client to the database. We currently drive our load processes from the ETL tool - after the transformations we pipe the data directly to sqlldr without landing them into files. I can probably benefit by using external tables sothat I can let Oracle decide on the appropriate DOP and also use sql functions in some cases. But I do not know how to implement the external table functionality in this scenario.

Is it possible create external tables on a pipe? even if that is possible, how can I call the load process from outside the database (I looked at some of the examples in your site about calling shell scripts from a procedure - not sure if that is the way to go). Appreciate if you can show an example of how to do this. As always, thank you verymuch for your fantastic solutions!

Tom Kyte
May 18, 2005 - 8:46 am UTC

No, you cannot use a pipe for the external table.

PROJECT COLUMN

Vrajesh Shah, June 15, 2005 - 12:32 pm UTC

Tom,
We are using external table for our incoming files from different vendors. They also sent us the record count file. Before loading the external table to stage table we check the count of external table using select count(*) from external table.
This count then check with the record count file to make sure the count are matching. We are not very sure about the
"PROJECT COLUMN" for oracle 9.2.0.6.0 (our database). Is there a chance that the count(*) from external table and load count of stage table ( stage table has no constraints, or validation) can give diffrent numbers?

Tom Kyte
June 16, 2005 - 3:24 am UTC

I don't understand?

count(*) from an external table will tell you how many records that are not "bad" or "discarded" will be returned

count(*) form a 'stage' table tells you how many records are in the table.

count

reader, June 17, 2005 - 1:10 pm UTC

If 10 records in data file and if we do select count(*) from external table it will always give you 10 irrespective of any bad or rejected records. when we do select * from external_table we might get 10 or less depends on the rejection of the records. So record count from external table is no guarantee of that many records you get in stage table.

External Table Access

Deepak, June 28, 2005 - 2:20 pm UTC

Hi Tom,

I have a very simple question.

Does Oracle store External table data in Database Buffer Cache when we query an external table?

I am asking this because external tables are stored in flat files and they do not have physical structure like the database block defined. What is the actual process behind external table access?

Thanks in advance.



Tom Kyte
June 28, 2005 - 2:40 pm UTC

no, it is always read from disk.

memory usage

Deepak, June 28, 2005 - 2:51 pm UTC

Thanks Tom,

But would you please let us know the Oracle memory structures used in accessing rows from external table?

Can we provide any kind of hints in queries that refer external tables?

Sometimes I get large pool memory allocation errors when accessing external tables. Why?


Tom Kyte
June 28, 2005 - 3:04 pm UTC

it just reads them straight into your session. just reads them. they are not cacheable in the buffer cache.

yes, you can use hints that make sense.


I don't know why on the last part, you give totally insufficient data to comment.

A reader, June 28, 2005 - 7:16 pm UTC

Does oracle employ direct path reads / writes while reading from external tables like direct io mechanism for non-cached CLOBs ?

Tom Kyte
June 28, 2005 - 8:43 pm UTC

we just read files from the OS, it is very similar to direct IO as direct IO is just IO that bypasses the cache.

External Table access Error

Deepak, June 29, 2005 - 1:27 am UTC

Hi Tom,

Here is the information regarding the error I wrote about earlier.

I executed the follownig query....

select * from code_search where item_DESC ='%PC%';

NOTE: code_search is the external table.

The ERROR is....

ERROR:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
KUP-04050: error while attempting to allocate 524800 bytes of memory
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1

Please tell me why does this error comes only while accessing an external table.

As you said external table access do not use any Oracle memory structure but uses direct I/O.

Kindly clarify...

Tom Kyte
June 29, 2005 - 8:49 am UTC

what does this have to do with large pool?!??!

<quote>
Sometimes I get large pool memory allocation errors when accessing external
tables. Why?
</quote>

hmmm.

Anyway, looks like your session ran out of the ability to allocate more process memory.

If it happens every time, I would suspect a bug in the manner in which you defined the table somehow. but we have no test case to see. no versions to consider. not much.


and where pray tell did I say it uses no memory???? I said "not cached in buffer cache", that is not anything like saying "no memory used"

Where in data dictionary is external table definition?

A reader, August 25, 2005 - 4:34 pm UTC

Is there a dba_view that holds the definition for an external table?

Tom Kyte
August 25, 2005 - 6:44 pm UTC


sys@ORA9IR2> desc dba_external_tables;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
TYPE_OWNER CHAR(3)
TYPE_NAME NOT NULL VARCHAR2(30)
DEFAULT_DIRECTORY_OWNER CHAR(3)
DEFAULT_DIRECTORY_NAME NOT NULL VARCHAR2(30)
REJECT_LIMIT VARCHAR2(40)
ACCESS_TYPE VARCHAR2(7)
ACCESS_PARAMETERS VARCHAR2(4000)


Sqlloader vs External Tables

THIRUMARAN, October 04, 2005 - 11:38 am UTC

Hi Tom,

A) with external tables can I achieve the following .I am working on migration from SOURCE 10g DB R1(unnormalized tables) to TARGET 10g DB R2 (Normalized tables)

my text below quotes "SOURCE" AS UNNORMALIZED DB & "TARGET" as new DB which is NORMALIZED.I need to migrate data from SOURCE to TARGET.

1) Can single row in SOURCE be migrated to multiple rows across one or more tables in TARGET.
2) Can multiple rows in single column in SOURCE be migrated to single row and multiple columns in TARGET.
3)Can single row in SOURCE be migrated to single row in multiple tables in the TARGET.

can this be achieved by using any ORACLE BUILT IN TOOLS / combination of any oracle built in tools?

B) The below site mentions
"Note that SQL*Loader
may be the better choice in data loading situations that require additional indexing of the staging table"
I am much thankful if you could elaborate this .
1) What is the purpose of staging tables
2) when should we should recommend to use staging tables ?
Ref:
</code> http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14215/et_concepts.htm#i1007707 <code>


Thanks
Thirumaran

Tom Kyte
October 04, 2005 - 4:52 pm UTC

1) yes, multi table inserts

2) sure, if you can write the query, you can insert it. You can pivot in SQL on a key

3) see #1


b) I cannot, I do not know why they said that. you could use sqlldr to load a staging table, you can use an external table to load a staging table - I see no benefit sqlldr would provide.

you would only use a staging table if you ABSOLUTELY need to read and reread the data and have a need for indexes on it (since external tables cannot be indexed)

A reader, October 12, 2005 - 2:16 pm UTC


CTAS with external table

A reader, October 14, 2005 - 1:39 pm UTC

I have a real table in the database that is loaded using sqlldr. I want to create a external table with the same structure.

I tried doing
create table my_ext
organization external
...
as select * from real_table where 1=2;

It doesnt like it.

Any way to do this?

Thanks

Tom Kyte
October 14, 2005 - 5:35 pm UTC

why doesn't it? In 10g that would work and would create an empty external table (not very useful). That is called an external table UNLOAD.


Not sure what you are trying to do?

Question

PRS, October 17, 2005 - 11:26 pm UTC

Tom,
I have a flat file which has multiple records per employee. I want to use external file. But this multiple record layout is different based on the record type. Example for EmpId 3005 (Position 2 to 5):
13005999-99-9999 LOAN1 TEST 1003772 O200507150015247
23005999-99-9999 LOAN1 2003772 TEST 012750000188663B
33005999-99-9999 LOAN1 3003772 TEST 012750000188663B
First character(i.e Position) indicates record type. Position 6 to 16 is SSN. Position 2 to 5 is EmpId. So my questions are
(1) How to load this using external table or SQL LOADER?
(2) How Do I merge number of records(three or two or one) per employee into single records?
As always appreciate your help and guidance. You are the Guru of ORACLE.

Tom Kyte
October 18, 2005 - 8:54 am UTC

you can either read the server utilities guide and see how to use multiple INTO clauses in sqlldr with a "when" clause, or you can use an external table like this:

ops$tkyte@ORA9IR2> create or replace directory data_dir as '/tmp';

Directory created.

ops$tkyte@ORA9IR2> drop table et;

Table dropped.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> CREATE TABLE et
  2  (
  3    record_type  varchar2(1),
  4    rec1_ssn     varchar2(11),
  5    rec1_empid   varchar2(4),
  6    rec2_ssn     varchar2(11),
  7    rec2_empid   varchar2(4)
  8    /* other fields here.... */
  9  )
 10  ORGANIZATION external
 11  (
 12    TYPE oracle_loader
 13    DEFAULT DIRECTORY DATA_DIR
 14    ACCESS PARAMETERS
 15    (
 16      RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
 17      READSIZE 1048576
 18      FIELDS LDRTRIM
 19      REJECT ROWS WITH ALL NULL FIELDS
 20      (
 21        record_type (1:1),
 22        rec1_ssn    (6:16),
 23            rec1_empid  (2:5),
 24            rec2_ssn    (6:16),
 25            rec2_empid  (2:5)
 26          )
 27    )
 28    location
 29    (
 30      'test.dat'
 31    )
 32  )REJECT LIMIT UNLIMITED
 33  /

Table created.


The first field will be the record type - then you reference the columns you want using rec1_  or rec2_  -- that is, you map each "type" separately using varchar2's (in case the space sometimes has a number, and sometimes a date and sometimes a string - varchar2 will be "type safe")

you can use to_number and to_date in the select to fix that. 

Direct path load, list partition, ora-00054

A reader, October 27, 2005 - 12:48 pm UTC

Hello Tom,

We're running into a problem loading a table using sqlldr in direct path mode.

We have a table that is list partitioned. This table has no FK's, and 2 indexes. There are no triggers on this table.

If we load one partition (for example, our 'CA' list partition) things are fine.

When we load more than one partition concurrently (for example, start 'CA' and in another window start 'NY'), we get the following in our log file:

SQL*Loader-951: Error calling once/load initialization
ORA-00604: error occurred at recursive SQL level 1
ORA-00054: resource busy and acquire with NOWAIT specified

We have the following set for sqlldr command line, to avoid any index maintenance issues:
skip_unusable_indexes=true
skip_index_maintenance=true

Given the simplicity of this table, what could possibly be causing the resource locks? Are we "ALTERING" anything on the structure level of this table where we'd need this based on how we're running this?



Tom Kyte
October 27, 2005 - 1:32 pm UTC

let us see an example... the control file is?

control file and sqlldr command

A reader, October 27, 2005 - 3:16 pm UTC

sqlldr / CONTROL=load_control.ctl DATA=data/load_tab.dat skip_unusable_indexes=true skip_index_maintenance=true direct=true LOG=logs/load_tab.log ROWS=100000


<FILE load_control.ctl>

LOAD DATA APPEND INTO TABLE LOAD_TAB
(
GENERATED_ID POSITION(01:08) CHAR ,
ST POSITION(09:10) CHAR,
FILE_LINE POSITION(11:22) INTEGER EXTERNAL,
PROVIDER POSITION(23:25) CHAR,
ZIP POSITION(26:30) CHAR "LTRIM(:ZIP)"
)

I have been reading up a little more. Someone on a mail list said Oracle issues an implicit "alter table <name> lock exclusive" for direct path loads. This would make sense why we couldn't concurrently direct path load into the same table.

Tom Kyte
October 28, 2005 - 1:39 am UTC

yeah, but it should work OK for partitioned tables - can you use


into table NAME (partition PNAME)


instead - then it'll be at the partition level. (shouldn't need to use parallel=true)

A reader, November 29, 2005 - 6:00 pm UTC


CJ, December 13, 2005 - 3:26 am UTC

Hi Tom,

How does Oracle "proactively" detect that the file pointed to by the external table has changed?

Example:

1.) external table points to 'somefile.dat'
2.) somefile.dat contains data from a timestamped file 'somefile_20051201000001.dat'
3.) two seconds later, the OS receives an independent timestamped file 'somefile_20051201000003.dat' and overwrites 'somefile.dat' with its contents

What I'd want to happen is whenever the entire contents of somefile.dat changes, Oracle will append its entire contents to a master table, and wait (indefinitely) until somefile.dat changes yet again (a la real-time ETL without the OS doing anything, as it did when it had to call SQL*Loader per newly-created file).

Thanks. =)

Tom Kyte
December 13, 2005 - 9:24 am UTC

it "doesn't"

whenever you query an external table we

a) open it
b) read it
c) close it


Oracle is not sitting there watching this file, you would have to write some (os specific) routine to do so.

different behavior between sqlldr and external table

gzhang, January 03, 2006 - 2:09 pm UTC

Tom, your example is relly helpful.

I am trying to use external tables to replace some sqlldr scripts, but have problem when the flat file has newline char in the data field enclosed by double quotes (yes, I admit the data is not perfect, but I have no control over that). Yet, sqlldr handles it well.

Table creation:
create table test_table (
key_code varchar2(10)
, key_num varchar2(10)
, big_text varchar2(4000)
, status_code varchar2(10)
);

Here is the infile (3 records, note the 2nd record has newline char in it):
5015,05-101042,"big text field .....",15
9950,05-100972,"big text

field
",20
5015,05-100963,"big text field ......",13

Here is the sqlldr control file:
LOAD DATA
INFILE 'C:\temp\testldr\test_data.csv'
BADFILE 'C:\temp\testldr\test_data.bad'
DISCARDFILE 'C:\temp\testldr\test_data.dsc'
INTO TABLE test_table

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
( key_code
, key_num
, big_text Char(4000) "substr(:big_text,1,250)"
, status_code
)

sqlldr is working fine and loads 3 records. When I use external table, a select returns 2 records (1st and 3rd). The log file shows that it complains about the 2nd record for not having second enclosing delimiter.

CREATE TABLE ext_test_table (
key_code VARCHAR2(10)
, key_num VARCHAR2(10)
, big_text VARCHAR2(4000)
, status_code VARCHAR2(10)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY criv_dir
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
BADFILE 'CRIV_DIR':'test_data.bad'
DISCARDFILE 'CRIV_DIR':'test_data.dsc'
LOGFILE 'test_data.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"KEY_CODE" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"KEY_NUM" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"BIG_TEXT" CHAR(4000)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"STATUS_CODE" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
)
)
location
(
'test_data.csv'
)
)REJECT LIMIT UNLIMITED;

Is there a way to use external table in this case?

Thanks,
George

Tom Kyte
January 03, 2006 - 2:39 pm UTC

sorry - but sqlldr doesn't work that way - that file has SIX records as far as sqlldr is concerned!  sqlldr isn't loading that file "ok".


ops$tkyte@ORA10GR2> select * from test_table;
 
no rows selected
 
ops$tkyte@ORA10GR2> !cat t.ctl
LOAD DATA
INFILE 'test_data.csv'
INTO TABLE test_table
replace
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  ( key_code
  , key_num
  , big_text Char(4000) "substr(:big_text,1,250)"
  , status_code
)
 
ops$tkyte@ORA10GR2> !cat test_data.csv
5015,05-101042,"big text field .....",15
9950,05-100972,"big text
 
field
",20
5015,05-100963,"big text field ......",13
 
ops$tkyte@ORA10GR2> !sqlldr / t
 
SQL*Loader: Release 10.2.0.1.0 - Production on Tue Jan 3 14:30:08 2006
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
Commit point reached - logical record count 6
 
ops$tkyte@ORA10GR2> select * from test_table;
 
KEY_CODE   KEY_NUM
---------- ----------
BIG_TEXT
-------------------------------------------------------------------------------
STATUS_COD
----------
5015       05-101042
big text field .....
15
 
5015       05-100963
big text field ......
13


Now, there are ways to get sqlldr to load data with embedded newlines, but that isn't one of them... 

difference of newline on windows vs linix?

A reader, January 03, 2006 - 3:29 pm UTC

Tom, Thanks for such a fast response. I did get the same result when I run this from the console of Oralce server (Linux, of course).

However, in my previous questions, I run it on my windows xp client desktop, it does load 3 records (both 10g and 9ir2 client).

CRIV@devl > truncate table test_table
2 /

Table truncated.

CRIV@devl > host type test_data.csv
5015,05-101042,"big text field .....",15
9950,05-100972,"big text

field
",20
5015,05-100963,"big text field ......",13

CRIV@devl > host type test_data.ctl
LOAD DATA
INFILE 'C:\temp\testldr\test_data.csv'
BADFILE 'C:\temp\testldr\test_data.bad'
DISCARDFILE 'C:\temp\testldr\test_data.dsc'
INTO TABLE test_table

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
( key_code
, key_num
, big_text Char(4000) "substr(:big_text,1,250)"
, status_code
)

CRIV@devl > host sqlldr criv@devl test_data.ctl
Password:

SQL*Loader: Release 10.2.0.1.0 - Production on Tue Jan 3 15:16:47 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 3

CRIV@devl > select * from test_table;

KEY_CODE KEY_NUM
---------- ----------
BIG_TEXT
----------------------------------------------------------------------------------------------------
-------------------------------
STATUS_COD
----------
5015 05-101042
big text field .....
15

9950 05-100972
big text

field
20

5015 05-100963
big text field ......
13


Is this because that newline is handled differently on windows and linux? I understand that newline is CR+LF on win, and LF in linux.

Thanks,
George

Tom Kyte
January 03, 2006 - 6:03 pm UTC

do you actually have a "windows newline" or a "unix newline" in there.

sqlldr will read to the end of line (\n on unix, \r\n on windows).

so, do you have a unix file you binary ftp, or a windows "text" file.

How to see which file is being used by an external table

Ajeet, January 04, 2006 - 7:58 am UTC

Hi Tom,

Suppose I have 10 external tables . How can I find that what file is being used by which external table ?

also is there is a way to list all files in a directory..using a sql statement. oracle version is 9i or 10g.

thanks

Tom Kyte
January 04, 2006 - 10:47 am UTC

 
ops$tkyte@ORA9IR2> desc user_external_tables;
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 TABLE_NAME                               NOT NULL VARCHAR2(30)
 TYPE_OWNER                                        CHAR(3)
 TYPE_NAME                                NOT NULL VARCHAR2(30)
 DEFAULT_DIRECTORY_OWNER                           CHAR(3)
 DEFAULT_DIRECTORY_NAME                   NOT NULL VARCHAR2(30)
 REJECT_LIMIT                                      VARCHAR2(40)
 ACCESS_TYPE                                       VARCHAR2(7)
 ACCESS_PARAMETERS                                 VARCHAR2(4000)
 
ops$tkyte@ORA9IR2> desc user_external_locations;
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 TABLE_NAME                               NOT NULL VARCHAR2(30)
 LOCATION                                          VARCHAR2(4000)
 DIRECTORY_OWNER                                   CHAR(3)
 DIRECTORY_NAME                                    VARCHAR2(30


for listing files in a directory:
http://asktom.oracle.com/pls/ask/search?p_string=dirlist

that explains it!

gzhang, January 04, 2006 - 8:19 am UTC

you are right, Tom. I thought of the newline thing a bit, but was not paying much attention.

This file is a windows text file, but it comes from FileMaker (Mac world, which might explains the following). After I exmained the file in hex mode, I found that the "newline" within the 2nd record is actually only a linefeed (0A). Therefore on windows, sqlldr only sees 3 real newline (0D0A) and handles it well.

I guess I'd have to run this on windows.

Thanks again for your help.
George

How to see which file is being used by an external table

gzhang, January 04, 2006 - 9:44 am UTC

Ajeet,

You can query user_external_locations to find out which files are used by external tables.

In sqlplus, you can run host command to list files in a directory:
  on *nix: SQL> !ls
  on windows: SQL> host dir

George 

Alexander, March 06, 2006 - 9:58 am UTC

Tom,

I have a question about a few of the things you mentioned about external tables above. You said

"with external tables you can

o merge a flat file with an existing table in one statement.
and
o do multi-table inserts"

Can you show a quick and dirty example of either or? Basically, I'm looking at loading data from a file, but I need to some processing first and I wasn't sure the best way to do it. Originally I thought I would have to

1)Create the external table
2)Load the data into an Oracle table
3)Write a procedure to do the processing
4)Insert new data back into different xyz tables

But perhaps I can cut out steps 3 and 4 and build that into step 2? Thanks for your advice.


Tom Kyte
March 08, 2006 - 4:05 pm UTC

all you need to do is look at any merge, any multi-table insert - doesn't matter if the example really really used external tables or not since you cannot tell the difference.


so, any merge will suffice (search, lots of them here)
any multi-table insert will suffice


you query external tables just like any other table.

external table limit?

J, March 17, 2006 - 1:56 am UTC

Is there any limit on number of columns on external table? I have flat file with over 2000 columns. It need to split into 5 tables, with same sequence number for one record. If I can't create external table for this one big fat file, I may have to use feature of multi-insert in sqlloader. Question to use sqlloader is:
can I insert sequenct number for one column (which has to be generated during loading process)? Can I use variable length of the file to do multi-table insert?

Thanks!

Tom Kyte
March 17, 2006 - 5:18 pm UTC

well 1000 for the create table, however - you could map larger strings and use substr() in a view to create a view with more if you wanted.


sqlldr can do sequences, yes. and varying width files.

how to year Concatenate in sqlloader

bala, April 26, 2006 - 3:07 am UTC

Dear tom,
How to year Concatenate in sqlloader
see "0426" this raw data 26 is day and 04 is month
if i am taking position than only date and month coming but year how to Concatenate to sqlloader.

please help us.


Tom Kyte
April 26, 2006 - 8:01 am UTC

huh?

did not understand this one.

data already looks pretty much "together" in your example - don't know what the reference to concatenate is.

load a formated number into oracle external table

a reader, April 27, 2006 - 1:36 pm UTC

Tom:

Can you show me or point me the answer of
loading a txt file like:
333.23
222.45
102.02
1.23e+3
1.2456e+6
..
into a oracle external table with the column defined as number. When I tried to load it, the lost 2 rows are rejected. I feel it would be a very simple solution, but couldn't find it. Thanks

Tom Kyte
April 27, 2006 - 3:40 pm UTC

"into a oracle exteral table" - no.

maybe you meant "from"

but it would be useful to see what you "tried"

load a formated number into oracle external table

A reader, April 27, 2006 - 4:49 pm UTC

Sorry Tom, I did the 'try' and it is ok now.
the reason I posted here is I was creating an external table based on a txt file and the only rejected row is something like 2345e+6, and I thought it was because Oracle can't read scientific format, but the real reason is ( when I did the 'try' as you suggested ) I did not define the number field big enough.

Thanks for your quick and sharp response, I really admire your work here.

sqlloader versus external table

Christopher, June 27, 2006 - 5:17 pm UTC

I need to load data from multiple large flat files into tables that will be repeatedly queried. The problem is that the data in each row in the flat files is different depending on the first character of the row. For example, if the first character was 1, the row might contain a name and address. If the first character was 2, the row might contain a phone number and e mail address. I could load the data into multiple staging tables (one for each type) using sqlloader like this:

</code> http://www.orafaq.com/faqloadr.htm#MULTITABLE <code>

Is sqlloader better then external tables in this case? Each file can contain multiple types of records.


Tom Kyte
June 28, 2006 - 7:31 am UTC

neither is "better", they both can accomplish this.

how does parallel load work

A reader, June 29, 2006 - 6:30 am UTC

Hi

I am testing some data loading processes.
I am using parallel and direct. I have a doubt, does parallel load use parallel processes, the p0x proceses? I dont think so, I have monitoring v$session and I always see one single process and this insert statement

INSERT /*+ SYS_DL_CURSOR */INTO scott.aus_lin_1
("CODEMP", "CODCEN", "NUMAUS",
"CODPRO", "VARLOG", "CANSER",
"FECENT", "CODPRV"
)
VALUES (NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL
)

This table is hash partitioned.

I was expecting to see several loading processes but doesnt seems like it.


Tom Kyte
June 29, 2006 - 7:30 am UTC

how are you doing the parallel load?

parallel load

A reader, June 29, 2006 - 8:13 am UTC

Hi I used this

userid=scott/tiger
control=T.ctl
log=T.log
bad=T.bad
direct=true
parallel=true
SKIP_INDEX_MAINTENANCE=true

My table has a parallel degree of 16

TIA

Tom Kyte
June 29, 2006 - 9:26 am UTC

then each sqlldr process will create it's own dedicated server process.

parallel load

A reader, June 29, 2006 - 11:49 am UTC

Hi

That is what i thought, but I only see a Server Process in v$session!

I have teste din 9.2.0.6 and 10.2.0.2 and I get same results for both.

I am loading 1200000 rows...

Tia

Tom Kyte
June 29, 2006 - 1:09 pm UTC

You are running more than one direct path load right? I mean - sqlldr PERMITS parallel loading. but sqlldr does not "DO IT" automagically.

You have to split the input file into N files (or use skip and load to load slices)
You have to run more than one sqlldr process.


[tkyte@dellpe ~]$ sqlldr big_table/big_table big_table direct=true parallel=true &

[1] 3979
[tkyte@dellpe ~]$
SQL*Loader: Release 9.2.0.6.0 - Production on Thu Jun 29 13:03:36 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

sqlldr big_table/big_table big_table direct=true parallel=true &
[2] 3982

SQL*Loader: Release 9.2.0.6.0 - Production on Thu Jun 29 13:03:36 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


and while they were running, v$session will show:


--------------------
BIG_TABLE(11,164) ospid = 3979 command = 0 program = sqlldr@dellpe (TNS V1-V3)
dedicated server=3980
Thursday 13:03 Thursday 13:03 last et = 0
INSERT /*+ SYS_DL_CURSOR */ INTO BIG_TABLE.BIG_TABLE (ID,OWNER,O
BJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,C
REATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECOND
ARY) VALUES (NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,N
ULL,NULL,NULL,NULL)
--------------------
BIG_TABLE(15,29) ospid = 3982 command = 0 program = sqlldr@dellpe (TNS V1-V3)
dedicated server=3983
Thursday 13:03 Thursday 13:03 last et = 0
INSERT /*+ SYS_DL_CURSOR */ INTO BIG_TABLE.BIG_TABLE (ID,OWNER,O
BJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,C
REATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECOND
ARY) VALUES (NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,N
ULL,NULL,NULL,NULL)


parallel load

A reader, June 30, 2006 - 5:04 am UTC

Hi

then what is the use of PARALLEL if I have to manually parallelize the process?!
What is the difference between

sqlldr big_table/big_table big_table direct=true &

sqlldr big_table/big_table big_table direct=true &

with

sqlldr big_table/big_table big_table direct=true parallel=true

sqlldr big_table/big_table big_table direct=true parallel=true

?


Tom Kyte
June 30, 2006 - 7:32 am UTC

you are telling sqlldr "I will be running many of you at the same time, please do the extra work in order to make this possible"



if you did it without parallel=true, the second one would block and wait for the first to continue. Normally - a direct path operation in session 1 precludes ANY OTHER SESSION from writing to the table.

direct=true lets sqlldr coordinate amongst itself over more than one session.

oracle sqlloader

badaubep, July 14, 2006 - 2:39 am UTC

hi tom!
i am a new in oracle.
i have a problem in sql loader .
this is the error
"unable to lock table khachhang due to oracle error 1031"
pls show me the solution
thanks so much
look forward your reply
ba dau bep




Tom Kyte
July 14, 2006 - 8:32 am UTC

well,

[tkyte@dellpe ~]$ oerr ora 1031
01031, 00000, "insufficient privileges"
// *Cause: An attempt was made to change the current username or password
// without the appropriate privilege. This error also occurs if
// attempting to install a database without the necessary operating
// system privileges.
// When Trusted Oracle is configure in DBMS MAC, this error may occur
// if the user was granted the necessary privilege at a higher label
// than the current login.
// *Action: Ask the database administrator to perform the operation or grant
// the required privileges.
// For Trusted Oracle users getting this error although granted the
// the appropriate privilege at a higher label, ask the database
// administrator to regrant the privilege at the appropriate label.


sounds most likely that you are using the replace option which tries to truncate and you do not own the table. So, don't use replace - use append or delete.

Using default column values in External table

Thiru, July 15, 2006 - 4:37 pm UTC

1.Is it possible to provide while creating external table a default value for a column whose value is null? If so, is it part of the access parameters options? Can you please provide an example.

2.I have a ascii file generated from mainframe that has columns with datatype 'char' while it should find a place in Number datatype. How is this converted while creating the ext table?




Tom Kyte
July 16, 2006 - 9:38 am UTC

1) I love views, use a view. Easiest of all possible approaches. That way I don't even have to dig into the docs myself :)

2) eh? you have strings right - a character string like 1234 that represents a number. It is highly unlikely you have a mainframe file with actual binary data in the Oracle number format. You just map as "char" and tell us "it is a number", we convert. You use char in the "access parameters" and "number" in the create table part.

recover database

A reader, July 23, 2006 - 11:46 pm UTC

thanks Tom for your reply.
i have a question :

unfortunately, my database is corrupted .
I have a copy of all data files ( include system.dbf, redo.dbf ...).can i restore my databse ?

My steps are :
+create new database
+copy all datafile to new
+create control file

but it's error.
pls show me the way to recover database
thank u very much

badaubep





Tom Kyte
July 24, 2006 - 10:06 am UTC

ouch, this hurts so bad.

please utilize support right now, before you do anything. They'll sort through what you have and what your options are.

you never want to be learning recovery the day you need to do it. The one thing - the only thing - a DBA is not allowed to get wrong is recovery (backup - they can do that wrong, we can fix that, tuning - same thing, but recovery! if they cannot recover they have missed the boat)

9iR2 external table bug fixed by 10gR2

Phil Miesle, August 23, 2006 - 5:28 am UTC

Discovered weird behaviour when comparing 'select count(*)' with 'select *' from an external table:

$ cat /tmp/test.txt
D20060515 V3069 W003063 SKPS0100255_1 132.0
D20060512 V3069 W003066 SKPS0100255_1 72.321
D20060517 V3069 W003061 SKPS0100255_1 48.0.3

===
create or replace directory external_test as '/tmp';
create table test 
(day_ varchar2(10),
 splr varchar2(20),
 wh__ varchar2(20),
 skps varchar2(20),
 val number)
organization external
(type oracle_loader default directory external_test
 access parameters 
 (
  records delimited by newline
  fields terminated by whitespace
  (day_ char,
   splr char,
   wh__ char,
   skps char,
   val float external
  )
 )
 location ('test.txt')
)
reject limit unlimited;
===

In 9iR2 (9.2.0.7):

SQL> select count(*) from test;

  COUNT(*)
----------
         3

SQL> select * from test;

[chomp]

2 rows selected.

===

The 'missing' row was the third record in test.txt; the '48.0.3' is not a valid number.  'select count(*)' gives no .bad file and no indication in the logfile that something is amiss.  The 'select *' does.

By 10gR2 (10.2.0.1) this is corrected; the 'select count(*)' gives the expected errors/behaviour.

Just an FYI for anyone reading this, as this particular AskTom article has a very high google pagerank. 

Tom Kyte
August 27, 2006 - 3:26 pm UTC

<quote src=Expert Oracle Database Architecture>
Dealing with Errors

In a perfect world, there would be no errors. The data in the input file would be perfect, and it would all load correctly. That almost never happens. So, how can we track errors with this process?

The most common method is to use the BADFILE option. Here, Oracle will record all records that failed processing. For example, if our control file contained a record with DEPTNO 'ABC', that record would fail and end up in the bad file because 'ABC' cannot be converted into a number. WeÂ’ll demonstrate that in the following example. 

First, we add the following as the last line of demo1.ctl (this will add a line of data that cannot be loaded to our input):
ABC,XYZ,Hello

Next, we run the following command, to prove that the demo1.bad file does not yet exist:

ops$tkyte@ORA10G> host ls -l demo1.bad
ls: demo1.bad: No such file or directory

Then we query the external table to display the contents:

ops$tkyte@ORA10G> select * from SYS_SQLLDR_X_EXT_DEPT;
 
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 Sales          Virginia
        20 Accounting     Virginia
        30 Consulting     Virginia
        40 Finance        Virginia

Now we will find that the file exists and we can review its contents:

ops$tkyte@ORA10G> host ls -l demo1.bad
-rw-r--r--    1 ora10g   ora10g         14 Jul 17 10:53 demo1.bad
 
ops$tkyte@ORA10G> host cat demo1.bad
ABC,XYZ,Hello

But how can we programmatically inspect these bad records and the log that is generated? Fortunately, that is easy to do by using yet another external table. Suppose we set up this external table:

ops$tkyte@ORA10G> create table et_bad
  2  ( text1 varchar2(4000) ,
  3    text2 varchar2(4000) ,
  4    text3 varchar2(4000)
  5  )
  6  organization external
  7  (type oracle_loader
  8   default directory SYS_SQLLDR_XT_TMPDIR_00000
  9   access parameters
 10   (
 11     records delimited by newline
 12     fields
 13     missing field values are null
 14     ( text1 position(1:4000),
 15       text2 position(4001:8000),
 16       text3 position(8001:12000)
 17     )
 18   )
 19   location ('demo1.bad')
 20  )
 21  /
Table created.

This is just a table that can read any file without failing on a datatype error, as long as the lines in the file consist of fewer than 12,000 characters. If they are longer than 12,000 characters, then we can simply add more text columns to accommodate them.

We can clearly see the rejected records via a simple query:

ops$tkyte@ORA10G> select * from et_bad;
 
TEXT1           TEXT2           TEXT3
--------------- --------------- ---------------
ABC,XYZ,Hello

A COUNT(*) could tell us how many records were rejected. Another external table created on the log file associated with this external table could tell us why the record was rejected. We would need to go one step further to make this a repeatable process, however. The reason is that the bad file is not “blanked” out if there were no errors in our use of the external table. So, if there were some preexisting bad file with data in it and our external table generated no errors, we would be misled into thinking there were errors. 

IÂ’ve taken three approaches in the past to resolve this issue:

    *    Use UTL_FILE and reset the bad file—truncate it, in effect, by simply opening it for write and closing it. 

    *    Use UTL_FILE to rename any preexisting bad files, preserving their contents, but allowing us to create a new one.

    *    Incorporate the PID into the bad (and log) file names. We’ll demonstrate this later in the “Multiuser Issues” section.

In that fashion, weÂ’ll be able to tell if the bad records in the bad file were generated by us just recently or if they were left over from some older version of the file itself and are not meaningful.

[NOTE]
ALTER TABLE T PROJECT COLUMN REFERENCED|ALL

The COUNT(*) earlier in this section made me think about a new feature in Oracle 10g: the ability to optimize external table access by only accessing the fields in the external file that are referenced in the query. That is, if the external table is defined to have 100 number fields, but you select out only one of them, you can direct Oracle to bypass converting the other 99 strings into numbers. It sounds great, but it can cause a different number of rows to be returned from each query. Suppose the external table has 100 lines of data in it. All of the data for column C1 is “valid” and converts to a number. None of the data for column C2 is “valid,” and it does not convert into a number. If you select C1 from that external table, you’ll get 100 rows back. If you select C2 from that external table, you’ll get 0 rows back.  

You have to explicitly enable this optimization, and you should think about whether it is “safe” for you to use or not (only you know enough about your application and its processing to answer the question “Is it safe?”). Using the earlier example with the bad line of data added, we would expect to see the following output upon querying our external table:

ops$tkyte@ORA10G> select dname
  2    from SYS_SQLLDR_X_EXT_DEPT
  3  /
 
DNAME
--------------
Sales
Accounting
Consulting
Finance
 
ops$tkyte@ORA10G> select deptno
  2    from SYS_SQLLDR_X_EXT_DEPT
  3  /
 
    DEPTNO
----------
        10
        20
        30
        40

We know the “bad” record has been logged into the BADFILE. But if we simply ALTER the external table and tell Oracle to only “project” (process) the referenced columns, as follows:

ops$tkyte@ORA10G> alter table SYS_SQLLDR_X_EXT_DEPT
  2  project column referenced
  3  /
 
Table altered.
 
ops$tkyte@ORA10G> select dname
  2    from SYS_SQLLDR_X_EXT_DEPT
  3  /
 
DNAME
--------------
Sales
Accounting
Consulting
Finance
XYZ
 
ops$tkyte@ORA10G> select deptno
  2    from SYS_SQLLDR_X_EXT_DEPT
  3  /
 
    DEPTNO
----------
        10
        20
        30
        40


we get different numbers of rows from each query. The DNAME field was valid for every single record in the input file, but the DEPTNO column was not. If we do not retrieve the DEPTNO column, it does not fail the record—the resultset is materially changed.
</quote> 

Is it possible to do in Sql loader?

A reader, October 08, 2006 - 2:14 am UTC

Hi Tom,
Thanks for your invaluable help to the oracle community. I have requirement to load the flat file, it would having 7 millions lines. Like that we have to load 90 files per day(90*7 million records). The trick is one record is number of x lines in the file, where x is not constant across all the files. Following is the sample file.

RCG 123672 0 0 1 0
OFG 20060701 20060731 LHR MEL LHR MEL GB I F F
ODI 0 LHR MEL EK 00022 F EK 00022 F 1000 2000
ODI 0 MEL LHR EK 00456 F EK 00456 F 2230 0255
GFS AU 7 21 26 16.64 15.08 0.67 0.77 0.9 0.87 0.1 1.1 2.1 0.33 0.31
GFD AU 22 40.2964 42.4656 20.1232 17.9568 18.1562 4.0373
GFD AU 23 42.6445 43.8285 21.1232 19.3555 19.4857 5.0643
GFD AU 24 40.1754 43.8285 22.1232 21.3584 22.0587 7.0893
GFD C 22 ? 32.9852 20.1232 ? 18.1562 4.0373
GFD C 23 ? 36.4555 21.1232 ? 19.4857 5.0643
GFD C 24 ? 36.4555 22.1232 ? 22.0587 7.0893
GFD OC 23 ? 41.5195 ? 0 0 ?
GFD OC 24 ? 40.9195 ? 0 0 ?
GFD PU 23 52.6415 52.1252 20.1232 19.3575 20.3545 5.0373
GFD PU 24 52.6415 52.1252 20.1232 19.3575 25.5407 5.0373
RCG 123672 0 0 1 0
OFG 20060701 20060731 LHR MEL LHR MEL GB I A F
ODI 0 LHR MEL EK 00022 A EK 00022 A 1000 2000
ODI 0 MEL LHR EK 00456 A EK 00456 A 2230 0255
GFS AU 7 21 26 16.64 15.08 0.67 0.77 0.9 0.87 0.1 1.1 2.1 0.33 0.31
GFD AU 22 40.2964 42.4656 20.1232 17.9568 18.1562 4.0373
GFD AU 23 42.6445 43.8285 21.1232 19.3555 19.4857 5.0643
GFD AU 24 40.1754 43.8285 22.1232 21.3584 22.0587 7.0893
GFD C 22 ? 32.9852 20.1232 ? 18.1562 4.0373
GFD C 23 ? 36.4555 21.1232 ? 19.4857 5.0643
GFD C 24 ? 36.4555 22.1232 ? 22.0587 7.0893
GFD OC 23 ? 41.5195 ? 0 0 ?
GFD OC 24 ? 40.9195 ? 0 0 ?
GFD PU 23 52.6415 52.1252 20.1232 19.3575 20.3545 5.0373
GFD PU 24 52.6415 52.1252 20.1232 19.3575 25.5407 5.0373

Each record is separated by the record 'RCG', that is the header for each record. In the above example from the line 'OFG' to 'GFD'(Before the next 'RCG' record starts) is the one record. From each record type I have to take the fixed position values , i.e for OFG - I have take position (5:12),(14,21). For ODI - position (7:9),(11:13) and so on.
I don't want to load 'RCG' record into table.

Is the above requirement can be done through sql loader? Please advise me.


Tom Kyte
October 08, 2006 - 7:36 am UTC

explain how they can be one record, yet you use "fixed positions" - giving us "non-fixed positions" when they are concatenated and a varying number of columns??

eg: cannot imagine what the table looks like here.

View on an external table

Igor Drozdov, October 18, 2006 - 8:30 am UTC

Hi,

Views on external tables are different from views on regular tables. They demand granting direct rights on the directory and the external table to non owner user for simple select from this view. Why?

Example (... means "skip").
User A:
creare or replace directory bsqpctrates_dat_dir as ...
create table A.bsq_pct_rates ...
create view A.vw_bsq_pct_rates as select * from A.bsq_pct_rates;
grant read on directory bsqpctrates_dat_dir to B;--(!!)
grant select on A.bsq_pct_rates to B;--(!!)
grant select on A.vw_bsq_pct_rates to B;

And only then user B:
select * from A.vw_bsq_pct_rates;

Thank you,
Igor Drozdov

Tom Kyte
October 18, 2006 - 9:28 am UTC

has nothing to do with views.

has everything to do with external tables.

remove the view from your example entirely:

ops$tkyte%ORA10GR2> create or replace directory data_dir as '/tmp/'
  2  /

Directory created.

ops$tkyte%ORA10GR2> create table external_table
  2  (EMPNO NUMBER(4) ,
  3   ENAME VARCHAR2(10),
  4   JOB VARCHAR2(9),
  5   MGR NUMBER(4),
  6   HIREDATE DATE,
  7   SAL NUMBER(7, 2),
  8   COMM NUMBER(7, 2) ,
  9   DEPTNO NUMBER(2)
 10  )
 11  ORGANIZATION EXTERNAL
 12  ( type oracle_loader
 13    default directory data_dir
 14    access parameters
 15    ( fields terminated by ',' )
 16    location ('emp.dat')
 17  )
 18  /

Table created.

ops$tkyte%ORA10GR2> select empno from external_table where rownum < 5;

     EMPNO
----------
      7369
      7499
      7521
      7566

ops$tkyte%ORA10GR2> create user a identified by a;

User created.

ops$tkyte%ORA10GR2> grant create session to a;

Grant succeeded.

ops$tkyte%ORA10GR2> grant select on external_table to a;

Grant succeeded.

ops$tkyte%ORA10GR2> connect a/a
Connected.
a%ORA10GR2> select empno from ops$tkyte.external_table where rownum < 5;
select empno from ops$tkyte.external_table where rownum < 5
                            *
ERROR at line 1:
ORA-06564: object DATA_DIR does not exist



<b>so, it has nothing to do with the view, it is a requirement of the external table itself</b>

In order to read that directory object, you must have read on the directory. 

View on an external table

Igor Drozdov, October 18, 2006 - 8:35 am UTC

Oracle 9.2.0.6

View on external table

Igor Drozdov, October 19, 2006 - 1:29 am UTC

But what about
grant select on A.bsq_pct_rates to B;--(!!) ?

With
grant read on directory bsqpctrates_dat_dir to B;--(!!)
and whithout
grant select on A.bsq_pct_rates to B;--(!!)
user B during:
select * from A.vw_bsq_pct_rates;
gets
ORA-04043 object A.bsq_pct_rates does not exist

The paradigm of the "view" is totally broken, mainly because ONLY the owner of the view must have been explicitly granted privileges to access ALL objects referenced in the view definition!

Thanks & Regards
Igor Drozdov





Tom Kyte
October 19, 2006 - 8:16 am UTC

that is your interpretation, but at least now you know how it is actually implemented and that after all is what "counts"

View on an external table

Igor Drozdov, October 20, 2006 - 12:46 am UTC

I am sorry. English is not my native language. What does the phrase [what "counts"] mean in [and that after all is what "counts"]?

Thank you,
Igor Drozdov

Tom Kyte
October 20, 2006 - 4:13 am UTC

that is all that matters.

that is all that is relevant.

View on an external table

Igor Drozdov, October 20, 2006 - 6:43 am UTC

Thank you for your reply.

Regards
Igor Drozdov


cloning database in different os

A reader, October 23, 2006 - 12:12 am UTC

Hi Tom!
thanks for your support.i have a new question.
" my database is on window 2000.now i want to change in window 2003 server. how can i do without exportation and importation ? "

Looking forward your reply.
badaubep







Tom Kyte
October 23, 2006 - 10:01 am UTC

just backup and restore. you do not want to export/import.

A reader, October 23, 2006 - 8:57 pm UTC

Hi Tom!
thanks for your support
" my database is on window 2000.now i want to change in window 2003 server and upgrade oracle 8i-->9i or 10g. how
can i do without exportation and importation ? "

Looking forward your reply.
thanks
badaubep


Tom Kyte
October 24, 2006 - 12:34 am UTC

just upgrade the database using the upgrade tools. read the migration guide that is supplied.

Followup of previous question

A reader, November 07, 2006 - 5:41 am UTC

Hi Tom,
On Oct 8 2006, I had asked you this question and you had asked me to explain further. Sorry, I couldn't send you the reply since I was on leave. Again I am posting the same question here.

Following is the flat file format.

RCG 123672 0 0 1 0
OFG 20060701 20060731 LHR MEL LHR MEL GB I F F
ODI 0 LHR MEL EK 00022 F EK 00022 F 1000 2000
ODI 0 MEL LHR EK 00456 F EK 00456 F 2230 0255
GFS AU 7 21 26 16.64 15.08 0.67 0.77 0.9 0.87 0.1 1.1 2.1 0.33 0.31
GFD AU 22 40.2964 42.4656 20.1232 17.9568 18.1562 4.0373
GFD AU 23 42.6445 43.8285 21.1232 19.3555 19.4857 5.0643
GFD AU 24 40.1754 43.8285 22.1232 21.3584 22.0587 7.0893
GFD AU 22 ? 32.9852 20.1232 ? 18.1562 4.0373
GFD AU 23 ? 36.4555 21.1232 ? 19.4857 5.0643
RCG 123672 0 0 1 0
OFG 20060701 20060731 LHR MEL LHR MEL GB I A F
ODI 0 LHR MEL EK 00022 A EK 00022 A 1000 2000
ODI 0 MEL LHR EK 00456 A EK 00456 A 2230 0255
GFS AU 7 21 26 16.64 15.08 0.67 0.77 0.9 0.87 0.1 1.1 2.1 0.33 0.31
GFD AU 22 40.2964 42.4656 20.1232 17.9568 18.1562 4.0373
GFD AU 23 42.6445 43.8285 21.1232 19.3555 19.4857 5.0643
GFD AU 24 40.1754 43.8285 22.1232 21.3584 22.0587 7.0893
GFD AU 22 ? 32.9852 20.1232 ? 18.1562 4.0373
GFD AU 23 ? 36.4555 21.1232 ? 19.4857 5.0643

Each set of lines is separated by the record 'RCG', that is
considered to be one record to oracle table. In the above example from the line 'OFG' to 'GFD'(Before the next 'RCG'
record starts) is the one record in oracle table. From each record type I have to take the
fixed position values

Columns in the table would be like this
DEPT_DT1,DEPT_DT2,ONLINE_ORGN,ONLINE_DSTN,
FLT_NO1,FLT_NO2,FX_DAYZERO_BKG_QTY,F0_DCP1_BKG_QTY,
F0_DCP1_BKG_QTY,F0_DCP2_BKG_QTY,F0_DCP3_BKG_QTY,
F0_DCP4_BKG_QTY,F0_DCP5_BKG_QTY

Values for these columns are
DEPT_DT1 - Record type OFG - Position (5:12)
DEPT_DT2 - Record type OFG - Position (14:21)
ONLINE_ORGN - Record type OFG - Position (19:21)
ONLINE_DSTN - Record type OFG - Position (23:25)

There will be always two ODI records for a set.

FLT_NO1 - Record type ODI(1st Rec)- Position (18:22)
FLT_NO2 - Record type ODI(2nd Rec)- Position (18:22)

There will be only one GFS record for a set.
FX_DAYZERO_BKG_QTY - Record type GFS - Position (16:20)

There will be maximum of 5 GFD records for a set. But it may vary from file to file.

F0_DCP1_BKG_QTY - Record type GFD(1st) - Position (11:17)
F0_DCP2_BKG_QTY - Record type GFD(2nd) - Position (11:17)
F0_DCP3_BKG_QTY - Record type GFD(3rd) - Position (11:17)
F0_DCP4_BKG_QTY - Record type GFD(4th) - Position (11:17)
F0_DCP5_BKG_QTY - Record type GFD(5th) - Position (11:17)

For e.g, if the file have only 3 GFD, we have to fill only three f0_dcp_bkg_qty column and other two columns(F0_DCP4_BKG_QTY,F0_DCP5_BKG_QTY) are empty.

Could you explain , whether this can be achievable using sqlloader. If so, please provide me a example.



Waiting for your reply

A reader, November 08, 2006 - 11:58 pm UTC

Hi Tom,
I am waiting for your reply. Kindly answer my above question , whether it can be done through sqlloader.

Tom Kyte
November 09, 2006 - 8:28 am UTC

use continueif to assemble your logical single record from the N number of physical records in the file.

continueif lets you say "keep appending input lines of data until...."

Followup of continueif logic

A reader, November 15, 2006 - 2:10 am UTC

Hi Tom,
Thanks for your wonderful idea.

Using the continueif I had done my requirement. But it can done using trigger only, which is time consuming.
The continueif statments provides me the full set as one record. Within the record , I have to take only few elements from each record type for different columns. What I had done is , I had created one extra column to put the entire record set from sqlldr. And in the before insert trigger I had procsssed the entire set using the newly created column and assigned the values for all other columns.

Can this be done without trigger?



Tom Kyte
November 15, 2006 - 7:05 am UTC

I don't know why a trigger is involved at all. you know where the columns are, at least you seemed to in the original example.

Convert from Table to Text File

Ron, November 15, 2006 - 5:20 pm UTC

Tom...

I need to convert an Oracle Table into text format (comma seperated or tab seperated). how do we do it ?..

I can write a SQL script, spool it and run that looks like
----------------------------------------
sql> select accounts || ' ' || report_date || ' '|| acct_type ||' '||receipts||' '||expenditures||' '||pack_decimal from treasury;
-----------------------------------------

but iam looking more for an output in the above format i was mentioning.

any help is apprecited..

Thanks




Tom Kyte
November 16, 2006 - 2:54 pm UTC

Exporting numeric fields in COMP/COMP-3 format to be used by COBOL

Venkataramesh K, December 05, 2006 - 4:11 am UTC

Hi Tom,

I have my database in Oracle 10g. I need to export data to a flat file which i am able to do successfully. My proc takes the query as input and does it. But the exported file will be used by a cobol program thru a copy book.
Now the copy book has some of the numberic fields as COMP and COMP-3 data types. How do i export the data from oracle to be suitable for COBOL.

Thanks is advance,
Ramesh

Tom Kyte
December 05, 2006 - 10:00 pm UTC

did you search this site for comp-3?

</code> http://asktom.oracle.com/pls/ask/search?p_string=%22comp-3%22 <code>

ORA-06564 and best practices

Duke Ganote, January 03, 2007 - 5:39 pm UTC

I was surprised to see that even a user with SELECT ANY TABLE privileges needs explicit grants on a directory while reading an external table:

dwdev1:CIDW\cidwview> select count(*) from catdm.ic129_parser; -- an external table
select count(*) from catdm.ic129_parser
*
ERROR at line 1:
ORA-06564: object CAT_DIR does not exist

dwdev1:CIDW\cidw> grant read, write on directory cat_dir to cidwview;

There doesn't seem to be a SELECT ANY DIRECTORY privilege as such. Do privileges need to be granted on individual directories?

I'm trying to think through any best practices on granting access on external tables generally. We've typically allowed a few select users to read the staging tables, but now I'm about to introduce external tables into our production database. Do you have any recommendations?
Tom Kyte
January 05, 2007 - 8:55 am UTC

you want to AVOID the "any" privileges like the plague - always. If there is a fined grained privilege that could be granted, grant that - NOT THE ANY privilege. Far too much destruction could be done otherwise.


grant the select and grant the directory access as needed, use roles if you want for end users (not developers writing stored procedures, there you want a direct grant of course)

External table, best practices: segregate log file directory?

Duke Ganote, January 05, 2007 - 10:58 am UTC

During the development process, log files are created by default in the same directory as the flat file. In a production database, I'd speculate that one should either
(a) set NOLOGFILE, NOBADFILE, etc. after the data warehouse load is completed
(b) create the log, etc files in a separate directory from the source directory.
For the first option, when looking at ALTER external TABLE options http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#i1007591
all the access parameters are part of the so-called opaque_format_spec
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7002.htm
which I'd have to store and modify as text.
Tom Kyte
January 06, 2007 - 8:21 am UTC

in all environments (development should mirror test and production, less issues as you go from environment to environment that way) - having separate directories would be a good idea. You don't necessarily want read/write on the directory containing the INPUTS - putting the logs off to the side makes sense.

and if you do in development what you intend to do in "real life", you'll have no need to modify anything.

Which is the best option for simply to load data into the table?

Gowtham Sen, April 18, 2007 - 12:36 pm UTC

Hi,

I am getting files of 30 GB to 50 GB.
So, I am using external tables to load data into the tables, without performating any operations.

So, in that case, would use of sqlloader imporves performance?
Which is the best option among these two.

Please suggest me.

Thank you,
Regards,

Gowtham Sen
Tom Kyte
April 18, 2007 - 12:56 pm UTC

create table as select or insert /*+ append */ into an existing table would be logically equivalent to direct path sqlldr

through a parallel in there

maybe nologging the table first

and you have a parallel direct path load without logging - using a simple single DML/DDL statement.

You don't need sqlldr to do that.

difference of external tables and SQl loader

Naveen Kumar, April 20, 2007 - 5:59 am UTC

for exrternal tables a single discard and bad file is generated for all the input files that you are using while loading multiple files at a time.Where as sql loader will generate multiple discard and bad files for each input files.

And also u cant have primary or any Referntial integrity on external tables.

and also u cant have clob,blob columns in your external tables
Tom Kyte
April 20, 2007 - 7:13 am UTC

and also YOU cannot have any primary or referential integrity constraints on files used by sqlldr either

who is this "U" guy, seems like they have problems...

Neha Verma, May 10, 2007 - 6:27 am UTC

SQL Loader is a good option avialable for importing the data.

Tom,

I want to import data from MS-Access to Oracle 8i. Is there any way to access the MS-Access data through some Oracle command or query? Is it possible to have stored Procedure doing the Export from MS-Access and Importing into Oracle? I want to have a scheduler, doing this activity.

SQLLDR / External tables / OCI

Phil, June 05, 2007 - 3:42 am UTC

Hi Tom,

Quick question for you, more architectural than code but related to the above. Telecom client wish to add call data (lots of it) to a CDR table. We want to product real(ish) time analytics on this table but want the data to come in fast and unaffected performance-wise. Was thinking of partitioned table with a refresh from the temp partition into the main CDR each minute but what is your first impression? If they used a flat file for example, how would we process this without affecting the data still being added to it? I assume we could not without interrupting load from the telephony side. This is very early days so I don't have all the information but would like to be pointed in vaguely the right direction so we don't waste time etc.
Regards
Phil

Tom Kyte
June 06, 2007 - 12:55 pm UTC

we are very good at reading and writing the same segments at the same time (non-blocking reads and all)

unless and until you identify factually that "we cannot do it", I would say "just load em up and query them"

Data typecasting while loading from flat files

Dheeraj, June 13, 2007 - 5:48 am UTC

Hi,

I have a requirement where-in I am loading a huge no. of records from flat file into the DB using SQLLDR or External table.
My question is:

Since each column of my record would have different data type (VARCHAR2, NUMBER, DATE etc), is it prudent to make all my staging table/external table columns as VARCHAR2 data types while loading so that data dump happens faster OR should I create staging table/external table with column data types same as what is expected from flat files so that invalid records are rejected & I do post-load processing only with the correct records ?

Issue with the former approach is that post-load processing could happen for incorrect records as well leading to delay in overall process...because at the end of process, these incorrect records are goind to be filtered-out.

Thanks in advance,

Dheeraj
Tom Kyte
June 13, 2007 - 8:14 am UTC

which approach would YOU like to take - it is a decision you need to make, there is no universal 'best' way.

Your requirements will dictate what approach you take.

Data typecasting while loading from flat files

Dheeraj, June 13, 2007 - 10:35 am UTC

Thats true, Tom. But the background I am coming from is -

Assuming this to be a part of ETL process where-in fair no. of transformations happen on the dumped records, will it be fair to waste effort in doing transformations for invalid records and rejecting them at a later stage, when we could have actually rejected them @ dump stage itself ?

Do we have any analysis or example to support each feature ? Some benchmarks like:

i) If there are costly transformations/data typecasting in almost every column of dumped record then use approach X else use Y

ii) If x% percentage of records are generally expected to be invalid from flat file then use approach X else approach Y?

Any help will be highly appreciated.

Cheers!!!
Tom Kyte
June 13, 2007 - 2:23 pm UTC

... ill it be fair to waste effort in doing transformations for invalid records and rejecting them at a later stage, ..


i don't know, what do you think?


It is purely 100% a matter of how you want to process this data. Do you want them rejected into a bad file OR do you want to see them, touch them and log them yourself?

You decide. Rejecting them before they get to your code will likely be more efficient - but now you'll have records in a bad file AND failed records you yourself saved. Will it be fair to waste effort in having to process the failed records from two different sources?

How to create a excel or csv file from Oracle using external files

Kandy Train, June 20, 2007 - 12:22 pm UTC

Hi Tom,

I can create a external file with the extension of .dmp

But How can I make it a excel file so that end users can read the data outside the database??

Thanks,

Kandy Train
Tom Kyte
June 20, 2007 - 1:26 pm UTC

just name it ".csv" and excel will be quite happy with it.

or generate .html and same will be true...

or generate .sylk and same.... (search site for owa_sylk)

Creating a excel file from external table

Kandy Trian, June 20, 2007 - 1:57 pm UTC

Hi Tom,

Thanks for the response. I created a csv file instead of a dmp file. Now excel can open it. But, the format doesn't look good with other characters and html like tags and all that. How could I see these data like a table??

I mean is there a straight forward way than using owa_sylk which uses utl_file_dir method and pl/sql programming??

Thanks for the response,

Kandy Train
Tom Kyte
June 20, 2007 - 2:22 pm UTC

html works...

html works??

Kandy Train, June 20, 2007 - 5:14 pm UTC

Hi Tom,

I have created the file from external table as .html and still it has those characters when I open it as a html(Web page) and opening the same file from a excel is still the same, I mean it doesn't look like a normal excel file but with some characters and tags.

This is how I created the file,

CREATE TABLE all_objects_html
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY xtern_data_dir
LOCATION ( 'all_objects_xt.html' )
)
AS
SELECT *
FROM all_objects;

Thanks for your time and response,

Kandy Train
Tom Kyte
June 20, 2007 - 5:18 pm UTC

umm, well - you have to create an html file.

that create table will create precisely one type of file: a data pump file. period.

Anything else will be done via java stored procedures, utl_file, mod_plsql running a plsql routine.

I never dreamed you would try to create HTML that way. That didn't even enter my thought process ;)

Creating a excel file from Oracle

Kandy Train, June 20, 2007 - 5:31 pm UTC

Hi Tom,

Thanks for the response. I love external tables and always starts from there. My requirement is to dump data to an excel work book. It should dump different tables (Or data sets) into different sheets and put all those into a one excel work book.

I would like to hear if any one has attempted to something like this.

I think i will have to use Java inside the database to do this.

Thanks,

Kandy Train
Tom Kyte
June 21, 2007 - 10:17 am UTC

you might be able to use simple html - but you would best be served by lurking in a microsoft help forum to get help on creating a file format that excel can deal with...

Another CSV option - APEX

David S, June 20, 2007 - 9:31 pm UTC

If you're interested in producing CSV and/or XML output, you should take a look at Application Express (ApEx). It's a great tool (this site was built with it), and very easy to use for development. Using ApEx, a user could select the proper data and download it immediately into Excel - ApEx is beautiful for this.

JExcelApi to Create a Workbook from Oracle

Kandy Train, June 21, 2007 - 10:46 am UTC

Hi,

Thanks for all your time and effort helping me.

I found your article, http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4406709207206 where it mention about JExcelApi program found in
http://www.andykhan.com/jexcelapi/

I have never used Java before, but in this case I got to use it. My question is, Could I install this Api into Oracle and use it?? If the answer is yes, How could I do it?? Because I have never used Java before, It would be great to see the steps that I should use in order to install this api and then use it.

Thanks,

Kandy Train

Creating Excel Workbook from PL/SQL

Kandy Train, June 28, 2007 - 5:55 pm UTC

Hi,

I just wanted to update the case that Jason Bennett ( http://radio.weblogs.com/0137094/2006/10/26.html ) has a exmaple that create a workbook with multiple sheets from PL/SQL. I used it and it works. So, I didn't have to go through Java for this.

Thanks,

Kandy Train

Interrupting high insert table

Phil, July 06, 2007 - 5:40 am UTC

Hi Tom
One last post before we prototype (I promise).
In your reply to a posting above:

Followup June 6, 2007 - 12pm US/Eastern:

we are very good at reading and writing the same segments at the same time (non-blocking reads and all)

unless and until you identify factually that "we cannot do it", I would say "just load em up and query them"


The client is still concerned. Would it be of any benefit to get them to use streams or something else to keep this table that they are inserting to at high rates somewhat isolated? The table will need some indexes which will reduce insert performance and they are naturally worried that if we produce aggregate queries on it while the inserts are occurring that performance will be affected.


Tom Kyte
July 06, 2007 - 12:54 pm UTC

how would streams help?

you insert fast into table T
you mine the logs for inserts into T and replicate them into T'
you have to query now T' which is a table undergoing high insertion rates

and streams has to replicate this excessively high insertion rate.
and your original source system will have to content for access to the online redo logs for real time apply - OR we'll mine the archives downstream, but think about "log jams" as you continuously insert....

this client would move the problem from T to T' and make is worse. Consider that :)

Variable lengths

A reader, July 09, 2007 - 8:35 pm UTC

Is there a way to load a file with variable lengths/formats in SQL Loader?
eg something like:

a,b,c,d
a,b
c
ab,c,d,d,e

(and no - the first set of characters are not the line lengths).
Tom Kyte
July 09, 2007 - 9:01 pm UTC

sure, but it would depend entirely on what you mean by "load it"

give us a table create and what that input file should result in and describe the logic behind it all.

A reader, July 09, 2007 - 10:01 pm UTC

Here goes:

create table t(
key varchar2(100), --the first column, the key for the row
col varchar2(100), -- the column position in th e row
value varchar2(100), -- the value in the column
row_cnt number); --the row identifier


File:

a,b,c,d
a,b
c
ab,c,d,d,e

The loaded table should look like:

The first column is the key for the row.

Key|col|value|row_cnt
a|1|a|1
a|2|b|1
a|3|c|1
a|4|d|1
a|1|a|2
a|2|b|2
c|1|c|3
ab|1|ab|4
ab|2|c|4
ab|3|d|4
ab|4|d|4
ab|5|e|4

Tom Kyte
July 10, 2007 - 11:46 am UTC

you absolutely lost me.

you have given ZERO logic here, no clue how to automagically transform your input.

I could probably guess (pretty sure i could) but I hate guessing.

and if my guess is correct, we'll be using an external table and SQL to do this load, not sqlldr.

To A Reader

Michel CADOT, July 10, 2007 - 2:27 am UTC


create table t_ext (line varchar2(2000))
organization external (
   type oracle_loader
   default directory work_dir
   access parameters (
      records delimited by newline
      nobadfile
      nologfile
      nodiscardfile
      fields terminated by '##################'
      missing field values are null
      (line)
      )
   location ('data.txt') 
   )
reject limit unlimited
/

SQL> with 
  2    cols as (select level col from dual connect by level <= 100),
  3    data as (select ','||line||',' line, rownum row_cnt from t_ext where rownum > 0)
  4  select substr(line, 2, instr(line,',',2)-2) key,
  5         col,
  6         substr(line, 
  7                instr(line,',',1,col)+1,
  8                instr(line,',',1,col+1)-instr(line,',',1,col)-1) value,
  9         row_cnt
 10  from data, cols
 11  where col < length(line)-length(replace(line,',',''))
 12  order by key, row_cnt, col
 13  /
KEY       COL VALUE ROW_CNT
----- ------- ----- -------
a           1 a           1
a           2 b           1
a           3 c           1
a           4 d           1
a           1 a           2
a           2 b           2
ab          1 ab          4
ab          2 c           4
ab          3 d           4
ab          4 d           4
ab          5 e           4
c           1 c           3

12 rows selected.

Regards
Michel

To A Reader

Michel CADOT, July 10, 2007 - 3:17 am UTC


If you know the maximum number of columns, for instance 10, then it is faster to use:
create table t_ext (
  key  varchar2(10),
  col1 varchar2(10),
  col2 varchar2(10),
  col3 varchar2(10),
  col4 varchar2(10),
  col5 varchar2(10),
  col6 varchar2(10),
  col7 varchar2(10),
  col8 varchar2(10),
  col9 varchar2(10)
)
organization external (
   type oracle_loader
   default directory work_dir
   access parameters (
      records delimited by newline
      nobadfile
      nologfile
      nodiscardfile
      fields terminated by ','
      missing field values are null
      (key,col1,col2,col3,col4,col5,col6,col7,col8,col9)
      )
   location ('data.txt') 
   )
reject limit unlimited
/

SQL> with
  2    cols as (select level col from dual connect by level < 11),
  3    data as (
  4      select key, col1, col2, col3, col4, col5, col6, col7, col8, col9, rownum row_cnt
  5      from t_ext
  6      where rownum > 0
  7    )
  8  select key, col,
  9         case when col = 1 then key
 10              when col = 2 then col1
 11              when col = 3 then col2
 12              when col = 4 then col3
 13              when col = 5 then col4
 14              when col = 6 then col5
 15              when col = 7 then col6
 16              when col = 8 then col7
 17              when col = 9 then col8
 18              when col = 10 then col9
 19         end value,
 20         row_cnt
 21  from data, cols
 22  where col < case when col1 is null then 2
 23                   when col2 is null then 3
 24                   when col3 is null then 4
 25                   when col4 is null then 5
 26                   when col5 is null then 6
 27                   when col6 is null then 7
 28                   when col7 is null then 8
 29                   when col8 is null then 9
 30                   when col9 is null then 10
 31                   else 11
 32              end
 33  order by key, row_cnt, col
 34  /
KEY       COL VALUE ROW_CNT
----- ------- ----- -------
a           1 a           1
a           2 b           1
a           3 c           1
a           4 d           1
a           1 a           2
a           2 b           2
ab          1 ab          4
ab          2 c           4
ab          3 d           4
ab          4 d           4
ab          5 e           4
c           1 c           3

12 rows selected.

Regards
Michel

A reader, July 10, 2007 - 6:26 pm UTC

Thanks!

How to find to which path it directing ?

A reader, November 06, 2007 - 12:58 pm UTC

Tom,
Is there a way to find the path of the directory.?
For ex: DBA created one directory called 'data_dir' sometime back.
Now I need to see what was the path used while creating that directory.

Regards
Chandra Matta
Tom Kyte
November 06, 2007 - 3:12 pm UTC

select * from dba_directories;

Direct path

Juel, July 06, 2008 - 8:10 am UTC

ON 10g (10.2.0.3)

Now the sqlloader w. direct path runs directly on the server (Solaris).

Could we run the sqloader job w. direct path from "anywhere" - ie a windows client (also 10g) or from another Unix/AIX server?

Someone meant that in order for direct path to work it needed to be on the very same sever - I cant find that info anywhere in the docs

regards
Tom Kyte
July 07, 2008 - 11:55 am UTC

someone is frequently wrong.

as they are here.

the requirement is that the sqlldr client is the same release as the server if you are pre 9i. that is all.


http://docs.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_modes.htm#sthref1449

(it would be very easy to test - you just need to use user/pass@DATABASE - connect over the listener, even on a single machine - to see it on the network)

Back to the spfile question (Pauline)

John A., August 25, 2008 - 10:05 am UTC

Hey Tom,

I "think" this qualifies as a current thread, but if not I apologize and you can delete at your pleasure.

Pauline from NY asked a question (back in 2003) about the spfile causing a core dump, which is what I'm experiencing. There are absolutely no trace files or any output in the alert log. The only thing I see is the ORA-03113 on startup. If I startup using pfile, I have no problem. For the record, the spfile was generated from the pfile immediately prior to shutdown, so it should be a duplicate.

When I startup using spfile, I see the aforementioned error on my command line. Checking the alert.log there is no new entry. No new traces in bdump or udump. There is, however, a lovely 245M core dump in the $ORACLE_HOME/dbs.

Starting up with pfile works like a charm, with no errors generated and seemingly innocuous traces in the alert.log. (Most of which mention Hash Bucket Statistics, and one which mentions failing as follows:

/oracle/admin/db_name/bdump/sid_lgwr_3675.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /oracle/product/10.2.0
System name: SunOS
Node name: server_name
Release: 5.10
Version: Generic_137111-05
Machine: sun4u
Instance name: instance_name
Redo thread mounted by this instance: 1
Oracle process number: 7
Unix process pid: 3675, image: username@server_name (LGWR)

*** 2008-08-25 07:37:46.914
*** SERVICE NAME:() 2008-08-25 07:37:46.914
*** SESSION ID:(5500.1) 2008-08-25 07:37:46.914
LGWR: Archivelog for thread 1 sequence 2224 will NOT be compressed
tkcrrsarc: (WARN) Failed to find ARCH for message (message:0x1)
tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0x1)
Maximum redo generation record size = 156160 bytes
Maximum redo generation change vector size = 150676 bytes
tkcrrsarc: (WARN) Failed to find ARCH for message (message:0x10)
tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0x10)

The previous DBA was trying to setup DataGuard but had problems, so we're not currently sending ARCH messages anywhere. I'm aware we're a bit behind on our patches, but so far nobody has been able to remember what the CSI is. (Old DBA took it with him when he left, I guess.)

Any ideas? (Again, if this doesn't qualify under Pauline's post, then you can delete me.)

Regardless, thanks for taking the time to read this!
Tom Kyte
August 26, 2008 - 8:45 pm UTC

please utilize support

if you

startup with pfile
create spfile from that pfile
shutdown
startup


it should be OK, but I don't see you doing that here - you state you did, but I don't see a cut&paste, so I cannot be sure.

RAC and parallel load

Bauer Wolfgang, August 27, 2008 - 6:50 am UTC

Hi Tom,

are there any drawbacks when using external tables, RAC and parallel load?
Our DBAs are not sure if there might be any problems when using RAC (2 Nodes on RH Linux / Oracle 10.2.0.3) and both instances access the same files.
We tested it and it seems to work perfect but is there anything we have to take care of? For example the logfiles. If an error occurs (rejected rows) both instances might wright into the same file at the same time.

The two server/Instances have access to the flatfiles over an nfs mount.

Regards
Wolfgang
Tom Kyte
August 27, 2008 - 9:54 am UTC

you need to have access to the file on all nodes that want to read it - nfs will "do", but you could use a clustered file system like ocfs for example as well.

with the generated logs, you just need to make sure they are going where you want them to go, they could go to local file systems - we would not care (eg: if both nodes have a /log/foo/bar device of their own - that'll work, or if /log/foo/bar is a shared disk - that'll work)

Thanks

Bauer Wolfgang, August 27, 2008 - 10:58 am UTC

Thanks Tom.
So apart from resisting the logs in different directories there is nothing "dangerous" from loading a table in parallel using RAC?

We will initial load a 1 TB online database from files from our mainfraim in about 2 month - I will report the results.
Tom Kyte
August 29, 2008 - 11:47 am UTC

bear in mind, "regular" NFS might not be the most concurrent, responsive input method - but not any more or less in RAC than non-RAC (eg: are you able to get the read input rates you would like to get....)

You'll need really good network response and a really fast NFS server in order to do this. Remember, as node 1 is loading and node 2 is loading, they'll be hitting that same resource - the single resource. Might look for a way to direct attach that storage unless the nfs is something like a netapp server.

ERROR IN DIRECTORY

BISWARANJAN, September 21, 2008 - 12:19 pm UTC

HELLO TOM , I HAVE CREATED A DIRECTORY IN A PARTICULAR LOCATION USING GTHE CREATE DIRECTORY OPTION , BUT WHEN I AM GOING TO THAT PARTICULAR LOCATION I AM NOE SEEING ANY WITH THAT NAME(WHEN OPENING THE CORRESPONDING IN WINDOS) .
SO CAN U PLEASE GUIDE ME HOW TO DO THIS?
Tom Kyte
September 21, 2008 - 2:25 pm UTC

AND YOU WON'T UNLESS YOU CREATED IT IN WINDOS YOURSELF

create directory 'my_dir' as '\foo\bar\whatever'

simply tells Oracle - when you see 'MY_DIR' referenced, we are really talking about the OS directory \foo\bar\whatever. It is a mapping feature, a level of indirection, instead of hard coding a path, you give us a name and a path, you can change the path without changing the name whenever you want.

but we do on create any OS directories
we do not change permissions on them

You must have that directory
You must have the permissions at the OS level such that Oracle can see that directory

external table row limit ?

joshuasingham, January 29, 2009 - 12:36 am UTC

hi,

i would just like to check whether there is a row limitation if we use external table say for example there is over few million rows in the flat file will the table be able to load all the data
Tom Kyte
January 30, 2009 - 1:59 pm UTC

there is no documented limit other than those imposed by the file system itself.

Error in SQL Loader

Abhisek, August 11, 2010 - 10:03 am UTC

Hi Tom,

I know you are a big supporter of external table which I prefer now as well, after sucha long discussion with you :)

But still , a question about SQL Loader. I am getting an error:

SQL*Loader-951: Error calling once/load initialization
ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges

In my SQL Loader, I am using DIRECT=TRUE and PARALLEL=TRUE..

Please suggest.
Tom Kyte
August 19, 2010 - 12:11 am UTC

are you trying to direct path load a table in a schema different from the one that is running sqlldr?

if so, they would need some powerful "any" privileges and you'll want to rethink that.

Solved

Abhisek, August 12, 2010 - 9:29 am UTC

hi Tom,

I have solved the problem. The issue was related to granting access to the table to the correct user.

Thanks.

some clarity needed

A reader, August 24, 2010 - 2:50 pm UTC

Hi Tom,

are Oracle data pump access driver and oracle export utility data pump are compatible. Means that if i have a requirement that from one system the data is being export using expdp in parallel fashion(no metadata) say it create 8 files. Can i use these files in oracle external table to get data and insert them in staging area of the warehouse.

Thanks
Tom Kyte
August 26, 2010 - 11:52 am UTC

you can move them to another system and use them by creating a table

for example, say you unloaded all_users to a file allusers.dat using a create table as select and the datapump driver.

You would just move the file to another system and create a table to map it:

ops$tkyte%ORA11GR2> create table all_users_unload_2
  2  (
  3   USERNAME   VARCHAR2(30) ,
  4   USER_ID    NUMBER ,
  5   CREATED    DATE
  6  )
  7  organization external
  8  ( type oracle_datapump
  9    default directory TMP
 10    location( 'allusers.dat' )
 11  )
 12  /

Table created.

ops$tkyte%ORA11GR2> pause

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select *
  2    from all_users_unload_2;

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
FB_DEMO                               187 13-JUL-10
BIG_TABLE                             131 06-NOV-09
....

sql loader and external table

A reader, October 12, 2010 - 10:41 pm UTC

Hi Tom,

I couldn't recollect the benefits of using external table rather than sql loader. Can you please explain why it is faster than SQL loader?

Also if external table loading is faster than sqlldr utility then why we shall not use external table loading always? I mean what are the disadvantages of external table?
Tom Kyte
October 13, 2010 - 6:55 am UTC

... Can you please explain why it is faster than SQL loader? ...

don't think anyone said faster, they (I) have said it is infinitely easier and more flexible.

Want to do a parallel direct path load? Your choices:

a) write a script to fire off and coordinate N copies of sqlplus where N = the degree of parallelism you think about be best

or

b) execute a create table PARALLEL as select - a single command, in a single session, coordinated for you


Want to do a complex transformation? Your choices:

a) fight the somewhat limited sqlldr abilities to perform transformations

or

b) use the entire power of SQL to do anything you can imagine


and so on.

why we shall not use external table loading always?

Indeed, why not? You'd have to tell me - I've said that myself..

I mean what are the disadvantages of external table?

The only drawback would be if the file to be loaded cannot be resident on the server itself. Here sqlldr has an advantage - sqlldr can load over the network - external tables cannot.

Please visit this posting

R Ganesh Ram, October 29, 2010 - 3:22 am UTC

Hi Tom, 
It would be great if you can answer this topic which is more of the external tables vs sqlldr. (real time scenario)

http://forums.oracle.com/forums/thread.jspa?messageID=8455266#8455266

Thanks in advance
Ganesh Ram R

Treat normal xls file

A reader, December 29, 2010 - 8:10 am UTC

Is it possible to treat file with excel features set ,like backcolor and forecolor ,font in Bold as an external file ?

Do I need to get rid of above and make it purely text in order to use it as external table.
Tom Kyte
December 30, 2010 - 1:18 pm UTC

you would need to filter it and make it some set of structured data if you want to process it as a table.

You could/might be able to use the heterogeneous services to use ODBC to query the spreadsheet perhaps.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4406709207206#18830681837358

using the when clause in an external table

steven kladitis, February 22, 2011 - 1:22 pm UTC

I saw you using the when cluase on the external table. I am using 11g and want to know if I can using multiple load when clauses to retrieve data from the external table.

for example there are 3 record types in the data
rec_typ 1 loads data from
c1 position (1:5) char
c2 position ( 60:85 ) char
rec_typ 7
c1 position (2:40) char
c2 position (46:79) char


I can not seem to get the syntax is possible using 11gr1
external tables.

Thanks,
Steven
Tom Kyte
February 23, 2011 - 8:26 pm UTC

do you have an existing control file? just use sqlldr with "external_table=generate_only"

otherwise, I'm not 100% sure what you mean here. I don't know what "rec_typ" is or how it is defined

but if you have a field that is "rec_typ" - you could do this in sql easily. If you map out your rec_typ 1 c1, and c2 as rec1_c1, rec1_c2 and your rec_typ 2 c1 and c2 as rec2_c1, rec2_c2, then you could

select case when rec_typ=1 then rec1_c1 when rec_typ=2 then rec2_c1 end,
.....
from external_table
/

easily (use a view to hide the "complexity")

External Table

A reader, March 23, 2011 - 6:19 am UTC

hi Tom,

Just a curious question.

Suppose the Database server is on UNIX platform and I am using TOAD on windows. Now is it possible to create a external table which accesses the folder created on Windows and the db server is on Unix.

I tried to do so and got this error message:

<code>
KUP-04063: unable to open log file EXT_TAB_24079.log
 OS error No such file or directory


Please suggest
</code>
Tom Kyte
March 23, 2011 - 8:34 am UTC

no, you cannot - it would be very 'virus like' if the database could reach out over the network and automagically connect to your disk drive on your pc, wouldn't it...


You have to make the file available on the database server - networked drives can work (easy under normal OS's like Unix, tricky and obscure under environments such as Windows - but doable)

no external table for asm?

jim, March 29, 2011 - 7:14 pm UTC

Hi, Tom,

I want to try out external table feature but our dba told me that we can't do so because we are using asm. Is it that external is not supported if we use RAC and asm?

Thanks
Tom Kyte
March 30, 2011 - 1:37 am UTC

your DBA is incorrect.

Your database uses ASM, but there would be a regular old file system somewhere out there as well (trace files go somewhere). In 11g, ASM supports a clustered filesystem as well. In 10g and before, you'd be using something for a clustered file system - in 11g and above you'd be using that same something OR asm to provide it.

but you have a regular old file system somewhere.

and even if you didn't - there is still a regular old non-clustered file system somewhere that can be used or even just an NFS mount.

In new Oracle 10g, is it still restriction to use Unix pipe?

Bhasker, April 12, 2011 - 1:13 am UTC

Tom,
You said,
"Followup May 11, 2005 - 7pm Central time zone:

search for pipelined, tons of examples in this site. "
Is this still a limitation with Oracle 10g/9i for Oracle external tables?
Thanks,
Bhasker
Tom Kyte
April 13, 2011 - 9:10 am UTC

the new preprocessor directive, new in 11gr2, allows you to use anything you want as an external table.

Prior to that - the external table must be a regular file available on the operating system of the database server.

replacing sqlldr with external tables

A reader, June 08, 2011 - 11:34 am UTC

we have few control files and we load flat files via sqlldr.
I saw here that there is no performance gain if our idea is just to load several sets of flat files ,currenly loading thr sqlldr. In our case,it doesnt make thus sense to change to
ET method ie.

1. create ext_table using flat file
2/insert into table t
selec * from ext_table

Please clarify.


Tom Kyte
June 08, 2011 - 12:27 pm UTC

you saw where that there is no performance gain?

there is a potential for a gain (parallel create table as select /parallel insert /*+ append */ rocks and easily beats sqlldr for ease of use and in fact performance - since you don't have to split up input files and script a bunch of stuff)


It is up to you - the power and flexibility of external tables might have you re-look at your processing though - if you currently load these flat files and then process the data, you might skip the "load these flat files" altogether and go straight to "process the data"

ET vs sqlldr

A reader, June 09, 2011 - 5:58 am UTC

 You mentioned in your followup post on October 13, 2010  that ET is not much faster than sqlloader (though we have parallel option).

I have another question - currently our sqlldr control files are in different order from the one in table.
ie. the order in control tables is a b c whereas in table column_id sort woud be a c b. If we switch over to ET,we would need to replace '*' with correct columnlist (in order) during insert from external table ,right ?


Tom Kyte
June 09, 2011 - 10:09 am UTC

read the followup again. External tables allow you to do things that would be incredibly hard and inefficient with sqlldr. External tables - if you just use them the same way you use sqlldr - would not necessarily be any faster - but they open the OPPORTUNITY to do things in a much more efficient manner.


I'm not following your second paragraph. Give an example.

example for column header

A reader, June 13, 2011 - 12:38 am UTC

To  give simple example of just 3 columns(actual scenario would have 100+ columns) ..

create table t (empno number(4),name varchar2(10),
sal number(10,2);

sqlldr ctl file could have empno followed by sal and then name.It still loads correctly.

For CTAS from external table , I would need to say 
create table t1 as select  empno,name, sal from ext_table
.

Tom Kyte
June 17, 2011 - 10:55 am UTC

you would have to do as much work in the external table as the sqlldr control file, I'm totally missing your point here.

You would have to list the columns in the order they arrive in the file for sqlldr.

You would have to list the columns in the order they need to be supplied in the insert statement.



and if you use external_table=generate_only with sqlldr, it will give you a create table like this (i mixed up the order of the columns)


CREATE TABLE "SYS_SQLLDR_X_EXT_EMP"
(
  "EMPNO" NUMBER(4),
  "JOB" VARCHAR2(9),
  "ENAME" VARCHAR2(10),
  "HIREDATE" DATE,
  "MGR" NUMBER(4),
  "SAL" NUMBER(7,2),
  "DEPTNO" NUMBER(2),
  "COMM" NUMBER(7,2)
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY MY_DIR
  ACCESS PARAMETERS
....



with an insert that looks like this:

INSERT /*+ append */ INTO EMP
(
  EMPNO,
  JOB,
  ENAME,
  HIREDATE,
  MGR,
  SAL,
  DEPTNO,
  COMM
)
SELECT
  "EMPNO",
  "JOB",
  "ENAME",
  "HIREDATE",
  "MGR",
  "SAL",
  "DEPTNO",
  "COMM"
FROM "SYS_SQLLDR_X_EXT_EMP"



meaning, it does all of the work for you

pisces, March 25, 2015 - 11:59 am UTC

I need some help. We have a 10g db running on linux and i am trying to create an external table reading a .txt file that was placed in the directory created in linux and shared with me on Windows. My statement is as follows:
DROP TABLE ADMINS111 CASCADE CONSTRAINTS;

The dba created the directory nlease and granted me teh permissions

CREATE TABLE ADMINS111
(
EMP_ID number,
ENAME varchar2(20),
DNAME varchar2(20)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY NLEASE
ACCESS PARAMETERS
( records delimited by newline
fields terminated by ','
)
LOCATION (NLEASE:'names.txt')
)
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;

The error i get is the following when trying to access the data in the table through Toad
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file ADMINS111_3228.log
OS error Permission denied
ORA-06512: at "SYS.ORACLE_LOADER", line 19

any option from command line parameters

Rajeshwaran Jeyabal, March 17, 2018 - 5:09 pm UTC

Team,

was playing with SQL*Loader Express mode.

When working with the "optionally_enclosed_by" option from the parameter file it works fine.

D:\>sqlldr demo/demo@ora12c parfile=d:\par.txt

SQL*Loader: Release 12.2.0.1.0 - Production on Sat Mar 17 22:28:41 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-800: SKIP specified and ignored
Express Mode Load, Table: EMP2
Path used:      External Table, DEGREE_OF_PARALLELISM=AUTO
SQL*Loader-816: error creating temporary directory object SYS_SQLLDR_XT_TMPDIR_00000 for file emp2.dat
ORA-01031: insufficient privileges
SQL*Loader-579: switching to direct path for the load
SQL*Loader-583: ignoring trim setting with direct path, using value of LDRTRIM
SQL*Loader-584: ignoring DEGREE_OF_PARALLELISM setting with direct path, using value of NONE
Express Mode Load, Table: EMP2
Path used:      Direct

Load completed - logical record count 14.

Table EMP2:
  14 Rows successfully loaded.

Check the log file:
  emp2.log
for more information about the load.

D:\>

Parameter file contents were this.
D:\>type d:\par.txt
table=emp2 skip=1 date_format="dd-mon-yyyy" optionally_enclosed_by='"'
D:\>


How ever when we give the "optionally_enclosed_by" from the command line - goes into this error.

D:\>sqlldr demo/demo@ora12c table=emp2 skip=1 date_format="dd-mon-yyyy" optionally_enclosed_by='"'

SQL*Loader: Release 12.2.0.1.0 - Production on Sat Mar 17 22:36:54 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-800: SKIP specified and ignored
Express Mode Load, Table: EMP2
Path used:      External Table, DEGREE_OF_PARALLELISM=AUTO
SQL*Loader-816: error creating temporary directory object SYS_SQLLDR_XT_TMPDIR_00000 for file emp2.dat
ORA-01031: insufficient privileges
SQL*Loader-579: switching to direct path for the load
SQL*Loader-583: ignoring trim setting with direct path, using value of LDRTRIM
SQL*Loader-584: ignoring DEGREE_OF_PARALLELISM setting with direct path, using value of NONE
Express Mode Load, Table: EMP2
Path used:      Direct

Load completed - logical record count 14.

Table EMP2:
  0 Rows successfully loaded.

Check the log file:
  emp2.log
for more information about the load.


looking into the sql-loader log file it has this.

Generated control file for possible reuse:
OPTIONS(DIRECT=TRUE)
LOAD DATA
INFILE 'emp2'
APPEND
INTO TABLE EMP2
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY ""
DATE FORMAT "dd-mon-yyyy"
(
  EMPNO,
  ENAME,
  JOB,
  MGR,
  HIREDATE DATE,
  SAL,
  COMM,
  DEPTNO
)
End of generated control file for possible reuse.

Record 1: Rejected - Error on table EMP2, column EMPNO.
second enclosure string not present
Record 2: Rejected - Error on table EMP2, column EMPNO.
second enclosure string not present
Record 3: Rejected - Error on table EMP2, column EMPNO.
second enclosure string not present
Record 4: Rejected - Error on table EMP2, column EMPNO.
second enclosure string not present
Record 5: Rejected - Error on table EMP2, column EMPNO.
second enclosure string not present
Record 6: Rejected - Error on table EMP2, column EMPNO.
second enclosure string not present
Record 7: Rejected - Error on table EMP2, column EMPNO.
second enclosure string not present
Record 8: Rejected - Error on table EMP2, column EMPNO.
second enclosure string not present
Record 9: Rejected - Error on table EMP2, column EMPNO.
second enclosure string not present
Record 10: Rejected - Error on table EMP2, column EMPNO.
second enclosure string not present
Record 11: Rejected - Error on table EMP2, column EMPNO.
second enclosure string not present
Record 12: Rejected - Error on table EMP2, column EMPNO.
second enclosure string not present
Record 13: Rejected - Error on table EMP2, column EMPNO.
second enclosure string not present
Record 14: Rejected - Error on table EMP2, column EMPNO.
second enclosure string not present

Table EMP2:
  0 Rows successfully loaded.
  14 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.

Bind array size not used in direct path.
Column array  rows :    5000
Stream buffer bytes:  256000
Read   buffer bytes: 1048576


though we have provided the optionally_enclosed_by='"' the control file from the sql-loader log file shows this
OPTIONALLY ENCLOSED BY "" - any option to overcome this?
Connor McDonald
March 18, 2018 - 3:40 am UTC

Its due to Windows interpreting the quote.

This should work:

optionally_enclosed_by='\"'

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library