Oracle database
CREATE PROCEDURE read_file(fname VARCHAR2) AS
v_file UTL_FILE.FILE_TYPE;
v_exists BOOLEAN;
v_length NUMBER;
v_bsize INTEGER;
v_rbuff VARCHAR2(1024);
BEGIN
UTL_FILE.FGETATTR('DIR', fname, v_exists, v_length, v_bsize); ...(2)
IF v_exists <> true THEN
DBMS_OUTPUT.PUT_LINE('-- FILE NOT FOUND --');
RETURN;
END IF;
DBMS_OUTPUT.PUT_LINE('-- FILE DATA --');
v_file := UTL_FILE.FOPEN('DIR', fname, 'r', 1024); ...(3)
FOR i IN 1..3 LOOP
UTL_FILE.GET_LINE(v_file, v_rbuff, 1024); ...(4)
DBMS_OUTPUT.PUT_LINE(v_rbuff);
END LOOP;
DBMS_OUTPUT.PUT_LINE('... more');
DBMS_OUTPUT.PUT_LINE('-- READ END --');
UTL_FILE.FCLOSE(v_file); ...(5)
RETURN;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('-- FILE END --');
UTL_FILE.FCLOSE(v_file);
RETURN;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('-- SQL Error --');
DBMS_OUTPUT.PUT_LINE('ERROR : ' || SQLERRM );
UTL_FILE.FCLOSE_ALL; ...(6)
RETURN;
END;
/
set serveroutput on
call read_file('file01.txt');Symfoware Server
CREATE FUNCTION read_file(fname VARCHAR) RETURNS void AS $$
DECLARE
v_file UTL_FILE.FILE_TYPE;
v_exists BOOLEAN;
v_length NUMERIC;
v_bsize INTEGER;
v_rbuff VARCHAR(1024);
BEGIN
PERFORM DBMS_OUTPUT.SERVEROUTPUT(TRUE);
SELECT fexists, file_length, blocksize
INTO v_exists, v_length, v_bsize
FROM UTL_FILE.FGETATTR('/home/symfo', fname); ...(2)
IF v_exists <> true THEN
PERFORM DBMS_OUTPUT.PUT_LINE('-- FILE NOT FOUND --');
RETURN;
END IF;
PERFORM DBMS_OUTPUT.PUT_LINE('-- FILE DATA --');
v_file := UTL_FILE.FOPEN('/home/symfo', fname, 'w', 1024); ...(3)
FOR i IN 1..3 LOOP
v_rbuff := UTL_FILE.GET_LINE(v_file, 1024); ...(4)
PERFORM DBMS_OUTPUT.PUT_LINE(v_rbuff);
END LOOP;
PERFORM DBMS_OUTPUT.PUT_LINE('... more');
PERFORM DBMS_OUTPUT.PUT_LINE('-- READ END --');
v_file := UTL_FILE.FCLOSE(v_file); ...(5)
RETURN;
EXCEPTION
WHEN NO_DATA_FOUND THEN
PERFORM DBMS_OUTPUT.PUT_LINE('-- FILE END --');
v_file := UTL_FILE.FCLOSE(v_file);
RETURN;
WHEN OTHERS THEN
PERFORM DBMS_OUTPUT.PUT_LINE('-- SQL Error --');
PERFORM DBMS_OUTPUT.PUT_LINE('ERROR : ' || SQLERRM );
PERFORM UTL_FILE.FCLOSE_ALL(); ...(6)
RETURN;
END;
$$
LANGUAGE plpgsql;
SELECT read_file('file01.txt');UTL_FILE.FGETATTR(firstArg, secondArg, thirdArg, fourthArg, fifthArg)
If using a CREATE DIRECTORY statement (Oracle9.2i or later), specify a directory object name for the directory name.
A directory object name cannot be specified for the directory name.
Obtained values are received with variables specified for arguments.
Since obtained values are the search results for UTL_FILE.FGETATTR, they are received with variables specified for the INTO clause of the SELECT statement.
Convert using the following procedure. Refer to UTL_FILE_DIR/CREATE DIRECTORY for information on how to check if the directory object name corresponds to the actual directory name.
Locate the places where the keyword "UTL_FILE.FOPEN" is used in the stored procedure.
Check the actual directory name ('/home/symfo' in the example) that corresponds to the directory object name ('DIR' in the example).
Replace the directory object name ('DIR' in the example) in firstArg with the actual directory name ('/home/symfo' in the example) verified in step 2.
Replace the UTL_FILE.FGETATTR location called with a SELECT INTO statement.
Use the literal "fexists, file_length, blocksize" in the select list.
Specify thirdArg, fourthArg, and fifthArg (v_exists, v_length, v_bsize, in the example) specified for UTL_FILE.FGETATTR to the INTO clause in the same order as that of the arguments.
Use UTL_FILE.FGETATTR in the FROM clause. Specify only the actual directory name for firstArg ('/home/symfo' in the example) and secondArg (fname in the example) before modification for the arguments.
UTL_FILE.FOPEN(firstArg, secondArg, thirdArg, fourthArg, fifthArg)
If using a CREATE DIRECTORY statement (Oracle9.2i or later), specify a directory object name for the directory name.
A directory object name cannot be specified for the directory name.
Convert using the following procedure. Refer to UTL_FILE_DIR/CREATE DIRECTORY for information on how to check if the directory object name corresponds to the actual directory name.
Locate the places where the keyword "UTL_FILE.FOPEN" is used in the stored procedure.
Check the actual directory name ('/home/symfo' in the example) that corresponds to the directory object name ('DIR' in the example).
Replace the directory object name ('DIR' in the example) in firstArg with the actual directory name ('/home/symfo' in the example) checked in step 1.
UTL_FILE.GET_LINE(firstArg, secondArg, thirdArg, fourthArg)
Obtained values are received with variables specified for arguments.
Since obtained values are the returned value of UTL_FILE.GET_LINE, they are received with variables specified for substitution statement.
Convert using the following procedure:
Locate the places where the keyword "UTL_FILE.GET_LINE" is used in the stored procedure.
Replace the UTL_FILE.GET_LINE location called with a value assignment (:=).
On the left-hand side, specify secondArg (v_rbuff in the example) specified for UTL_FILE.GET_LINE.
Use UTL_FILE.GET_LINE in the right-hand side. Specify only firstArg (v_file in the example) and thirdArg (1024 in the example) before modification.
UTL_FILE.FCLOSE(firstArg)
After closing, the file handler specified for the argument becomes NULL.
After closing, set the file handler to NULL by assigning the return value of UTL_FILE.FCLOSE to it.
Convert using the following procedure:
Locate the places where the keyword "UTL_FILE.FCLOSE" is used in the stored procedure.
Replace the UTL_FILE.FCLOSE location called with a value assignment (:=) so that the file handler (v_file in the example) becomes NULL.
On the left-hand side, specify the argument (v_file in the example) specified for UTL_FILE.FCLOSE.
Use UTL_FILE.FCLOSE in the right-hand side. For the argument, specify the same value (v_file in the example) as before modification.
Same as NEW_LINE in the DBMS_OUTPUT package. Refer to NEW_LINE in the DBMS_OUTPUT for information on specification differences and conversion procedures associated with specification differences.