Pipelined function in Oracle. Example of reading flat file from /usr/tmp into SQL*Plus, sql query.

Below is an example of Oracle pipelined function.
On the downside you have to create an expicit Type, which sometimes hard to make one generic enough.
On the upside memory footprint is certainly smaller than creating a collection in memory and then pass it for processing.

---> CREATE CUSTOM TYPES



create Type xx_utl_r_type as object (s Varchar2(1020) );

create type xx_utl_t_type as table of xx_utl_r_type;


----<


---------->---------- Custom Package ---<<<<<<<<<<<<<<<

CREATE OR REPLACE package XX_UTL_FILE_PLUGIN as

Type v_type is table of Varchar2(500) index by binary_Integer;

Function ReadFile2Array(Path Varchar2,FileName Varchar2) return xx_utl_t_type pipelined;


gDir Varchar2(100);
gFile Varchar2(30);


Function getStrings
(p_items Varchar2,
p_delimiter Varchar2)
Return v_type;



End;
/

CREATE OR REPLACE package body XX_UTL_FILE_PLUGIN as


iFileCount Number:=0;

fileName UTL_FILE.FILE_TYPE;
--------------------------------

Function mergeStrings(v v_type,p_delimiter Varchar2,p_globalDelimiter Varchar2) Return v_type
is


i number:=1;
i_Temp Number :=1;
k Number:=0;


v_Temp V_type;

Begin


while(i_Temp <= v.LAST) Loop


if (v(i_temp) is null) Then v_temp(i):=v(i_temp); i:=i+1;i_temp:=i_temp+1;
Else

if regexp_instr(v(i_temp),'^'||p_delimiter)>0 and regexp_instr(v(i_temp),p_delimiter||'$') >0 Then
v_temp(i):=v(i_Temp);
i:=i+1;
i_temp:=i_temp+1;


Elsif regexp_instr (v(i_temp),'^'||p_delimiter)<1 and regexp_instr(v(i_temp),p_delimiter||'$')<1 Then
v_temp(i):=v(i_Temp);
i:=i+1;
i_temp:=i_temp+1;

ElsIf (regexp_instr (v(i_temp),'^'||p_delimiter)>0 and instr(v(i_temp),p_delimiter||'$')<1) Then
v_Temp(i):=v(i_temp);
i_temp:=i_temp+1;


k:=nvl(regexp_instr(v(i_temp),p_delimiter||'$'),0);

if k=0 Then

while ( k=0) Loop

v_Temp(i):=v_Temp(i)||p_globalDelimiter||v(i_temp);
i_Temp:=i_Temp+1;

k:=nvl(regexp_instr(v(i_temp),p_delimiter||'$'),0);


End Loop;


End If;

v_Temp(i):=v_Temp(i)||p_globalDelimiter||v(i_temp);

v_Temp(i):=ltrim(v_Temp(i),p_delimiter);
v_Temp(i):=rtrim(v_Temp(i),p_delimiter);


i:=i+1;
i_temp:=i_temp+1;

End If;

End If;


End Loop;

return v_Temp;

Exception
When NO_DATA_FOUND Then

dbms_output.put_line('String '||v(i_temp) ||' cannot be processed! Row Number='||i);

return v_temp;

End;

--------------------------------
Procedure myDebug(msg Varchar2) is

Begin


-- fnd_file.put_line(fnd_file.log,msg);
dbms_output.put_line(msg);

End;
-----------------------------------------------------------------------------

Function ReadFile2Array(Path Varchar2,FileName Varchar2) return xx_utl_t_type pipelined is


S Varchar2(1020);

FileHandle utl_file.file_type;

sr xx_utl_r_type;
iCount Number :=1;

Begin

dbms_output.put_line(path||'-'||filename);

fileHandle:=utl_file.fopen(path,FileName,'r');

while true Loop

Begin

iCount := iCount+1;

utl_file.get_line(fileHandle,s);

-- myDebug(iCount||'-'||substr(s,1,200));

iFileCount:=iFileCount+1;


mydebug('st1 '||length(s));
sr:=xx_utl_r_type(s);
mydebug('st2');
pipe row (sr);

Exception
When NO_DATA_FOUND Then
mydebug('No data found');
utl_file.fclose(fileHandle);
return;

End;

End Loop;


return;

Exception

When utl_file.INVALID_PATH Then
utl_file.fclose(fileHandle);
myDebug('Wrong file path! '|| substr(SQLERRM,1,100));
sr:=xx_utl_r_type('INVALID PATH');
pipe row (sr);
return;

When utl_file.INVALID_FILEHANDLE Then
utl_file.fclose(fileHandle);
myDebug('Wrong file ! '|| substr(SQLERRM,1,100));
sr:=xx_utl_r_type('INVALID FILEHANDLE');
pipe row (sr);
return;

When utl_file.INVALID_OPERATION Then
utl_file.fclose(fileHandle);
myDebug('Wrong file ! '|| substr(SQLERRM,1,100));
sr:=xx_utl_r_type('FILE NOT FOUND!');
pipe row (sr);
return;

When utl_file.READ_ERROR Then
utl_file.fclose(fileHandle);
myDebug('Can''t read file ! '|| substr(SQLERRM,1,100));
sr:=xx_utl_r_type('READ ERROR');
pipe row (sr);
return;




When Others Then

mydebug(substr(SQLERRM,1,200));
utl_file.fclose(fileHandle);
myDebug('Done file reading ');
myDebug('Read '||iFileCount||' records!');


End;




/*---------------------------------------------------------------------------------------------*/



/*----------------------------------------------------------------------------------------------*/
Function getStrings
(p_items Varchar2,
p_delimiter Varchar2)
Return v_type

is

v_varTab_i v_type;
tempString Varchar2(16000);
i Binary_Integer:=1;
i_s number;
i_e number;
i_sp number;
i_ep number:=1;
--add Varchar2(100);
t_items Varchar2(16000);



Begin

t_items:=rtrim(p_items,p_delimiter);
t_items:=t_items||p_delimiter;
i_s :=1;
tempString:=substr(t_items,i_s);
i:=1;

while i_ep > 0
Loop

i_ep:=instr(tempString,p_delimiter,i_s);


if i_ep >0 then
if i_ep-i_s = 0 then v_varTab_i(i):= NULL;
else
v_varTab_i(i):=substr(tempString,i_s,i_ep-i_s);
end if;
i:=i+1;
tempString:=substr(tempString,i_ep+length(p_delimiter));
end if;

End Loop;

v_varTab_i:=mergeStrings(v_varTab_i,'"',p_delimiter);

Return v_varTab_i;

Exception
when others then
null;

end getStrings;



End xx_utl_file_plugin ;
/


---------->---------- Custom Package ---<<<<<<<<<<<<<<<


-- query below allows you to read flat file from OS straight into SQL query.
-- Directory. which in this case is '/usr/tmp' needs to be replaced with your
-- directory name. Security limitation on UTL_FILE is also applied here.
-- You can only use directories stored in DBA_DIRECTORIES table.

select * from table(XX_UTL_FILE_PLUGIN.ReadFile2Array('/usr/tmp','yourfilename.extension'));