Introduction/ Issue:
CSV file move from client local machine to database table blob column.
Why we need to do / Cause of the issue:
We can store the CSV file from client local machine to database table blob column.
How do we solve:
Step 1: Create new table in database
CREATE TABLE TB_FILE_UPLOAD (ID NUMBER, FILE_DATA BLOB);
Step 2: Create new sequence in database
CREATE SEQUENCE FILE_UPLOAD_SEQ START WITH 1
MAXVALUE 999999999999999999999999999
MINVALUE 1
INCREMENT BY 1
NOCYCLE
CACHE 20
NOORDER
NOKEEP
GLOBAL;
Step 3: In oracle form builder create on button and write the below code in WHEN-BUTTON-PRESSED trigger.
DECLARE
v_seq number;
v_file_path varchar2(500) := ‘C:\test_file.csv’;
v_bool boolean;
BEGIN
SELECT FILE_UPLOAD_SEQ .NEXTVAL INTO v_seq FROM DUAL;
INSERT INTO TB_FILE_UPLOAD (ID, file_data) VALUES (v_seq, NULL);
IF v_file_path IS NOT NULL THEN
v_bool := WEBUTIL_FILE_TRANSFER.CLIENT_TO_DB ( ClientFile => v_file_path,
TableName => ‘TB_FILE_UPLOAD‘,
ColumnName => ‘file_data’,
WhereClause => ‘id= ‘ || v_seq);
END IF;
EXCEPTION
WHEN OTHERS THEN
MESSAGE(‘Error: ’||sqlerrm||’-’||sqlcode);
END;
Output:
After clicking button ‘test_file.csv’ file stored as a blob in database table column.
Conclusion:
We can store the csv file from local client machine to database table blob column.
