SQL & PL/SQL

Hallo Everyone,

I’m fairly new (read: about 2 days) to the whole PL/SQL world and I’ve run into a little problem with one of my projects. I’ve googled and looked around the forums and tried to apply what I have picked up but so far without much success.

The general situation is as follows:

I am creating a .bat file which will take data from a comma separated file and “upload” the data into a table of my choosing. However, in the file I’m trying to “upload”, one of the columns contains a time value of the type HH24:MI:SS (for example: 0:05:32). The column sequence in the file matches that of my table and the .bat file.

My standalone table for testing this conversion looks like this:

Table: Conversion_Test

Column Name Data Type
AUTO_ID NUMBER (12)
TIME NUMBER (12)
WORKING VARCHAR2 (32 Byte)
UPLOAD_DATE DATE

The columns AUTO_ID and UPLOAD_DATE are filled by triggers which have been tried, tested and found to be working just fine. My test file contains 1 row of raw data (no headers or other information is present).

I have managed to convert the HH24:MI:SS into seconds through the SQL*Plus interface with the following query:

----------------------------------------------------------------------------------------------------------------------------------------
SELECT
sysdate, to_number(substr('00:05:32',1,2)*3600 + substr('00:05:32',4,2)*60 + substr('00:05:32',7,2))
from dual

Which returns the correct result:

SYSDATE
--------
TO_NUMBER(SUBSTR('00:05:32',1,2)*3600+SUBSTR('00:05:32',4,2)*60+SUBSTR('00:05:32
--------------------------------------------------------------------------------
23-10-08
332

Potential Relevant Content off .bat file (not all of it):

LOAD DATA
INFILE "dat\%date%.dat"
Append
INTO TABLE Conversion_Test
FIELDS TERMINATED BY ';'
(
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX*
Working
)

* XXXXXXXX = Location of relevant statement.

I have tried duplicating the results from my sysdate query with the following statements:

1. Time "to_number(substr('Time',1,2))*3600" + "to_number(substr('Time',4,2))*60" + "to_number(substr('Time',7,2))",

This generates the following error message:

SQL*Loader-350: Syntaxiserror in row 7.
"," of ")" expected, "+" found.
Time "to_number(substr('Time',1,2))*3600" + "to_number(substr('Time' ^


2. Time “to_number(substr('Time',1,2))*3600 + to_number(substr('Time',4,2))*60 + to_number(substr('Time',7,2))",

3. Time "to_number(substr('Time',1,2))*3600 + (substr('Time',4,2))*60 + (substr('Time',7,2))",

These generate the following error message:

Record 1: denied – error in table CONVERSION_TEST, Column TIME.
ORA-01722: invalid number

4. Time "to_number(substr('Time',1,2))*3600 + (substr('Time',4,2))*60 + (substr('Time',7,2)) from dual",

This generates the following error message:

Record 1: denied – error in table CONVERSION_TEST, Column TIME.
ORA-00917: missing comma

Question: What I want to do is convert this HH24:MI:SS “format” into seconds and then put them into a numbers column in my table by running the .bat file.

I hope I have provided enough information to give you a good insight into the problem and potential solutions.

With regards,

Luhaine

Edited by: user10465140 on 23-okt-2008 7:28

Edited by: user10465140 on 23-okt-2008 7:30

Edited by: user10465140 on 23-okt-2008 7:30

Edited by: user10465140 on 23-okt-2008 7:33 - Clearafication and Clean up.