Oracle PL/SQL Programming: Chapter 10 - Oracle-Supplied Packages and SQL*Loader

1. Oracle10g Developer: PL/SQL Programming Chapter 10 Oracle-Supplied Packages and SQL*Loader

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

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_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

10. UTL_MAIL Example

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

12. 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

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

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

15. 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

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

17. DBMS_OUTPUT Example

18. 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)

19. UTL_FILE Example • Write to a file

20. UTL_FILE Example • Read a file

21. 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

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

23. DBMS_LOB Example

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

25. 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

26. 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 );

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; /

28. DBMS_JOB Example (continued)

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

30. 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

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

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

33. SQL*Loader Utility Example • Example data file for coffee suppliers "Reeding Trades",Brazil,25,2 "Beans Here, There, Everywhere",Ethiopia,,4 "Coffee Suppliers Inc.",Spain,20,1 "Traders First Beans",Costa Rica, 50,2

34. SQL*Loader Utility Example (continued) • Example control file LOAD DATA INFILE 'C:\vendors.csv' REPLACE INTO TABLE bb_vendor_list FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ' "' TRAILING NULLCOLS (vname, v_cntry, min_lbs INTEGER EXTERNAL, ship_days INTEGER EXTERNAL)

35. SQL*Loader Utility Example (continued) • Example command to execute sqlldr scott/tiger@orcl control=C:\vendors.ctl

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

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