0

I am trying to create a procedure that reads a tab-delimited file and enters the values in Oracle DB.

Here is my procedure :

   create or replace PROCEDURE PLK_USER_LOAD_PROC
AS
     F UTL_FILE.FILE_TYPE;
     V_LINE VARCHAR2 (1000);
     V_USERID VARCHAR2(1000);
     V_FNAME VARCHAR2(1000);
     V_LNAME VARCHAR2(1000);
     V_EMAIL VARCHAR2(1000);
     V_ROLE VARCHAR2(1000);
     V_ISACTIVE VARCHAR2(1000);
     V_SEP VARCHAR2(10):='[^'||chr(09)||']+';
   BEGIN
     F := UTL_FILE.FOPEN ('WORK_DIRECTORY', 'test.txt', 'R');
    IF UTL_FILE.IS_OPEN(F) THEN
      LOOP
        BEGIN
          UTL_FILE.GET_LINE(F, V_LINE, 1000);
          IF V_LINE IS NULL THEN
            EXIT;
          END IF;
          V_USERID := TRIM(REGEXP_SUBSTR(V_LINE, V_SEP, 1, 1));
          V_FNAME := TRIM(REGEXP_SUBSTR(V_LINE, V_SEP, 1, 2));
          V_LNAME := TRIM(REGEXP_SUBSTR(V_LINE, V_SEP, 1, 3));
          V_EMAIL := TRIM(REGEXP_SUBSTR(V_LINE, V_SEP, 1, 4));
          V_ROLE  := TRIM(REGEXP_SUBSTR(V_LINE, V_SEP, 1, 5));
          V_ISACTIVE  := TRIM(REGEXP_SUBSTR(V_LINE, V_SEP, 1, 6));
            IF V_USERID = 'login_name'
                THEN 
                DBMS_OUTPUT.put_line('Skipped');
            ELSE 
                INSERT INTO plk_users VALUES (V_USERID, V_FNAME, V_LNAME, V_EMAIL, V_ROLE, V_ISACTIVE);
            END IF;
          COMMIT;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
          EXIT;
        END;
      END LOOP;
    END IF;
    UTL_FILE.FCLOSE(F);
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.put_line(V_USERID);
            dbms_output.put_line('Exception:'||SUBSTR(SQLERRM, 1, 1000));
  END PLK_USER_LOAD_PROC;

Here is the file I am trying to read:

    login_name  first_name  last_name   email   role_name   is_active
     MARSAL002    Mary       Salter         Restaurant Manager in Training  1

As you can see, there is no email, the program enters null at the end and shifts role in the email field and active in the role field. I want the email to be null and the values should not be shifted.

Thanks in Advance.

  • Looks like you are hitting [this problem](https://stackoverflow.com/a/31464699/266304). Just change the comma to tab in Gary_W's version of the regex; and modify your `regexp_substr` calls to match. – Alex Poole Aug 19 '21 at 16:24

0 Answers0