PL/SQL extract part of filename 1

[rogerte]

Hi folks - relatively new to this PL/SQL so please excuse dumb question.

I have to extract data from a text file into a table. That bit is no problem, but one of the columns is a date, and the date is held in the text file name. The text file is named "INPFILE.01.A.YYYYMMDD.TXT" where the YYYYMMDD is the date required. How do I extract that part of the filename into a date variable that can then used as part of the load?

Thanks

Rog

 

[taupirho]

SELECT SUBSTR('INPFILE.01.A.YYYYMMDD.TXT',14,8)
INTO your_date_variable
FROM dual

ought to do it

 

[rogerte]

Tom

Thanks for that - didn't realise you could do a select into a variable - live and learn all the time.

Roger

 

[SantaMufasa]

Roger,

Here are a couple of issues regarding your thread:

I have to extract data from a text file into a table.

1) By what method are you "extracting" date from the text file into the table?

2) The "into" construct that Tom presents works only within the PL/SQL environment.

3) If your target variable is of type DATE, then the SUBSTR expression should instead read:

...[b]to_date([/b]SUBSTR('INPFILE.01.A.YYYYMMDD.TXT',14,8)[b],'YYYYMMDD')[/b]...

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[rogerte]

Mufasa

Thanks for your reply, you are correct I did need the to_date function.

I have written the procedure as a SQL plus package (just following on from the way things seem to have been done here before). However all the SQL Plus documentation appears to have disappeared!

Basically I need to check for the presence of the file. I know it will always start with "INPFILE.01.A." and the variable part will be the YYYYMMDD. I presume there must be some sort of FILE("INPFILE.01.A.????????.TXT" ) function to check if the file can be opened. There will only ever be 1 file of that type in the incoming directory, as it is cleared down on a weekly basis, and the process will be run once a week.
Once the file is opened I presume there must also be a way of reading in the lines. (In the example below I have used a mixture of oracle and from another programming language sorry about that)

I had intended to looping through the file reading in the line and doing something like:
==============
CURSOR curRecordExists is
SELECT * FROM INCOMING_DELIVERY_DATA WHERE
BRANCH_NUMBER = nBranchNum AND
WEEK_STARTING = dWeekStarting AND
DEPOT_NUMBER = nDepotNum

---
if file(("INPFILE.01.A.????????.TXT") then
SELECT to_date(.........) into dWeekStarting

nHandle = fopen(filename)

while not eof(nhandle)
newline = FGetLine(nHandle)
nBranchNum = to_number(substr(newline,1,5))
nDepotNum = to_number(substr(newline,6,5))
nGrns = to_number(substr(newline),11,5)

IF curRecordExists%FOUND THEN
update INCOMING_DELIVERY_DATA
set TOTAL_GRNS = TOTAL_GRNS + nGrns;

commit;
ELSE
insert into INCOMING_DELIVERY_DATA
( WEEK_STARTING,
BRANCH_NUMBER,
DEPOT_NUMBER,
TOTAL_GRNS )
values
( dweekstarting date,
nbranchnum char,
ndepotnum char,
ngrns);

commit;
endwhile
else
.... exceptions

Can you let me know if this approach is correct, and more to the point what the PL\SQL functions are!

Many thanks - sorry to be a PITA but I have been "dumped on from a great height" and am struggling to get out of the goo!

Roger

 

[SantaMufasa]

Roger,

First, please clarify the following:

1) What is the latest installed version of Oracle you have available?

2) What operating system is all of this taking place under?

3) Is the file which name is of the format, "INPFILE.01.A.????????.TXT", always in the same path?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[rogerte]

Mufasa

Thanks for your help

It's Oracle 7, Running on a Windows 2000 Server box.

The input file is always FTP'd to the same directory.

Roger