display the content of a file split by a delimiter character

Question

I am trying to display the content of a file, split by a delimiter character. More exactly, starting from this topic, I am trying to display the result as:

bbb
aaa
qqq
ccc

but the data source to be taken from a file.

Until now, I tried:

DECLARE
    l_bfile bfile;
BEGIN
    l_bfile := bfilename(my_dir, my_file);
    dbms_lob.fileopen(l_bfile);

    FOR i IN
        (SELECT TRIM(regexp_substr(TO_CHAR(l_bfile),'[^;]+',1,level) ) AS q
         FROM dual
         CONNECT BY regexp_substr(TO_CHAR(l_bfile),'[^;]+',1,level) IS NOT NULL
         ORDER BY level
        )
    LOOP
        dbms_output.put_line(i.q);
    END LOOP;

EXCEPTION
WHEN No_Data_Found THEN
    NULL;
END; 

As result, I got

PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got FILE

Can anyone give me a hint, please?

Answer 1

Have to write this as a new answer since this is too big for a comment to @SmartDumb:

Be advised the regex of the form '[^;]+' (commonly used for parsing delimited lists) fails when NULL elements are found in the list. Please see this post for more information: https://stackoverflow.com/a/31464699/2543416

Instead please use this form of the call to regexp_substr (note I removed the second element):

SELECT TRIM(regexp_substr('bbb;;qqq;ccc','(.*?)(;|$)',1,level, null, 1) ) AS q
     FROM dual
     CONNECT BY regexp_substr('bbb;;qqq;ccc','(.*?)(;|$)',1,level) IS NOT NULL
     ORDER BY level

It may or may not be important in this example, it depends on if the order of the element in the string has importance to you or if you need to preserve the NULL. i.e. if you need to know the second element is NULL then this will work.

P.S. Do a search for external tables and see if that is a solution you could use. That would let you query a file as if it were a table.

Answer 2

You could possible try this if your file contains single line (hence the question about file structure):

DECLARE

utlFileHandle  UTL_FILE.FILE_TYPE;
vLine varchar2(100);

BEGIN

utlFileHande := UTL_FILE.FOPEN(my_dir, my_file, 'r');
utl_file.get_line(utlFileHande, vLine);

FOR i IN
    (SELECT TRIM(regexp_substr(vLine,'[^;]+',1,level) ) AS q
     FROM dual
     CONNECT BY regexp_substr(vLine,'[^;]+',1,level) IS NOT NULL
     ORDER BY level
    )
LOOP
    dbms_output.put_line(i.q);
END LOOP;

utl_file.fclose(utlFileHande);

EXCEPTION
WHEN No_Data_Found THEN
   utl_file.fclose(utlFileHande);
   null;
END;