DB2 9.7

Using PL/SQL anonymous blocks in DB2 9.7

Learn how to use PL/SQL anonymous blocks in a DB2 environment


Content series:

This content is part # of # in the series: DB2 9.7

Stay tuned for additional content in this series.

This content is part of the series:DB2 9.7

Stay tuned for additional content in this series.

This article provides guidance for using anonymous blocks in DB2 9.7 in the following scenarios:

  • Testing, troubleshooting, and developing new PL/SQL stored procedures
  • Simulating application runs with PL/SQL
  • Building complex ad-hoc queries and reports on the fly with PL/SQL

Reviewing the prerequisites and system requirements

This article is written for PL/SQL applications developers and database administrators who are moving from Oracle to DB2. You should understand the concept of PL/SQL procedural language. SQL PL developers should use the corresponding function provided by the DB2 native compound SQL statements.

To use the examples in the article, you must have installed DB2 9.7 Workgroup or Enterprise Edition for Linux, UNIX, and Windows. Refer to the Related topics section to download a free trial version of DB2 9.7 for Linux, UNIX, and Windows.

Using the examples

You can run the examples using various tools, including the DB2 command line processor (CLP) and the command utilities (CLPPLUS), or the visual tools such as Optim Development Studio. If you plan to run the examples from CLP, you need to run the SET SQLCOMPAT PLSQL command to enable recognition of the forward slash character (/) on a new line as a PL/SQL statement termination character.

To enable support in DB2 for PL/SQL and Oracle data types, your database must be created with the DB2_COMPATIBILITY_VECTOR registry variable set to ORA, as shown in Listing 1.

Listing 1. Setting the DB2_COMPATIBILITY_VECTOR registry variable
db2 create db test

For this article, Listing 2 offers the code to create a simple e-commerce PL/SQL application that manages online orders after you create a database. You will also populate the tables with some sample data.

Listing 2. The example code
Access the example code

Understanding anonymous blocks

Anonymous blocks are PL/SQL structures that provide the capability to create and execute procedural code on the fly without persistently storing the code as database objects in the system catalogs. The concept of anonymous blocks is similar to UNIX shell scripts, which enable several manually entered commands to be grouped and executed as one step. As the name implies, anonymous blocks do not have a name, and for this reason they cannot be referenced from other objects. Although built dynamically, anonymous blocks can be easily stored as scripts in the operating system files for repetitive execution.

Anonymous blocks are standard PL/SQL blocks. They carry the syntax and obey the rules that apply to all PL/SQL blocks, including declaration and scope of variables, execution, exception handling, and SQL and PL/SQL usage.

The compilation and execution of anonymous blocks are combined in one step, while a PL/SQL stored procedure needs to be re-defined before use each time its definition changes. This is one of the significant advantages of the anonymous blocks over the persistently named database objects, such as stored procedures and user-defined functions, because it reduces the time between implementing changes in the code and the actual execution. That makes the anonymous blocks very useful when troubleshooting, prototyping, and testing procedural code, because these are tasks that usually require multiple change-and-execute runs.

Another benefit of the anonymous blocks is that they do not create any dependencies, and they do not require any special privileges for object creation, which can avoid complications in a production environment. The anonymous blocks provide the flexibility to run any procedural sequence of actions based on simple, select privileges, and they enable you to test without creating or implicating existing database objects.

You can execute anonymous blocks from:

  • SQL (for example, inside EXECUTE IMMEDIATE statements)
  • DB2 APIs such as JDBC and ODBC
  • Various DB2 tools, including CLP, CLPPlus, Optim Database Administrator, and Optim Development Studio

Prototyping PL/SQL code with anonymous blocks

In Listing 3, an application developer anticipates the business requirement for a mechanism to communicate (by e-mail) with the customers defined in the CUSTOMER table. To proactively satisfy this requirement, he decides to write a simple prototype PL/SQL anonymous block that sends email containing some message to the customers in the CUSTOMER table. Later, once the business requirement is finalized, the prototyped anonymous block can be enhanced and easily transformed into a new PL/SQL stored procedure. Note that this anonymous block uses new built-in packages, including UTL_SMTP (the package for sending emails) and DBMS_OUTPUT (the package for writing messages to the standard output), which are part of DB2 9.7.

Listing 3. A simple prototype PL/SQL anonymous block that sends email containing some message to the customers in the CUSTOMER table

   conn UTL_SMTP.connection;
   reply UTL_SMTP.reply;
   msg VARCHAR2(1024);
   sender VARCHAR2(255) DEFAULT 'demo\';
   recipients VARCHAR2(255);
   subject VARCHAR2(255) DEFAULT 'Quick notification';
   crlf VARCHAR2(2);

  crlf := UTL_TCP.CRLF;
  FOR row IN (SELECT first_name, email FROM customer) LOOP
      DBMS_OUTPUT.PUT_LINE('Sending test email to customer ' || row.first_name || '...');
      recipients :=;
      msg := 'FROM: ' || sender || crlf ||
                'TO: ' || recipients || crlf ||
                'SUBJECT: ' || subject || crlf ||
                crlf ||
                'Hi ' || row.first_name || ', this is a test notification.';

      UTL_SMTP.OPEN_CONNECTION('', 25, conn, 10, reply );
      UTL_SMTP.HELO(conn, 'localhost');
      UTL_SMTP.MAIL(conn, sender);
      UTL_SMTP.RCPT(conn, recipients);
      UTL_SMTP.DATA(conn, msg);

Sending test email to customer Mike...
Sending test email to customer Joan...
Sending test email to customer Colin...
Sending test email to customer Graham...
Sending test email to customer Patsy...

Simulating application runs with anonymous blocks

As mentioned, one of the most common uses of anonymous blocks is to invoke procedural language objects, typically for testing purposes. Listing 4 demonstrates how to simulate an application run with the help of a PL/SQL anonymous block. The code simulates an application run while capturing performance metrics. The anonymous block simulates the creation of 10 random orders for random customers from the existing CUSTOMER table. It also prints test start and end times, along with the order details for each run. It is easy to change the number of orders from 10 to 20 and then re-run this anonymous block without recompiling. You can also add more performance metrics for additional testing.

Listing 4. An application running with the help of a PL/SQL anonymous block

   v_customer_id customer.customer_id%TYPE; 
   product_id product.product_id%TYPE:=1;
   o_order_id orders.order_id%TYPE;
   v_test_start TIMESTAMP;
  FOR k IN 1..10 LOOP 
  	SELECT customer_id INTO v_customer_id FROM customer ORDER BY RAND() FETCH FIRST 1 
		ROW ONLY;                                                             
  	FOR i IN (
                  SELECT product_id, CAST(RAND()*50 as integer)+1 as quantity 
                  FROM product 
                  WHERE ROWNUM < CAST(RAND()*10 as integer)) 
      	      add_item_to_shopping_cart(i.product_id, i.quantity); 
        END LOOP;
        create_order(v_customer_id, o_order_id);  
  DBMS_OUTPUT.PUT_LINE('Test start: ' || v_test_start);     



Customer           : Mike, Smith
Order creation     : 07-06-2009
Estimated Delivery : 07-09-2009
Status             : Shipped  
Total price        : $ 150,615.44
Customer           : Joan, Jett
Order creation     : 07-06-2009
Estimated Delivery : 07-09-2009
Status             : Shipped  
Total price        : $ 159,445.77
Customer           : Colin, Taylor
Order creation     : 07-06-2009
Estimated Delivery : 07-09-2009
Status             : Shipped  
Total price        : $ 266,242.78
Test start: 2009-07-06-
Test end  : 2009-07-06-

Generating ad-hoc reports with anonymous blocks

A common reporting requirement is to concatenate data from more than one column into a single string. It is possible to write pure SQL statements with complex recursion to do this. However, you can use anonymous blocks instead to do this quickly with dynamic formatting options and simple logic flow.

Listing 5 shows how to create an ad-hoc report with the help of anonymous blocks. The code retrieves the list of all customers that ordered products from the store and the total value of all orders during last month. The names are displayed in one line and are separated by commas.

Listing 5. Creating an ad-hoc report with the help of anonymous blocks

     v_customer_names VARCHAR2(4000);
     v_total_sales NUMBER(19,2);
     DBMS_OUTPUT.PUT_LINE('           Last Month Sales Report     ');

     DBMS_OUTPUT.PUT('Customer List: ');
     FOR row IN 
       (SELECT distinct(a.customer_id),first_name, last_name FROM customer a, orders b 
		WHERE a.customer_id=b.order_id AND b.creation_time>CURRENT DATE -1 month)
      v_customer_names := v_customer_names || '"' || row.first_name || ' ' || 
		row.last_name || '", '; 
     END LOOP;
     IF(LENGTH(v_customer_names) > 0) THEN
        v_customer_names := SUBSTR(v_customer_names,1, LENGTH(v_customer_names)-2);
        v_customer_names := 'None';
     END IF; 
     SELECT NVL(SUM(total_price),0) INTO v_total_sales FROM orders WHERE creation_time>
	 CURRENT DATE - 1 month;
     DBMS_OUTPUT.PUT_LINE('Total Sales: ' || TO_CHAR(v_total_sales, '$99,999,999.99')); 


          Last Month Sales Report     ---------------------------------------
Customer List: "Mike Smith", "Joan Jett", "Colin Taylor", "Graham Norton", "Patsy Stone"
Total Sales: $ 49,772.56


This article presented the following:

  • The PL/SQL anonymous blocks feature introduced in DB2 9.7.
  • The concept of anonymous blocks.
  • How anonymous blocks can facilitate the process of testing, prototyping, and troubleshooting of procedural code.
  • How anonymous blocks can simulate application runs.
  • How to use anonymous blocks for powerful ad-hoc reporting.

With the support for PL/SQL anonymous blocks, you can quickly enable PL/SQL solutions in the DB2 environment by using existing PL/SQL scripts or by using individual PL/SQL and SQL statements that work with other database management systems.

Downloadable resources

Related topics


Sign in or register to add and subscribe to comments.

Zone=Information Management
ArticleTitle=DB2 9.7: Using PL/SQL anonymous blocks in DB2 9.7