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?

the first thing i notice is that quotes are missing when you use bfilename. See here https://docs.oracle.com/database/121/SQLRF/functions020.htm#SQLRF00610. Example from oracle docs (notice quotes) : `BFILENAME('MEDIA_DIR', 'modem_comp_ad.gif')` — Peter, Jan 04 '19 at 07:52
You are right, I was trying to use variables but for the file name I kept the extension too. I corrected it. — mikcutu, Jan 04 '19 at 07:58
Does your file have any structure? Can u post file contents ? — SmartDumb, Jan 04 '19 at 08:02
Your code seems to work fine for me ( I tested in Oracle 12.2 ). I Just defined `my_dir` and `my_file` as variables assigning proper values. — Kaushik Nayak, Jan 04 '19 at 08:05

score 1 · Answer 1 · answered Jan 04 '19 at 08:40

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;

The file may contain 1 or n lines but, anyway it can be done with a loop. — mikcutu, Jan 04 '19 at 09:04

score 1 · Accepted Answer · answered Jan 08 '19 at 16:26

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.

display the content of a file split by a delimiter character

2 Answers2