1 / 37

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

Learn how to use communications packages, generate output, work with large objects, explore dynamic SQL and PL/SQL, and identify important built-in packages in Oracle 10g Developer. Includes examples and scripts.

loriowen
Download Presentation

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

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

More Related