Home » RDBMS Server » Server Utilities » SQL*Loader - wildcard character
SQL*Loader - wildcard character [message #676806] Thu, 18 July 2019 10:03 Go to next message
clockdva
Messages: 8
Registered: July 2019
Junior Member
is it possibile to use a wildcard character to indicate the file?

something like

"load data 
infile 'myfile*'"

to load a file with name 'myfile2001'

another information: i'm working on Linux e my file has no extension, thanks and sorry for my bad english
Re: SQL*Loader - wildcard character [message #676807 is a reply to message #676806] Thu, 18 July 2019 10:45 Go to previous messageGo to next message
Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No, you can't do it but you can do it at shell level and give the infile name on the command line instead of inside the control file.

What is your Oracle version?

Re: SQL*Loader - wildcard character [message #676809 is a reply to message #676807] Thu, 18 July 2019 13:22 Go to previous messageGo to next message
clockdva
Messages: 8
Registered: July 2019
Junior Member
in this way it works but not with a file without extension, thanks

for file in   myfile*
  
   do
                    sqlldr $DB_USER/$DB_PWD@$DB_NAME   CONTROL=test.ctl   data=$file 

 
   done

[Updated on: Thu, 18 July 2019 13:22]

Report message to a moderator

Re: SQL*Loader - wildcard character [message #676810 is a reply to message #676809] Thu, 18 July 2019 13:25 Go to previous messageGo to next message
clockdva
Messages: 8
Registered: July 2019
Junior Member
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE 11.2.0.4.0 Production"
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
Re: SQL*Loader - wildcard character [message #676813 is a reply to message #676809] Thu, 18 July 2019 14:19 Go to previous messageGo to next message
Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
clockdva wrote on Thu, 18 July 2019 20:22
in this way it works but not with a file without extension, thanks
...

Linux does not care about extension, this is not a Unix/Linux notion, dot (".") is like any other characters in the file name for Linux.
So, explain how it does not work.

[Updated on: Thu, 18 July 2019 14:26]

Report message to a moderator

Re: SQL*Loader - wildcard character [message #676814 is a reply to message #676813] Thu, 18 July 2019 14:34 Go to previous messageGo to next message
clockdva
Messages: 8
Registered: July 2019
Junior Member
slq loader log



SQL*Loader: Release 11.2.0.4.0 - Production on Thu Jul 18 20:54:17 2019

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

Control File: test.ctl
Data File: ./test/myfile567.dat
...
SQL*Loader-500: Unable to open file (./test/myfile567.dat)
SQL*Loader-553: file not found



and the name of the file is myfile567

it adds the extension automatically
Re: SQL*Loader - wildcard character [message #676816 is a reply to message #676814] Fri, 19 July 2019 00:19 Go to previous messageGo to next message
Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Ah! OK, forgot that, sorry, this is why it is important to copy and paste what you do and get to show us.
You then have to rename the file (or create a symbolic link) with the extension in your shell loop.

Re: SQL*Loader - wildcard character [message #676817 is a reply to message #676816] Fri, 19 July 2019 02:50 Go to previous messageGo to next message
clockdva
Messages: 8
Registered: July 2019
Junior Member
So the only way to use a file without extension is to rename it or to use a link?
Thanks
Re: SQL*Loader - wildcard character [message #676818 is a reply to message #676817] Fri, 19 July 2019 05:14 Go to previous messageGo to next message
Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In Linux/Unix, yes.
With Windows, you have a workaround.

Re: SQL*Loader - wildcard character [message #676820 is a reply to message #676818] Fri, 19 July 2019 06:26 Go to previous messageGo to next message
clockdva
Messages: 8
Registered: July 2019
Junior Member
thanks

the last question: i think it's impossibile to give some params to the code in the file .ctl; i need something as:

(this is my .ctl file)

load data

REPLACE
into table $MY_TABLE
 ...


where $MY_TABLE is the param that i give from the .sh script

do you confirm that it is impossibile?

i have remedied creating the .ctl file directly in the .sh file:

(this is my .sh file)
readonly SQLLDR_CTL="load data

REPLACE
into table  $MY_TABLE..."

echo "$SQLLDR_CTL" > "file.ctl"

[Updated on: Fri, 19 July 2019 06:27]

Report message to a moderator

Re: SQL*Loader - wildcard character [message #676821 is a reply to message #676820] Fri, 19 July 2019 07:26 Go to previous messageGo to next message
Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
do you confirm that it is impossibile?

I confirm.

Quote:
i have remedied creating the .ctl file directly in the .sh file:

Dynamically creating or modifying the control file at shell level is the way to do it.

Re: SQL*Loader - wildcard character [message #676823 is a reply to message #676821] Fri, 19 July 2019 08:15 Go to previous messageGo to next message
clockdva
Messages: 8
Registered: July 2019
Junior Member
thanks
Re: SQL*Loader - wildcard character [message #676826 is a reply to message #676823] Fri, 19 July 2019 11:09 Go to previous messageGo to next message
BlackSwan
Messages: 26640
Registered: January 2009
Location: SoCal
Senior Member
You could maintain a single static file name used by SQLLDR & manipulate the actual data being loaded by using softlink to point various different OS files
Re: SQL*Loader - wildcard character [message #676835 is a reply to message #676826] Sat, 20 July 2019 10:35 Go to previous message
clockdva
Messages: 8
Registered: July 2019
Junior Member
BlackSwan wrote on Fri, 19 July 2019 11:09
You could maintain a single static file name used by SQLLDR & manipulate the actual data being loaded by using softlink to point various different OS files
thanks
Previous Topic: datapump Export taking long time(HUNG)
Next Topic: oracle solution for sqoop
Goto Forum:
  


Current Time: Mon Dec 09 07:04:56 CST 2019