How to optimize the substring & instring or substitute the functions for debugging

Question

I wrote the stored procedure below for the purposes of reading a comma delimited file that is moved onto the server ("DIR" folder), and when script is executed, it essentially parses the file (.csv), and assigns the data to its respective variables (xJOB_ID, xCTRL_ID, xACCT_SEC, xCREATEDON_DATE) such that I can insert the data into a table.

I am using Oracle SQL Developer version 4.0.0.13 in a Windows 7 environment. Fortunately, after banging my head on the table a couple of times the code works and I have not had any issues running the script.

Example format of the file:
1111, 2, T, 10/10/2000
2222, 12345, U, 10/10/2001
5555, 123, S, 10/10/1999

MY QUESTION: I found a little difficulty using the SUBSTRING & INSTRING functions to parse the data and wanted to know how can I improve the script so that in the event some debugging was needed, it can be easily resolved for someone that did not write the stored procedure.

Please let me know if that makes sense. I gave you the entire script so that you can understand what I was trying to accomplish and so that I can improve the code for debugging purposes.

create or replace PROCEDURE SP_INSERT_INTO_TABLE(xFILE_NAME IN VARCHAR2)

IS 


--UTL_FILE is an oracle package that allows you to read and write operating system files. 

TEXT_DATA UTL_FILE.FILE_TYPE;

v_ROW_LENGTH    NUMBER := 1024;
v_TEXTSTRING    VARCHAR2(4000);
cLINE           VARCHAR2(100);
xJOB_ID         NUMBER;
xCTRL_ID        NUMBER;
xACCT_SEC       VARCHAR2(1);
xCREATEDON_DATE DATE;
xCOUNT          NUMBER := 0;

BEGIN
    BEGIN
    --Streams in the file data and assigns it to TEXT_DATA variable. 
    TEXT_DATA := UTL_FILE.FOPEN('DIR', xFILE_NAME, 'R', v_ROW_LENGTH);      
END;

--Begin LOOP to get each line and assign to cLINE to extract, assign to each variable, and insert into the table
LOOP    
    BEGIN
        --Gets each string/line up to the line terminator
        UTL_FILE.GET_LINE(TEXT_DATA, v_TEXTSTRING);
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            EXIT;
    END;

    --Each line is assigned to the variable cLINE.
    cLINE := v_TEXTSTRING;

    --Begin to parse data using SUBSTRING and INSTRING functions
    BEGIN        
        --Extracts string from cLINE position 1 up to the first occurrence, converts it to a number, and assigns it to the variable. 
        xJOB_ID         := TO_NUMBER(SUBSTR(cLINE, 1,INSTR(cLINE, ',', 1, 1)-1));

        --Extracts string from cLINE between the 1st and 2nd occurrence, converts it to a number, and assigns it to the variable.
        xCTRL_ID        := TO_NUMBER(SUBSTR(cLINE, INSTR(cLINE, ',', 1, 1)+1, INSTR(cLINE, ',', 1,2)-INSTR(cLINE, ',', 1,1)-1));

        --Extracts string from cLINE between the 2nd and 3rd occurrence and assigns it to the variable.
        xACCT_SEC       := SUBSTR(cLINE, INSTR(cLINE, ',', 1, 2) +1, INSTR(cLINE, ',', 1,3)-INSTR(cLINE, ',', 1,2) -1);  

        --Extracts string from cLINE after the last occurrence, converts it to a date, and assigns it the variable.
        xCREATEDON_DATE := TO_DATE(SUBSTR(cLINE, INSTR(cLINE, ',', 1, 3)+1), 'MM/DD/YYYY');

        INSERT INTO TABLE(JOB_ID, CTRL_ID, ACCT_SEC, CREATEDON_DATE)
        VALUES(xJOB_ID, xCTRL_ID, xACCT_SEC, xCREATEDON_DATE);
        COMMIT;

        --Counter to count the amount of inserts
        xCOUNT := xCOUNT + 1;

    EXCEPTION
        --Exception to handle the conversion of a string to a NUMBER or value is longer than the declared length of the variable.
        WHEN VALUE_ERROR THEN 
        NULL;

    END;        
END LOOP;

DBMS_OUTPUT.PUT_LINE('RECORDS INSERTED: ' || xCOUNT);    
UTL_FILE.FCLOSE(TEXT_DATA);

END;

Answer 1

You could use REGEXP_SUBSTR instead, since that just requires one function call, rather than a series of SUBSTR and INSTR calls, eg:

(courtesy of Gary_W):

declare
  v_str varchar2(20) := 'a,,bcd';
  v_substr1 varchar2(10);
  v_substr2 varchar2(10);
  v_substr3 varchar2(10);
begin
  v_substr1 := regexp_substr(v_str, '([^,]*)(,|$)', 1, 1, NULL, 1);
  v_substr2 := regexp_substr(v_str, '([^,]*)(,|$)', 1, 2, NULL, 1);
  v_substr3 := regexp_substr(v_str, '([^,]*)(,|$)', 1, 3, NULL, 1);
  dbms_output.put_line(v_substr1||':'||v_substr2||':'||v_substr3);
end;
/

a::bcd

The above will work with strings that have null portions, as demonstrated. The search pattern has been split into two groups (aka subexpressions): [^,]* and ,|$.

The first group says: any character that isn't a comma ([^,]) that appears 0 or more times (*).

The second group says: comma or the end of the line.

So the whole pattern is looking for a set of any characters excluding the comma that may or may not exist which is followed by either a comma or the end of the line.

The last parameter in the regexp_substr is indicating that we want to pick the 1st subexpression from the search pattern to display - if we didn't include this, then you'd end up with the commas being displayed as part of the string being returned.

---

If you're absolutely sure that none of the elements of the string will ever be null, then the following will work:

declare
  v_str varchar2(20) := 'a,123,bcd';
  v_substr1 varchar2(10);
  v_substr2 varchar2(10);
  v_substr3 varchar2(10);
begin
  v_substr1 := regexp_substr(v_str, '[^,]+', 1, 1);
  v_substr2 := regexp_substr(v_str, '[^,]+', 1, 2);
  v_substr3 := regexp_substr(v_str, '[^,]+', 1, 3);
  dbms_output.put_line(v_substr1||':'||v_substr2||':'||v_substr3);
end;
/

a:123:bcd

This is just looking for the specified occurrence of a string of characters that aren't a comma, which is slighly easier to grok (imho!) than the search pattern used in the previous example, but is a lot less robust.