[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1427
  • Last Modified:

Error while loading the date column into oracle table

Hello Experts,

I have a function which takes the file from the directory and load it into the oracle tables.
It works fine but when there is a date column or timestamp then I am getting error while loading that.  
When we pass the directory , file name and table name . This function will pick the file and load to the table which is been passed from the plsql block.

The code which I am having issue is :

for IREC                       in
  (SELECT COLUMN_NAME , DATA_TYPE FROM USER_TAB_COLUMNS WHERE table_name = P_TABLE
  )
  LOOP

   IF (IREC.DATA_TYPE LIKE 'DATE%') THEN  
        l_cnames:= l_cnames || ',' || 'to_date(' || IREC.COLUMN_NAME || ',' ||'''MM/DD/YYYY HH24:MI:SS'')';    
     
   ELSE
   
         L_CNAMES:= L_CNAMES || ',' || IREC.COLUMN_NAME ;
  end if;  

Open in new window


Below is my function code:


create or replace
function LOAD ( P_TABLE     in varchar2,
                     p_dir       in varchar2,
                     p_filename  in varchar2,
                     p_ignore_headerlines   IN INTEGER DEFAULT 1,
                     p_delimiter in varchar2 default '|' )
return number
/*************************************************************************** 
--    FUNCTION LOAD
--    PURPOSE: Load the flat files i.e. only text files to Oracle
--             tables. 
--    PARAMETERS:
--         P_TABLE
--            Pass name of the table for which import has to be done.
--         P_DIR
--            Name of the directory where the file is been placed. 
--            Note: The grant has to be given for the user to the directory
--                  before executing the function 
--         P_FILENAME
--            The name of the flat file(a text file)
--
**************************************************************************/
is
    l_input         utl_file.file_type;
    l_theCursor     integer default dbms_sql.open_cursor;
    L_BUFFER        varchar2(4000);
    L_LASTLINE      varchar2(4000);
    l_cnames        varchar2(4000);
    l_status        integer;
    l_colCnt        number default 0;
    l_cnt           number default 0;
    l_sep           char(1) default NULL;
    l_errmsg        varchar2(4000);
    v_eof              BOOLEAN := FALSE;
begin
    l_input := utl_file.fopen( p_dir, p_filename, 'r' );
IF p_ignore_headerlines > 0
    THEN
        BEGIN
        FOR i IN 1 .. p_ignore_headerlines
            LOOP
                UTL_FILE.get_line(l_input, l_lastLine);
            END LOOP;
        EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
                v_eof  := TRUE;
        end;
    end if;
   for IREC                       in
  (SELECT COLUMN_NAME , DATA_TYPE FROM USER_TAB_COLUMNS WHERE table_name = P_TABLE
  )
  LOOP

   IF (IREC.DATA_TYPE LIKE 'DATE%') THEN  
        l_cnames:= l_cnames || ',' || 'to_date(' || IREC.COLUMN_NAME || ',' ||'''MM/DD/YYYY HH24:MI:SS'')';    
     
   ELSE
   
         L_CNAMES:= L_CNAMES || ',' || IREC.COLUMN_NAME ;
  end if;  
  end LOOP;
   -- ADDED TO TEST
    DBMS_OUTPUT.PUT_LINE(l_cnames);
    l_cnames:=( LTRIM(l_cnames,','));

    l_buffer := 'insert into ' || p_table || ' values ( ';

    l_colCnt := length(l_cnames)-
                  length(replace(l_cnames,',',''))+1;

    for i in 1 .. l_colCnt
    loop
        l_buffer := l_buffer || l_sep || ':b'||i;
        l_sep    := ',';
    end loop;
    l_buffer := l_buffer || ')';
-- ADDED TO TEST
DBMS_OUTPUT.PUT_LINE(l_buffer);
    dbms_sql.parse(  l_theCursor, l_buffer, dbms_sql.native );


    loop
        begin
            utl_file.get_line( l_input, l_lastLine );
        exception
            when NO_DATA_FOUND then
                exit;
        end;
        l_buffer := l_lastLine || p_delimiter;


        for i in 1 .. l_colCnt
        loop
            dbms_sql.bind_variable( l_theCursor, ':b'||i,
                            substr( l_buffer, 1, 
                            instr(l_buffer,p_delimiter)-1 ) ) ;
            l_buffer := substr( l_buffer, 
                          instr(l_buffer,p_delimiter)+1 );
        end loop;

        begin
            l_status := dbms_sql.execute(l_theCursor);
            l_cnt := l_cnt + 1;
        exception
            when others then
                l_errmsg := sqlerrm;
                insert into badlog ( errm, data, error_date ) 
                values ( l_errmsg, l_lastLine ,systimestamp );
        end;
    end loop;

    dbms_sql.close_cursor(l_theCursor);
    utl_file.fclose( l_input );
	
    UTL_FILE.FRENAME(
        'MY_DIR',
        p_filename,
        'MY_DIR',
        replace(P_FILENAME,
                '.txt',
                '_' || TO_CHAR(SYSDATE, 'DD_MON_RR_HH24_MI_SS_AM') || '.txt'
               ));
	
	commit;

    return L_CNT;
end LOAD;

Open in new window


My question is when any table will have a date or timestamp column then where I can handle
the same on my function or the part which I think is really having some issue for handling this.
0
Swadhin Ray
Asked:
Swadhin Ray
  • 17
  • 12
1 Solution
 
slightwv (䄆 Netminder) Commented:
>>then I am getting error while loading that.  

What is the error?

I see that in the function if the data type is date, you have the TO_CHAR call to convert it into a string.  As long as the formats are the same when you convert it and when you go to load it, there shouldn't be a problem.
0
 
Swadhin RaySenior Technical Engineer Author Commented:
Here is my table structure:

SQL>CREATE TABLE test_tab
  (
    COL1  NUMBER ,
    col2   VARCHAR2(20) unique,
    COL3  NUMBER(1) ,
    COL4   VARCHAR2(1000) ,
    col5 DATE
  );
 
Here is the sample file what I have now:

col1|"col2"|col3|"col4"|col5
136012|"S6O5TO"|True|""|2012-04-06 20:43:00
562838|"SIEZRT"|True|""|2012-04-06 20:43:00
60741|"SIT3WX"|True|""|2012-04-06 20:43:00
 
And here is the error that I am getting :

Error report:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "TEST1.LOAD", line 76
ORA-06512: at line 7
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    
*Action:
0
 
Swadhin RaySenior Technical Engineer Author Commented:
When I changed the date format to 'YYYY-MM-DD HH24:MI:SS' as per the file what I am having then I get the below error :

Error report:
ORA-00913: too many values
ORA-06512: at "SYS.DBMS_SQL", line 1199
ORA-06512: at "TEST1.LOAD", line 84
ORA-06512: at line 7
00913. 00000 -  "too many values"
*Cause:    
*Action:

And while printing the l_cnames and :

   -- ADDED TO TEST
    DBMS_OUTPUT.PUT_LINE(l_cnames);
and
-- ADDED TO TEST
DBMS_OUTPUT.PUT_LINE(l_buffer);

I get the below output which is not correct on the function for inserting the values :

,COL1,COL2,COL3,COL4,to_date(COL5,'YYYY-MM-DD HH24:MI:SS')
insert into test_tab values ( :b1,:b2,:b3,:b4,:b5,:b6)

Open in new window


whereas this should print like below:
COL1,COL2,COL3,COL4,to_date(COL5,'YYYY-MM-DD HH24:MI:SS')
insert into test_tab values ( :b1,:b2,:b3,:b4,:b5)

Open in new window

0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
slightwv (䄆 Netminder) Commented:
>>I get the below output which is not correct on the function for inserting the values :

Looks like you have a logic problem.  Sorry but I don't have time to debug your code for you.

On a quick scan this looks like an oversight:  you still have 'MY_DIR' hard-coded in FRENAME.

I will see if I can get some time later today to see if I can take a look to find the bug.
0
 
Swadhin RaySenior Technical Engineer Author Commented:
now I am getting like below :

COL1,COL2,COL3,COL4,to_date(COL5,'YYYY-MM-DD HH24:MI:SS')
insert into test_tab values ( :b1,:b2,:b3,:b4,:b5,:b6)
0
 
awking00Commented:
I may be missing something and, perhaps a little more explanation might help, but it seems to me you're checking to see if the column is a date and, if so, concatenating the l_cnames with to_date(<that column>). Since it is already a date, the to_date function on a date field will return an error (there is an exception to that which I'll show below). You need to modify that to a to_char function. Also, you mention that "when there is a date column or timestamp" but the code doesn't seem to address the timestamp possibility (i.e. when irec.data_type like 'TIMESTAMP%').

As for the error -
SQL> select * from bday;
BIRTH_DAT
---------
29-FEB-84

SQL> select to_date(birth_date,'mm/dd/yyyy hh24:mi:ss') from bday;
select to_date(birth_date,'mm/dd/yyyy hh24:mi:ss') from bday
               *
ERROR at line 1:
ORA-01843: not a valid month

The exception is
SQL> select to_date(birth_date,'dd-MON-yy') from bday;
TO_DATE(B
---------
29-FEB-84

What it appears you may need -
SQL> select to_char(birth_date,'mm/dd/yyyy hh24:mi:ss') from bday;
TO_CHAR(BIRTH_DATE,
-------------------
02/29/1984 00:00:00
0
 
Swadhin RaySenior Technical Engineer Author Commented:
I modified the code and now I get the print as expected as below:

COL1,COL2,COL3,COL4,to_date(COL5,'YYYY-MM-DD HH24:MI:SS')
insert into test_tab values ( :b1,:b2,:b3,:b4,:b5)

Open in new window


But now I get this error, I have the rights for UTL_FILE  :

Error report:
ORA-29292: file rename operation failed
ORA-06512: at "SYS.UTL_FILE", line 348
ORA-06512: at "SYS.UTL_FILE", line 1290
ORA-06512: at "TEST1.LOAD", line 123
ORA-06512: at line 7
29292. 00000 -  "file rename operation failed"
*Cause:    A file rename attempt was refused by the operating system
           either because the source or destination directory does not
           exist or is inaccessible, or the source file isn't accessible,
           or the destination file exists.
*Action:   Verify that the source file, source directory, and destination
           directory exist and are accessible, and that the destination
           file does not already exist.

Open in new window


Here is the modified code for my function :
create or replace
function LOAD ( P_TABLE     in varchar2,
                     p_dir       in varchar2,
                     p_filename  in varchar2,
                     p_ignore_headerlines   IN INTEGER DEFAULT 1,
                     p_delimiter in varchar2 default '|' )
return number
/*************************************************************************** 
--    FUNCTION LOAD
--    PURPOSE: Load the flat files i.e. only text files to Oracle
--             tables. 
--             This is a generic function which can be used for 
--             importing any text flat files to oracle database.
--    PARAMETERS:
--         P_TABLE
--            Pass name of the table for which import has to be done.
--         P_DIR
--            Name of the directory where the file is been placed. 
--            Note: The grant has to be given for the user to the directory
--                  before executing the function 
--         P_FILENAME
--            The name of the flat file(a text file)
--
**************************************************************************/
is
    l_input         utl_file.file_type;
    l_theCursor     integer default dbms_sql.open_cursor;
    L_BUFFER        varchar2(4000);
    L_LASTLINE      varchar2(4000);
    l_cnames        varchar2(4000);
    l_status        integer;
    l_colCnt        number default 0;
    l_cnt           number default 0;
    l_sep           char(1) default NULL;
    l_errmsg        varchar2(4000);
    v_eof              BOOLEAN := FALSE;
begin
    l_input := utl_file.fopen( p_dir, p_filename, 'r' );
IF p_ignore_headerlines > 0
    THEN
        BEGIN
        FOR i IN 1 .. p_ignore_headerlines
            LOOP
                UTL_FILE.get_line(l_input, l_lastLine);
            END LOOP;
        EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
                v_eof  := TRUE;
        end;
    end if;
   for IREC                       in
  (SELECT COLUMN_NAME , DATA_TYPE FROM USER_TAB_COLUMNS WHERE table_name = P_TABLE
  )
  LOOP
--2012-04-06 20:43:00
   IF (IREC.DATA_TYPE LIKE 'DATE%') THEN  
        l_cnames:= l_cnames || ',' || 'to_char(' || IREC.COLUMN_NAME || ',' ||'''YYYY-MM-DD HH24:MI:SS'')';    
     
   ELSE
   
         L_CNAMES:= L_CNAMES || ',' || IREC.COLUMN_NAME ;
  END IF;  
 end LOOP;

    l_cnames:=( LTRIM(l_cnames,','));
   -- ADDED TO TEST
    DBMS_OUTPUT.PUT_LINE(l_cnames);
    l_buffer := 'insert into ' || p_table || ' values ( ';

    L_COLCNT := LENGTH(L_CNAMES)-
                  LENGTH(REPLACE(L_CNAMES,',',''))+1;
                 -- length(replace(l_cnames,',',''));

    for i in 1 .. l_colCnt
    LOOP
        L_BUFFER := L_BUFFER || L_SEP || ':b'||I;
        L_SEP    := ',';
       
    END LOOP;

    l_buffer := l_buffer || ')';
-- ADDED TO TEST
DBMS_OUTPUT.PUT_LINE(l_buffer);
    dbms_sql.parse(  l_theCursor, l_buffer, dbms_sql.native );


    loop
        begin
            utl_file.get_line( l_input, l_lastLine );
        exception
            when NO_DATA_FOUND then
                exit;
        end;
        l_buffer := l_lastLine || p_delimiter;


        for i in 1 .. l_colCnt
        loop
            dbms_sql.bind_variable( l_theCursor, ':b'||i,
                            substr( l_buffer, 1, 
                            INSTR(L_BUFFER,P_DELIMITER)-1 ) ) ;
                            
            L_BUFFER := SUBSTR( L_BUFFER, 
                          instr(l_buffer,p_delimiter)+1 ); 
        end loop;

        begin
            l_status := dbms_sql.execute(l_theCursor);
            l_cnt := l_cnt + 1;
        exception
            when others then
                l_errmsg := sqlerrm;
                insert into badlog ( errm, data, error_date ) 
                values ( l_errmsg, l_lastLine ,systimestamp );
        end;
    end loop;

    dbms_sql.close_cursor(l_theCursor);
    utl_file.fclose( l_input );
	 UTL_FILE.FRENAME(
        'MY_DIR',
        p_filename,
        'MY_DIR',
        REPLACE(p_filename,
                '.txt',
                '_' || TO_CHAR(SYSDATE, 'DD_MON_RRRR_HH24_MI_SS_AM') || '.txt'
               ));

	commit;

    return L_CNT;
end LOAD;

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Had a couple of minutes.

Didn't we try to talk you ouf of doing this in previous questions and using something like External Tables or sql loader?  It is Sooooooo much easier than writing your own.




First:  Check where you set l_colCnt.  That is why you get :b6.

Now for the rest of the issues:  Why is this a function?  You cannot commit in a function.

You will also have problems when you are binding the variables to what is in l_buffer.  It isn't extracting the individual objects the way you think they are.

Add some dbms_output statements around and you will see what I mean.
0
 
slightwv (䄆 Netminder) Commented:
>>But now I get this error, I have the rights for UTL_FILE  :

MY_DIR is hard-coded.  Is that the correct location for the file?
0
 
Swadhin RaySenior Technical Engineer Author Commented:
@slightwv:

>>First:  Check where you set l_colCnt.  That is why you get :b6.
This is been handled now . I am not getting any issues for this as of now .

>>Now for the rest of the issues:  Why is this a function?  

Yes in my earlier question you and other experts proposed to use external tables but writing own function is the requirement . So cannot do anything on this.

>>MY_DIR is hard-coded.  Is that the correct location for the file?

Yes the file is there in the same directory . and I was able to load the files which are not having any issues when there is no date column , I came across when a date column data was been passed .
0
 
slightwv (䄆 Netminder) Commented:
>>but writing own function is the requirement

The commit cannot exist in a function.  How is this working with no date in the table?

Can you post a working example that I can run?
0
 
Swadhin RaySenior Technical Engineer Author Commented:
For L_COLCNT : I changed it as below which I am getting the correct bind variables :

From
 L_COLCNT := LENGTH(L_CNAMES)-
                 LENGTH(REPLACE(L_CNAMES,',',''))+1;
               
TO

 L_COLCNT := LENGTH(L_CNAMES)-
                          length(replace(l_cnames,',',''));

But I am not sure why I am getting UTL_FILE error.
0
 
Swadhin RaySenior Technical Engineer Author Commented:
Here is the sample function which works for me with commit inside:

http://www.experts-exchange.com/Database/Oracle/Q_27830094.html
0
 
Swadhin RaySenior Technical Engineer Author Commented:
Here you go with the function which load the files successfully if the column is not having any date datatype :

create or replace
FUNCTION       load ( p_table     in varchar2,
                     p_cnames    in varchar2,
                     p_dir       in varchar2,
                     P_FILENAME  in varchar2,
                     p_ignore_headerlines   IN INTEGER DEFAULT 1,
                     p_delimiter in varchar2 default '|' )
return number
is
    l_input         utl_file.file_type;
    l_theCursor     integer default dbms_sql.open_cursor;
    l_buffer        varchar2(4000);
    l_lastLine      varchar2(4000);
    l_status        integer;
    l_colCnt        number default 0;
    l_cnt           number default 0;
    l_sep           char(1) default NULL;
    L_ERRMSG        varchar2(4000);
    v_eof              BOOLEAN := FALSE;
begin
    
    L_INPUT := UTL_FILE.FOPEN( P_DIR, P_FILENAME, 'r' );
    IF p_ignore_headerlines > 0
    THEN
        BEGIN
        FOR i IN 1 .. p_ignore_headerlines
            LOOP
                UTL_FILE.get_line(l_input, l_lastLine);
            END LOOP;
        EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
                v_eof  := TRUE;
        end;
    END IF;
    l_buffer := 'insert into ' || p_table || ' values ( ';
    l_colCnt := length(p_cnames)-
                  length(replace(p_cnames,',',''))+1;

    for i in 1 .. l_colCnt
    loop
        l_buffer := l_buffer || l_sep || ':b'||i;
        l_sep    := ',';
    end loop;
    l_buffer := l_buffer || ')';

    dbms_sql.parse(  l_theCursor, l_buffer, dbms_sql.native );


    loop
        begin
            utl_file.get_line( l_input, l_lastLine );
        exception
            when NO_DATA_FOUND then
                exit;
        end;
        l_buffer := l_lastLine || p_delimiter;


        for i in 1 .. l_colCnt
        loop
            dbms_sql.bind_variable( l_theCursor, ':b'||i,
                            substr( l_buffer, 1, 
                            instr(l_buffer,p_delimiter)-1 ) ) ;
            l_buffer := substr( l_buffer, 
                          instr(l_buffer,p_delimiter)+1 );
        end loop;

        begin
            l_status := dbms_sql.execute(l_theCursor);
            l_cnt := l_cnt + 1;
        exception
            when others then
                l_errmsg := sqlerrm;
                insert into badlog ( errm, data, error_date ) 
                values ( l_errmsg, l_lastLine ,systimestamp );
        end;
    end loop;

    dbms_sql.close_cursor(l_theCursor);
    utl_file.fclose( l_input );
    commit;

    RETURN L_CNT;
end load;

Open in new window


Sample table :

CREATE TABLE test_tab
  (
    COL1  NUMBER ,
    col2   VARCHAR2(20) unique,
    COL3  NUMBER(1) ,
    COL4   VARCHAR2(1000) ,
    col5 VARCHAR2(20)
  );

Open in new window



Sample data:

COL1|COL2|COL3|COL4|COL5
214517|AAADD|True||
562822|CCGAA|True||
343633|KOPSUS|True||

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
>>Here is the sample function which works for me with commit inside:

Apologizes.  It was how I was testing it.

>>Here you go with the function which load the files successfully if the column is not having any date

Here is my complete setup and test run.  It 'completes' but doesn't insert any rows.

What am I supposed to see after running this?

drop TABLE test_tab purge;
CREATE TABLE test_tab
  (
    COL1  NUMBER ,
    col2   VARCHAR2(20) unique,
    COL3  NUMBER(1) ,
    COL4   VARCHAR2(1000) ,
    col5 VARCHAR2(20)
  );




create or replace
FUNCTION       load ( p_table     in varchar2,
                     p_cnames    in varchar2,
                     p_dir       in varchar2,
                     P_FILENAME  in varchar2,
                     p_ignore_headerlines   IN INTEGER DEFAULT 1,
                     p_delimiter in varchar2 default '|' )
return number
is
    l_input         utl_file.file_type;
    l_theCursor     integer default dbms_sql.open_cursor;
    l_buffer        varchar2(4000);
    l_lastLine      varchar2(4000);
    l_status        integer;
    l_colCnt        number default 0;
    l_cnt           number default 0;
    l_sep           char(1) default NULL;
    L_ERRMSG        varchar2(4000);
    v_eof              BOOLEAN := FALSE;
begin
    
    L_INPUT := UTL_FILE.FOPEN( P_DIR, P_FILENAME, 'r' );
    IF p_ignore_headerlines > 0
    THEN
        BEGIN
        FOR i IN 1 .. p_ignore_headerlines
            LOOP
                UTL_FILE.get_line(l_input, l_lastLine);
            END LOOP;
        EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
                v_eof  := TRUE;
        end;
    END IF;
    l_buffer := 'insert into ' || p_table || ' values ( ';
    l_colCnt := length(p_cnames)-
                  length(replace(p_cnames,',',''))+1;

    for i in 1 .. l_colCnt
    loop
        l_buffer := l_buffer || l_sep || ':b'||i;
        l_sep    := ',';
    end loop;
    l_buffer := l_buffer || ')';

    dbms_sql.parse(  l_theCursor, l_buffer, dbms_sql.native );


    loop
        begin
            utl_file.get_line( l_input, l_lastLine );
        exception
            when NO_DATA_FOUND then
                exit;
        end;
        l_buffer := l_lastLine || p_delimiter;


        for i in 1 .. l_colCnt
        loop
            dbms_sql.bind_variable( l_theCursor, ':b'||i,
                            substr( l_buffer, 1, 
                            instr(l_buffer,p_delimiter)-1 ) ) ;
            l_buffer := substr( l_buffer, 
                          instr(l_buffer,p_delimiter)+1 );
        end loop;

        begin
            l_status := dbms_sql.execute(l_theCursor);
            l_cnt := l_cnt + 1;
        exception
            when others then
                l_errmsg := sqlerrm;
                --insert into badlog ( errm, data, error_date ) 
                --values ( l_errmsg, l_lastLine ,systimestamp );
        end;
    end loop;

    dbms_sql.close_cursor(l_theCursor);
    utl_file.fclose( l_input );
    commit;

    RETURN L_CNT;
end load;
/



declare
	junk number;
begin
	junk := load('TEST_TAB','COL1,COL2,COL3,COL4,COL5','MY_DIR','q.txt',1,'|');
	dbms_output.put_line('Got: ' || junk);
end;
/

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
I added a debug output line in the exception handler and the code above that you say 'works' for you, for me produces an "ORA-01722: invalid number" each time it tries to execute: l_status := dbms_sql.execute(l_theCursor);
0
 
slightwv (䄆 Netminder) Commented:
I found the problem:  You can COL3 in the table as a number.  The data has it as a varchar2.

Let me continue looking at this.
0
 
Swadhin RaySenior Technical Engineer Author Commented:
Here is the test for my code:

Function code where we do not have to pass the column names which will be picked up dynamically :
create or replace
function       LOAD ( P_TABLE     in varchar2,
                     --p_cnames    in varchar2,
                     p_dir       in varchar2,
                     P_FILENAME  in varchar2,
                     p_ignore_headerlines   IN INTEGER DEFAULT 1,
                     p_delimiter in varchar2 default '|' )
return number
is
    l_input         utl_file.file_type;
    l_theCursor     integer default dbms_sql.open_cursor;
    l_buffer        varchar2(4000);
    L_LASTLINE      varchar2(4000);
    l_cnames        varchar2(4000);
    l_status        integer;
    l_colCnt        number default 0;
    l_cnt           number default 0;
    l_sep           char(1) default NULL;
    L_ERRMSG        varchar2(4000);
    v_eof              BOOLEAN := FALSE;
begin
    
    L_INPUT := UTL_FILE.FOPEN( P_DIR, P_FILENAME, 'r' );
    IF p_ignore_headerlines > 0
    THEN
        BEGIN
        FOR i IN 1 .. p_ignore_headerlines
            LOOP
                UTL_FILE.get_line(l_input, l_lastLine);
            END LOOP;
        EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
                V_EOF  := true;
        end;
    end if;
     FOR IREC IN
  (SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE table_name = P_TABLE
  )
  LOOP
    l_cnames:= l_cnames || ',' || IREC.COLUMN_NAME ;
  end LOOP;
  
  
  
  l_cnames:=( LTRIM(l_cnames,','));
    
    l_buffer := 'insert into ' || p_table || ' values ( ';
    l_colCnt := length(l_cnames)-
                  length(replace(l_cnames,',',''))+1;

    for i in 1 .. l_colCnt
    loop
        l_buffer := l_buffer || l_sep || ':b'||i;
        l_sep    := ',';
    end loop;
    l_buffer := l_buffer || ')';

    dbms_sql.parse(  l_theCursor, l_buffer, dbms_sql.native );


    loop
        begin
            utl_file.get_line( l_input, l_lastLine );
        exception
            when NO_DATA_FOUND then
                exit;
        end;
        l_buffer := l_lastLine || p_delimiter;


        for i in 1 .. l_colCnt
        loop
            dbms_sql.bind_variable( l_theCursor, ':b'||i,
                            substr( l_buffer, 1, 
                            instr(l_buffer,p_delimiter)-1 ) ) ;
            l_buffer := substr( l_buffer, 
                          instr(l_buffer,p_delimiter)+1 );
        end loop;

        begin
            l_status := dbms_sql.execute(l_theCursor);
            l_cnt := l_cnt + 1;
        exception
            when others then
                l_errmsg := sqlerrm;
                insert into badlog ( errm, data, error_date ) 
                values ( l_errmsg, l_lastLine ,systimestamp );
        end;
    end loop;

    dbms_sql.close_cursor(l_theCursor);
    utl_file.fclose( l_input );
    commit;

    RETURN L_CNT;
end load;

Open in new window


Create Table :

CREATE TABLE test_tab
  (
    COL1  NUMBER ,
    col2   VARCHAR2(20) unique,
    COL3  NUMBER(1) ,
    COL4   VARCHAR2(1000) ,
    col5 VARCHAR2(20)
  );

Open in new window


Test data (save this as test.txt file):

COL1|COL2|COL3|COL4|COL5
136012|"S6O5TO"|True|""|
562838|"SIEZRT"|True|""|
214517|AAADD|True||
562822|CCGAA|True||
343633|KOPSUS|True||

Open in new window


PLSQL Block:
DECLARE
  P_TABLE    VARCHAR2(200);
  P_CNAMES   VARCHAR2(200);
  P_DIR      VARCHAR2(200);
  P_FILENAME VARCHAR2(200);
  v_Return   NUMBER;
BEGIN
  V_RETURN := load(
  P_TABLE => 'TEST_TAB',
  P_DIR => 'MY_DIR',
  P_FILENAME => 'test.txt'
    );
end;

Open in new window


This works perfectly for me.

Can you rerun the plsql block as I am using and check again ?

As here I am also facing two issues on :
Row Delimiter      CrLf
Text Qualifier      "

For which I have raise another question :

http://www.experts-exchange.com/Database/Oracle/Q_27841512.html
0
 
slightwv (䄆 Netminder) Commented:
>>Can you rerun the plsql block as I am using and check again ?

I found the problem and what you posted as 'working' cannot.  COL3 in the table is NUMBER(1).  In the text file it is "True".

I have fixed that and am working on the date issue.
0
 
slightwv (䄆 Netminder) Commented:
While I was working on this for you I see a future fatal flaw in your design.

What happens when the input date formats change?

Today it is: 2003-01-02 20:01:02
Next month:  01/02/2003 08:01:02 PM

Also: What if there are two or more date columns in the file each with it's own format?
0
 
slightwv (䄆 Netminder) Commented:
Here is what I have for what I was provided.

It is only for DATE right now and only for YYYY-MM-DD HH24:MI:SS format.

drop TABLE test_tab purge;
CREATE TABLE test_tab
  (
    COL1  NUMBER ,
    col2   VARCHAR2(20) unique,
    COL3  varchar2(20),
    COL4   VARCHAR2(1000) ,
    col5 date
  );




create or replace
FUNCTION       load ( p_table     in varchar2,
                     p_dir       in varchar2,
                     P_FILENAME  in varchar2,
                     p_ignore_headerlines   IN INTEGER DEFAULT 1,
                     p_delimiter in varchar2 default '|' )
return number
is
    l_input         utl_file.file_type;
    l_theCursor     integer default dbms_sql.open_cursor;
    l_buffer        varchar2(4000);
    l_lastLine      varchar2(4000);
    l_cnames        varchar2(4000);
    l_bindvars      varchar2(4000);
    l_status        integer;
    l_cnt           number default 0;
    l_rowCount      number default 0;
    l_sep           char(1) default NULL;
    L_ERRMSG        varchar2(4000);
    v_eof              BOOLEAN := FALSE;
begin

	l_cnt := 1;
	for tab_columns in (select column_name, data_type from user_tab_columns where table_name=p_table order by column_id) loop
		l_cnames := l_cnames || tab_columns.column_name || ',';
        l_bindvars := l_bindvars || case when tab_columns.data_type = 'DATE' then 'to_date(:b' || l_cnt || ',''YYYY-MM-DD HH24:MI:SS'')' else ':b'|| l_cnt || ',' end;
        
	l_cnt := l_cnt + 1;
	end loop;
	l_cnames := rtrim(l_cnames,',');
	l_bindvars := rtrim(l_bindvars,',');

	dbms_output.put_line('l_cnames: ' || l_cnames);
	dbms_output.put_line('l_bindvars: ' || l_bindvars);
    
    L_INPUT := UTL_FILE.FOPEN( P_DIR, P_FILENAME, 'r' );
    IF p_ignore_headerlines > 0
    THEN
        BEGIN
        FOR i IN 1 .. p_ignore_headerlines
            LOOP
                UTL_FILE.get_line(l_input, l_lastLine);
            END LOOP;
        EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
                v_eof  := TRUE;
        end;
    END IF;

    l_buffer := 'insert into ' || p_table || '(' || l_cnames || ') values (' || l_bindvars || ')';
    dbms_sql.parse(  l_theCursor, l_buffer, dbms_sql.native );

    loop
        begin
            utl_file.get_line( l_input, l_lastLine );
        exception
            when NO_DATA_FOUND then
                exit;
        end;

        l_buffer := l_lastLine || p_delimiter;

        for i in 1 .. l_cnt-1
        loop
            dbms_sql.bind_variable( l_theCursor, ':b'||i,
                            substr( l_buffer, 1, 
                            instr(l_buffer,p_delimiter)-1 ) ) ;
            l_buffer := substr( l_buffer, 
                          instr(l_buffer,p_delimiter)+1 );
        end loop;

        begin
            l_status := dbms_sql.execute(l_theCursor);
            l_rowCount := l_rowCount + 1;
        exception
            when others then
                l_errmsg := sqlerrm;
                --dbms_output.put_line('OOPS: ' || l_errmsg);
                insert into badlog ( errm, data, error_date ) 
                values ( l_errmsg, l_lastLine ,systimestamp );
        end;
    end loop;

    dbms_sql.close_cursor(l_theCursor);
    utl_file.fclose( l_input );
    commit;

    RETURN L_ROWCOUNT;
end load;
/


show errors

declare
	junk number;
begin
	junk := load('TEST_TAB','MY_DIR','q.txt',1,'|');
	dbms_output.put_line('Got: ' || junk);
end;
/

select * from test_tab;

Open in new window

0
 
Swadhin RaySenior Technical Engineer Author Commented:
>>Today it is: 2003-01-02 20:01:02
>>Next month:  01/02/2003 08:01:02 PM

Only this format will come "2003-01-02 20:01:02" no PM or AM (this is fixed)


>>Also: What if there are two or more date columns in the file each with it's own format?
True , this is possible as other tables may have multiple date columns.

Will this logic not work :

create table TEMP_DATE
(COL1 number ,
COL2 date ,
COL3 varchar2(10),
col4 date );

DECLARE
  P_TABLE  VARCHAR2(30):='TEMP_DATE';
  L_COLCNT NUMBER DEFAULT 0;
  l_cnames VARCHAR2(4000);
BEGIN
  for IREC in
  (SELECT COLUMN_NAME ,data_type FROM USER_TAB_COLUMNS WHERE table_name = P_TABLE
  )
  LOOP
     if (irec.data_type like 'TIMESTAMP%' OR irec.data_type like 'DATE%') THEN  
        l_cnames:= l_cnames || ',' || 'to_date(' || IREC.COLUMN_NAME || ',' ||'''MM/DD/YYYY HH12:MI:SS:AM'')';    
       
   ELSE    
         L_CNAMES:= L_CNAMES || ',' || IREC.COLUMN_NAME ;
         end if;
  end LOOP;
  l_cnames:=( LTRIM(l_cnames,','));
  DBMS_OUTPUT.PUT_LINE(l_cnames);
END;
0
 
slightwv (䄆 Netminder) Commented:
>>True , this is possible as other tables may have multiple date columns.

As long as the format is fixed, what I posted should work.
0
 
slightwv (䄆 Netminder) Commented:
Small bug fix and added TIMESTAMP:

...
l_bindvars := l_bindvars || case when tab_columns.data_type in ('DATE', 'TIMESTAMP(6)') then 'to_date(:b' || l_cnt || ',''YYYY-MM-DD HH24:MI:SS''),' else ':b'|| l_cnt || ',' end;
...
0
 
Swadhin RaySenior Technical Engineer Author Commented:
I am getting error when I load this sample file :

col1|"col2"|col3|"col4"|col5
136012|"S6O5TO"|True|""|2012-04-06 20:43:00
562838|"SIEZRT"|True|""|2012-04-06 20:43:00
60741|"SIT3WX"|True|""|2012-04-06 20:43:00
484186|"S5PDEI"|True|""|2012-04-06 20:43:00
635235|"SI8CB4"|True|""|2012-04-06 20:43:00
484186|"S5PDEI"|True|""|2012-04-06 20:43:00
635235|"SI8CB4"|True|""|2012-04-06 20:43:00
57166|"SI2HS6"|True|""|2012-04-06 20:43:00
71100|"S2IE5F"|True|""|2012-05-03 20:13:00
61720|"SIU60T"|True|""|2012-04-06 20:43:00
567200|"S93BJS"|True|""|2012-04-06 20:43:00
49995|"SIT5EP"|True|""|2012-04-06 20:43:00
35468|"SIT5HV"|True|""|2012-04-06 20:43:00
621043|"S8HYM1"|True|""|2012-04-06 20:43:00
173112|"SI2QLC"|True|""|2012-04-06 20:43:00
484908|"S3V15J"|True|""|2012-04-06 20:43:00

Open in new window


Table structure:
CREATE TABLE test_tab
  (
    col1 NUMBER ,
    col2   VARCHAR2(20) NOT NULL,
    col3   VARCHAR2(20),
    col4   VARCHAR2(1000) ,
    col5 DATE
  );

Open in new window


Error message:
ORA-01861: literal does not match format string

But the function created successfully.
0
 
slightwv (䄆 Netminder) Commented:
It ran for me as-is.  I got all 16 rows.

I'm using Oracle version 10.2.0.3.

Might be an NLS date issue.  What are your NLS parameters?
0
 
slightwv (䄆 Netminder) Commented:
Also, which code are you running?

I just tested using the latest code I posted in your other question.

Here it is:

create or replace
FUNCTION       load ( p_table     in varchar2,
                     p_dir       in varchar2,
                     P_FILENAME  in varchar2,
                     p_ignore_headerlines   IN INTEGER DEFAULT 1,
                     p_delimiter in varchar2 default '|',
                     p_optional_enclosed in varchar2 default '"' )
return number
is
    l_input         utl_file.file_type;
    l_theCursor     integer default dbms_sql.open_cursor;
    l_buffer        varchar2(4000);
    l_lastLine      varchar2(4000);
    l_cnames        varchar2(4000);
    l_bindvars      varchar2(4000);
    l_status        integer;
    l_cnt           number default 0;
    l_rowCount      number default 0;
    l_sep           char(1) default NULL;
    L_ERRMSG        varchar2(4000);
    v_eof              BOOLEAN := FALSE;
begin

	l_cnt := 1;
	for tab_columns in (
	select column_name, data_type from user_tab_columns where table_name=p_table order by column_id
	) loop
		l_cnames := l_cnames || tab_columns.column_name || ',';
        l_bindvars := l_bindvars || case when tab_columns.data_type in ('DATE', 'TIMESTAMP(6)') then 'to_date(:b' || l_cnt || ',''YYYY-MM-DD HH24:MI:SS''),' else ':b'|| l_cnt || ',' end;
        
	l_cnt := l_cnt + 1;
	end loop;
	l_cnames := rtrim(l_cnames,',');
	l_bindvars := rtrim(l_bindvars,',');

	dbms_output.put_line('l_cnames: ' || l_cnames);
	dbms_output.put_line('l_bindvars: ' || l_bindvars);
    
    L_INPUT := UTL_FILE.FOPEN( P_DIR, P_FILENAME, 'r' );
    IF p_ignore_headerlines > 0
    THEN
        BEGIN
        FOR i IN 1 .. p_ignore_headerlines
            LOOP
                UTL_FILE.get_line(l_input, l_lastLine);
            END LOOP;
        EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
                v_eof  := TRUE;
        end;
    END IF;

    l_buffer := 'insert into ' || p_table || '(' || l_cnames || ') values (' || l_bindvars || ')';
    dbms_sql.parse(  l_theCursor, l_buffer, dbms_sql.native );

    loop
        begin
            utl_file.get_line( l_input, l_lastLine );
        exception
            when NO_DATA_FOUND then
                exit;
        end;

        if length(l_lastLine) > 0 then

        	l_buffer := l_lastLine || p_delimiter;

        	for i in 1 .. l_cnt-1
        	loop
            	dbms_sql.bind_variable( l_theCursor, ':b'||i,
                            	ltrim(rtrim(substr( l_buffer, 1, 
                            	instr(l_buffer,p_delimiter)-1 ),p_optional_enclosed),p_optional_enclosed) ) ;
            	l_buffer := substr( l_buffer, 
                          	instr(l_buffer,p_delimiter)+1 );
        	end loop;

        	begin
            	l_status := dbms_sql.execute(l_theCursor);
            	l_rowCount := l_rowCount + 1;
        	exception
            	when others then
                	l_errmsg := sqlerrm;
                	dbms_output.put_line('OOPS: ' || l_errmsg);
                	--insert into badlog ( errm, data, error_date ) 
                	--values ( l_errmsg, l_lastLine ,systimestamp );
        	end;
        end if;
    end loop;

    dbms_sql.close_cursor(l_theCursor);
    utl_file.fclose( l_input );
    commit;

    RETURN L_ROWCOUNT;
end load;
/


show errors

declare
	junk number;
begin
	junk := load('TEST_TAB','MY_DIR','q.txt',1,'|','"');
	dbms_output.put_line('Got: ' || junk);
end;
/

select * from test_tab;

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Another bug you will have:  If the optional enclosed text has one of your delimiters in it.

For example:
col1|"col2"|col3|"col4"|col5
136012|"S6O|5TO"|True|""|2012-04-06 20:43:00

Here is an updated version.

Main change: regexp_substr to parse the row.

I also added a check if the file is empty.  You set v_eof when skipping the header rows but never checked it.  It doesn't cause an error but why do the parse if you don't need to and why set v_eof if you never use it.

create or replace
FUNCTION       load ( p_table     in varchar2,
                     p_dir       in varchar2,
                     P_FILENAME  in varchar2,
                     p_ignore_headerlines   IN INTEGER DEFAULT 1,
                     p_delimiter in varchar2 default '|',
                     p_optional_enclosed in varchar2 default '"' )
return number
is
    l_input         utl_file.file_type;
    l_theCursor     integer default dbms_sql.open_cursor;
    l_lastLine      varchar2(4000);
    l_cnames        varchar2(4000);
    l_bindvars      varchar2(4000);
    l_status        integer;
    l_cnt           number default 0;
    l_rowCount      number default 0;
    l_sep           char(1) default NULL;
    L_ERRMSG        varchar2(4000);
    v_eof              BOOLEAN := FALSE;
begin

	l_cnt := 1;
	for tab_columns in (
	select column_name, data_type from user_tab_columns where table_name=p_table order by column_id
	) loop
		l_cnames := l_cnames || tab_columns.column_name || ',';
        l_bindvars := l_bindvars || case when tab_columns.data_type in ('DATE', 'TIMESTAMP(6)') then 'to_date(:b' || l_cnt || ',''YYYY-MM-DD HH24:MI:SS''),' else ':b'|| l_cnt || ',' end;
        
	l_cnt := l_cnt + 1;
	end loop;
	l_cnames := rtrim(l_cnames,',');
	l_bindvars := rtrim(l_bindvars,',');

	dbms_output.put_line('l_cnames: ' || l_cnames);
	dbms_output.put_line('l_bindvars: ' || l_bindvars);
    
    L_INPUT := UTL_FILE.FOPEN( P_DIR, P_FILENAME, 'r' );
    IF p_ignore_headerlines > 0
    THEN
        BEGIN
        FOR i IN 1 .. p_ignore_headerlines
            LOOP
                UTL_FILE.get_line(l_input, l_lastLine);
            END LOOP;
        EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
                v_eof  := TRUE;
        end;
    END IF;
    
    if not v_eof then
    	dbms_sql.parse(  l_theCursor, 'insert into ' || p_table || '(' || l_cnames || ') values (' || l_bindvars || ')', dbms_sql.native );

    	loop
        	begin
            	utl_file.get_line( l_input, l_lastLine );
        	exception
            	when NO_DATA_FOUND then
                	exit;
        	end;

        	if length(l_lastLine) > 0 then
        		for i in 1 .. l_cnt-1
        		loop
            		--dbms_output.put_line(':b'||i || ':' ||  regexp_substr(l_lastLine,'([^|]*)(\||$)',1,i));
            		dbms_sql.bind_variable( l_theCursor, ':b'||i,
            			ltrim(rtrim(rtrim(
            				regexp_substr(l_lastLine,'([^|]*)(\||$)',1,i),p_delimiter),p_optional_enclosed),p_optional_enclosed));
        		end loop;

        		begin
            		l_status := dbms_sql.execute(l_theCursor);
            		l_rowCount := l_rowCount + 1;
        		exception
            		when others then
                		l_errmsg := sqlerrm;
                		dbms_output.put_line('OOPS: ' || l_errmsg);
                		--insert into badlog ( errm, data, error_date ) 
                		--values ( l_errmsg, l_lastLine ,systimestamp );
        		end;
        	end if;
    	end loop;

    	dbms_sql.close_cursor(l_theCursor);
    	utl_file.fclose( l_input );
    	commit;
    end if;

    RETURN L_ROWCOUNT;
end load;
/


show errors

declare
	junk number;
begin
	junk := load('TEST_TAB','MY_DIR','q.txt',1,'|','"');
	dbms_output.put_line('Got: ' || junk);
end;
/

select * from test_tab;

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Also note I didn't create the badlog table on my side so some of my code has the insert into it commented out and an OOPS dbms_output in it's place.
0
 
Swadhin RaySenior Technical Engineer Author Commented:
Thanks a lot ......
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 17
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now