• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 974
  • Last Modified:

Handling Delimiter and Qualifier using PLSQL

Hello Experts,

I have a flat file which comes in this fashion :  

Column Delimiter      |
Row Delimiter      CrLf
Text Qualifier      "

I am able to handle if that is " Column Delimiter      |"  in my function which loads the file so how to handle if :
Row Delimiter      CrLf
Text Qualifier      "
0
Swadhin Ray
Asked:
Swadhin Ray
  • 4
  • 3
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
Need sample data and expected results.

Are you asking how to handle a blank line?

As far as delimieters: ltrim and rtrim the value to remove them.
0
 
sdstuberCommented:
do you mean you get those exact phrases embedded in a file and you than have to use that information to parse the remainder of the file?

you can do that, but after parsing, then what?

also, as slightwv noted.  Please post sample data and expected results.
0
 
Swadhin RaySenior Technical Engineer Author Commented:
Function code:

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

0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
slightwv (䄆 Netminder) Commented:
>>Function code:

This doesn't help answer the questions.

What do you mean by:  in my function which loads the file so how to handle if : Row Delimiter      CrLf

The text delimieters:  Again: LTRIM and RTRIM.
0
 
sdstuberCommented:
I recognize your code, it's a variant of some stuff I posted a few years ago.  I guess that gives me a head start on this.

looking at your function it appears that you are assuming each line will have exactly as many columns as one row in your table and the columns line up one-for-one in order between the table and the file.

Is that correct?

 i mean that question in two senses...

first is that a correct understanding on my part of what you are trying to do and the assumption you are making?

second, if my understanding is correct,  is your code assumption valid?  if not, what should happen when a line of the file is either missing fields or has extras?
0
 
sdstuberCommented:
also, how dynamic does this function really need to be?

is this supposed to work for any table (i.e. possibly thousands of different tables?)
or is this dynamic in that it will be used for 2 or 3 or some other small number of tables?

if the latter, how many exactly?
0
 
slightwv (䄆 Netminder) Commented:
sdstuber has very good points and you should continue to work on them.

Based on the code I just posted to your other open question, I have added an 'optional enclosed' input parameter that will remove leading and trailing characters from the column values.

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 '|',
                     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 = '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,
                            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 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:
If by "Row Delimiter      CrLf" you mean skip blank lines, this should meet all your requirements in this question and your other one.



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


--sample data
--COL1|COL2|COL3|COL4|COL5
--214517|AAADD|True|2001-01-02 20:01:02|2001-01-02 20:01:02
--562822|"CCGAA"|True|2001-01-02 20:01:02|2002-01-02 20:01:02
--343633|KOPSUS|True|2001-01-02 20:01:02|2003-01-02 20:01:02


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
 
Swadhin RaySenior Technical Engineer Author Commented:
@sdstuber :  The flat file which I have to load will have all the columns for the target table.

Sequence for column on the table and on the flat file will

>>looking at your function it appears that you are assuming each line will have exactly as many columns as one row in your table and the columns line up one-for-one in order between the table and the file.

Is that correct?
Yes. Sequence are in order.

And the code which slightwv posted works for me now.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

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