Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Norbert.

Asked: November 13, 2019 - 4:30 pm UTC

Last updated: March 14, 2022 - 3:44 am UTC

Version: SQL*Loader: Release 19.0.0.0.0

Viewed 1000+ times

You Asked

Hi TOM,

next Month we are migrating from Oracle 12.1.0.2 to Oracle 19, so I installed the new Oracle Client 19c on my PC.

Using SQLLDR with (all in one line)

sqlldr userid=user/passwd@server
control=\\server\share$\folder\controlfile.ini
data= \\server\share$\folder\datafile.txt
bad= \\server\share$\folder\datafile.bad
log= \\server\share$\folder\datafile.log
ERRORS=0

I get the following Errors :

SQL*Loader-503: Fehler beim Anhõngen der Erweiterung an Datei \\server\share$\folder\controlfile.ini)
SQL*Loader-567: Dateiname kann nicht abgeleitet werden
SQL*Loader-509: Systemfehler: Der Vorgang wurde erfolgreich beendet.


Trying the same with

sqlldr userid=user/passwd@server
control=\\server\share\folder\controlfile.ini
data= \\server\share\folder\datafile.txt
bad= \\server\share\folder\datafile.bad
log= \\server\share\folder\datafile.log
ERRORS=0

the data is imported successfully.

How can I Import data from a fileshare that Ends with a $?

With Oracle Client 12.1 all works fine.


Kind regards
Norbert

and Connor said...

Hmmmm....18c works fine, but 19 does not

C:\>sqlldr userid=scott/tiger@db18_pdb1 control=\\gtx\c$\temp\dept.ctl data=\\gtx\c$\temp\dept.dat log=\\gtx\c$\temp\dept.log  bad=\\gtx\c$\temp\dept.bad errors=0

SQL*Loader: Release 18.0.0.0.0 - Production on Wed Nov 20 13:29:13 2019
Version 18.6.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 3
Commit point reached - logical record count 4

Table DEPT:
  4 Rows successfully loaded.

Check the log file:
  \\gtx\c$\temp\dept.log
for more information about the load.


versus

C:\>sqlldr userid=scott/tiger@db19_pdb1 control=\\gtx\c$\temp\dept.ctl data=\\gtx\c$\temp\dept.dat log=\\gtx\c$\temp\dept.log  bad=\\gtx\c$\temp\dept.bad errors=0

SQL*Loader-503: Error appending extension to file (\\gtx\c$\temp\dept.ctl)
SQL*Loader-567: unable to derive file name
SQL*Loader-509: System error: The operation completed successfully.


A workaround is to have true share names not the admin ones, eg

C:\>sqlldr userid=scott/tiger@db19_pdb1 control=\\gtx\c\temp\dept.ctl data=\\gtx\c\temp\dept.dat log=\\gtx\c\temp\dept.log

SQL*Loader: Release 19.0.0.0.0 - Production on Wed Nov 20 13:37:27 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 3
Commit point reached - logical record count 4

Table DEPT:
  4 Rows successfully loaded.

Check the log file:
  \\gtx\c\temp\dept.log
for more information about the load.


I'm asking around internally to see if there's a reason, or whether its a bug.

Rating

  (2 ratings)

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

Comments

Sounds like a bug

Daniel Overby Hansen, November 20, 2019 - 7:19 am UTC

I experienced the exact same behavior however with the ODP.NET managed driver. Didn't work on hidden shares either in 19c however it did work fine in lower versions. You could check bug 30308079 reported to be fixed in 19.6.0.
Problem was also that it couldn't locate tnsnames.ora and failed with same error message.
Connor McDonald
November 25, 2019 - 12:25 pm UTC

Thanks for that info. I'll keep chasing it up

Following up...

A reader, March 10, 2022 - 10:05 pm UTC

I've just recently migrated our Oracle databases to 19c (v19.14.0).

When I install the 19.3 client (admin option) to my PC, I get the same errors as the OP. Have been searching Oracle MOS notes and the internet at large to see if there's a patch to apply or a newer client that has this fix.

Note: I can run the SQLLDR from the DB itself and it has no issue with admin shares. I'm think the latest server patch addresses the issue but it hasn't been pushed to the clients yet.

Thoughts?
Connor McDonald
March 14, 2022 - 3:44 am UTC

Maybe give the instantclient a go instead? Its at 19.14 now

https://www.oracle.com/au/database/technologies/instant-client/winx64-64-downloads.html

More to Explore

Utilities

All of the database utilities are explained in the Utilities guide.