Archive for the ‘DBMS_SQL’ tag
A \G Option for Oracle?
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.
Entering a colon in an NDS statement
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; / |
Subscribe via RSS