MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘Oracle’ Category

Correlated Update Statement

without comments

My students wanted some additional examples on correlated update statements. I’ve been struggling with what the right way may be to illustrate them.

Correlated subqueries are a hard concept to explain to those new to SQL. While correlated update statements seem impossibly obscure to many or inordinately complex. New SQL developers often flee to the comfort of procedural programs when it comes to update statements.

This uses my video store data model. It’s a correlated update statement to clean up potential corrupt data. More or less something a DBA might run to ensure a business rule hasn’t been violated over time. It checks for the correct foreign key value in a table when a dependent table contains one or more than one row of data.

The aqua-green box highlights a subquery that aggregates foreign key columns and groups the result with the foreign key value. The results from this subquery become a run-time view or derived table. The result set is a foreign key value and a substitute string literal value for each row in the contact table. These results correlate to the update statement’s rows based on the input parameter. The input parameter is a column from each updated row.

A unique key (or check constraint) exists on the combination of the common_lookup_table, common_lookup_column, and common_lookup_type columns. This ensures that only one row is returned and assigned to the member_type column in the member table. The update statement naturally works in either Oracle or MySQL without any porting changes.

Correlated Update Statement ↓

Expand this section to see the clear text for the foregoing image.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

UPDATE member m
SET    member_type =
 (SELECT common_lookup_id
  FROM   common_lookup
  WHERE  common_lookup_table = 'MEMBER'
  AND    common_lookup_column = 'MEMBER_TYPE'           
  AND    common_lookup_type =
          (SELECT  dt.member_type
           FROM   (SELECT   c.member_id
                   ,        CASE
                              WHEN COUNT(c.member_id) > 1 THEN 'GROUP'
                              ELSE 'INDIVIDUAL'
                            END AS member_type
                   FROM     contact c
                   GROUP BY c.member_id) dt
           WHERE   dt.member_id = m.member_id));

While this type of solution is powerful in its own right, I thought it might be interesting to see their procedural equivalents. These correlated subqueries run for each row returned by the master query (or outermost statement). Therefore, they act like functions.

Procedural equivalents (or user-defined functions) simplify the update statement like so:

UPDATE member m
SET    member_type = get_member_type(m.member_id);

If you’re interested in seeing how you would implement this solution in a user-defined function, just expand the dropdown that interest you.

Oracle User-Defined Function (UDF) ↓

Expand this section to see how to map this logic to a PL/SQL schema-level 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
45
46
47
48
49
50

CREATE OR REPLACE FUNCTION get_member_type
(pv_member_id NUMBER) RETURN NUMBER IS
 
  -- Define a collection of strings.
  TYPE type_options IS TABLE OF VARCHAR2(10);
 
  -- Declare local variables.
  lv_dependent NUMBER := 1;
  lv_return_id NUMBER;
 
  -- Declare a local variable of the collection.
  lv_member_type TYPE_OPTIONS := type_options('INDIVIDUAL','GROUP');
 
  -- Define a dynamic cursor to count the number of foreign key values. 
  CURSOR count_contact (cv_member_id NUMBER) IS
    SELECT COUNT(c.member_id)
    FROM   contact c
    WHERE  c.member_id = cv_member_id;
 
  -- Define a dynamic cursor to find a key for an individual or group member type.    
  CURSOR get_lookup_id (cv_type VARCHAR2) IS
    SELECT common_lookup_id
    FROM   common_lookup
    WHERE  common_lookup_context = 'MEMBER'
    AND    common_lookup_type = cv_type;
 
BEGIN
 
  -- Get the number of foreign key values for a contact.
  OPEN  count_contact(pv_member_id);
  FETCH count_contact
  INTO  lv_dependent;
  CLOSE count_contact;
 
  -- Open the dynamic cursor with the required value.  
  IF lv_dependent = 1 THEN
    OPEN get_lookup_id(lv_member_type(1));
  ELSE
    OPEN get_lookup_id(lv_member_type(2));
  END IF;
 
  -- Get the correct surrogate primary key value.
  FETCH get_lookup_id
  INTO  lv_return_id;
  CLOSE get_lookup_id;
 
  -- Return the correct primary key for use as a foreign key.
  RETURN lv_return_id;
END;
/

MySQL User-Defined Function (UDF) ↓

Expand this section to see how to map this logic to a Persistent Stored Module (PSM) 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
45
46
47
48
49
50
51
52
53
54
55
56

SELECT 'DROP FUNCTION IF EXISTS get_member_type' AS "Statement";
DROP FUNCTION IF EXISTS get_member_type;
 
SELECT 'DELIMITER $$' AS "Statement";
DELIMITER $$
 
SELECT 'CREATE FUNCTION get_member_type' AS "Statement"$$
CREATE FUNCTION get_member_type(pv_member_id INT) RETURNS INT 
BEGIN
 
  /* Define three local variables. */
  DECLARE lv_contact_number  INT;
  DECLARE lv_member_type     CHAR(30);
  DECLARE lv_return_value    INT;
 
  /* Define a dynamic cursor to count the number of foreign key values. */  
  DECLARE contact_cursor CURSOR FOR
    SELECT COUNT(c.member_id)
    FROM   contact c
    WHERE  c.member_id = pv_member_id;
 
  /* Define a dynamic cursor to find a key for an individual or group member type. */
  DECLARE common_lookup_cursor CURSOR FOR
    SELECT common_lookup_id
    FROM   common_lookup
    WHERE  common_lookup_table = 'MEMBER'
    AND    common_lookup_column = 'MEMBER_TYPE'           
    AND    common_lookup_type = lv_member_type;
 
  /* Get the number of foreign key values for a contact. */    
  OPEN  contact_cursor;
  FETCH contact_cursor
  INTO  lv_contact_number;
  CLOSE contact_cursor;
 
  /* Assign group membership when more than one foreign key value is found; and
     assign individual membership when only one foreign key value is found. */  
  IF lv_contact_number > 1 THEN
    SET lv_member_type = 'GROUP';
  ELSE
    SET lv_member_type = 'INDIVIDUAL';
  END IF;
 
  /* Get the correct surrogate primary key value. */  
  OPEN  common_lookup_cursor;
  FETCH common_lookup_cursor
  INTO  lv_return_value;
  CLOSE common_lookup_cursor;
 
  /* Return the correct primary key for use as a foreign key. */  
  RETURN lv_return_value;
END;
$$
 
SELECT 'DELIMITER $$' AS "Statement"$$
DELIMITER ;

You can query the results of the update statement with the following.

Change Confirmation Query ↓

Expand this section to see the query that lets you examine the changes. It runs in either Oracle or MySQL without any changes.

1
2
3
4
5
6
7
8
9
10
11

SELECT   m.member_id
,        dt.quantity
,        m.member_type
,        cl.common_lookup_type
FROM     member m JOIN (SELECT   member_id
                        ,        COUNT(c.member_id) AS quantity
                        FROM     contact c
                        GROUP BY c.member_id) dt
ON       m.member_id = dt.member_id JOIN common_lookup cl
ON       m.member_type = cl.common_lookup_id
ORDER BY m.member_id;

As always, I look forward to helping and gaining insight.

Written by maclochlainn

June 27th, 2010 at 10:12 pm

Posted in MySQL,Oracle,Oracle XE,Persistent Stored Modules,pl/sql,PSM,sql

Show indexes in Oracle

with 2 comments

One of my students asked how you could show index from table_name; in Oracle. They were chagrined when I told them there wasn’t an equivalent command. Outside of using Quest’s Toad or Oracle SQL*Developer, you can query the data catalog, like so:

-- SQL*Plus formatting commands.
COLUMN index_name FORMAT A32
COLUMN column_position FORMAT 999 HEADING "COLUMN|POSITION"
COLUMN column_name FORMAT A32
 
-- Ordinary query with a substitution variable.
SELECT i.index_name
,      ic.column_position
,      ic.column_name
FROM   user_indexes i JOIN user_ind_columns ic
ON     i.index_name = ic.index_name
WHERE  i.table_name = UPPER('&input')

Naturally, this is a subset of what’s returned by the show index from table_name; syntax. There is much more information in these tables but I only wanted to show an example.

The UPPER function command ensures that the table name is found in the database. Unless you’ve created a case sensitive object, they’re stored in uppercase strings.

While a single SQL statement works well, a little organization in PL/SQL makes it more readable. A display_indexes function provides that organization. It only displays normal indexes, not LOB indexes, and it depends on a schema-level collection of strings. This is the user-defined type (UDT) that I used for the collection.

CREATE OR REPLACE TYPE index_table AS TABLE OF VARCHAR2(200);
/

The following is the definition of the 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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

CREATE OR REPLACE FUNCTION display_indexes
( pv_table_name VARCHAR2 ) RETURN INDEX_TABLE IS
 
  -- Declare an iterator for the collection return variable.
  index_counter  NUMBER := 1;
  column_counter NUMBER;
 
  -- Declare and initialize local collection variable as return type.
  index_desc INDEX_TABLE := index_table();
 
  -- Get indexes.
  CURSOR index_name (cv_table_name VARCHAR2) IS
    SELECT   i.index_name
    FROM     user_indexes i
    WHERE    i.table_name = cv_table_name
    AND      i.index_type = 'NORMAL'
    ORDER BY 1;
 
  -- Get index columns.    
  CURSOR index_columns (cv_index_name VARCHAR2) IS
    SELECT   ic.column_position
    ,        ic.column_name
    FROM     user_ind_columns ic
    WHERE    ic.index_name = cv_index_name
    ORDER BY 1;
 
BEGIN
 
  -- Assign the table name to the collection.
  index_desc.EXTEND;
  index_desc(index_counter) := UPPER(pv_table_name);
  index_counter := index_counter + 1;
 
  FOR i IN index_name(UPPER(pv_table_name)) LOOP
 
    -- Assign the index name to the collection.
    index_desc.EXTEND;
    index_desc(index_counter) := LPAD(i.index_name,2 + LENGTH(i.index_name),' ');
 
    -- Set column counter on entry to nested loop.
    column_counter := 1;
 
    FOR j IN index_columns(i.index_name) LOOP
 
      IF column_counter = 1 THEN
 
        -- Increment the column counter, extend space, and concatenate to string.
        column_counter := column_counter + 1;
        index_desc.EXTEND;
        index_desc(index_counter) := index_desc(index_counter) || '(' || LOWER(j.column_name);
 
      ELSE
 
        -- Add a subsequent column to the list.
        index_desc(index_counter) := index_desc(index_counter) || ',' || LOWER(j.column_name);
 
      END IF;
 
    END LOOP;
 
    -- Append a close parenthesis and incredment index counter.
    index_desc(index_counter) := index_desc(index_counter) || ')';
    index_counter := index_counter + 1;
 
  END LOOP;
 
  -- Return the array.
  RETURN index_desc;
END;
/

You can call the function with this syntax:

SELECT column_value AS "TRANSACTION INDEXES"
FROM   TABLE(display_indexes('TRANSACTION'));

It returns the following formatted output for the TRANSACTION table, which is much nicer than the SQL output. Unfortunately, it will take more effort to place it on par with the show index from table_name; in MySQL.

TRANSACTION INDEXES
------------------------------------------------------------------------------------------------------------------------------
TRANSACTION
  PK_TRANSACTION(transaction_id)
  UQ_TRANSACTION(rental_id,transaction_type,transaction_date,payment_method_type,payment_account_number,transaction_account)

As always, I hope it helps folks.

Written by maclochlainn

June 22nd, 2010 at 1:28 am

Posted in Oracle,Oracle XE,pl/sql,sql

A \G Option for Oracle?

with 7 comments

The \G option in MySQL lets you display rows of data as sets with the columns on the left and the data on the write. I figured it would be fun to write those for Oracle when somebody pointed out that they weren’t out there in cyberspace (first page of a Google search ;-)).

I started the program with a student’s code. I thought it a bit advanced for the student but didn’t check if he’d snagged it somewhere. Thanks to Niall Litchfield, I now know that the base code came from an earlier post of Tom Kyte. Tom’s example code failed when returning a Blob, BFile, or CFile column.

Naturally, there are two ways to write this. One is a procedure and the other is the function. This post contains both. The procedure is limited because of potential buffer overflows associated with the DBMS_OUTPUT package’s display. A function isn’t limited because you can return a collection from the function.

Required setup to use the DBMS_SQL package ↓

The DBMS_SQL package requires permissions. There are two ways to provide those permissions. One is more secure and sensible in a production system and the other is great in a development test system.

Production or Test System

If this is a production system, you probably want to grant permissions only to the SYSTEM schema. This follows the practice of narrowing access to powerful features and control systems.

The first step requires the SYS user to grant permissions and authority to re-grant to individual users. You connect as the privileged user, like:

sqlplus / AS sysdba

When connected as the SYS, you run the following two commands:

GRANT EXECUTE ON dbms_sys_sql TO system;
GRANT EXECUTE ON dbms_sql TO system;

You should then define the procedure or function as a CURRENT_USER module. This type of module is known as an invoker’s right program. The code is owned by the SYSTEM schema but you run it on your own objects in your less privileged schema.

You can do that by replacing the function and procedure headers with these:

CREATE OR REPLACE PROCEDURE display_vertical
( TABLE_NAME VARCHAR2, where_clause VARCHAR2 ) AUTHID CURRENT_USER IS
CREATE OR REPLACE FUNCTION vertical_query
( TABLE_NAME VARCHAR2, where_clause VARCHAR2 ) RETURN query_result
AUTHID CURRENT_USER IS

After you compile the procedure and function in the SYSTEM schema, you should grant access to a schema (more restricted) or public (as generic tools). You should also create synonyms. The following commands assume you want to deploy these as generic tools. As the SYSTEM user, it grants privileges and then creates public synonyms.

-- Grant privileges.
GRANT EXECUTE ON display_vertical TO PUBLIC;
GRANT EXECUTE ON vertical_query TO PUBLIC;
 
-- Create public synonyms.
CREATE PUBLIC SYNONYM display_vertical FOR system.display_vertical;
CREATE PUBLIC SYNONYM vertical_query FOR system.vertical_query;

You should now be able to call these from any schema to work with their own tables and views.

Student Development System

If this is a test system and you’re new to Oracle, the following should help you. This shows you how to implement these in a Definer’s right model, inside a STUDENT schema.

This isn’t a secure design, but it allows you to keep your testing limited to a STUDENT schema. When these permissions aren’t granted the examples won’t work at all.

The first step requires the SYS user to grant permissions and authority to re-grant to individual users. You connect as the privileged user, like:

sqlplus / AS sysdba

When connected as the SYS, you run the following two commands:

GRANT EXECUTE ON dbms_sys_sql TO system WITH GRANT OPTION;
GRANT EXECUTE ON dbms_sql TO system WITH GRANT OPTION;

You don’t have to exit to reconnect as the SYSTEM user. Just type the following at the SQL command prompt (substitute your password ;-)).

CONNECT system/password

When connected as the SYSTEM user, you run the following two commands:

GRANT EXECUTE ON dbms_sys_sql TO student;
GRANT EXECUTE ON dbms_sql TO student;

You should now be able to compile the function and procedure.

Procedure for \G output ↓

The procedure nice because there’s only a dependency on the buffer size for the DBMS_OUTPUT package. The procedure only returns column values that are printable at the console, and it only returns the first 40 characters of long text strings.

Here’s the procedure definition:

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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182

-- Create procedure.
CREATE OR REPLACE PROCEDURE display_vertical
( TABLE_NAME VARCHAR2, where_clause VARCHAR2 ) IS
 
  -- Open a cursor for a query against all columns in a table. 
  base_stmt  INTEGER := dbms_sql.open_cursor;
 
  -- Open a cursor for a dynamically constructed query, which excludes
  -- any non-displayable columns with text.
  stmt  INTEGER := dbms_sql.open_cursor;
 
  -- Declare local variables.
  colValue   VARCHAR2(4000);      -- Declare a maximum string length for column values.
  STATUS     INTEGER;             -- Declare a variable to hold acknowledgement of DBMS_SQL.EXECUTE
  tableDesc  dbms_sql.desc_tab2;  -- Declare a table to hold metadata for the queries.
  colCount   NUMBER;              -- Declare a variable for the column count.
  rowIndex   NUMBER := 0;         -- for displaying the row number retrieved from the cursor
  colLength  NUMBER := 0;         -- for keeping track of the length of the longest column name
 
  -- Declare local variable for the dynamically constructed query.
  dynamic_stmt VARCHAR2(4000) := 'SELECT ';
 
  -- Declare an exception for a bad table name, raised by a call to
  -- the dbms_assert.qualified_sql_name function.
  table_name_error EXCEPTION;
  PRAGMA EXCEPTION_INIT(table_name_error, -942);
 
  -- Declare exception handlers for bad WHERE clause statements.
 
  -- Declare an exception for a missing WHERE keyword.
  missing_keyword EXCEPTION;
  PRAGMA EXCEPTION_INIT(missing_keyword, -933);  
 
  -- Declare an exception for a bad relational operator.
  invalid_relational_operator EXCEPTION;
  PRAGMA EXCEPTION_INIT(invalid_relational_operator, -920);  
 
  -- Declare an exception for a bad column name.
  invalid_identifier EXCEPTION;
  PRAGMA EXCEPTION_INIT(invalid_identifier, -904);  
 
  -- Declare an exception for a missing backquoted apostrophe.
  misquoted_string EXCEPTION;
  PRAGMA EXCEPTION_INIT(misquoted_string, -1756);  
 
  -- Declare a function that replaces non-displayable values with text messages.
  FUNCTION check_column( p_name   VARCHAR2
                       , p_type   NUMBER ) RETURN VARCHAR2 IS
 
    -- Return column name or literal value.
    retval VARCHAR2(30);			   
 
  BEGIN
 
    -- Find strings, numbers, dates, timestamps, rowids and replace non-display values.
    IF p_type IN (1,2,8,9,12,69,96,100,101,112,178,179,180,181,231) THEN
 
      -- Assign the column name for a displayable column value.
      retval := p_name;
 
    ELSE
 
      -- Re-assign string literals for column names where values aren't displayable.      
      SELECT DECODE(p_type, 23,'''RAW not displayable.'''
                          ,105,'''MLSLABEL not displayable.'''
                          ,106,'''MLSLABEL not displayable.'''
                          ,113,'''BLOB not displayable.'''
                          ,114,'''BFILE not displayable.'''
                          ,115,'''CFILE not displayable.'''
                          ,'''UNDEFINED not displayable.''')
      INTO retval
      FROM dual;
 
    END IF;
 
    -- Return the column name or a apostrophe delimited string literal.
    RETURN retval;
  END check_column;
 
BEGIN
 
  -- Prepare unfiltered display cursor.
  dbms_sql.parse(base_stmt, 'SELECT * FROM ' || dbms_assert.simple_sql_name(TABLE_NAME) || ' ' || where_clause, dbms_sql.native);
 
  -- Describe the table structure:
  -- --------------------------------------------------------
  --  1. Store metadata in tableDesc
  --  2. Store the number of columns in colCount
  -- --------------------------------------------------------
  dbms_sql.describe_columns2(base_stmt, colCount, tableDesc);
 
  -- Define individual columns and assign value to colValue variable.
  FOR i IN 1..colCount LOOP
 
    -- Define columns for each column returned into tableDesc.
    dbms_sql.define_column(base_stmt, i, colValue, 4000);
 
    -- Find the length of the longest column name.
    IF LENGTH(tableDesc(i).col_name) > colLength THEN
      colLength := LENGTH(tableDesc(i).col_name);
    END IF;
 
    -- Replace non-displayable column values with displayable values.
    IF i < colCount THEN
      dynamic_stmt := dynamic_stmt || check_column(tableDesc(i).col_name,tableDesc(i).col_type) || ' AS '
                   || tableDesc(i).col_name || ', ';
    ELSE
      dynamic_stmt := dynamic_stmt || check_column(tableDesc(i).col_name,tableDesc(i).col_type) || ' AS '
                   || tableDesc(i).col_name || ' ' || 'FROM ' || dbms_assert.simple_sql_name(TABLE_NAME)
                   || ' ' || where_clause;
    END IF;
 
  END LOOP;
 
  -- Provide conditional debugging instruction that displays dynamically created query.
  $IF $$DEBUG = 1 $THEN
     dbms_output.put_line(dynamic_stmt);
  $END
 
  -- Prepare unfiltered display cursor. 
  dbms_sql.parse(stmt, dynamic_stmt, dbms_sql.native);
 
  -- Describe the table structure:
  -- --------------------------------------------------------
  --  1. Store metadata in tableDesc (reuse of existing variable)
  --  2. Store the number of columns in colCount
  -- --------------------------------------------------------
  dbms_sql.describe_columns2(stmt, colCount, tableDesc);
 
  -- Define individual columns and assign value to colValue variable.
  FOR i IN 1..colCount LOOP
    dbms_sql.define_column(stmt, i, colValue, 4000);
  END LOOP;
 
  -- Execute the dynamic cursor.
  STATUS := dbms_sql.execute(stmt);
 
  -- Fetch the results, row-by-row.
  WHILE dbms_sql.fetch_rows(stmt) > 0 LOOP
 
    -- Reset row counter for display purposes.
    rowIndex := rowIndex + 1;
    dbms_output.put_line('********************************** ' || rowIndex || '. row **********************************');
 
    -- For each column, print left-aligned column names and values.
    FOR i IN 1..colCount LOOP
 
      -- Limit display of long text.  
      IF tableDesc(i).col_type IN (1,9,96,112) THEN
 
        -- Display 40 character substrings of long text.  
  	dbms_sql.column_value(stmt, i, colValue);
        dbms_output.put_line(RPAD(tableDesc(i).col_name, colLength,' ') || ' : ' || SUBSTR(colValue, 1,40));	  
 
      ELSE
 
        -- Display full value as character string.  
        dbms_sql.column_value(stmt, i, colValue);
        dbms_output.put_line(RPAD(tableDesc(i).col_name, colLength,' ') || ' : ' || colValue);
 
      END IF;
 
    END LOOP;
 
  END LOOP;
 
EXCEPTION
  -- Customer error handlers.
  WHEN table_name_error THEN
    dbms_output.put_line(SQLERRM);
  WHEN invalid_relational_operator THEN
    dbms_output.put_line(SQLERRM);
  WHEN invalid_identifier THEN
    dbms_output.put_line(SQLERRM);
  WHEN missing_keyword THEN
    dbms_output.put_line(SQLERRM);
  WHEN misquoted_string THEN
    dbms_output.put_line(SQLERRM);
  WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
END;
/

You can run the procedure with the following syntax:

EXECUTE display_vertical('ITEM','WHERE item_title LIKE ''Star%''');

It’ll return the following display of data:

********************************** 1. ROW **********************************
ITEM_ID           : 1002
ITEM_BARCODE      : 24543-02392
ITEM_TYPE         : 1011
ITEM_TITLE        : Star Wars I
ITEM_SUBTITLE     : Phantom Menace
ITEM_RATING       : PG
ITEM_RELEASE_DATE : 04-MAY-99
CREATED_BY        : 3
CREATION_DATE     : 09-JUN-10
LAST_UPDATED_BY   : 3
LAST_UPDATE_DATE  : 09-JUN-10
ITEM_DESC         :
DISPLAY_PHOTO     : BLOB NOT displayable.
********************************** 2. ROW **********************************
ITEM_ID           : 1003
ITEM_BARCODE      : 24543-5615
ITEM_TYPE         : 1010
ITEM_TITLE        : Star Wars II
ITEM_SUBTITLE     : Attack OF the Clones
ITEM_RATING       : PG
ITEM_RELEASE_DATE : 16-MAY-02
CREATED_BY        : 3
CREATION_DATE     : 09-JUN-10
LAST_UPDATED_BY   : 3
LAST_UPDATE_DATE  : 09-JUN-10
ITEM_DESC         :
DISPLAY_PHOTO     : BLOB NOT displayable.
********************************** 3. ROW **********************************
ITEM_ID           : 1004
ITEM_BARCODE      : 24543-05539
ITEM_TYPE         : 1011
ITEM_TITLE        : Star Wars II
ITEM_SUBTITLE     : Attack OF the Clones
ITEM_RATING       : PG
ITEM_RELEASE_DATE : 16-MAY-02
CREATED_BY        : 3
CREATION_DATE     : 09-JUN-10
LAST_UPDATED_BY   : 3
LAST_UPDATE_DATE  : 09-JUN-10
ITEM_DESC         : This IS designed TO be a long enough str
DISPLAY_PHOTO     : BLOB NOT displayable.
********************************** 4. ROW **********************************
ITEM_ID           : 1005
ITEM_BARCODE      : 24543-20309
ITEM_TYPE         : 1011
ITEM_TITLE        : Star Wars III
ITEM_SUBTITLE     : Revenge OF the Sith
ITEM_RATING       : PG13
ITEM_RELEASE_DATE : 19-MAY-05
CREATED_BY        : 3
CREATION_DATE     : 09-JUN-10
LAST_UPDATED_BY   : 3
LAST_UPDATE_DATE  : 09-JUN-10
ITEM_DESC         :
DISPLAY_PHOTO     : BLOB NOT displayable.

Function for \G output ↓

The function is the best solution. It does have a dependency on a user-defined type (UDT). The function, like the procedure, only returns column values that are printable at the console. It also parses the first 40 characters from long text strings.

Before you create the function, you must create a UDT collection variable. The following syntax creates a schema-level UDT.

CREATE OR REPLACE TYPE query_result AS TABLE OF VARCHAR2(77);
/

Here’s the function definition:

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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216

CREATE OR REPLACE FUNCTION vertical_query
( TABLE_NAME VARCHAR2, where_clause VARCHAR2 ) RETURN query_result IS
 
  -- Open a cursor for a query against all columns in a table. 
  base_stmt	INTEGER := dbms_sql.open_cursor;
 
  -- Open a cursor for a dynamically constructed query, which excludes
  -- any non-displayable columns with text.
  stmt      INTEGER := dbms_sql.open_cursor;
 
  -- Declare local variables.
  colValue	VARCHAR2(4000);      -- Declare a maximum string length for column values.
  STATUS		INTEGER;             -- Declare a variable to hold acknowledgement of DBMS_SQL.EXECUTE
  tableDesc	dbms_sql.desc_tab2;  -- Declare a table to hold metadata for the queries.
  colCount	NUMBER;              -- Declare a variable for the column count.
  rowIndex	NUMBER := 0;         -- for displaying the row number retrieved from the cursor
  colLength	NUMBER := 0;         -- for keeping track of the length of the longest column name
 
  -- Declare local variable for the dynamically constructed query.
  dynamic_stmt VARCHAR2(4000) := 'SELECT ';
 
  -- Declare a index for the return collection.
  rsIndex NUMBER := 0;
 
  -- Declare a collection variable and instantiate the collection.
  result_set QUERY_RESULT := query_result();
 
  -- Declare an exception for a bad table name, raised by a call to
  -- the dbms_assert.qualified_sql_name function.
  table_name_error EXCEPTION;
  PRAGMA EXCEPTION_INIT(table_name_error, -942);
 
  -- Declare exception handlers for bad WHERE clause statements.
 
  -- Declare an exception for a missing WHERE keyword.
  missing_keyword EXCEPTION;
  PRAGMA EXCEPTION_INIT(missing_keyword, -933);  
 
  -- Declare an exception for a bad relational operator.
  invalid_relational_operator EXCEPTION;
  PRAGMA EXCEPTION_INIT(invalid_relational_operator, -920);  
 
  -- Declare an exception for a bad column name.
  invalid_identifier EXCEPTION;
  PRAGMA EXCEPTION_INIT(invalid_identifier, -904);  
 
  -- Declare an exception for a missing backquoted apostrophe.
  misquoted_string EXCEPTION;
  PRAGMA EXCEPTION_INIT(misquoted_string, -1756);  
 
  -- ------------------------------------------------------------------
 
  -- Declare a function that replaces non-displayable values with text messages.
  FUNCTION check_column( p_name   VARCHAR2
                       , p_type   NUMBER ) RETURN VARCHAR2 IS
 
    -- Return column name or literal value.
    retval VARCHAR2(30);			   
 
  BEGIN
 
    -- Find strings, numbers, dates, timestamps, rowids and replace non-display values.
    IF p_type IN (1,2,8,9,12,69,96,100,101,112,178,179,180,181,231) THEN
 
      -- Assign the column name for a displayable column value.
      retval := p_name;
 
    ELSE
 
      -- Re-assign string literals for column names where values aren't displayable.      
      SELECT DECODE(p_type, 23,'''RAW not displayable.'''
                          ,105,'''MLSLABEL not displayable.'''
                          ,106,'''MLSLABEL not displayable.'''
                          ,113,'''BLOB not displayable.'''
                          ,114,'''BFILE not displayable.'''
                          ,115,'''CFILE not displayable.'''
                          ,'''UNDEFINED not displayable.''')
      INTO retval
      FROM dual;
 
    END IF;
 
    -- Return the column name or a apostrophe delimited string literal.
    RETURN retval;
  END check_column;
 
  -- ------------------------------------------------------------------
 
BEGIN
 
	-- Prepare unfiltered display cursor.
	dbms_sql.parse(base_stmt, 'SELECT * FROM ' || dbms_assert.simple_sql_name(TABLE_NAME) || ' ' || where_clause, dbms_sql.native);
 
  -- Describe the table structure:
  -- --------------------------------------------------------
  --  1. Store metadata in tableDesc
  --  2. Store the number of columns in colCount
  -- --------------------------------------------------------
  dbms_sql.describe_columns2(base_stmt, colCount, tableDesc);
 
  -- Define individual columns and assign value to colValue variable.
  FOR i IN 1..colCount LOOP
 
    -- Define columns for each column returned into tableDesc.
    dbms_sql.define_column(base_stmt, i, colValue, 4000);
 
    -- Find the length of the longest column name.
    IF LENGTH(tableDesc(i).col_name) > colLength THEN
      colLength := LENGTH(tableDesc(i).col_name);
    END IF;
 
    -- Replace non-displayable column values with displayable values.
    IF i < colCount THEN
      dynamic_stmt := dynamic_stmt || check_column(tableDesc(i).col_name,tableDesc(i).col_type) || ' AS '
                   || tableDesc(i).col_name || ', ';
    ELSE
      dynamic_stmt := dynamic_stmt || check_column(tableDesc(i).col_name,tableDesc(i).col_type) || ' AS '
                   || tableDesc(i).col_name || ' ' || 'FROM ' || dbms_assert.simple_sql_name(TABLE_NAME)
                   || ' ' || where_clause;
    END IF;
 
  END LOOP;
 
  -- Provide conditional debugging instruction that displays dynamically created query.
  $IF $$DEBUG = 1 $THEN
     dbms_output.put_line(dynamic_stmt);
  $END
 
  -- Prepare unfiltered display cursor. 
  dbms_sql.parse(stmt, dynamic_stmt, dbms_sql.native);
 
  -- Describe the table structure:
  -- --------------------------------------------------------
  --  1. Store metadata in tableDesc (reuse of existing variable)
  --  2. Store the number of columns in colCount
  -- --------------------------------------------------------
  dbms_sql.describe_columns2(stmt, colCount, tableDesc);
 
  -- Define individual columns and assign value to colValue variable.
  FOR i IN 1..colCount LOOP
    dbms_sql.define_column(stmt, i, colValue, 4000);
  END LOOP;
 
  -- Execute the dynamic cursor.
  STATUS := dbms_sql.execute(stmt);
 
  -- Fetch the results, row-by-row.
  WHILE dbms_sql.fetch_rows(stmt) > 0 LOOP
 
    -- Reset row counter for output display purposes.
    rowIndex := rowIndex + 1;
 
    -- Increment the counter for the collection and extend space before assignment.
    rsIndex := rsIndex + 1;
    result_set.EXTEND;
    result_set(rsIndex) := '********************************** ' || rowIndex || '. row **********************************';
 
    -- For each column, print left-aligned column names and values.
    FOR i IN 1..colCount LOOP
 
      -- Increment the counter for the collection and extend space before assignment.
      rsIndex := rsIndex + 1;
      result_set.EXTEND;
 
      -- Limit display of long text.  
      IF tableDesc(i).col_type IN (1,9,96,112) THEN
 
        -- Display 40 character substrings of long text.  
        dbms_sql.column_value(stmt, i, colValue);
        result_set(rsIndex) := RPAD(tableDesc(i).col_name, colLength,' ') || ' : ' || SUBSTR(colValue, 1,40);	  
 
      ELSE
 
        -- Display full value as character string.  
        dbms_sql.column_value(stmt, i, colValue);
        result_set(rsIndex) := RPAD(tableDesc(i).col_name, colLength,' ') || ' : ' || colValue;
 
      END IF;
 
    END LOOP;
 
  END LOOP;
 
  -- Increment the counter for the collection and extend space before assignment.
  FOR i IN 1..3 LOOP
    rsIndex := rsIndex + 1;
    result_set.EXTEND;
 
    CASE i
      WHEN 1 THEN
        result_set(rsIndex) := '****************************************************************************';
      WHEN 2 THEN
        result_set(rsIndex) := CHR(10);    
      WHEN 3 THEN
        result_set(rsIndex) := rowIndex || ' rows in set';    
    END CASE;
  END LOOP;
 
  -- Return collection.
  RETURN result_set;
EXCEPTION
  -- Customer error handlers, add specialized text or collapse into one with the OTHERS catchall.
  WHEN table_name_error THEN
    dbms_output.put_line(SQLERRM);
  WHEN invalid_relational_operator THEN
    dbms_output.put_line(SQLERRM);
  WHEN invalid_identifier THEN
    dbms_output.put_line(SQLERRM);
  WHEN missing_keyword THEN
    dbms_output.put_line(SQLERRM);
  WHEN misquoted_string THEN
    dbms_output.put_line(SQLERRM);
  WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
END;
/

Before you attempt to run the function, you should set two Oracle SQL*Plus environment commands. One suppresses a message saying what just ran, and the other removes column headers. Clearly, the output is sufficient and the headers are clutter. You set these, as noted below:

SET FEEDBACK OFF
SET PAGESIZE 0

You can run the function with the following syntax (the COLUMN_VALUE is the standard name returned from a scalar schema-level collection.

SELECT column_value
FROM   TABLE(vertical_query('ITEM','WHERE item_title LIKE ''Star%'''));

It’ll return the following display of data:

********************************** 1. ROW **********************************
ITEM_ID           : 1002
ITEM_BARCODE      : 24543-02392
ITEM_TYPE         : 1011
ITEM_TITLE        : Star Wars I
ITEM_SUBTITLE     : Phantom Menace
ITEM_RATING       : PG
ITEM_RELEASE_DATE : 04-MAY-99
CREATED_BY        : 3
CREATION_DATE     : 09-JUN-10
LAST_UPDATED_BY   : 3
LAST_UPDATE_DATE  : 09-JUN-10
ITEM_DESC         :
DISPLAY_PHOTO     : BLOB NOT displayable.
********************************** 2. ROW **********************************
ITEM_ID           : 1003
ITEM_BARCODE      : 24543-5615
ITEM_TYPE         : 1010
ITEM_TITLE        : Star Wars II
ITEM_SUBTITLE     : Attack OF the Clones
ITEM_RATING       : PG
ITEM_RELEASE_DATE : 16-MAY-02
CREATED_BY        : 3
CREATION_DATE     : 09-JUN-10
LAST_UPDATED_BY   : 3
LAST_UPDATE_DATE  : 09-JUN-10
ITEM_DESC         :
DISPLAY_PHOTO     : BLOB NOT displayable.
********************************** 3. ROW **********************************
ITEM_ID           : 1004
ITEM_BARCODE      : 24543-05539
ITEM_TYPE         : 1011
ITEM_TITLE        : Star Wars II
ITEM_SUBTITLE     : Attack OF the Clones
ITEM_RATING       : PG
ITEM_RELEASE_DATE : 16-MAY-02
CREATED_BY        : 3
CREATION_DATE     : 09-JUN-10
LAST_UPDATED_BY   : 3
LAST_UPDATE_DATE  : 09-JUN-10
ITEM_DESC         : This IS designed TO be a long enough str
DISPLAY_PHOTO     : BLOB NOT displayable.
********************************** 4. ROW **********************************
ITEM_ID           : 1005
ITEM_BARCODE      : 24543-20309
ITEM_TYPE         : 1011
ITEM_TITLE        : Star Wars III
ITEM_SUBTITLE     : Revenge OF the Sith
ITEM_RATING       : PG13
ITEM_RELEASE_DATE : 19-MAY-05
CREATED_BY        : 3
CREATION_DATE     : 09-JUN-10
LAST_UPDATED_BY   : 3
LAST_UPDATE_DATE  : 09-JUN-10
ITEM_DESC         :
DISPLAY_PHOTO     : BLOB NOT displayable.
****************************************************************************

As usual, I hope this helps folks.

Written by maclochlainn

June 14th, 2010 at 1:01 am

Posted in MySQL,Oracle,Oracle XE,pl/sql,sql

Tagged with ,

A couple DBMS_SQL limits

without comments

While developing a dynamic SQL example in Oracle 11g that builds a query based on available display columns, I found two interesting error messages. Now instead of noting it for the umpteenth time, I’m documenting it for everybody. The error messages are generated when this DBMS_SQL package’s statement is a SELECT statement, and is executed with either a BLOB, BFILE or CFILE column in the list of returned columns.

26

STATUS := dbms_sql.execute(stmt);

BLOB data type

You get the following error when a column in the query has a BLOB data type. If you alter the query to exclude the column, no error occurs.

BEGIN test('DEMO'); END;
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got BLOB
ORA-06512: at "SYS.DBMS_SQL", line 1575
ORA-06512: at "STUDENT.TEST", line 26
ORA-06512: at line 1

BFILE or CFILE data type

You get the following error when a column in the query has a BFILE or CFILE data type. If you alter the query to exclude the column, no error occurs.

BEGIN test('DEMO'); END;
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got FILE
ORA-06512: at "SYS.DBMS_SQL", line 1575
ORA-06512: at "STUDENT.TEST", line 26
ORA-06512: at line 1

It’s never a joy to debug the DBMS_SQL package, at least it’s never a joy for me. I hope this helps somebody sort out an issue more quickly.

Written by maclochlainn

June 12th, 2010 at 11:43 pm

Posted in blob,clob,Oracle,Oracle XE,pl/sql,sql

User-defined SYS_CONTEXT

with 2 comments

Looking through an error on the web, I notices that the solution is nested in Ask Tom. That’s true for so many solutions, but they likewise have long discussions like this one in the OraFAQ Forum.

It seems that most folks search on is the following. The problem appears to be linked to attempts to call the DBMS_SESSION.SET_CONTEXT directly in their code, instead of through a predefined procedure. The procedure is generally inside a security package in a security schema for reference.

BEGIN
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 94
ORA-06512: at line 2

I figured it might help to provide a simple example because I use VPDs in my second database class, and this is where some of my students get hung up. It strikes me others in the Oracle community may get stuck here too.

  1. Create a user with necessary permissions as the SYSTEM user:
CREATE USER sample IDENTIFIED BY sample;
GRANT CREATE SESSION, CREATE ANY CONTEXT, CREATE ANY PROCEDURE TO sample;
  1. Create the CONTEXT reference as the SAMPLE user, which uses a function to populate the CONTEXT.
CREATE OR REPLACE CONTEXT sample_ctx USING set_context;
  1. Create the function as the SAMPLE user to set the context. The CONTEXT is a literal value inside the procedure with a name and value pair.
CREATE OR REPLACE PROCEDURE set_context
( pname  VARCHAR2
, pvalue VARCHAR2) IS
BEGIN
  -- Create a session with a previously defined context.
  DBMS_SESSION.SET_CONTEXT('SAMPLE_CTX',pname,pvalue);
END;
/
  1. Set the local session sample_ctx CONTEXT as the SAMPLE user.
EXECUTE set_context('email','sherman@atlanta.org');
  1. You now query the user-defined CONTEXT with case insensitive strings that match the CONTEXT and pname call parameter that you set it. The following shows that query against dual. You should note that it returns a case sensitive string of the pvalue call parameter.
SELECT sys_context('sample_ctx','email') FROM dual;

As always, I hope this helps somebody and saves them time.

Written by maclochlainn

May 5th, 2010 at 8:15 am

Posted in Oracle,Oracle XE,sql

When dropping is adding?

without comments

I was working through some example files and test scripts with Virtual Private Databases and discovered a nifty and potentially misleading error. Google didn’t pick up any search results with it, so I thought noting it would be a good idea.

When you create a security policy with DBMS_RLS.ADD_POLICY incorrectly, and then try to drop it, you must make sure to include the OBJECT_SCHEMA parameter. If you don’t and provide named parameters like the following, you’ll raise an error.

BEGIN
  DBMS_RLS.DROP_POLICY(object_name=>'valid_table'
                      ,policy_name=>'valid_policy');
END;
/

The error is quite misleading, as shown below.

BEGIN
*
ERROR at line 1:
ORA-28103: adding a policy TO an object owned BY SYS IS NOT allowed
ORA-06512: at "SYS.DBMS_RLS", line 59
ORA-06512: at line 2

The error is actually triggered when the OBJECT_SCHEMA is required. The default value is a NULL in the DBMS_RLS package specification.

The correct syntax is:

BEGIN
  DBMS_RLS.DROP_POLICY(object_schema=>'valid_schema'
                      ,object_name=>'valid_table'
                      ,policy_name=>'valid_policy');
END;
/

Written by maclochlainn

May 4th, 2010 at 5:35 pm

Posted in Oracle,sql

Manual Oracle Service

with 6 comments

Ruairi asked how you could disable automatic start of the Oracle Service for Oracle 11g on Windows 7 (a comment here). Ruairi also provided a nice Windows shell script that you can copy for starting and stopping the Oracle Service in his last comment.

The simplest way is to launch a command shell because I don’t want to provide all the navigation variations for different Windows versions.

Basically, you do that by clicking the Windows Start button and type cmd word in the run entry box. This launches a command session. Type the following from the prompt. It launches the Windows Services console in all relevant versions:

C:\> services.msc

Now you’ll see the Windows Services console. Navigate to the Oracle Service and right click on it. You choose Properties.

That will bring you to this screen. Click on the drop down for the Startup type and choose Manual. Click the OK button to complete the step. That’s it, the next time you start the machine the Oracle database won’t start automatically. You should do the same to the other Oracle Services.

If you don’t have a lot of memory and it’s a development machine, this makes a lot of sense.

Written by maclochlainn

March 18th, 2010 at 11:03 pm

Posted in Microsoft Vista,Microsoft Windows 7,Oracle

SQL Certified Expert Exam

without comments

I’ve been working with one of my lab tutors to have him take the 1Z0-047 Oracle Database SQL Expert test. He checked out the online practice exam, and found a couple interesting questions and new syntax. At least, it was new to me.

Naturally, I checked it out. I’ve also added it to my online tutorial for the class. Perhaps I’m a creature of habit but a range non-equijion is always a filtered cross product logically. Certainly, the explain plans indicate that this new syntax has zero performance change over the other forms.

I once used the comma-delimited tables (like everybody else), but now I try to always use the newer CROSS JOIN syntax. In both cases the range join is put in the WHERE clause. The new syntax uses an INNER JOIN and an ON clause to hold the range match. Examples of all are below.

Comma-delimited Filtered Cross Join

1
2
3
4
5

SELECT   c.month_short_name
,        t.transaction_amount
FROM     calendar_join c, transaction_join t
WHERE    t.transaction_date BETWEEN c.start_date AND c.end_date
ORDER BY EXTRACT(MONTH FROM t.transaction_date);

Filtered CROSS JOIN

1
2
3
4
5

SELECT   c.month_short_name
,        t.transaction_amount
FROM     calendar_join c CROSS JOIN transaction_join t
WHERE    t.transaction_date BETWEEN c.start_date AND c.end_date
ORDER BY EXTRACT(MONTH FROM t.transaction_date);

Range filtered INNER JOIN

1
2
3
4
5

SELECT   c.month_short_name
,        t.transaction_amount
FROM     calendar_join c INNER JOIN transaction_join t
ON      (t.transaction_date BETWEEN c.start_date AND c.end_date)
ORDER BY EXTRACT(MONTH FROM t.transaction_date);

Without an INDEX on the start and end date of the CALENDAR_JOIN table, the Oracle explain plan for all three queries is:

1
2
3
4
5
6
7
8
9
10

Query Plan
----------------------------------------------
SELECT STATEMENT   Cost = 9
  SORT ORDER BY
    MERGE JOIN
      SORT JOIN
        TABLE ACCESS FULL TRANSACTION_JOIN
      FILTER
        SORT JOIN
          TABLE ACCESS FULL CALENDAR_JOIN

Naturally, an INDEX on the START_DATE and END_DATE columns improves performance. The results again for all three are the same.

1
2
3
4
5
6
7
8

Query Plan
----------------------------------------------
SELECT STATEMENT   Cost = 6
  SORT ORDER BY
    TABLE ACCESS BY INDEX ROWID CALENDAR_JOIN
      NESTED LOOPS
        TABLE ACCESS FULL TRANSACTION_JOIN
        INDEX RANGE SCAN DATE_RANGE

Unless I’m missing something, it looks like its only a matter of style. However, make sure you know that new one because it appears that it’s on the OCP exam. 😉

Comments are always welcome …

Written by maclochlainn

March 17th, 2010 at 9:34 pm

Posted in MySQL,Oracle,Oracle XE,sql

Oracle Trigger on Merge

without comments

An interesting question came up today while discussing PL/SQL database triggers. Could you create a trigger on a MERGE statement, like this:

1
2
3
4
5
6
7
8

CREATE OR REPLACE TRIGGER contact_merge_t1
BEFORE MERGE OF last_name ON contact_merge
FOR EACH ROW
WHEN (REGEXP_LIKE(NEW.last_name,' '))
BEGIN
  :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1);
END contact_merge_t1;
/

The answer is, no you can’t. It’ll raise an ORA-04073 error if you attempt it, like this:

BEFORE MERGE OF last_name ON contact
             *
ERROR at line 2:
ORA-04073: COLUMN list NOT valid FOR this TRIGGER TYPE

The only supported DML events are INSERT, UPDATE, and DELETE. The following DML trigger works against a MERGE statement. After all a MERGE statement is nothing more than an INSERT or UPDATE statement.

1
2
3
4
5
6
7
8

CREATE OR REPLACE TRIGGER contact_merge_t1
BEFORE INSERT OR UPDATE OF last_name ON contact_merge
FOR EACH ROW
WHEN (REGEXP_LIKE(NEW.last_name,' '))
BEGIN
  :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1);
END contact_merge_t1;
/

Complete Code Sample ↓

Expand this section to see the sample working code.

This script creates a CONTACT table, a row-level TRIGGER, a MERGE statement, and query to display the results.

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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66

-- Conditionally drop the table.
BEGIN
  FOR i IN (SELECT NULL FROM user_tables WHERE TABLE_NAME = 'CONTACT_MERGE') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE contact_merge';
  END LOOP;
END;
/
 
-- Create the table.
CREATE TABLE contact_merge
( contact_id                  NUMBER
, member_id                   NUMBER       NOT NULL
, contact_type                NUMBER       NOT NULL
, first_name                  VARCHAR2(20) NOT NULL
, middle_name                 VARCHAR2(20)
, last_name                   VARCHAR2(20) NOT NULL
, created_by                  NUMBER       NOT NULL
, creation_date               DATE         NOT NULL
, last_updated_by             NUMBER       NOT NULL
, last_update_date            DATE
, CONSTRAINT contact_merge_pk PRIMARY KEY(contact_id));
 
-- Create the trigger to enforce hyphenated last names..
CREATE OR REPLACE TRIGGER contact_merge_t1
BEFORE INSERT OR UPDATE OF last_name ON contact_merge
FOR EACH ROW
WHEN (REGEXP_LIKE(NEW.last_name,' '))
BEGIN
  :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1);
END contact_merge_t1;
/
 
-- Merge statement that violates business rule.
MERGE INTO contact_merge target
USING
( SELECT 2001 AS contact_id
  ,      1001 AS member_id
  ,      1001 AS contact_type
  ,'Catherine' AS first_name
  ,'' AS middle_name
  ,'Zeta Jones' AS last_name
  , 2 AS created_by
  , SYSDATE AS creation_date
  , 2 AS last_updated_by
  , SYSDATE AS last_update_date
FROM dual) SOURCE
ON (target.contact_id = SOURCE.contact_id)
WHEN MATCHED THEN
UPDATE SET target.last_updated_by = 3
WHEN NOT MATCHED THEN
INSERT VALUES
( SOURCE.contact_id
, SOURCE.member_id
, SOURCE.contact_type
, SOURCE.first_name
, SOURCE.middle_name
, SOURCE.last_name
, SOURCE.created_by
, SOURCE.creation_date
, SOURCE.last_updated_by
, SOURCE.last_update_date );
 
-- Query results.
SELECT first_name||DECODE(middle_name,NULL,' ',' '||middle_name||' ')||last_name AS full_name
FROM   contact_merge
WHERE  first_name = 'Catherine';

Written by maclochlainn

March 16th, 2010 at 10:40 pm

Posted in Oracle,pl/sql,sql

Multiple Column Lookups?

with 4 comments

I’ve been working with Oracle so long, sometimes it’s frustrating when I find a syntax feature isn’t in another database. I ran into another example tonight. There isn’t a multiple column look up operator in MySQL. For example, you can do this in Oracle:

1
2
3
4
5

DELETE FROM common_lookup
WHERE  (common_lookup_table,common_lookup_column) IN
         (('TRANSACTION','PAYMENT_METHOD_TYPE')
         ,('TRANSACTION','TRANSACTION_TYPE')
         ,('RENTAL_ITEM','RENTAL_ITEM_TYPE'));

When I transformed it to comply with MySQL, it seems just as clean. In fact, with strings it’s simpler.

1
2
3

DELETE FROM common_lookup
WHERE  common_lookup_table IN ('TRANSACTION','RENTAL_ITEM')
AND    common_lookup_column IN ('TRANSACTION_TYPE','PAYMENT_METHOD_TYPE','RENTAL_ITEM_TYPE');

Then, I thought about it. Oracle would let me write a single subquery returning the two columns, whereas MySQL requires two subqueries in their syntax. Likewise, MySQL doesn’t support the WITH clause, which would let me reference a single query result in the scope of the master query (Oracle and SQL Server do support that).

I guess we can hope that Oracle will implement the feature in MySQL now that they own it. 🙂 Let me know if I’ve missed some fabulous syntax alternative.

Written by maclochlainn

March 16th, 2010 at 8:00 pm

Posted in MySQL,Oracle,Oracle XE,sql

« Older Entries
Newer Entries »