SQL*Loader-500: Unable to open file
Moderators: chulett, rschirm, roy
SQL*Loader-500: Unable to open file
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.
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.
Output from the &ph& folder
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
SQL*Loader log output
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
ray.wurlod wrote: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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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*
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*
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.