Oracle-provided package support for developers [important] (vii)

1.dbms_alert--Packet for message transmission
2.dbms_lock--handling of locks Oracle Lock Management Service
3.dbms_session--session-level commands
4.dbms_output--Output results
5.HTP--Write HTML in the database Cache-Create a simple Web page
6.utl_file--interacting with the operating system text file
7.utl_mail--Send and receive mail
8.dbms_scheduler--Periodic execution of PL/SQL code-Job

Oracle's own script storage path:
D:\oracle11g\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN

--dbms_output fetching data from the database cache
1.put, Put_Line
2.get, Get_line
3. Turn on the display: SET serveroutput on;

--Utl_file

--Read and write files

1F:=fopen (dir,FILE,'R');--Read the file2Get_line (F,buf,LEN);--read a line3Fclose (f)--Close File4 put (F,BUF)5Put_Line (F,BUF)--Write File6F:=fopen (dir,FILE,'W');--Write files in a write-only manner7F:=fopen (dir,FILE,'a');--Append write mode

--Utl_file errors in reading and writing files

1 Invalid_path 2 Invalid_mode 3 Invalid_filehandle 4 invalid_operation 5 Read_error 6 Write_error 7 Internal_error

--General error

1 No_data_found 2 Value_error

----Examples of utl_file operation files
---prep work

1 /  asSYSDBA;--DBA Authority required2 CREATEDIRECTORY My_dir as 'C:\dir';--Create a directory3 GRANT READ, WRITE onDIRECTORY My_dir to  Public --Authorized Directory4SHOW parameter Utl_file_dir;--View System Variables (NO)5 ALTERSYSTEMSETUtl_file_dir= 'C:\dir'SCOPE=SPFILE;--Changing system Parameters6 [SCOPE = Spfile/memory/both]7 SHUTDOWNIMMEDIATE;--Close the database8Startup--Start the database9SHOW parameter Utl_file_dir;--View System variables (already in effect)

--Create a stored procedure

1 CREATE OR REPLACE PROCEDURESal_status (2Dirinch VARCHAR2,3FileNameinch VARCHAR24) is5 FILEUtl_file.file_type;6   CURSOREmpc is SELECTlast_name,salary,department_id fromEmployeesORDER  bydepartment_id;--Record in table7Newdeptno employees.department_id%TYPE;--The new department number8Olddeptno employees.department_id%TYPE:= 0;--The old department number9 BEGINTen   FILE:=Utl_file.fopen (Dir,filename,'W');--Open File OneUtl_file.put_line (file,'report:generated on' ||Sysdate);--Write Content AUtl_file.new_line (FILE);--New Row -    forEmp_recinchEMPC LOOP--Cycle Employee Records -     IFemp_rec.department_id<>Olddeptno Then   --Record Department value <> old Department theUtl_file.put_line (FILE,'Department:' ||EMP_REC.DEPARTMENT_ID);--New Append Row -     END IF; -Utl_file.put_line (FILE,'Employee:' ||Emp_rec.last_name|| 'earns:' ||Emp_rec.salary);--Append Log -Olddeptno:=emp_rec.department_id;--Old Department Value update +   ENDLOOP; -Utl_file.put_line (FILE,'xxxend ofreportxxx'); +Utl_file.fclose (FILE); A EXCEPTION at      whenUtl_file.invalid_filehandle Then -Raise_application_error ('-20001','Invalid file'); -      whenUtl_file.write_error Then -Raise_application_error ('-20002','unable to be write'); - ENDSal_status;

--Operational issues

Execute sal_status ('my_dir','salreport.txt');

--Error

1Sql> ExecuteSal_status ('My_dir','Salreport.txt');2 beginSal_status ('My_dir','Salreport.txt');End;3ORA-29283: invalidfileOperation4ORA-06512: At "SYS. Utl_file ", line5365ORA-29283: invalidfileOperation6ORA-06512: At "HR. Sal_status ", lineTen7ORA-06512: At line1

--reason

C:\dir dir directory needs to be created manually, and Oracle does not check if the folder already exists.

Oracle-provided package support for developers [important] (vii)