Write txt text in ORACLE and read data from Txt file to Modify Table Structure

Write txt text in ORACLE and read data from Txt file to Modify Table Structure

-- Create a table drop table test cascade constraints; create table test (a varchar (30), B VARCHAR (30 )); -- view the specific permission select distinct privilege from DBA_SYS_PRIVS where privilege like '% DIRECTORY %'; -- use the SYS user TO authorize scottgrant create any directory to scott; -- create or replace directory filename as 'd: \ temporary after authorization; -- query the created table SELECT * from test; -- write the TXT file declare filehandle UTL_FILE.FILE_TYPE; -- The handle BEGIN--FILENAME must be in uppercase; otherwise, the system will prompt that the directory path is invalid. ILEHANDLE: = UTL_FILE.FOPEN ('filename', 'util _ FILE. TXT ', 'w'); UTL_FILE.PUT_LINE (FILEHANDLE, 'Hello ORACLE! '); UTL_FILE.PUT_LINE (FILEHANDLE,' Hello, fat man! '); UTL_FILE.FCLOSE (FILEHANDLE); -- close the handle END; -- read the content from the TXT file and insert it into the table TEST/* set serveroutput on */declare filehandle UTL_FILE.FILE_TYPE; filebuffer varchar (200); begin filehandle: = UTL_FILE.FOPEN ('filename', 'util _ FILE. TXT ', 'R'); loop begin UTL_FILE.GET_LINE (FILEHANDLE, FILEBUFFER); insert into test (A) VALUES (FILEBUFFER); exception when NO_DATA_FOUND then exit; end loop; UTL_FILE.FCLOSE (FILEHANDLE); COMMIT; END; -- query the TABLE again SELECT * from test;/* -- modify the TABLE structure alter table stu add (NAME2 VARCHAR2 (10 )); add the new field update stu set NAME2 = SUBSTR (TRIM (NAME),); assign the value to the new field alter table stu drop (NAME ); delete the original field alter table stu rename column NAME2 to name; RENAME the new field alter table name modify Field NAME VARCHAR2 (length );*/