Learn how to a build a cloud-first strategyRegister Now

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

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;

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

0
Swadhin Ray
Asked:
Swadhin Ray
  • 3
1 Solution
 
slightwv (䄆 Netminder) Commented:
>>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.
0
 
Swadhin RaySenior Technical Engineer Author Commented:
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.
0
 
Swadhin RaySenior Technical Engineer Author Commented:
Oh I got it and now it is solved :-).
0
 
Swadhin RaySenior Technical Engineer Author Commented:
Thanks a lot.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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