pl/sqlpl/sql oracle10g developer: pl/sql programming chapter 10 oracle-supplied packages and...
TRANSCRIPT
PL/SQL
Oracle10g Developer: PL/SQL Programming
Chapter 10
Oracle-Supplied Packagesand SQL*Loader
PL/SQL
Oracle10g Developer: PL/SQL Programming 2
Objectives
• After completing this lesson, you should be able to understand:– Using communications packages– Generating output via packages– Including large objects in the Oracle
database– Exploring dynamic SQL and PL/SQL– Identifying other important built-in
packages– Using the SQL*Loader utility
PL/SQL
Oracle10g Developer: PL/SQL Programming 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
PL/SQL
Oracle10g Developer: PL/SQL Programming 4
Communications Packages
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
PL/SQL
Oracle10g Developer: PL/SQL Programming 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
PL/SQL
Oracle10g Developer: PL/SQL Programming 6
DBMS_PIPE Example
PL/SQL
Oracle10g Developer: PL/SQL Programming 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
PL/SQL
Oracle10g Developer: PL/SQL Programming 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);
PL/SQL
Oracle10g Developer: PL/SQL Programming 9
UTL_MAIL
• Simplifies sending e-mail via a PL/SQL block• Introduced in Oracle10g• Scripts must be executed to set up the
package• SMTP server must be defined on the system
PL/SQL
Oracle10g Developer: PL/SQL Programming 10
UTL_MAIL Example
PL/SQL
Oracle10g Developer: PL/SQL Programming 11
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
PL/SQL
Oracle10g Developer: PL/SQL Programming 12
UTL_SMTP
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
PL/SQL
Oracle10g Developer: PL/SQL Programming 13
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
PL/SQL
Oracle10g Developer: PL/SQL Programming 14
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
PL/SQL
Oracle10g Developer: PL/SQL Programming 15
Generating Output
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
PL/SQL
Oracle10g Developer: PL/SQL Programming 16
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
PL/SQL
Oracle10g Developer: PL/SQL Programming 17
DBMS_OUTPUT Example
PL/SQL
Oracle10g Developer: PL/SQL Programming 18
UTL_FILE
• Enables reading and writing text data to operating system files (import and export data)
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
PL/SQL
Oracle10g Developer: PL/SQL Programming 19
UTL_FILE Example
• Write to a file
PL/SQL
Oracle10g Developer: PL/SQL Programming 20
UTL_FILE Example
• Read a file
PL/SQL
Oracle10g Developer: PL/SQL Programming 21
Large Objects (LOBs)
• Handle media such as images, video segments, and large documents
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
PL/SQL
Oracle10g Developer: PL/SQL Programming 22
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
PL/SQL
Oracle10g Developer: PL/SQL Programming 23
DBMS_LOB Example
PL/SQL
Oracle10g Developer: PL/SQL Programming 24
Dynamic SQL and PL/SQL
• Allow construction and parsing of statements at run time
• Enable the execution of DDL statements from within PL/SQL
• Two mechanisms– DBMS_SQL package
– Dynamic SQL
• Covered in Chapter 10
PL/SQL
Oracle10g Developer: PL/SQL Programming 25
DBMS_JOB
• Enables jobs to be scheduled for execution
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
PL/SQL
Oracle10g Developer: PL/SQL Programming 26
DBMS_JOB
• INIT.ORA settings JOB_QUEUE_PROCESSES=1 JOB_QUEUE_INTERVAL=60
• SUBMIT procedurePROCEDURE 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 );
PL/SQL
Oracle10g Developer: PL/SQL Programming 27
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;
/
PL/SQL
Oracle10g Developer: PL/SQL Programming 28
DBMS_JOB Example (continued)
PL/SQL
Oracle10g Developer: PL/SQL Programming 29
DBMS_DDL
• Allows access to two specific DDL statements:– ALTER_COMPILE
– ANALYZE_OBJECT
PL/SQL
Oracle10g Developer: PL/SQL Programming 30
Exploring More
• Search the OTN Web site for more Oracle-supplied packages
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
PL/SQL
Oracle10g Developer: PL/SQL Programming 31
SQL*Loader Utility
• Client tool provided by Oracle• Simplifies loading data from a flat file into the
database• Can execute a large number of rows
efficiently• Can read data in many different formats
PL/SQL
Oracle10g Developer: PL/SQL Programming 32
SQL*Loader Utility (continued)
• Involves three items– A data file– A control file that defines how the data
should be read and loaded– A command to execute the utility
PL/SQL
Oracle10g Developer: PL/SQL Programming 33
SQL*Loader Utility Example
"Reeding Trades",Brazil,25,2"Beans Here, There, Everywhere",Ethiopia,,4"Coffee Suppliers Inc.",Spain,20,1"Traders First Beans",Costa Rica, 50,2
•Example data file for coffee suppliers
PL/SQL
Oracle10g Developer: PL/SQL Programming 34
SQL*Loader Utility Example (continued)
LOAD DATAINFILE 'C:\vendors.csv'REPLACEINTO TABLE bb_vendor_listFIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ' "'TRAILING NULLCOLS(vname,v_cntry,min_lbs INTEGER EXTERNAL,ship_days INTEGER EXTERNAL)
•Example control file
PL/SQL
Oracle10g Developer: PL/SQL Programming 35
SQL*Loader Utility Example (continued)
sqlldr scott/tiger@orcl control=C:\vendors.ctl
•Example command to execute
PL/SQL
Oracle10g Developer: PL/SQL Programming 36
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• UTL_HTTP enables HTML source retrieval• UTL_TCP enables TCP/IP communications
PL/SQL
Oracle10g Developer: PL/SQL Programming 37
Summary (continued)
• DBMS_OUTPUT allows data display• UTL_FILE enables reading and writing to text
files• DBMS_LOB manages LOBs• DBMS_JOB provides job scheduling capabilities• DBMS_DDL enables the COMPILE and
ANALYZE commands to be processed within PL/SQL
• SQL*Loader utility enables loading data from external files