Upload .txt file and insert into Table X

SQL Navigator
1

Hello, I want to know if this is possible within Toad for Oracle 2017. I want to select a static named .txt file from shared location. Once that .txt file is hard coded into the package procedure then I want to execute the procedure then the data from the .txt file gets inserted within a table. My example has table x. All the examples I am seeing online uses .csv documents but I dont want to convert the .txt file to .csv; that will be an extra step that is not needed. I plan on having the procedure execute daily with a scheduled job but I can not create the scheduled job until the execute inserts the data from the .txt file into table_x.

This is the example that I am referencing but I am not certain that this works for a .txt file; not sure.

I am getting an error message ORA-01403: no data found.

CREATE TABLE TABLE_X
(
v1 NUMBER NOT NULL,
v2 NUMBER,
v3 NUMBER,
v4 NUMBER
);

PROCEDURE P_INSERT_TABLE_X

IS
v_blob_data BLOB;
v_blob_len NUMBER;
v_position NUMBER;
v_raw_chunk RAW(10000);
v_char CHAR(1);
c_chunk_len number := 1;
v_line VARCHAR2 (32767) := NULL;
v_data_array wwv_flow_global.vc_arr2;
BEGIN
-- Read data from wwv_flow_files
select blob_content into v_blob_data
from wwv_flow_files where name = 'C:\Users\ccarter\Desktop\TABLE_X_TEST.txt';

v_blob_len := dbms_lob.getlength(v_blob_data);
v_position := 1;

-- Read and convert binary to char
WHILE ( v_position <= v_blob_len ) LOOP
v_raw_chunk := dbms_lob.substr(v_blob_data,c_chunk_len,v_position);
v_char := chr(f_hex_to_decimal(rawtohex(v_raw_chunk)));
v_line := v_line || v_char;
v_position := v_position + c_chunk_len;
-- When a whole line is retrieved
IF v_char = CHR(10) THEN
-- Convert comma to : to use wwv_flow_utilities
v_line := REPLACE (v_line, ',', ':');
-- Convert each column separated by : into array of data
v_data_array := wwv_flow_utilities.string_to_table (v_line);
-- Insert data into target table
EXECUTE IMMEDIATE 'insert into TABLE_X (v1, v2, v3, v4)
values (:1,:2,:3,:4)'
USING
v_data_array(1),
v_data_array(2),
v_data_array(3),
v_data_array(4);
-- Clear out
v_line := NULL;
END IF;
END LOOP;
END P_INSERT_TABLE_X;

I am hoping that this procedure when executed can insert those four column values from the .txt file into table_x. Any help is appreciated. Thanks

execute SCHEMA_NAME.P_INSERT_TABLE_X;

2

Hi, have you tried to import your file using SQLLoader? Got managed to import a textfile into a table in the past using SQLLoader.

Regards, Woolve

3

Hello Woolve,
Sorry for the late response, first time using this forum I did not have my alert set up. Yes, that is the step that I am using now. Thanks