Oracle9i笔记2(2)

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

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库Oracle9i笔记2(2)在线全文阅读。