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.