View Exhibit1 and examine the structure of the employees table.
User SCOTT needs to generate a text report that contains the names of all employees and their salaries.
Examine the following commands issued by the DBA:
SQL_CREATE DICTORY my_dir AS ‘/temp/my_files* ;
SQL_GRANT WRITE ON DIRECTORY my_dir TO SCOTT;
View Exhibit2 and examine the procedure code. You issue the following command:
You issue the following command:
SQL_EXEC sal_5tatus (‘MY_DIR’, ‘EMPREPORT.TXT’)
What is the outcome?
A. It executes successfully and creates the report.
B. It gives an error because the text file should be opened in append mode.
C. It gives an error because the "no data found" condition is not handled to come out of the loop.
D. It gives an error because user SCOTT should be granted both read and write privileges to the directory alias.
E. It executes but no data is written to the text file because the FFLUSH subprogram is not used to write all the data buffered in memory to a file.
|
Download Printable PDF. VALID exam to help you PASS. |
 |
Also think that correct answer is A.
Verified it by executing following code in HR schema instead of SCOTT though.
sqlplus sys as sysdba
SQL>create directory my_dir as ‘C:\oraclexe’;
SQL>GRANT WRITE ON DIRECTORY my_dir TO hr;
conn HR
SQL>create or replace procedure sal_status(p_dir in varchar2, p_filename in varchar2) is
f_file utl_file.file_type;
cursor cur_emp is
select last_name,salary
from employees order by salary;
begin
f_file:=utl_file.fopen(p_dir,p_filename,’W’);
utl_file.put_line(f_file,’REPORT: Generated on ‘||sysdate);
for emp_rec in cur_emp loop
utl_file.put_line(f_file,’ Employee: ‘||emp_rec.last_name ||
‘ earns: ‘ || emp_rec.salary);
end loop;
utl_file.fclose(f_file);
exception
when utl_file.invalid_filehandle then
raise_application_error(-20001, ‘Invalid file.’);
when utl_file.write_error then
raise_application_error(-20002,’Unable to write to file’);
end sal_status;
/
EXEC sal_status (‘MY_DIR’,’EMPREPORT.TXT’);