Database Tutorial

Course Tutorial Site

Site Admin

CIT325: Lab 12 Instructions

without comments

Lab #12: Oracle Assignment

Objectives

The lab is designed to teach you how to work with Oracle’s Dynamic SQL (NDS). NDS allows you to write PL/SQL blocks that dynamically generate other SQL statements or PL/SQL blocks.

  • Learn how to create and run a dynamically generated SQL statement.
  • Learn how to create and run a function that returns a result from a dynamically generated SQL statement.
  • Learn how to work with bind variables.

Business Scenario

Sometimes you have use-cases that require your program’s behaviors to change dynamically at runtime. Oracle provides Native Dynamic SQL (NDS) and an older DBMS_OUTPUT package for these type of use-cases. NDS is the easier to use solution and lets you manage the following use-cases:

  • Creating dynamic SQL statements as strings to run in your code blocks.
  • Creating dynamic SQL statements as strings that accept positionally bound variables, known as bind variables. They can be:

    • Traditional ANSI standard INSERT, UPDATE, MERGE, or DELETE statements that return nothing.
    • Oracle INSERT, UPDATE, MERGE, or DELETE statements that use the RETURNING INTO clause.
    • Queries that return a static column result set for one row of data.
    • Queries that return a static column result set for any number of rows of data.

  • Creating dynamic PL/SQL blocks that accept:

    • Only inputs, where the inputs may be numeric or string literal values or variables.
    • Inputs and outputs – where the inputs may only be variables because otherwise they couldn’t output results from the dynamic statement.

This lab asks you to work with creating a dynamic query that returns a static column result set for any number of rows of data. You need to use the DBMS_OUTPUT package when you want your program to return a variable number of columns or you need to convert a LONG data type to a CLOB data type.

Help Section

The following is an set of sample programs and steps that address concepts in the lab. The instructor should review these in classroom or in a Google Hangout with students. Click the Instructional Material link to see the preparation lesson material.

Instructional Material →

The following code shows you how to create and test a Dynamic Native SQL (NDS) statement. You need to create an object type, a table of object types, and a function that returns a table of that object type.

  • How to create a contact_obj object type, like the following: 

    CREATE OR REPLACE
  TYPE contact_obj IS OBJECT
  ( first_name   VARCHAR2(20)
  , middle_name  VARCHAR2(20)
  , last_name    VARCHAR2(20));
/

    After you create the contact_obj type, you create a table of contact_obj like:

    CREATE OR REPLACE
  TYPE contact_tab IS TABLE of contact_obj;
/

    With the contact_obj object type and contact_tab collection, you can create a customer_list function that returns a contact_tab collection. The following does that by doing the following:

    With the contact_obj object type and contact_tab collection, you can create a customer_list function that returns a contact_tab collection. The following creates the customer_list function:

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

    CREATE OR REPLACE
  FUNCTION customer_list
  ( pv_last_name  VARCHAR2 ) RETURN contact_tab IS
 
    /* Declare a record type. */
    TYPE customer_rec IS RECORD
    ( first_name   VARCHAR2(20)
    , middle_name  VARCHAR2(20)
    , last_name    VARCHAR2(20));
 
    /* Declare reference cursor for an NDS cursor. */
    customer_cur   SYS_REFCURSOR;
 
    /* Declare a customer row for output from an NDS cursor. */
    customer_row   CUSTOMER_REC;
    customer_set   CONTACT_TAB := contact_tab();
 
    /* Declare dynamic statement. */
    stmt  VARCHAR2(2000);
  BEGIN
    /* Create a dynamic statement. */
    stmt := 'SELECT first_name, middle_name, last_name '
         || 'FROM   contact '
         || 'WHERE  REGEXP_LIKE(last_name,''^.*''||:input||''.*$'')';
 
    /* Open and read dynamic cursor. */
    OPEN customer_cur FOR stmt USING pv_last_name;
    LOOP
      /* Fetch the cursror into a customer row. */
      FETCH customer_cur INTO customer_row;
      EXIT WHEN customer_cur%NOTFOUND;
 
      /* Extend space and assign a value collection. */      
      customer_set.EXTEND;
      customer_set(customer_set.COUNT) :=
        contact_obj( first_name  => customer_row.first_name
                   , middle_name => customer_row.middle_name
                   , last_name   => customer_row.last_name );
    END LOOP;
 
    /* Return customer set. */
    RETURN customer_set;
  END customer_list;
/

    The following qualifies what the customer_list function does:

    • Create a customer_rec record type on lines 6 through 9 that mirrors the customer_obj object type.
    • Create a customer_cur system reference cursor that is weakly typed cursor on line 12.
    • Create customer_row variable of the customer_rec data type on line 15.
    • Create a customer_set variable of the contact_tab collection type, and create an empty instance of the contact_tab collection.
    • Create a stmt string variable to hold a Native Dynamic SQL (NDS) variable on line 19.
    • Assign an NDS string to the stmt variable on lines 22 through 24.
    • Open the customer_cur system reference cursor with the stmt dynamic statement, and assign the pv_last_name variable to the :input bind variable on line 27.
    • Fetch customer_cur system reference cursor into the customer_row variable of the customer_rec data type on line 30.
    • Extend space in the customer_set variable of the contact_tab collection on line 34.
    • Use a customer_row variable to create an instance of the customer_set collection on lines 35 through 38.

  • You can call the customer_list function, like 

    1
2
3
4
5
6
7
8

    COL last_name   FORMAT A12 HEADING "Last Name"
COL first_name  FORMAT A12 HEADING "First Name"
COL middle_name FORMAT A12 HEADING "Middle Name"
SELECT   il.last_name
,        il.first_name
,        il.middle_name
FROM     TABLE(customer_list('Potter')) il
ORDER BY 1, 2, 3;

    It returns the following data set:

    LAST Name    FIRST Name   Middle Name
------------ ------------ ------------
Potter	     Albus	  Severus
Potter	     Ginny
Potter	     Harry
Potter	     James	  Sirius
Potter	     Lily	  Luna

Students may want or need supplemental articles that let them review tips and techniques. The following is a function that checks for valid dates inside strings. You should run this type of check after you rule out a number and alphanumeric string.

There are four elements to this lab. It requires you to create:

  • An item_obj object type
  • An item_tab collection type
  • An item_list function

It is important that you develop each piece sequentially. You should also test each piece of the code as you develop them.

Lab Description

  1. [25 points] Create the item_obj object type, a item_tab collection object type, and item_list function.

    The sections cover the detailed tasks of the lab:

Instruction Details →

The following qualifies the instructions for this element’s steps:

  • You create the item_obj object type: 

    desc item_obj

    It will display the following item_obj definition:

     Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 TITLE						    VARCHAR2(60)
 SUBTITLE					    VARCHAR2(60)
 RATING 					    VARCHAR2(8)
 RELEASE_DATE					    DATE

  • You create the item_tab object type: 

    desc item_tab

    It will display the following item_tab definition:

     item_tab TABLE OF ITEM_OBJ
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 TITLE						    VARCHAR2(60)
 SUBTITLE					    VARCHAR2(60)
 RATING 					    VARCHAR2(8)
 RELEASE_DATE					    DATE

  • After you have created the item_obj object type and item_tab collection object type, you create a item_list function that does the following features:

    • Assign TRUNC(SYSDATE) + 1 as the DEFAULT value of the pv_end_date parameter.
    • Create an item_rec record type that mirrors the item_obj object type.
    • Create an item_cur system reference cursor that is weakly typed cursor.
    • Create an item_row variable of the item_rec data type.
    • Create an item_set variable of the item_tab collection type, and create an empty instance of the item_tab collection.
    • Create a stmt string variable to hold a Native Dynamic SQL (NDS) variable.
    • Assign an NDS string to the stmt variable. The string should return the following columns:

      • item_title with an alias of title
      • item_subtitle with an alias of subtitle
      • item_rating with an alias of rating
      • item_release_date with an alias of release_date

      and the WHERE clause should check for an item_rating_agency value of MPAA and an item_release_date between the:

      • bind variables for the pv_start_date and pv_end_date values

    • Open the item_cur system reference cursor with the stmt dynamic statement, and assign the pv_start_date and pv_end_date variables inside the USING clause.
    • Fetch the item_cur system reference cursor into the item_row variable of the item_rec data type.
    • Extend space in the item_set variable of the item_tab collection.
    • Use a item_row variable to create an instance of the item_set collection.

  • After you create the item_list function, you can describe the item_list function: 

    desc item_list

    It will display the following item_list function definition:

    FUNCTION item_list RETURNS ITEM_TAB
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 PV_START_DATE			DATE			IN
 PV_END_DATE			DATE			IN     DEFAULT

Test Case

  • You test the item_list function with a query that uses the TABLE function and returns only the title and rating members from each of the item_obj object types.

  • The query should return the following results when you submit a pv_start_date value of January 1, 2000: 

    TITLE							     RATING
------------------------------------------------------------ --------
Around the World in 80 Days				     NR
Around the World in 80 Days				     PG
Casino Royale						     PG-13
Casino Royale						     PG-13
Die Another Day 					     PG-13
Die Another Day 					     PG-13
Die Another Day 					     PG-13
Golden Eye						     PG-13
Golden Eye						     PG-13
Tomorrow Never Dies					     PG-13
The World Is Not Enough 				     PG-13
The World Is Not Enough 				     PG-13
Brave Heart						     R
Christmas Carol 					     NR
Scrooge 						     G
Clear and Present Danger				     PG-13
Harry Potter and the Sorcer's Stone			     PG
Harry Potter and the Sorcer's Stone			     PG
Harry Potter and the Chamber of Secrets 		     PG
Harry Potter and the Chamber of Secrets 		     PG
Harry Potter and the Prisoner of Azkaban		     PG
Harry Potter and the Prisoner of Azkaban		     PG
Harry Potter and the Chamber of Secrets 		     PG
Harry Potter and the Goblet of Fire			     PG-13
Harry Potter and the Goblet of Fire			     PG-13
Harry Potter and the Goblet of Fire			     PG-13
Harry Potter and the Order of the Phoenix		     PG-13
The Hunt for Red October				     PG
King Arthur - The Director's Cut			     R
King Arthur						     PG-13
King Arthur - The Director's Cut			     R
The Lord of the Rings - Fellowship of the Ring		     PG-13
The Lord of the Rings - Fellowship of the Ring		     PG-13
The Lord of the Rings - Fellowship of the Ring		     PG-13
The Lord of the Rings - Two Towers			     PG-13
The Lord of the Rings - Two Towers			     PG-13
The Lord of the Rings - Two Towers			     PG-13
The Lord of the Rings - The Return of the King		     PG-13
The Lord of the Rings - The Return of the King		     PG-13
The Lord of the Rings - The Return of the King		     PG-13
The Patriot Games					     R
Pirates of the Caribbean - The Curse of the Black Pearl      PG-13
Pirates of the Caribbean - The Curse of the Black Pearl      PG-13
Pirates of the Caribbean - The Curse of the Black Pearl      PG-13
Pirates of the Caribbean - Dead Man's Chest		     PG-13
Pirates of the Caribbean - Dead Man's Chest		     PG-13
Pirates of the Caribbean - At World's End		     PG-13
Pirates of the Caribbean - At World's End		     PG-13
Indiana Jones and the Raiders of the Lost Ark		     PG
Indiana Jones and the Raiders of the Lost Ark		     PG
Indiana Jones and the Temple of Doom			     PG
Indiana Jones and the Temple of Doom			     PG
Indiana Jones and the Last Crusade			     PG-13
Indiana Jones and the Last Crusade			     PG-13
Spider-Man						     PG-13
Spider-Man						     PG-13
Spider-Man 2						     PG-13
Spider-Man 2						     PG-13
Spider-Man 3						     PG-13
Spider-Man 3						     PG-13
Star Wars - Episode I					     PG
Star Wars - Episode II					     PG
Star Wars - Episode II					     PG
Star Wars - Episode III 				     PG-13
Star Wars - Episode III 				     PG-13
Star Wars - Episode IV					     PG
Star Wars - Episode IV					     PG
Star Wars - Episode V					     PG
Star Wars - Episode V					     PG
Star Wars - Episode VI					     PG
Star Wars - Episode VI					     PG
The Sum of All Fears					     PG-13
The Patriot						     R
The Patriot						     NR
We Were Soldiers					     R
Chronicles of Narnia - The Lion, the Witch and the Wardrobe  PG
Chronicles of Narnia - The Lion, the Witch and the Wardrobe  PG

Written by michaelmclaughlin

December 15th, 2016 at 11:33 am

Posted in