Begin
DBMS_JOB.CHANGE(121,NULL,TRUNC(SYSDATE+1)+6/24,’SYSDATE+4/24’); END;
這個例子改變了121號工作的執行時間從早上六點開始，每四個小時執行一回。 Running, removing, and breaking jobs
? DBMS_JOB.RUN: runs a submitted job immediately.
? DBMS_JOB.REMOVE: Removes a submitted job from the job queue.
? DBMS_JOB.BROKEN: Marks a submitted job as broken, and a broken job will not run.
例：
EXECUTE DBMS_JOB.RUN(121) EXECUTE DBMS_JOB.REMOVE(121)
EXECUTE DBMS_JOB.BROKEN(121,TRUE)
Set this parameter to FALSE to indicate that a job is not broken, and set it to TRUE to indicate that it is broken.
Viewing Information on Submitted Jobs
Use the DBA_JOBS dictionary view to see the status of submitted jobs. 如：SELECT job, log_user, next_date ,next_sec, broken, what
FROM DBA_JOBS;
? Use the DBA_JOBS_RUNNING dictionary view to display jobs that are currently running.
SELECT job, log_user, next_date ,next_sec, broken, what
FROM USER_JOBS
Using the DBMS_OUTPUT Package
The DBMS_OUTPUT package enables you to output messages from PL/SQL blocks.
Available procedures include:
? PUT appends text from the procedure to the current line of the line output buffer ? NEW_LINE Places and end_of_line marker in the output buffer ? PUT_LINE Combines the action of PUT and NEW_LINE
? GET_LINE Retrieves the current line from the output buffer into the procedure 把BUFFER中的數據讀到過程中
? GET_LINES Retrieves and array of lines from the output buffer into the procedure ? ENABLE/DISABEL enables or disables calls to the DBMS_OUTPUT procedures
Interacting with Operating System Files
? UTL_FILE Oracle-supplied package: (you can read from and write to operating system files)
? Provides text file I/O capabilities
? Is available with version 7.3 and later
? The DBMS_LOB Oracle-supplied package: (you can read from binary files on the operating
system)
? Provides read-only operations on external BFILES ? Is available with version 8 and later
? Enables read and write operations on internal LOBS
What Is the UTL_FILE Package?
? Extends I/O to text files within PL/SQL
? Provides security for directories on the server through the init.ora file ? Is similar to standard operating system I/O
? Open files
? Get text ? Put text
? Close files
? Use the exceptions specific to the UTL_FILE package
UTL_FILE Procedures and Functions ? Function FOPEN 打開一個文件
? Function IS_OPEN判斷一個文件是否已打開。
? Procedure GET_LINE 把文件中的數據讀取到BUFFER中。最多1023字節 ? Procedure PUT,PUT_LINE,PUTF 把數據保存到BUFFER中。 ? Procedure NEW_LINE換行
? Procedure FFLUSH 把BUFFER中的數據寫到文件中 ? Procedure FCLOSE, FCLOSE_ALL 關閉打開的[所有]文件。
Exceptions specific to the UTL_FILE Package ? INVALID_PATH
? INVALID_MODE fopen中的open_mode參數無效 ? ? ? ?
INVALID_FILEHANDLE
INVALID_OPERATION 文件打不開 READ_ERROR WRITE_ERROR
? INTERNAL_ERROR 未知錯誤
The FOPEN and IS_OPEN Functions FUNCTION FOPEN (location in varchar2,
Filename in varchar2,
Open_mode in varchar2) 模式有’r’,’w’,’a’.分別代表，read,write,append Return UTL_FILE.FILE_TYPE;
FUNCTION IS_OPEN(file_handle in FILE_TYPE) RETURN BOOLEAN;
Using UTL_FILE
用UTL_FILE.FOPEN(‘PATH’,’NAME’,’W’)打開文件時，UTL_FILE 函數錯誤，有必要找一個UTL_FILE所在的包研究一下。
Exceptions Specific to the UTL_FILE Package ? INVALIDE_PATH ? INVALID_MODE
? INVALIDE_FILEHANDLE ? INVALIDE_OPERATION
? READ_ERROR ? WRITE_ERROR ? INTERNAL_ERROR
UTL_HTTP Package
The UTL_HTTP Package:
? Enables HTTP callouts from PL/SQL and SQL to access data on the internet.
? Contains the functions REQUEST and REQUEST_PIECES which take the URL of a site as a
parameter, contact that site, and return the data obtained from that site. ? Requires a proxy parameter to be specified in the above functions, if the client is behind a
firewall.
? Reports an HTML error message if specified URL is not accessible.
UTL_HTTP is a package that allows you to make HTTP requests directly from the database. By coupling UTL_HTTP with the DBMS_JOBS package, you can easily schedule reoccurring requests be made from you database server out to the Web. UTL_HTTP.REQUEST(‘網址’,’代理地址’);
UTL_HTTP.REQUEST_pieces(‘網址’,’代理地址’); 可以獲取100片數據，每片最多2000個字節。
Using the UTL_TCP Package
The UTL_TCP Package:
? Enables PL/SQL applications to communicate with external TCP/IP-based server using TCP/IP ? Contains functions to open and close connections, to read or write binary or text data to or
from a service on an open connection. ? Requires remote host and port as well as local host and port as arguments to its functions. ? Raises exceptions if the buffer size is too small, when no more data is available to read from a connection, when a generic network error occurs, or when bad arguments are passed to a function call.
The package contains functions such as:
OPEN_CONNECTION:
CLOSE_CONNECTION: 另外有：CLOSE_ALL_CONNECTIONS
READ_BINARY()/TEXT()/LINE(): receives binary, text or text line message from a service on an open connection.
WRITE_BINARY()/TEXT()/LINE(): transmits binary, text or text line message to a service on an open connection.
Other Oracle-supplied packages include:
? DBMS_ALTER 數據庫事件提示
? DBMS_APPLICATION_INFO 允許數據庫執行外部應用程序的指令 ? DBMS_DESCRIBE返回存儲過程的參數
? DBMS_LOCK 請求，轉換，釋放鎖定。
? DBMS_SESSION 為SQL session iformation提供方法 ? DBMS_SHARED_POOL 在共享內存中，保持組件
? DBMS_TRANSACTION 控制邏輯傳送并改善其性能
? DBMS_UTILITY 分析個別主題中的組件，檢查服務器是否在PARALLEL模式下運行并返回
時間。
Oracle supplied packages
The following list summzrizes and provides a brief description of the packages upplied with oracle9i
1. Calendar 日歷維護函數 2. Dbms_alter
3. Dbms_application_info
4. Dbms_aq 提供Oracle server 的隊列信息，并被用來添加一個信息到隊列 5. Dbms_aqadm 管理隊列中預定義信息的函數 6. Dbms_ddl 7. Dbms_debug
8. Dbsm_defer/ dbms_defer_query /dbms_defer_sys(第一個DBSM不知道是不是輸入錯誤) 9. Dbms_distribruted_trust_admin 10. Dbms_hs
11. Dbms_hs_extproc
12. Dbms_hs_passthrough 13. Dbms_iot 14. Dbms_job 15. Dbms_lob 16. Dbms_lock 17. Dbms_logmnr 18. Dbms_logmnr_d 19. Dbms_offline_og
20. Dbms_offline_snapshot 21. Dbms_olap
22. Dbms_oracle_trace_agent 23. Dbms_oracle_trace_user 24. Dbms_output 25. Dbms_pclxutil 26. Dbms_pipe 27. Dbms_profiler 28. Dbms_random 29. Dbms_rectifier_diff 30. Dbms_refresh 31. Dbms_repair 32. Dbms_repcat
33. Dbms_repcat_admin 34. Dbms_repcat_instatiate 35. Dbms_repcat_rgt 36. Dbms_reputil
37. Dbms_resource_nanager
38. Dbms_resource_manager_privs 39. Dbms_rls 40. Dbms_rowid 41. Dbms_session
42. Dbms_shared_pool 43. Dbms_snapshot 44. Dbms_space 45. Dbms_space_admin 46. Dsms_sql 47. Dbms_standard 48. Dbms_stats 49. Dbms_trace 50. Dbms_transaction 51. Dbms_tts 52. Dbms_utility 53. Debug_extproc 54. Outln_pkg 55. Plitblm 56. Sdo_admin 57. Sdo_geom 58. Sdo_migrate 59. Sdo_tune 60. Standard 61. Timeseries 62. Timescale 63. Stools 64. Utl_coll 65. Utl_file 66. Utl_http 67. Utp_pg 68. Utl_raw 69. Utl_ref
70. Vir_pkg
以上具信息參考下冊97—100頁
DBMS Packages and the scripts to execute them Dbms_alter dbmsalrt.sql
Dbms_application_info dbmsutil.sql Dbms_ddl dbmsutil.sql Dbms_lock dbmslock.sql Dbms_output dbmsotpt.sql Dbms_pipe dbmspipe.sql Dbms_session dbmsutil.sql Dbms_shared_pool dbmsspool.sql Dbms_sql dbmssql.sql