SQL*Loader-500: Unable to open file

[venkat_kp]

Hi All,

Oracle Enterpise stage Database 10g is giving this error.

SQL*Loader-500: Unable to open file (\\.\pipe\ora.376.390000d.fifo.0)
SQL*Loader-554: error opening file
SQL*Loader-509: System error: The operation completed successfully.
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

Generated Control file is

OPTIONS(DIRECT=TRUE, PARALLEL=TRUE)
LOAD DATA INFILE '\\.\pipe\ora.3036.640000.fifo.0' "FIX 6"
APPEND INTO TABLE EDW.TEST2
(
C1 POSITION(1:5) NULLIF C1 = BLANKS
)


Where is '\\.\pipe\ora.3036.640000.fifo.0 file being created or is it a temp file generated and deleted on the fly. What is the meaning of \\.\pipe\
Any help appreciated.

[narasimha]

Post the errors you get (if any) from your load log file.

[venkat_kp]

DataStage Job 7 Phantom 3200

3 record(s) selected to SELECT list #1.
Program "DSD.OshRun": Line 1306, Unable to unlink the operating system file "DOS_61155_14290". Program "DSD.OshRun": Line 1319, Unable to unlink the operating system file "DSD.OshMonitor_61156_14290".
[2600] Done : DOS /c 'C:\Ascential\DataStage\Engine\bin\NT_OshWrapper.exe RT_SC7\OshExecuter.sh RT_SC7\OshScript.osh \\.\pipe\dstage-RT_SC7-OracleTest -monitorport 13400 -pf RT_SC7\jpfile -input_charset ASCL_MS1252 -output_charset ASCL_MS1252 -string_fields '
DataStage Phantom Finished.

[ray.wurlod]

That's not the sqlldr log file. It's probably a separate issue, or the message here and the message in the sqlldr log file may relate to the two ends of the same named pipe. Please post the sqlldr log file error message as requested, so that your diagnosis can be more accurate.

[venkat_kp]

SQL*Loader: Release 10.2.0.1.0 - Production on Wed Feb 14 16:59:46 2007

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

Control File: ora.376.390000d.0.ctl
Data File: \\.\pipe\ora.376.390000d.fifo.0
File processing option string: "FIX 6"
Bad File: ora.376.390000d.0.log.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: Direct - with parallel option.

Table EDW.TEST2, loaded from every logical record.
Insert option in effect for this table: APPEND

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
C1 1:5 5 CHARACTER
NULL if C1 = BLANKS

SQL*Loader-500: Unable to open file (\\.\pipe\ora.376.390000d.fifo.0)
SQL*Loader-554: error opening file
SQL*Loader-509: System error: The operation completed successfully.
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

Table EDW.TEST2:
0 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: 0
Total logical records rejected: 0
Total logical records discarded: 0
Direct path multithreading optimization is disabled

Run began on Wed Feb 14 16:59:46 2007
Run ended on Wed Feb 14 16:59:56 2007

Elapsed time was: 00:00:09.47
CPU time was: 00:00:00.56

[narasimha]

Looks like the sqlldr is not getting reference to your data file.
Specify your datafile properly, if it is in a different location, specify its path.

[ray.wurlod]

OK they are separate issues. The problem sqlldr is having is that it can not open the file in the .\pipe folder. Verify that the executing user (the sqlldr identity) has sufficient permission to the pipe folder in the project folder and/or write permission to the project folder itself (so that it can create the pipe folder if it needs to).

[venkat_kp]

OK they are separate issues. The problem sqlldr is having is that it can not open the file in the .\pipe folder. Verify that the executing user (the sqlldr identity) has sufficient permission to the pipe folder in the project folder and/or write permission to the project folder itself (so that it can create the pipe folder if it needs to).

Where do I find this .\pipe folder on windows? I have seen in the scrach and projects could not find. Permissions on the C:\Ascential are given to write.

[ray.wurlod]

Where is your project folder? This is determined when the project is created, and can be recovered using the Administrator client - when the project is selected the project folder's pathname is displayed in the status bar. Give it a second or two to refresh.

[lfong]

Ray, what is the APT_ variable name that has to be set to fix this problem?

[ray.wurlod]

There are no magic bullets. There are two problems in this thread, and no indication given anywhere that an environment variable will fix either of them. Why do you believe (hope?) that there is?

The pathname "\\." indicates "current machine". The pathname "\\.\pipe" therefore suggests a (sub)directory called "pipe" on the current machine, presumably in the project directory.

You can use the find command in a UNIX (MKS Toolkit) shell to search for pipe or for ora*fifo*