MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘sql’ Category

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

MySQL Timestamp to Date?

with 3 comments

One of my ex-students asked for an example of converting a DATETIME column into a DATE data type in MySQL. He’d tried a few approaches and hadn’t been successful. It’s best to use the DATE function to convert a DATETIME to a DATE in MySQL.

  1. Create a sample TIMECLOCK table.
DROP TABLE IF EXISTS timeclock;
 
CREATE TABLE TIMECLOCK
( timeclock_id  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
, timein        datetime
, timeout       datetime);
  1. Insert two rows with values in the TIMEIN column.
-- Insert two rows with automatic numbering and only a "time in" value.
INSERT INTO timeclock
( timein )
VALUES
(NOW()),(NOW());
  1. Update the previously inserted rows with values in the TIMEOUT column. The first UPDATE statement inserts a UTC date into the DATETIME column. That date is a time stamp of of the next day at 12:00 A.M. in the morning. The second UPDATE statement updates the TIMEOUT column with a current time stamp plus 4 hours, thirty-two minutes, and thirty-three seconds. Then, the code segment queries the results.
-- Update with tomorrow's future date at 12:00 A.M..
UPDATE timeclock
SET    timeout = ADDDATE(DATE(NOW()), INTERVAL 1 DAY)
WHERE  timeclock_id = 1;
 
-- Update with a timestamp 4 hours, thirty-two minutes, and thirty-three seconds in the future.
UPDATE timeclock
SET    timeout = ADDTIME(NOW(), '4:32:33')
WHERE  timeclock_id = 2;
 
-- Query the value sets.
SELECT timein, timeout FROM timeclock;

This returns:

+---------------------+---------------------+
| timein              | timeout             |
+---------------------+---------------------+
| 2010-06-18 16:16:08 | 2010-06-19 00:00:00 |
| 2010-06-18 16:16:08 | 2010-06-18 20:48:42 |
+---------------------+---------------------+
2 rows in set (0.00 sec)
  1. Query the differences of the timestamps as dates and times. The DATE function lets you convert a DATETIME into a DATE data type. Then, the DATEDIFF calculates the difference and returns an integer result (the interval of days). You calculate the time difference by using the TIMEDIFF function.
-- Query the difference in intervals of days.
SELECT DATE(timeout) AS dateout
,      DATE(timein) AS datein
,      DATEDIFF(DATE(timeout),DATE(timein))
FROM timeclock;
 
-- Query the difference in intervals of time.
SELECT timeout
,      timein
,      TIMEDIFF(timeout,timein)
FROM   timeclock;

These return:

+------------+------------+--------------------------------------+
| dateout    | datein     | DATEDIFF(DATE(timeout),DATE(timein)) |
+------------+------------+--------------------------------------+
| 2010-06-19 | 2010-06-18 |                                    1 |
| 2010-06-18 | 2010-06-18 |                                    0 |
+------------+------------+--------------------------------------+
2 rows in set (0.00 sec)
 
+---------------------+---------------------+--------------------------+
| timeout             | timein              | TIMEDIFF(timeout,timein) |
+---------------------+---------------------+--------------------------+
| 2010-06-19 00:00:00 | 2010-06-18 16:16:08 | 07:43:52                 |
| 2010-06-18 20:48:42 | 2010-06-18 16:16:08 | 04:32:34                 |
+---------------------+---------------------+--------------------------+
2 rows in set (0.00 sec)

You can also use:

SELECT CAST(timein AS DATE)
,      CAST(timeout AS DATE)
FROM   timeclock;

Hope this helps the one who asked how and anybody else who runs across it.

Written by maclochlainn

June 18th, 2010 at 5:48 pm

Posted in MySQL,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

Missing Features in R2

without comments

As I’ve mentioned before, I try to support Oracle, MySQL, and SQL Server in my database classes. When I downloaded SQL Server 2008 R2 Express Edition, I just downloaded the Database with Management Tools. That’s the one on the first page and shown on the left.

It appears what I really wanted was the Database with Advanced Services because I found basic features weren’t available in the Database with Management Tools version. So, you should click the Other Installation Options link. On that page you can choose between the various options.

For example, you can’t use an IF statement or local variable assignment. Also, the available data types are a subset of the mainline product. You can’t use a VARCHAR but must use the NVARCHAR. Many features of the Microsoft SQL Server Management Studio are removed too. A brief forum discussion shows that these types of problem exist in other versions too.

I thought a couple quick examples of raised exceptions would be helpful. They’re listed below.

Missing IF Syntax:

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CORE_SYSTEM_USER')
  DROP TABLE CORE_SYSTEM_USER
GO

Generated error message:

Major Error 0x80040E14, Minor Error 25501
> IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CORE_SYSTEM_USER')
  DROP TABLE CORE_SYSTEM_USER
There was an error parsing the query. [ Token line NUMBER = 1,Token line offset = 1,Token IN error = IF ]

Local variable assignment Syntax:

DECLARE @TABLE_NAME nvarchar(30)
 
@TABLE_NAME = SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CORE_SYSTEM_USER'
GO

Generated error message:

Major Error 0x80040E14, Minor Error 25501
> DECLARE @TABLE_NAME nvarchar(30)
 
@TABLE_NAME = SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CORE_SYSTEM_USER'
There was an error parsing the query. [ Token line NUMBER = 1,Token line offset = 1,Token IN error = DECLARE ]

After I clear out the install and retry it with the other, I’ll update this as to whether or not these are available in the Database with Advanced Services SQL Server 2008 R2 Express Edition.

Written by maclochlainn

May 23rd, 2010 at 9:24 pm

Posted in Microsoft Windows 7,sql,SQL Server 2008

MySQL REPLACE INTO

with 7 comments

I overlooked MySQL’s real equivalent to a MERGE statement, which is the REPLACE INTO statement. A previous example uses the INSERT statement with the ON DUPLICATE KEY clause. The following demonstrates how to perform a left join from and exernal source. These related posts all started with this one.

Demonstration

Here are the steps to accomplish an import/upload with the REPLACE INTO statement. In this example, you upload data from a flat file, or Comma Separated Value (CSV) file to a denormalized table (actually in unnormalized form). This type of file upload transfers information that doesn’t have surrogate key values. You have to create those in the scope of the transformation to the normalized tables.

Step #1 : Position your CSV file in the physical directory

After creating the virtual directory, copy the following contents into a file named kingdom_mysql_import.csv in the C:\Data\Download directory or folder. If you have Windows UAC enabled in Windows Vista or 7, you should disable it before performing this step.

Place the following in the kingdom_mysql_import.csv file. The trailing commas are meaningful in MySQL and avoid problems when reading CSV files.

Narnia, 77600,'Peter the Magnificent',12720320,12920609,
Narnia, 77600,'Edmund the Just',12720320,12920609,
Narnia, 77600,'Susan the Gentle',12720320,12920609,
Narnia, 77600,'Lucy the Valiant',12720320,12920609,
Narnia, 42100,'Peter the Magnificent',15310412,15310531,
Narnia, 42100,'Edmund the Just',15310412,15310531,
Narnia, 42100,'Susan the Gentle',15310412,15310531,
Narnia, 42100,'Lucy the Valiant',15310412,15310531,
Camelot, 15200,'King Arthur',06310310,06861212,
Camelot, 15200,'Sir Lionel',06310310,06861212,
Camelot, 15200,'Sir Bors',06310310,06351212,
Camelot, 15200,'Sir Bors',06400310,06861212,
Camelot, 15200,'Sir Galahad',06310310,06861212,
Camelot, 15200,'Sir Gawain',06310310,06861212,
Camelot, 15200,'Sir Tristram',06310310,06861212,
Camelot, 15200,'Sir Percival',06310310,06861212,
Camelot, 15200,'Sir Lancelot',06700930,06821212,

Step #2 : Connect as the student user

Disconnect and connect as the student user, or reconnect as the student user. The reconnect syntax that protects your password is:

mysql -ustudent -p

Connect to the sampledb database, like so:

mysql> USE sampledb;

Step #3 : Run the script that creates tables and sequences

Copy the following into a create_mysql_kingdom_upload.sql file within a directory of your choice. Then, run it as the student account.

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

-- This enables dropping tables with foreign key dependencies.
-- It is specific to the InnoDB Engine.
SET FOREIGN_KEY_CHECKS = 0; 
 
-- Conditionally drop objects.
SELECT 'KINGDOM' AS "Drop Table";
DROP TABLE IF EXISTS KINGDOM;
 
SELECT 'KNIGHT' AS "Drop Table";
DROP TABLE IF EXISTS KNIGHT;
 
SELECT 'KINGDOM_KNIGHT_IMPORT' AS "Drop Table";
DROP TABLE IF EXISTS KINGDOM_KNIGHT_IMPORT;
 
-- Create normalized kingdom table.
SELECT 'KINGDOM' AS "Create Table";
CREATE TABLE kingdom
( kingdom_id    INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, kingdom_name  VARCHAR(20)
, population    INT UNSIGNED) ENGINE=INNODB;
 
-- Create normalized knight table.
SELECT 'KNIGHT' AS "Create Table";
CREATE TABLE knight
( knight_id             INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, knight_name           VARCHAR(24)
, kingdom_allegiance_id INT UNSIGNED
, allegiance_start_date DATE
, allegiance_end_date   DATE
, CONSTRAINT fk_kingdom FOREIGN KEY (kingdom_allegiance_id)
  REFERENCES kingdom (kingdom_id)) ENGINE=INNODB;
 
-- Create external import table in memory only - disappears after rebooting the mysqld service.
SELECT 'KINGDOM_KNIGHT_IMPORT' AS "Create Table";
CREATE TABLE kingdom_knight_import
( kingdom_name          VARCHAR(20)
, population            INT UNSIGNED
, knight_name           VARCHAR(24)
, allegiance_start_date DATE
, allegiance_end_date   DATE) ENGINE=MEMORY;

Step #4 : Load the data into your target upload table

There a number of things that could go wrong but when you choose LOCAL there generally aren’t any problems. Run the following query from the student account while using the sampledb database, and check whether or not you can access the kingdom_import.csv file.

1
2
3
4
5
6

LOAD DATA LOCAL INFILE 'c:/Data/kingdom_mysql_import.csv'
INTO TABLE kingdom_knight_import
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n';

Step #5 : Create the upload procedure

Copy the following into a create_mysql_upload_procedure.sql file within a directory of your choice. You should note that unlike Oracle’s MERGE statement, this is done with the ON DUPLICATE KEY clause and requires actual values not a source query. This presents few options other than a stored routine, known as a stored procedure. As you can see from the code, there’s a great deal of complexity to the syntax and a much more verbose implementation than Oracle’s equivalent PL/SQL.

Then, run it as the student account. As you look at the structure to achieve this simple thing, the long standing complaint about PL/SQL being a verbose language comes to mind. Clearly, stored procedures are new to MySQL but they’re quite a bit more verbose than PL/SQL.

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

-- Conditionally drop the procedure.
SELECT 'UPLOAD_KINGDOM' AS "Drop Procedure";
DROP PROCEDURE IF EXISTS upload_kingdom;
 
-- Reset the execution delimiter to create a stored program.
DELIMITER $$
 
-- The parentheses after the procedure name must be there or the MODIFIES SQL DATA raises an compile time exception.
CREATE PROCEDURE upload_kingdom() MODIFIES SQL DATA
 
BEGIN
 
  /* Declare a handler variables. */
  DECLARE duplicate_key INT DEFAULT 0;
  DECLARE foreign_key   INT DEFAULT 0;
 
  /* Declare a duplicate key handler */
  DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key = 1;
  DECLARE CONTINUE HANDLER FOR 1216 SET foreign_key = 1;
 
  /* ---------------------------------------------------------------------- */
 
  /* Start transaction context. */
  START TRANSACTION;
 
  /* Set savepoint. */  
  SAVEPOINT both_or_none;
 
  /* Open a local cursor. */  
  REPLACE INTO kingdom
  (SELECT   DISTINCT
            k.kingdom_id
   ,        kki.kingdom_name
   ,        kki.population
   FROM     kingdom_knight_import kki LEFT JOIN kingdom k
   ON       kki.kingdom_name = k.kingdom_name
   AND      kki.population = k.population);
 
   REPLACE INTO knight
   (SELECT   kn.knight_id
    ,        kki.knight_name
    ,        k.kingdom_id
    ,        kki.allegiance_start_date AS start_date
    ,        kki.allegiance_end_date AS end_date
    FROM     kingdom_knight_import kki INNER JOIN kingdom k
    ON       kki.kingdom_name = k.kingdom_name
    AND      kki.population = k.population LEFT JOIN knight kn
    ON       k.kingdom_id = kn.kingdom_allegiance_id
    AND      kki.knight_name = kn.knight_name
    AND      kki.allegiance_start_date = kn.allegiance_start_date
    AND      kki.allegiance_end_date = kn.allegiance_end_date);
 
  /* ---------------------------------------------------------------------- */
 
  /* This acts as an exception handling block. */  
  IF duplicate_key = 1 OR foreign_key = 1 THEN
 
    /* This undoes all DML statements to this point in the procedure. */
    ROLLBACK TO SAVEPOINT both_or_none;
 
  ELSE
 
    /* This commits the writes. */
    COMMIT;
 
  END IF;
 
END;
$$
 
-- Reset the delimiter to the default.
DELIMITER ;

Step #6 : Run the upload procedure

You can run the file by calling the stored procedure built by the script. The procedure ensures that records are inserted or updated into their respective tables.

CALL upload_kingdom;

Step #7 : Test the results of the upload procedure

You can test whether or not it worked by running the following queries.

-- Check the kingdom table.
SELECT * FROM kingdom;
SELECT * FROM knight;

It should display the following information:

+------------+--------------+------------+
| kingdom_id | kingdom_name | population |
+------------+--------------+------------+
|          1 | Narnia       |      77600 |
|          2 | Narnia       |      42100 |
|          3 | Camelot      |      15200 |
+------------+--------------+------------+
 
+-----------+-------------------------+-----------------------+-----------------------+---------------------+
| knight_id | knight_name             | kingdom_allegiance_id | allegiance_start_date | allegiance_end_date |
+-----------+-------------------------+-----------------------+-----------------------+---------------------+
|         1 | 'Peter the Magnificent' |                     1 | 1272-03-20            | 1292-06-09          |
|         2 | 'Edmund the Just'       |                     1 | 1272-03-20            | 1292-06-09          |
|         3 | 'Susan the Gentle'      |                     1 | 1272-03-20            | 1292-06-09          |
|         4 | 'Lucy the Valiant'      |                     1 | 1272-03-20            | 1292-06-09          |
|         5 | 'Peter the Magnificent' |                     2 | 1531-04-12            | 1531-05-31          |
|         6 | 'Edmund the Just'       |                     2 | 1531-04-12            | 1531-05-31          |
|         7 | 'Susan the Gentle'      |                     2 | 1531-04-12            | 1531-05-31          |
|         8 | 'Lucy the Valiant'      |                     2 | 1531-04-12            | 1531-05-31          |
|         9 | 'King Arthur'           |                     3 | 0631-03-10            | 0686-12-12          |
|        10 | 'Sir Lionel'            |                     3 | 0631-03-10            | 0686-12-12          |
|        11 | 'Sir Bors'              |                     3 | 0631-03-10            | 0635-12-12          |
|        12 | 'Sir Bors'              |                     3 | 0640-03-10            | 0686-12-12          |
|        13 | 'Sir Galahad'           |                     3 | 0631-03-10            | 0686-12-12          |
|        14 | 'Sir Gawain'            |                     3 | 0631-03-10            | 0686-12-12          |
|        15 | 'Sir Tristram'          |                     3 | 0631-03-10            | 0686-12-12          |
|        16 | 'Sir Percival'          |                     3 | 0631-03-10            | 0686-12-12          |
|        17 | 'Sir Lancelot'          |                     3 | 0670-09-30            | 0682-12-12          |
+-----------+-------------------------+-----------------------+-----------------------+---------------------+

You can rerun the procedure to check that it doesn’t alter any information, then you could add a new knight to test the insertion portion.

Written by maclochlainn

May 14th, 2010 at 12:46 am

Posted in MySQL,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

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

« Older Entries
Newer Entries »