OraFAQ Forum: Server Utilities » SQL*Loader

Home » RDBMS Server » Server Utilities » SQL*Loader - wildcard character

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL*Loader - wildcard character [message #676806]

Thu, 18 July 2019 10:03

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

Report message to a moderator

Re: SQL*Loader - wildcard character [message #676807 is a reply to message #676806]

Thu, 18 July 2019 10:45

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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?

Report message to a moderator

Re: SQL*Loader - wildcard character [message #676809 is a reply to message #676807]

Thu, 18 July 2019 13:22

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

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

Report message to a moderator

Re: SQL*Loader - wildcard character [message #676813 is a reply to message #676809]

Thu, 18 July 2019 14:19

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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

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

Report message to a moderator

Re: SQL*Loader - wildcard character [message #676816 is a reply to message #676814]

Fri, 19 July 2019 00:19

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: SQL*Loader - wildcard character [message #676817 is a reply to message #676816]

Fri, 19 July 2019 02:50

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

Report message to a moderator

Re: SQL*Loader - wildcard character [message #676818 is a reply to message #676817]

Fri, 19 July 2019 05:14

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Report message to a moderator

Re: SQL*Loader - wildcard character [message #676820 is a reply to message #676818]

Fri, 19 July 2019 06:26

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

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: SQL*Loader - wildcard character [message #676823 is a reply to message #676821]

Fri, 19 July 2019 08:15

clockdva
Messages: 8
Registered: July 2019

Junior Member

thanks

Report message to a moderator

Re: SQL*Loader - wildcard character [message #676826 is a reply to message #676823]

Fri, 19 July 2019 11:09

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: SQL*Loader - wildcard character [message #676835 is a reply to message #676826]

Sat, 20 July 2019 10:35

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

Report message to a moderator

Previous Topic:	datapump Export taking long time(HUNG)
Next Topic:	oracle solution for sqoop

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 03 17:38:55 CST 2025