MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘pl/sql’ tag

PHP leveraging PL/SQL

with one comment

Somebody wanted another example of how to leverage a true/false condition from a PL/SQL stored function in PHP. The first key is that you write the function as if you were using it in SQL not PL/SQL. That means you return a NUMBER data type not a PL/SQL-only BOOLEAN data type.

Here’s the schema-level PL/SQL function:

CREATE OR REPLACE FUNCTION like_boolean
( a NUMBER, b NUMBER ) RETURN NUMBER IS
 
  /* Declare default false return value. */
  lv_return_value NUMBER := 0;
 
BEGIN
 
  /* Compare numbers and return true for a match. */
  IF a = b THEN
    lv_return_value := 1;
  END IF;
 
  /* Return value. */
  RETURN lv_return_value;
 
END;
/

Here’s the PHP that leverages the PL/SQL in an if-statement on line #24:

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

<?php
  // Capture local variables when provided.
  $thingOne = (isset($_GET['thingOne'])) ? $_GET['thingOne'] : 1;
  $thingTwo = (isset($_GET['thingTwo'])) ? $_GET['thingTwo'] : 1;
 
  // Open a connection.
  if(!$c = oci_connect("student","student","localhost/xe"))
  {
    die;
  }
  else
  {
 
    // Parse a statement.  
    $s = oci_parse($c,"BEGIN
                         :returnValue := LIKE_BOOLEAN(:thingOne,:thingTwo);
                       END;");
 
    // Bind input and output values to the statement.
    oci_bind_by_name($s,":returnValue",$returnValue);
    oci_bind_by_name($s,":thingOne",$thingOne);
    oci_bind_by_name($s,":thingTwo",$thingTwo);
 
    // Execute the statement.
    if (@oci_execute($s))
    {
      // Print lead in string.
      print "[".$thingOne."] and [".$thingTwo."] ";  
      if ($returnValue)
        print "are equal.<br />";
      else
        print "aren't equal.<br />";
    }
 
    // Clean up resources.
    oci_close($c);
  }
?>

If you run into a parsing error, which is infrequent now. You can wrap the multiple row PL/SQL anonymous block call with this function. It strips tabs and line returns. Alternatively, you can put all the lines of PL/SQL on a single line.

  // Strip special characters, like carriage or line returns and tabs.
  function strip_special_characters($str)
  {
    $out = "";
    for ($i = 0;$i < strlen($str);$i++)
      if ((ord($str[$i]) != 9) && (ord($str[$i]) != 10) &&
          (ord($str[$i]) != 13))
        $out .= $str[$i];
 
    // Return pre-parsed SQL statement.
    return $out;
  }

If you run into a parsing problem on Oracle XE 10g, you can wrap the PL/SQL call like the following. Alternatively, you can place the entire anonymous PL/SQL block on a single line without embedded tabs or return keys..

10
11
12
13

  $s = oci_parse($c,strip_special_characters(
                    "BEGIN
                       :returnValue := LIKE_BOOLEAN(:thingOne,:thingTwo);
                     END;"));

Hope that answers the question and helps some folks.

Written by maclochlainn

December 22nd, 2010 at 11:53 pm

Posted in LAMP,OPAL,Oracle,PHP,pl/sql

Tagged with , , ,

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 ,

Synchronizing file deletion with transaction control

without comments

I finally got back to synchronizing file deletion with transaction control. You’ll need some code from an earlier blog page. The reference to that page is the new page referenced below.

Probably the largest disappointment was discovering the details of global temporary tables. A commit on a DML statement against another table doesn’t trigger a DELETE event on the temporary table. This means a trigger built on a global temporary table doesn’t fire unless you delete from the temporary table. If they did, you could commit a change in one table and trigger another event through the temporary table. That would be a great feature.

You can find it here ….

Written by maclochlainn

August 22nd, 2008 at 6:46 am

Posted in Oracle

Tagged with , , ,

Code for my Oracle Database 11g PL/SQL Programming

with 3 comments

Somebody asked for a URI reference to the PL/SQL code for my Oracle Database 11g PL/SQL ProgrammingOracle Database 11g PL/SQL Programming book. You can find it here on the McGraw-Hill site. If you find any glitches, just update my blog. I’ll fix them and get them reposted.

Also, I’ve updated the Comparative Data Modeling Relational versus XML with screen shots for the code and the mapping of a FLOWR (For, Let, Order by, Where, and Return) statements to nested loops for PL/SQL.

Written by maclochlainn

August 13th, 2008 at 7:28 pm

Posted in Oracle,xml

Tagged with , , ,

Reversing direction in a PL/SQL range for-loop

with 2 comments

Have you ever wondered how to decrement index values in a PL/SQL range for-loop. You’ve probably heard that you can’t. That’s part true and part false.

The true part is two fold. First, range for-loops move forward from a starting low value to high value. Second, the loop exits immediately when your starting value is high and ending value low. If you use the REVERSE keyword, the loop moves backward from an ending point to the starting point. This happens because a PL/SQL range for-loop is a guard on entry loop. The starting value is compared against the ending value and exits when the starting value is greater than the ending value.

The false part happens when you want to traverse a collection backward. Oracle collections are varrays, tables, or associative arrays. Associative arrays are also known as PL/SQL tables. You overcome the ascending only limitation by using the REVERSE keyword or leveraging the Oracle Collection API library functions.

This code works when the index is numeric:

DECLARE
  TYPE collection IS TABLE OF VARCHAR2(20);
  fellowship COLLECTION := collection('Frodo','Gandalf','Aragorn');
BEGIN
  -- Increasing iterator values.
  FOR i IN REVERSE 1..fellowship.COUNT LOOP
    DBMS_OUTPUT.put('Iterator ['||i||'] ');
    DBMS_OUTPUT.put('Value ['||fellowship(i)||']');
    DBMS_OUTPUT.new_line();
  END LOOP;
END;
/

This code works when the index is a string in an associative array:

DECLARE
  CURRENT VARCHAR2(8);
  element VARCHAR2(20);
  TYPE collection IS TABLE OF VARCHAR2(20)
    INDEX BY VARCHAR2(8);
  fellowship COLLECTION;
BEGIN
  -- Assign character indexed values.
  fellowship('Hobbit') := 'Frodo';
  fellowship('Wizard') := 'Gandalf';
  fellowship('Human')  := 'Aragorn';
  -- Increasing iterator values.
  FOR i IN 1..fellowship.COUNT LOOP
    IF i = 1 THEN -- Set starting point and assign value.
      CURRENT := fellowship.LAST;
      element := fellowship(CURRENT);
    ELSE -- Change value until you read the first element.
      IF fellowship.PRIOR(CURRENT) IS NOT NULL THEN
        CURRENT := fellowship.PRIOR(CURRENT);
        element := fellowship(CURRENT);
      ELSE -- Exit when all elements read.
        EXIT;
      END IF;
    END IF;
    DBMS_OUTPUT.put('Iterator ['||i||'] ');
    DBMS_OUTPUT.put('Index ['||CURRENT||'] ');
    DBMS_OUTPUT.put('Value ['||element||']');
    DBMS_OUTPUT.new_line();
  END LOOP;
END;
/

Chris Neumüller suggested another example with a WHILE loop:

DECLARE
  TYPE collection IS TABLE OF VARCHAR2(20) INDEX BY VARCHAR2(8);
  fellowship COLLECTION;
  CURRENT    VARCHAR2(20);
BEGIN
  -- Assign character indexed values.
  fellowship('Hobbit') := 'Frodo';
  fellowship('Wizard') := 'Gandalf';
  fellowship('Human')  := 'Aragorn';
  -- Increasing iterator values.
  IF fellowship.COUNT > 0 THEN
    CURRENT := fellowship.LAST;
    WHILE CURRENT IS NOT NULL LOOP
      DBMS_OUTPUT.put('Iterator ['||CURRENT||'] ');
      DBMS_OUTPUT.put('Value ['||fellowship(CURRENT)||'] ');
      DBMS_OUTPUT.new_line();
      CURRENT := fellowship.PRIOR(CURRENT);
    END LOOP;
  END IF;
END;
/

While the former works, you should consider changing the cursor that populates the collection by adding a descending ORDER BY clause when indexing with a string. The algorithm is a bit more work but it also supports sparsely populated indexes and string-indexed associative arrays. All you need do is swap the LAST for FIRST and PRIOR for NEXT to go forward through a sparsely populated index.

Written by maclochlainn

May 21st, 2008 at 4:29 am

Posted in Oracle

Tagged with , , , ,

UTOUG web site updates links

without comments

There were two broken links in earlier blogs because UTOUG moved their presentation download repository. I’ve updated those links for my Utah Oracle User Group Training Days 2008 presentations. You can also uses these new links: PL/SQL 11g New Features and an Oracle LOBs. You can learn more about UTOUG and prior presentations at http://www.utoug.org, including Tom Kyte’s Materialized Views presentation from the same 2008 spring conference.

Written by maclochlainn

May 13th, 2008 at 4:00 am

Posted in Oracle

Tagged with , , , , , , , ,

PHP code to read a PL/SQL reference cursor

with 3 comments

The following demonstrates how to read a PL/SQL reference cursor in a PHP program. The reference cursor function is defined in the Pipelined Functions & PL/SQL Tables blog page. I’ve commented it to the hilt for those new to PHP.

The reference cursor maintains a separate connection to the database to access the reference cursor. You also use the oci_fetch_assoc() function to get the data. That strip_special_characters() function lets you format your call to the PL/SQL program and remove non-parsing line returns and tabs before running the oci_parse() function.

<?php
  // Return successful attempt to connect to the database.
  if ($c = @oci_connect("plsql","plsql","orcl"))
  {
    // Declare input variables.
    (isset($_GET['table'])) ? $table = (int) $_GET['table'] : $table = 'ITEM';
    (isset($_GET['column'])) ? $column = (int) $_GET['column'] : $column = 'ITEM_TYPE';
 
    // Declare a PL/SQL execution command.
    $stmt = "BEGIN
              :return_cursor := get_common_cursor(:table,:column);
            END;";
 
    // Strip special characters to avoid ORA-06550 and PLS-00103 errors.
    $stmt = strip_special_characters($stmt);
 
    // Parse a query through the connection.
    $s = oci_parse($c,$stmt);
 
    // Declare a return cursor for the connection.
    $rc = oci_new_cursor($c);
 
    // Bind PHP variables to the OCI input or in mode variables.
    oci_bind_by_name($s,':table',$table);
    oci_bind_by_name($s,':column',$column);
 
    // Bind PHP variables to the OCI output or in/out mode variable.
    oci_bind_by_name($s,':return_cursor',$rc,-1,OCI_B_CURSOR);
 
    // Execute the PL/SQL statement &amp; reference cursor.
    oci_execute($s);
    oci_execute($rc);
 
    // Print the table header with known labels.
    print '<table border="1" cellpadding="3" cellspacing="0">';
 
    // Set dynamic labels control variable true.
    $label = true;
 
    // Read the contents of the reference cursor.
    while($row = oci_fetch_assoc($rc))
    {
      // Declare header and data variables.
      $header = "";
      $data = "";
 
      // Read the reference cursor into a table.
      foreach ($row as $name => $column)
      {
        // Capture labels for the first row.
        if ($label)
        {
          $header .= '<td class="e">'.$name.'</td>';
          $data .= '<td class="v">'.$column.'</td>';
        }
        else
          $data .= '<td class=v>'.$column.'</td>';
      }
 
      // Print the header row once.
      if ($label)
      {
        print '<tr>'.$header.'</tr>';
        $label = !$label;
      }
 
      // Print the data rows.
      print '<tr>'.$data.'</tr>';
    }
 
    // Print the HTML table close.
    print '</table>';
 
    // Disconnect from database.
    oci_close($c);
  }
  else
  {
    // Assign the OCI error and format double and single quotes.
    $errorMessage = oci_error();
    print htmlentities($errorMessage['message'])."<br />";
  }
 
  // Strip special characters, like carriage or line returns and tabs.
  function strip_special_characters($str)
  {
    $out = "";
    for ($i = 0;$i &lt; strlen($str);$i++)
      if ((ord($str[$i]) != 9) && (ord($str[$i]) != 10) &&
          (ord($str[$i]) != 13))
        $out .= $str[$i];
 
  // Return character only strings.
  return $out; }
?>

Written by maclochlainn

May 12th, 2008 at 5:17 am

Posted in PHP

Tagged with , ,

Entering a colon in an NDS statement

with 2 comments

Somebody asked me how you include a colon in a Native Dynamic SQL (NDS) statement when it’s not related to a placeholder. A colon without a placeholder raises an ORA-00947 error when parsing the statement. The error means you’ve failed to submit enough bind variables. You can substitute a CHR(58) where you need to insert the standalone colon. The NDS or DBMS_SQL parsing phase ignores a CHR(58), which translates during actual SQL statement parsing as a colon.

Let’s say you want to insert a column value with an ASIN (Amazon Standard Identification Number) code in the format: ASIN: B000VBJEEG

Using NDS, you have two choices. You can let the entry person type the full string and pass that string as a bind variable, or you can substitute CHR(58) for the colon and enter only the ASIN code. The example (Oracle Database 11g PL/SQL Programming, pp. 386-387) implements the latter:

CREATE OR REPLACE PROCEDURE insert_item
( asin VARCHAR2
, item_type VARCHAR2
, item_title VARCHAR2
, item_subtitle VARCHAR2 := ''
, rating VARCHAR2
, agency VARCHAR2
, release_date DATE ) IS
 
  -- Local variable for a dynamic SQL statement.
  stmt VARCHAR2(2000);
 
BEGIN
 
  -- Create a dynamic statement with bind variables.
  stmt := 'INSERT INTO item '
  || '( item_id'
  || ', item_barcode'
  || ', item_type'
  || ', item_title'
  || ', item_subtitle'
  || ', item_desc'
  || ', item_blob'
  || ', item_photo'
  || ', item_rating'
  || ', item_rating_agency'
  || ', item_release_date' 
  || ', created_by'
  || ', creation_date'
  || ', last_updated_by'
  || ', last_update_date ) '
  || 'VALUES '
  || '( item_s1.nextval'
  || ',''ASIN''||CHR(58)||:asin'
  || ',(SELECT common_lookup_id'
  || ' FROM common_lookup'
  || ' WHERE common_lookup_type = :item_type)'
  || ', :item_title'
  || ', :item_subtitle'
  || ', empty_clob()'
  || ', NULL, NULL'
  || ', :rating'
  || ', :agency'
  || ', :release_date'
  || ', 3, SYSDATE, 3, SYSDATE)';
 
  -- Print debug statement.
  dbms_output.put_line(stmt);
 
  -- Execute dynamic statement with bind variables.
  EXECUTE IMMEDIATE stmt
  USING asin, item_type, item_title, item_subtitle, rating, agency, release_date;
END insert_item;
/

There’s quite a nifty or nasty trick inside the dynamic SQL statement. You’ll notice that the colon is concatenated to the ASIN and a bind variable. It is critical that you don’t encapsulate the bind variable inside quotes, or you’ll raise an ORA-01006 exception (check Table 11-2 in the Oracle Database 11g PL/SQL Programming book for more detail). You can’t enclose a string in single quotes because the string is substituted as a string, and it trips up the parser.

You can test this dynamic SQL statement with the following anonymous PL/SQL block:

BEGIN
  insert_item('B00005JPO1'
             ,'DVD_WIDE_SCREEN'
             ,'Indiana Jones and the Crystal Skull'
             ,'2-Disc Special Edition'
             ,'PG-13'
             ,'MPAA'
             ,'14-OCT-08');
END;
/

Written by maclochlainn

May 10th, 2008 at 4:58 am

Posted in Oracle,sql

Tagged with , , , , , , ,

Newer Entries »