Function in Oracle

Hello Experts,

I have a function where it load the text file from the directory to my target table successfully.
I want to add two logic on to it:
1) Skip the header of the file or ignore the header.
2) The file has to rename like its name concatenate with systimestamp on the directory for the file which was loaded on the target table.

Here is the code :

 

create or replace
FUNCTION       "LOAD_DATA" ( p_table     in varchar2,
                     p_cnames    in varchar2,
                     p_dir       in varchar2,
                     p_filename  in varchar2,
                     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);
begin
    l_input := utl_file.fopen( p_dir, p_filename, 'r' );

    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_data;

Select allOpen in new window

For my second part I want to add something like as below:

    UTL_FILE.FRENAME(
        'ORA_DIR',
        p_filename,
        'ORA_DIR',
        REPLACE(p_filename,
                '.txt',
                '_' || TO_CHAR(SYSDATE, 'DD_MON_RR') || '.txt'
               ));

Select allOpen in new window