/temp/my_files is an existing folder in the server, facultylist.txt is an existing text file in this folder
Examine the following commands that are executed by the DBA:
SQL>CREATE DIRECTION my_dir AS ‘ /temp/my_files’:
SQL>GRANT READ ON DIRECTORY my_dir To pubiic:
View the Exhibit and examine the procedure created by user SCOTT to read the list of faculty names from the text file.
SCOTT executes the procedure as follows:
SQL>SET SERVEROUTPUT ON
SQL>EXEC read_file (‘MY_DIR’, FACULTYLIST.TXT’)
What is the outcome?
A. It goes into an infinite loop.
B. It executes successfully and displays only the list of faculty names.
C. It does not execute and displays an error message because the end-of-file condition is not taken care of.
D. It executes successfully and displays the list of faculty names followed by a "no data found” error message.
I also think that correct answer is D.
Confirmed it using following code:
CREATE DIRECTORY my_dir AS ‘D:\Temp’;
GRANT READ ON DIRECTORY my_dir to public;
create or replace procedure read_file (dirname varchar2,txtfile varchar2) is
f_file utl_file.file_type;
v_buffer varchar2(200);
begin
f_file:=utl_file.fopen(dirname,txtfile,’R’);
loop
utl_file.get_line(f_file,v_buffer);
dbms_output.put_line(v_buffer);
end loop;
utl_file.fclose(f_file);
end read_file;
/
SQL> SET SERVEROUTPUT ON
SQL> EXEC read_file (‘MY_DIR’, ‘FACULTYLIST.TXT’);
test line 1
test line 2
test line 3
BEGIN read_file (‘MY_DIR’, ‘FACULTYLIST.TXT’); END;
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at “SYS.UTL_FILE”, line 106
ORA-06512: at “SYS.UTL_FILE”, line 746
ORA-06512: at “HR.READ_FILE”, line 7
ORA-06512: at line 1