Oracle9 i Developer: PL/SQL Programming - PowerPoint PPT Presentation

slide1 n.
Skip this Video
Loading SlideShow in 5 Seconds..
Oracle9 i Developer: PL/SQL Programming PowerPoint Presentation
Download Presentation
Oracle9 i Developer: PL/SQL Programming

play fullscreen
1 / 31
Download Presentation
Oracle9 i Developer: PL/SQL Programming
Download Presentation

Oracle9 i Developer: PL/SQL Programming

- - - - - - - - - - - - - - - - - - - - - - - - - - - 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(; • 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


  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