We help IT Professionals succeed at work.
Get Started

Function in Oracle

Swadhin Ray
Swadhin Ray asked
on
1,104 Views
Last Modified: 2012-08-15
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;

Open 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'
               ));

Open in new window

Comment
Watch Question
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2020
Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE