Skip to Main Content

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Connor McDonald

Thanks for the question, Norbert.

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

Answered by: Connor McDonald - Last updated: November 25, 2019 - 12:25 pm UTC

Category: SQL - Version: SQL*Loader: Release 19.0.0.0.0

Viewed 100+ times

Whilst you are here, check out some content from the AskTom team: Hybrid histograms

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 we 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.

and you rated our response

  (1 rating)

Reviews

Sounds like a bug

November 20, 2019 - 7:19 am UTC

Reviewer: Daniel Overby Hansen

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

Followup  

November 25, 2019 - 12:25 pm UTC

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

More to Explore

Utilities

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