Home » Infrastructure » Windows » Use file name to create the table first (Oracle DB 11g Release-2 , windos)
Use file name to create the table first [message #641780] Mon, 24 August 2015 00:59 Go to next message
mokarem
Messages: 109
Registered: November 2013
Location: Dhaka
Senior Member

Hi all,

I load csv file data using below script. After loading I manipulate data using @update.sql file:

sqlldr user/pass@conn control=D:\Load\My_data.ctl log=D:\Load\My_data.log

sqlplus user/pass@conn @update.sql


Now my requirement is before loading csv file data, I have to create the target table dynamically. The table name would be a fixed text and the last 7 characters of file name from which I am loading data. The columns are fixed of that table.

Please help how to get the last 7 characters of file name and pass it to sqlplus so that I can create the table first and then I will load the data in the same batch file.
Re: Use file name to create the table first [message #641781 is a reply to message #641780] Mon, 24 August 2015 01:25 Go to previous messageGo to next message
Littlefoot
Messages: 21647
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:

before loading csv file data, I have to create the target table dynamically


What benefit do you expect from doing it that way?
Re: Use file name to create the table first [message #641782 is a reply to message #641780] Mon, 24 August 2015 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 67644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
how to get the last 7 characters of file name


This is your shell language issue.

Quote:
pass it to sqlplus s


sqlplus usr/psw @myscript.sql parameters

Quote:
so that I can create the table first


myscript.sql contains CREATE TABLE

Quote:
I will load the data in the same batch file.


sqlldr user/pass@conn control=D:\Load\My_data.ctl log=D:\Load\My_data.log data=<your filename>

All this are ONLY a shell issue.

Re: Use file name to create the table first [message #641783 is a reply to message #641781] Mon, 24 August 2015 01:32 Go to previous messageGo to next message
mokarem
Messages: 109
Registered: November 2013
Location: Dhaka
Senior Member

Each time I load the data that should be in a separate table.
Thats why I want to create the table dynamically. Month_Year are concatenated with the file name. So If I can parse this part of file name. I can create this table dynamically.

Re: Use file name to create the table first [message #641784 is a reply to message #641783] Mon, 24 August 2015 01:48 Go to previous messageGo to next message
Littlefoot
Messages: 21647
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
mokarem

Each time I load the data that should be in a separate table.


That doesn't look like a good design, in my opinion.
Re: Use file name to create the table first [message #641785 is a reply to message #641782] Mon, 24 August 2015 01:58 Go to previous messageGo to next message
mokarem
Messages: 109
Registered: November 2013
Location: Dhaka
Senior Member

Thanks All

Using below script I am very close to the solution:

for %%i in (*.csv) do (
set fName = %%i
sqlplus user/pwd@conn @ddl.sql %%i
)


sqlldr user/pwd@conn control=D:\Roche\Roche_Zyto.ctl log=D:\Roche\Roche_Zyto.log

sqlplus user/pwd@conn @update.sql
Re: Use file name to create the table first [message #641786 is a reply to message #641785] Mon, 24 August 2015 02:01 Go to previous message
Michel Cadot
Messages: 67644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Read what I posted.
This is a shell script issue and NOT an Oracle one.
Please find a more appropriate forum.
The topic is locked. /forum/fa/448/0/
If I'm wrong, please, PM me (or report this message to a moderator, explain why you think it should be unlocked and it might be done).


Previous Topic: Alert_orcl.log
Next Topic: J2SE1.7.0_80 plugin wont read
Goto Forum:
  


Current Time: Tue Jan 19 22:46:52 CST 2021