310 likes | 466 Views
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.
E N D
Oracle9i 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
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
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
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
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
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);
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
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
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
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
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
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
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)
UTL_FILE Example • Write to a file:
UTL_FILE Example • Read a file:
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
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
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
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
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 );
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; /
DBMS_DDL • Allows access to two specific DDL statements: • ALTER_COMPILE • ANALYZE_OBJECT
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
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
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
Summary • DBMS_JOB provides job scheduling capabilities • DBMS_DDL enables the COMPILE and ANALYZE commands to be processed within PL/SQL