Function in Oracle

Posted on 2012-08-15
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
    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);
    l_input := utl_file.fopen( p_dir, p_filename, 'r' );

    l_buffer := 'insert into ' || p_table || ' values ( ';
    l_colCnt := length(p_cnames)-

    for i in 1 .. l_colCnt
        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 );

            utl_file.get_line( l_input, l_lastLine );
            when NO_DATA_FOUND then
        l_buffer := l_lastLine || p_delimiter;

        for i in 1 .. l_colCnt
            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;

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

    utl_file.fclose( l_input );

end load_data;

Open in new window

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

                '_' || TO_CHAR(SYSDATE, 'DD_MON_RR') || '.txt'

Open in new window

Question by:Swadhin Ray
    LVL 76

    Accepted Solution

    >>1) Skip the header of the file or ignore the header.

    Do an initial get_line before the loop to read the header then just don't do anything with it.

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

    Does that not work?  On a quick scan it looks OK.
    LVL 16

    Author Comment

    by:Swadhin Ray
    Thanks slightwv, my first part is done now and getting the correct result now.

    But I can you please let me know in where I need to add the code for rename on my function.
    LVL 16

    Author Comment

    by:Swadhin Ray
    Oh I got it and now it is solved :-).
    LVL 16

    Author Closing Comment

    by:Swadhin Ray
    Thanks a lot.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Stay Alert! 13 47
    Right Function in Oracle SQL Query 6 38
    Value of 0's not appearing. 9 40
    Create index on View 27 45
    This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
    This video shows how to recover a database from a user managed backup

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now