Plsql_plsql read/write CSV file mode (case)

2012-01-06 Created by Baoxinjin

I. Summary

How to read and write CSV

1. Write a CSV

Step1. Create a Test Catalog

Step2. Write a CSV file

Step3. View CSV file

2. Read CSV

Step1. Create a test table

Step2. Read CSV file

Step3. View Table

Ii. writing a CSV file

Step1. Create a Test Catalog

-- Create a Test catalog CREATE OR REPLACE  as ' /home/oracle/bxjcsv '  - - Directory permission assignment GRANTREADon to public;

Step2. Write a CSV file

DECLAREf_emp Utl_file.   File_type; CURSORcur_emp is      SELECTfirst_name,last_name,email,phone_number,salary fromHR. EMPLOYEES
       WHERE   rownum<=10; Rec_emp cur_emp%ROWTYPE;BEGINf_emp:=Utl_file. FOPEN ('Bxjcsv','emp_dept. CSV','W',32767);  forRec_empinchcur_emp LOOP utl_file.      PUT (F_emp, rec_emp.first_name); Utl_file. PUT (F_emp,',' ||rec_emp.last_name); Utl_file. PUT (F_emp,',' ||rec_emp.email); Utl_file. PUT (F_emp,',' ||rec_emp.phone_number); Utl_file. PUT (F_emp,',' ||rec_emp.salary); Utl_file.   New_line (f_emp); ENDLOOP; Utl_file. FCLOSE (f_emp);END;

Step3. View CSV file

Third, read the CSV file

Step1. Create a test table

CREATE TABLE  VARCHAR2 (VARCHAR2 (VARCHAR2 (   VARCHAR2 (number)

Step2. Read CSV file

DECLAREf_emp Utl_file.   File_type; V_lineVARCHAR2( +); V_first_name Hr.bxj_employees.first_name%TYPE; V_last_name Hr.bxj_employees.last_name%TYPE; V_email Hr.bxj_employees.email%TYPE; V_phone_number Hr.bxj_employees.phone_number%TYPE; V_salary hr.bxj_employees.salary%TYPE;BEGINf_emp:=Utl_file. FOPEN ('Bxjcsv','emp_dept. CSV','R'); IFUtl_file.is_open (f_emp) ThenLOOPBEGINUtl_file. Get_line (F_emp, V_line, +); IFV_line is NULL             Then               EXIT; END IF; V_first_name:=Regexp_substr (V_line,'[^,]+',1,1); V_last_name:=Regexp_substr (V_line,'[^,]+',1,2); V_email:=Regexp_substr (V_line,'[^,]+',1,3); V_phone_number:=Regexp_substr (V_line,'[^,]+',1,4); V_salary:=Regexp_substr (V_line,'[^,]+',1,5); INSERT  intohr.bxj_employeesVALUES(v_first_name,v_last_name,v_email,v_phone_number,v_salary); COMMIT; EXCEPTION whenNo_data_found Then           EXIT; END; ENDLOOP; END IF; Utl_file. FCLOSE (f_emp);END;

Step3. View Table

Thanks and regards

Plsql_plsql read/write CSV file mode (case)