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 specific Permissions
Select distinct privilege from DBA_SYS_PRIVS where privilege like '% DIRECTORY % ';
-- Authorize SCOTT with SYS user
Grant create any directory to scott;
-- Create a directory after authorization
Create or replace directory filename as 'd: \ temporary ';
-- Query the created table
SELECT * from test;
-- Write a TXT file
Declare filehandle UTL_FILE.FILE_TYPE; -- handle
BEGIN
-- FILENAME must be in uppercase; otherwise, the system will prompt that the directory path is invalid.
FILEHANDLE: = 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;
End loop;
UTL_FILE.FCLOSE (FILEHANDLE );
COMMIT;
END;
-- Query the table again
SELECT * from test;
/*
-- Modify Table Structure
Alter table stu add (NAME2 VARCHAR2 (10); ADD a new field
Update stu set NAME2 = SUBSTR (TRIM (NAME),); assign a 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 );*/

Share: http://space.itpub.net/519536/viewspace-691051