1 / 31

Oracle9 i Developer: PL/SQL Programming

Oracle9 i Developer: PL/SQL Programming. Chapter 9. Oracle-Supplied Packages. Objectives. Use communications packages Generate output via packages Include large objects in the Oracle database Explore dynamic SQL and PL/SQL Identify other important built-in packages.

nira
Download Presentation

Oracle9 i Developer: PL/SQL Programming

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle9i Developer: PL/SQL Programming Chapter 9 Oracle-Supplied Packages

  2. Objectives • Use communications packages • Generate output via packages • Include large objects in the Oracle database • Explore dynamic SQL and PL/SQL • Identify other important built-in packages

  3. Brewbean’s Challenge • Credit card verification • Real-time messages • E-mail generation • Import external file data • Include image files in the database • Schedule program execution

  4. Built-in Package Name Description Script Filename DBMS_PIPE Allows different database sessions to communicate dbmspipe.sql DBMS_ALERT Enables notification of database events dbmsalrt.sql UTL_SMTP Enables e-mail features utlsmtp.sql Communications Packages

  5. DBMS_PIPE • Allows different sessions in the same instance to communicate • Uses buffers in the SGA • Typically used to interface with the operating system or an external system • Sending a message is a two step process of packing and sending

  6. DBMS_PIPE Example

  7. DBMS_ALERT • Allows real-time messages or alerts to be sent to users upon a particular event • Accomplished in a database trigger to be associated with an event • An example use is online auctions • Process includes: register an alert name, set when alert should signal, and identify users that should be recipients

  8. DBMS_ALERT Example • Register name: DBMS_ALERT.REGISTER(‘new_bid’); • Fire signal in database trigger: DBMS_ALERT.SIGNAL(‘new_bid’, TO_CHAR(:new.bid)); • Register recipient: DBMS_ALERT.WAITONE(‘new_bid’, v_msg, v_status, 600);

  9. UTL_SMTP • Simplifies e-mail generation • Simple Mail Transfer Protocol (SMTP) used to send e-mails • Programmatically create all the parts of an e-mail • Used to communicate with customers, suppliers, and internal employees as appropriate

  10. Function Name Description HELO Performs initial handshaking to identify the sender to the mail server MAIL Initiates a mail transaction which sends messages to mailbox destinations RCPT Identifies each of the recipients of an e-mail DATA Specifies the lines in the body of an e-mail RSET Aborts the current mail transaction NOOP Requests a reply from the mail server to verify connection is still alive QUIT Terminates the SMTP session and disconnects from the mail server OPEN_CONNECTION Opens a connection to the mail server OPEN_DATA Sends the DATA command WRITE_DATA Adds data to message body CLOSE_DATA Ends the message UTL_SMTP

  11. UTL_HTTP • Used to analyze HTML source of Web pages • Makes Hypertext Transfer Protocol (HTTP) calls from within PL/SQL • REQUEST_PIECES function will retrieve the HTML source of a specified URL in 2,000 byte segments

  12. UTL_TCP • Allows low-level calls using TCP/IP • Internet communications rely on TCP/IP • The UTL_SMTP and UTL_HTTP packages rely on this package

  13. Built-in Package Name Description Script Filename DBMS_OUTPUT Displays data to the screen dbmsotpt.sql UTL_FILE Read and write data to external files utlfile.sql Generating Output

  14. DBMS_OUTPUT • Displays data from within PL/SQL code • Most popularly used for debugging • In SQL*Plus, SET SERVEROUTPUT ON must be set • The PUT procedure continues to place data on the same output line • The PUT_LINE procedure will start a new line

  15. DBMS_OUTPUT Example

  16. Program Unit Name Description FOPEN Opens a file to write to or be read PUT_LINE Writes a line to a file GET_LINE Reads a line from a file FCLOSE Closes a file UTL_FILE • Enables reading and writing text data to operating system files (import and export data)

  17. UTL_FILE Example • Write to a file:

  18. UTL_FILE Example • Read a file:

  19. LOB Type Description BLOB Binary large object such as a photo file CLOB Character large object such as text documentation BFILE Binary large object such as a streaming video or movie file NCLOB Fixed-width character data for storing character data in other languages Large Objects (LOBs) • Handle media such as images, video segments, and large documents

  20. LOBs • Enable the storage of large objects as columns in a database table • Can hold up to 4GB of data • Multiple LOB columns allowed • All except BFILE are stored internally in the database • A LOB column contains pointer to actual LOB data

  21. DBMS_LOB Example

  22. Dynamic SQL and PL/SQL • Allow construction and parsing of statements at runtime • Enable the execution of DDL statements from within PL/SQL • Two mechanisms: • DBMS_SQL package • Dynamic SQL • Covered in Chapter 10

  23. Program Unit Name Description BROKEN Flags the job as broken so it will not be executed CHANGE Alters job parameters set by a user INTERVAL Modifies execution interval for a job ISUBMIT Submits a job with a specified job number NEXT_DATE Modifies the next date of execution REMOVE Deletes job from the queue RUN Executes the specified job immediately SUBMIT Adds a job to the queue USER_EXPORT Creates text of call to recreate the job WHAT Modifies the PL/SQL code to be executed DBMS_JOB • Enables jobs to be scheduled for execution

  24. DBMS_JOB • INIT.ORA settings: JOB_QUEUE_PROCESSES=1 JOB_QUEUE_INTERVAL=60 • SUBMIT procedure: PROCEDURE submit ( job OUT BINARY_INTEGER, what IN VARCHAR2, next_date IN DATE DEFAULT sysdate, interval IN VARCHAR2 DEFAULT 'null', no_parse IN BOOLEAN DEFAULT FALSE, instance IN BINARY_INTEGER DEFAULT 0, force IN BOOLEAN DEFAULT FALSE );

  25. DBMS_JOB Example BEGIN DBMS_JOB.SUBMIT (job => :jobno, what => 'BEGIN prod_sum_sp; END;', next_date => TRUNC(SYSDATE+1), interval => 'TRUNC(SYSDATE+1)' ); COMMIT; END; /

  26. DBMS_JOB Example

  27. DBMS_DDL • Allows access to two specific DDL statements: • ALTER_COMPILE • ANALYZE_OBJECT

  28. Package Name Description DBMS_JAVA Controls the behavior of the Java Virtual Machine used to run Java stored procedures DBMS_METADATA Retrieves information about database objects DBMS_RANDOM Random number generator DBMS_SESSION Allows access to session options directly from PL/SQL DBMS_UTILITY Contains a miscellaneous group of programs ranging from capabilities to assist in procedure management to reporting error information DBMS_XMLGEN Converts data from an SQL query into XML UTL_HTTP Accesses Web pages UTL_INADDR Retrieves Internet site host name or IP address Exploring More • Search the OTN web site for more Oracle-supplied packages

  29. Summary • Oracle-supplied packages are pre-built packages to address common processing needs • DBMS_PIPE allows communication between sessions • DBMS_ALERT enables event notification • UTL_SMTP simplifies e-mail generation

  30. Summary • UTL_HTTP enables HTML source retrieval • UTL_TCP enables TCP/IP communications • DBMS_OUTPUT allows data display • UTL_FILE enables reading and writing to text files • DBMS_LOB manages LOBs

  31. Summary • DBMS_JOB provides job scheduling capabilities • DBMS_DDL enables the COMPILE and ANALYZE commands to be processed within PL/SQL

More Related