-1

this command is successfully working but don't know how to insert date in date column if i add date column in table and feed date in my file. any suggestion?

DECLARE
    F UTL_FILE.FILE_TYPE;
    V_LINE VARCHAR2 (1000);
    V_EMPNO NUMBER(4);
    V_ENAME VARCHAR2(10);
    V_JOB VARCHAR2(10);
    V_MGR VARCHAR2(4);
    V_SAL NUMBER(6);
    V_COMM NUMBER(6);
    V_DEPTNO NUMBER(2);
BEGIN
    F := UTL_FILE.FOPEN ('TEMP', 'empup.txt', 'R');
    IF UTL_FILE.IS_OPEN(F) THEN
    LOOP
        BEGIN
            UTL_FILE.GET_LINE(F, V_LINE, 1000);
            IF V_LINE IS NULL THEN
            EXIT;
            END IF;
            V_EMPNO := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 1);
            V_ENAME := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 2);
            V_JOB := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 3);
            V_MGR := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 4);
            V_SAL := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 5);
            V_COMM := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 6);
            V_DEPTNO := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 7);
            INSERT INTO demp VALUES(V_EMPNO, V_ENAME, V_JOB, V_MGR, V_SAL, V_COMM, V_DEPTNO);
            COMMIT;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                EXIT;
        END;
    END LOOP;
    END IF;
    UTL_FILE.FCLOSE(F);
END;
D'Mayal
  • 7
  • 1
  • 6
  • 1
    Extract the data field from line as you did for others data type, and convert that string into date format using TO_DATE('string','format') this will work for sure. – Vivek Jul 19 '18 at 09:18

1 Answers1

1

You need to know the format of dates in your CSV file; say dates are in format yyyy-mm-dd, you need something like:

declare
    V_yourDate  date;
    ...
begin
    ...
    V_yourDate := to_date( REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 8), 'yyyy-mm-dd');
    INSERT INTO demp VALUES(V_EMPNO, V_ENAME, V_JOB, V_MGR, V_SAL, V_COMM, V_DEPTNO, v_yourDate);
    ...
end;

A better way to write the insert is with explicit columns:

insert into table(col1, col2, ...) values (val1, val2, ...)
Aleksej
  • 22,443
  • 5
  • 33
  • 38