Overview of Some Useful Packages

 Previous
Previous		 Next
Next

This section provides a summary of some useful packages.

This section contains the following topics:

DBMS_OUTPUT Package

The DBMS_OUTPUT package enables you to display output from PL/SQL blocks, subprograms, packages, and triggers. This package is especially useful for displaying PL/SQL debugging information. The PUT_LINE procedure outputs information to a buffer that can be read by another trigger, procedure, or package. You display the information by calling the GET_LINE procedure or by setting the SERVEROUTPUT ON setting in SQL Command Line.

For more information, see "Inputting and Outputting Data with PL/SQL". For examples of the use of DBMS_OUTPUT.PUT_LINE, see Example: Creating a Simple Stored Procedure, Example: Creating a Stored Procedure That Uses Parameters, and Example: Creating a Stored Procedure With the AUTHID Clause.


See Also:

Oracle Database PL/SQL Packages and Types Reference for information about the DBMS_OUTPUT packages

DBMS_RANDOM Package

The DBMS_RANDOM package provides a built-in random number generator. DBMS_RANDOM can be explicitly initialized, but does not need to be initialized before calling the random number generator. It will automatically initialize with the date, userid, and process id if no explicit initialization is performed.

If this package is seeded twice with the same seed, then accessed in the same way, it will produce the same results in both cases.

The DBMS_RANDOM.VALUE function can be called with no parameters to return a random number, greater than or equal to 0 and less than 1, with 38 digits to the right of the decimal (38-digit precision). Alternatively, you can call the function with low and high parameters to return a random number which is greater than or equal to the low parameter and less than high parameter.

Example: Using the DBMS_RANDOM Package shows the use of the DBMS_RANDOM.VALUE function to return random numbers from 1 ton 100. The random numbers are truncated to integer values and stored in an array.

Using the DBMS_RANDOM Package

DECLARE
-- declare an array type with 10 elements of NUMBER
  TYPE random_array IS VARRAY(10) OF NUMBER;
  random_numbers random_array;
  j NUMBER;
BEGIN
  random_numbers := random_array(); -- initialize the array
  FOR i IN 1..10 LOOP
-- add an element to the array
    random_numbers.EXTEND(1); 
-- insert a random number in the next element in the array
    random_numbers(i) := TRUNC(DBMS_RANDOM.VALUE(1,101));

    j := 1;
-- make sure the random number is not already in the array
-- if it is, generated a new random number and check again
    WHILE j < random_numbers.LAST LOOP
       IF random_numbers(i) = random_numbers(j) THEN
          random_numbers(i) := TRUNC(DBMS_RANDOM.VALUE(1,101));
          j := 1;
       ELSE
         j := j + 1;
       END IF;
    END LOOP;

 END LOOP;

-- display the random numbers in the array
 FOR k IN random_numbers.FIRST..random_numbers.LAST LOOP
   DBMS_OUTPUT.PUT_LINE(random_numbers(k));
 END LOOP;

END;
/

See Also:

Oracle Database PL/SQL Packages and Types Reference for information about the DBMS_RANDOM packages

HTP Package

With the HTP package, you can create a Web page using HTP hypertext procedures to generate HTML tags. For example the procedure HTP.PARA generates the <P> paragraph tag and HTP.ANCHOR generates the <A> anchor tag. You can also use HTP.PRINT to explicit print HTML tags.

Note that for nearly every HTP procedure that generates one or more HTML tags, there is a corresponding HTF package hypertext function with identical parameters.

Example: Using HTP Print Procedure is a modification of Example: Passing Parameters to a Cursor in PL/SQL using the HTP.PRINT procedure. For each DBMS_OUTPUT.PUT_LINE in the original example, an HTP.PRINT has been substituted in the modified example.

Using HTP Print Procedure

CREATE OR REPLACE PROCEDURE htp_yearly_bonus AS 
-- declare variables for data fetched from cursor
  empid       employees.employee_id%TYPE; -- variable for employee_id
  hiredate    employees.hire_date%TYPE;   -- variable for hire_date
  firstname   employees.first_name%TYPE;  -- variable for first_name
  lastname    employees.last_name%TYPE;   -- variable for last_name
  rowcount    NUMBER;
  bonusamount NUMBER;
  yearsworked NUMBER;
-- declare the cursor with a parameter
  CURSOR cursor1 (thismonth NUMBER)IS 
    SELECT employee_id, first_name, last_name, hire_date FROM employees 
       WHERE EXTRACT(MONTH FROM hire_date) = thismonth;
BEGIN
  HTP.PRINT('<html>');                                   -- HTML open
  HTP.PRINT('<head>');                                   -- HEAD open
  HTP.PRINT('<title>Using the HTP Package</title>');     -- title line
  HTP.PRINT('</head>');                                  -- HEAD close 
  HTP.PRINT('<body TEXT="#000000" BGCOLOR="#FFFFFF">') ; -- BODY open
-- open and pass a parameter to cursor1, select employees hired on this month
  OPEN cursor1(EXTRACT(MONTH FROM SYSDATE));
  HTP.PRINT('<h1>----- Today is ' || TO_CHAR(SYSDATE, 'DL') || ' -----</h1>');
  HTP.PRINT('<p>Employees with yearly bonus amounts:</p>');
  HTP.PRINT('<pre>'); -- insert the preformat tag
  LOOP
-- fetches 4 columns into variables
    FETCH cursor1 INTO empid, firstname, lastname, hiredate; 
-- check the cursor attribute NOTFOUND for the end of data
    EXIT WHEN cursor1%NOTFOUND;
-- calculate the yearly bonus amount based on months (years) worked
  yearsworked := ROUND( (MONTHS_BETWEEN(SYSDATE, hiredate)/12) );
  IF yearsworked > 10   THEN bonusamount := 2000;
    ELSIF yearsworked > 8 THEN bonusamount := 1600;
    ELSIF yearsworked > 6 THEN bonusamount := 1200;
    ELSIF yearsworked > 4 THEN bonusamount := 800;
    ELSIF yearsworked > 2 THEN bonusamount := 400;
    ELSIF yearsworked > 0 THEN bonusamount := 100;
  END IF;
-- display the employee Id, first name, last name, hire date, and bonus 
-- for each record (row) fetched
    HTP.PRINT( empid || ' ' || RPAD(firstname, 21, ' ') ||
         RPAD(lastname, 26, ' ') || hiredate || TO_CHAR(bonusamount, '$9,999'));
  END LOOP;
  HTP.PRINT('</pre>'); -- end the preformat tag
  rowcount := cursor1%ROWCOUNT;
  HTP.PRINT('<p>The number of rows fetched is ' || rowcount || '</p>');
  CLOSE cursor1;
  HTP.PRINT('</body>');                                  -- BODY close
  HTP.PRINT('</html>');                                  -- HTML close
END;
/

See Also:

Oracle Database PL/SQL Packages and Types Reference for information about the HTP packages

UTL_FILE Package

The UTL_FILE package enables PL/SQL programs to read and write operating system (OS) text files. It provides a restricted version of standard OS stream file I/O, including open, put, get, and close operations. When you want to read or write a text file, you call the FOPEN function, which returns a file handle for use in subsequent procedure calls. When opening a file with FOPEN, the file can be opened in append (A), read (R), or write (W) mode. After a file is opened, you can use UTL_FULE procedures such as PUT_LINE to write a text string and line terminator to an open file and GET_LINE to read a line of text from an open file into an output buffer.

Before a user can run UTL_FILE procedures, there must be an accessible directory for the user to read and write files. As the user SYSTEM (or SYS), you need to run the SQL CREATE DIRECTORY statement to set up an accessible directory and run the SQL GRANT ... DIRECTORY statement to grant privileges to that directory. Example: Setting up a Directory for Use With UTL_FILE shows how to set up an existing directory and grant the HR user access to that directory.

Setting up a Directory for Use With UTL_FILE

-- first connect as SYSTEM to run the SQL statements on the directory
-- when you run the following to connect as SYSTEM, use your password for SYSTEM
CONNECT SYSTEM/ORACLE
-- the following sets up directory access for /tmp on a Linux platform
CREATE OR REPLACE DIRECTORY temp_dir AS '/tmp';
-- you could use 'c:\temp' for temp_dir on a Windows platform, note that
-- c:\temp must exist on the Windows computer
-- the following grants the user read and write access to the directory
GRANT READ, WRITE ON DIRECTORY temp_dir TO HR;
-- now connect as user HR/HR to check directory setup 
-- when you connect as HR, use your password for HR
CONNECT HR/HR
-- the following SELECT query lists information about all directories that 
-- have been set up for the user
SELECT * FROM ALL_DIRECTORIES;
-- if TEMP_DIR is listed, then you are ready to run UTL_FILE procedures as HR

After the SQL statements in Example: Setting up a Directory for Use With UTL_FILE are executed, you can connect as the user HR and run UTL_FILE procedures. Some simple examples are shown in Example: Using the UTL_FILE Package.

Using the UTL_FILE Package

-- connect as user HR and run UTL_FILE procedures
DECLARE
  string1 VARCHAR2(32767);
  file1 UTL_FILE.FILE_TYPE; 
BEGIN
  file1 := UTL_FILE.FOPEN('TEMP_DIR','log_file_test','A'); -- open in append mode
  string1 := TO_CHAR(SYSDATE) || ' UTL_FILE test';
  UTL_FILE.PUT_LINE(file1, string1); -- write a string to the file
  UTL_FILE.FFLUSH(file1);
  UTL_FILE.FCLOSE_ALL; -- close all open files
END;
/

DECLARE
  string1 VARCHAR2(32767);
  file1 UTL_FILE.FILE_TYPE; 
BEGIN
  file1 := UTL_FILE.FOPEN('TEMP_DIR','log_file_test','R'); -- open in read mode
  UTL_FILE.GET_LINE(file1, string1, 32767); -- read a string from the file
  DBMS_OUTPUT.PUT_LINE(string1); -- display the string
  UTL_FILE.FCLOSE_ALL; -- close all open files
END;
/

See Also:

Oracle Database PL/SQL Packages and Types Reference for information about the UTL_FILE packages

Previous
Previous		 Next
Next