working with long columns
The LONG and LONG RAW datatypes have been deprecated in favour of LOBs for many Oracle versions, yet they still exist in the data dictionary and legacy systems. For this reason, it is still quite common to see questions in Oracle forums about querying and manipulating LONGs. These questions are prompted because the LONG datatype is extremely inflexible and is subject to a number of restrictions. In fact, it's fair to say that there isn't much we can do with a LONG value once it has been inserted into a table. The Oracle documentation describes the LONG datatype's main restrictions as follows:
The use of LONG values is subject to these restrictions:In addition, LONG columns cannot appear in these parts of SQL statements:
- A table can contain only one LONG column.
- You cannot create an object type with a LONG attribute.
- LONG columns cannot appear in WHERE clauses or in integrity constraints (except that they can appear in NULL and NOT NULL constraints).
- LONG columns cannot be indexed.
- LONG data cannot be specified in regular expressions.
- A stored function cannot return a LONG value.
- You can declare a variable or argument of a PL/SQL program unit using the LONG data type. However, you cannot then call the program unit from SQL.
- Within a single SQL statement, all LONG columns, updated tables, and locked tables must be located on the same database.
- LONG and LONG RAW columns cannot be used in distributed SQL statements and cannot be replicated.
- If a table has both LONG and LOB columns, then you cannot bind more than 4000 bytes of data to both the LONG and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the LONG or the LOB column.
- GROUP BY clauses, ORDER BY clauses, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements
- The UNIQUE operator of a SELECT statement
- The column list of a CREATE CLUSTER statement
- The CLUSTER clause of a CREATE MATERIALIZED VIEW statement
- SQL built-in functions, expressions, or conditions
- SELECT lists of queries containing GROUP BY clauses
- SELECT lists of subqueries or queries combined by the UNION, INTERSECT, or MINUS set operators
- SELECT lists of CREATE TABLE ... AS SELECT statements
- ALTER TABLE ... MOVE statements
- SELECT lists in subqueries in INSERT statements
Despite the size of this list, we'll find that most of the time we are blocked by the two restrictions highlighted above. For this reason, we'll concentrate on these in this article and provide some solutions to workaround them.
an example problem
We'll begin by trying to solve the following problem: what are all the mandatory columns in our schema (i.e. the "not null" columns)? To help us to investigate and solve this problem, we will create a table with range of mandatory columns, as follows.
SQL> CREATE TABLE nullability_test 2 ( c1 INT NOT NULL PRIMARY KEY 3 , c2 INT NOT NULL 4 , c3 INT CONSTRAINT column_level_check CHECK (c3 IS NOT NULL) 5 , c4 INT 6 , c5 INT 7 , CONSTRAINT table_level_check CHECK (c4 IS NOT NULL) 8 );
Table created.
Our table has 5 columns, 4 of which are mandatory. We can see that the not null constraints have been applied in different ways but ultimately they all have the same effect. We will now try to solve our problem by querying USER_TAB_COLUMNS, which has a column named "NULLABLE".
SQL> SELECT column_name 2 , nullable 3 FROM user_tab_columns 4 WHERE table_name = 'NULLABILITY_TEST' 5 ORDER BY 6 column_id;
COLUMN_NAME NULLABLE --------------- -------- C1 N C2 N C3 Y C4 Y C5 Y 5 rows selected.
We can see that the USER_TAB_COLUMNS view only lists the first two columns as being mandatory. These two columns were defined with the "NOT NULL" keywords during table creation and this appears to be the only method that this view understands (note that this view uses USER_TAB_COLS which in turn decodes the COL$.NULL$ column). Therefore, to solve this problem, we need to look elsewhere, so we'll use the USER_CONSTRAINTS view, described as follows.
SQL> DESC user_constraints
Name Null? Type ---------------------------------- -------- ----------------- OWNER VARCHAR2(30) CONSTRAINT_NAME NOT NULL VARCHAR2(30) CONSTRAINT_TYPE VARCHAR2(1) TABLE_NAME NOT NULL VARCHAR2(30) SEARCH_CONDITION LONG R_OWNER VARCHAR2(30) R_CONSTRAINT_NAME VARCHAR2(30) DELETE_RULE VARCHAR2(9) STATUS VARCHAR2(8) DEFERRABLE VARCHAR2(14) DEFERRED VARCHAR2(9) VALIDATED VARCHAR2(13) GENERATED VARCHAR2(14) BAD VARCHAR2(3) RELY VARCHAR2(4) LAST_CHANGE DATE INDEX_OWNER VARCHAR2(30) INDEX_NAME VARCHAR2(30) INVALID VARCHAR2(7) VIEW_RELATED VARCHAR2(14)
The USER_CONSTRAINTS view exposes a LONG column named SEARCH_CONDITION and this column contains the NOT NULL constraints in our table. We can demonstrate this below, but rather than query this view directly, we'll create a utility view to join USER_CONSTRAINTS, USER_TAB_COLUMNS and USER_CONS_COLUMNS. It will make our subsequent examples shorter and simpler. We create and query the view as follows.
SQL> CREATE VIEW nullability_view 2 AS 3 SELECT utc.table_name 4 , utc.column_name 5 , ucc.constraint_name 6 , uc.search_condition 7 FROM user_tab_columns utc 8 LEFT OUTER JOIN 9 user_cons_columns ucc 10 ON ( utc.table_name = ucc.table_name 11 AND utc.column_name = ucc.column_name) 12 LEFT OUTER JOIN 13 user_constraints uc 14 ON (ucc.constraint_name = uc.constraint_name) 15 WHERE utc.table_name = 'NULLABILITY_TEST' 16 AND ( uc.constraint_type = 'C' 17 OR uc.constraint_type IS NULL) 18 ORDER BY 19 utc.column_id;
View created.
SQL> SELECT * 2 FROM nullability_view;
TABLE_NAME COLUMN_NAME CONSTRAINT_NAME SEARCH_CONDITION -------------------- --------------- -------------------- ---------------------------- NULLABILITY_TEST C1 SYS_C0011302 "C1" IS NOT NULL NULLABILITY_TEST C2 SYS_C0011303 "C2" IS NOT NULL NULLABILITY_TEST C3 COLUMN_LEVEL_CHECK c3 IS NOT NULL NULLABILITY_TEST C4 TABLE_LEVEL_CHECK c4 IS NOT NULL NULLABILITY_TEST C5 5 rows selected.
As described, the SEARCH_CONDITION column describes all of our NOT NULL constraints. Oracle has generated the conditions for the C1 and C2 columns, based on the fact that we added the inline "NOT NULL" directive to these in our CREATE TABLE command. For columns C3 and C4, however, Oracle has taken the text of our inline and table-level check constraints directly.
This means that we should be able to use SEARCH_CONDITION to solve our problem of identifying all mandatory columns. However, when we try to use this LONG column, we find that we can't, as the following example demonstrates.
SQL> SELECT * 2 FROM nullability_view 3 WHERE UPPER(search_condition) LIKE '%IS NOT NULL%';
WHERE UPPER(search_condition) LIKE '%IS NOT NULL%'
*
ERROR at line 3:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG
Due to restrictions with querying LONG columns, we can't interrogate the SEARCH_CONDITION directly. We therefore need to find workarounds, which is of course the subject of this article.
workarounds
There are several workarounds we can use to solve our sample problem. We will examine each of the following in turn:
- TO_LOB;
- PL/SQL;
- DBMS_XMLGEN;
- Dictionary Long Application (oracle-developer.net utility).
method one: to_lob
The first method is the built-in SQL function TO_LOB. This function is designed to convert a LONG or LONG RAW column to a CLOB or BLOB, respectively. It responds to the following LONG restriction.
SQL> CREATE TABLE nullability_snapshot 2 AS 3 SELECT * 4 FROM nullability_view;
SELECT *
*
ERROR at line 3:
ORA-00997: illegal use of LONG datatype
Therefore, we can use the TO_LOB function to convert our SEARCH_CONDITION column to a CLOB, as follows.
SQL> CREATE TABLE nullability_snapshot 2 AS 3 SELECT table_name 4 , column_name 5 , constraint_name 6 , TO_LOB(search_condition) AS search_condition 7 FROM nullability_view;
Table created.
We have copied the NULLABILITY_VIEW resultset to a new table and converted SEARCH_CONDITION to a CLOB in the process, which we will confirm as follows.
SQL> DESC nullability_snapshot
Name Null? Type ---------------------------------- -------- --------------- TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME NOT NULL VARCHAR2(30) CONSTRAINT_NAME VARCHAR2(30) SEARCH_CONDITION CLOB
As highlighted, the SEARCH_CONDITION column is a CLOB in our NULLABILITY_SNAPSHOT table, which means we can interrogate it with standard SQL string functions, as follows.
SQL> SELECT * 2 FROM nullability_snapshot 3 WHERE UPPER(search_condition) LIKE '%IS NOT NULL%';
TABLE_NAME COLUMN_NAME CONSTRAINT_NAME SEARCH_CONDITION -------------------- --------------- -------------------- ------------------------------ NULLABILITY_TEST C1 SYS_C0011302 "C1" IS NOT NULL NULLABILITY_TEST C2 SYS_C0011303 "C2" IS NOT NULL NULLABILITY_TEST C3 COLUMN_LEVEL_CHECK c3 IS NOT NULL NULLABILITY_TEST C4 TABLE_LEVEL_CHECK c4 IS NOT NULL 4 rows selected.
We have solved our sample problem but we had to take a copy of the underlying data. This is not necessarily an issue for these examples, as we are using database object metadata, which should be reasonably static in controlled systems. Developers wishing to use this technique on application data rather than dictionary data might be deterred by having to take a snapshot that might quickly become stale. Unfortunately, the TO_LOB function cannot be used in SQL outside of a Create Table As Select (CTAS) statement, as the following example demonstrates.
SQL> SELECT table_name 2 , column_name 3 , constraint_name 4 , TO_LOB(search_condition) AS search_condition 5 FROM nullability_view;
, TO_LOB(search_condition) AS search_condition
*
ERROR at line 4:
ORA-00932: inconsistent datatypes: expected - got LONG
For volatile data, therefore, we might want to consider the remaining workarounds, as they do not involve copying data to a static target.
method two: pl/sql
The second method simply takes advantage of PL/SQL's ability to convert LONG data to VARCHAR2 while fetching from cursors. In the following example, we will loop through the NULLABILITY_VIEW data and interrogate the SEARCH_CONDITION column for NOT NULL constraints.
SQL> BEGIN 2 FOR r IN (SELECT * FROM nullability_view) LOOP 3 IF UPPER(r.search_condition) LIKE '%IS NOT NULL%' THEN 4 DBMS_OUTPUT.PUT_LINE( 5 'Column name ' || r.column_name || ' is mandatory' 6 ); 7 END IF; 8 END LOOP; 9 END; 10 /
Column name C1 is mandatory Column name C2 is mandatory Column name C3 is mandatory Column name C4 is mandatory PL/SQL procedure successfully completed.
We can see, therefore, that it is simple to solve this problem with PL/SQL. However, an anonymous block such as the above example is not particularly useful as it isn't a rowsource, so we can easily change it to a pipelined function (which will enable us to query it). We will begin by creating the supporting object and collection types for the function, as follows.
SQL> CREATE TYPE nullability_ot AS OBJECT 2 ( table_name VARCHAR2(30) 3 , column_name VARCHAR2(30) 4 , search_condition CLOB 5 ); 6 /
Type created.
SQL> CREATE TYPE nullability_ntt AS TABLE OF nullability_ot; 2 /
Type created.
Note that we have defined SEARCH_CONDITION as a CLOB in our object type. We create the pipelined function as follows.
SQL> CREATE FUNCTION nullability_pipelined RETURN nullability_ntt PIPELINED AS 2 BEGIN 3 FOR r IN (SELECT * FROM nullability_view) LOOP 4 PIPE ROW ( nullability_ot(r.table_name, 5 r.column_name, 6 r.search_condition) ); 7 END LOOP; 8 RETURN; 9 END; 10 /
Function created.
This function is extremely simple. As with our anonymous block example, we loop through the NULLABILITY_VIEW data and exploit PL/SQL's conversion of LONG data to VARCHAR2. Instead of printing the output, however, we pipe the data from the pipelined function, converting the SEARCH_CONDITION to CLOB in the process (PL/SQL can handle VARCHAR2 data up to 32Kb).
We will search for mandatory columns using the new function below.
SQL> SELECT * 2 FROM TABLE(nullability_pipelined) 3 WHERE UPPER(search_condition) LIKE '%IS NOT NULL%';
TABLE_NAME COLUMN_NAME SEARCH_CONDITION -------------------- --------------- ------------------------------ NULLABILITY_TEST C1 "C1" IS NOT NULL NULLABILITY_TEST C2 "C2" IS NOT NULL NULLABILITY_TEST C3 c3 IS NOT NULL NULLABILITY_TEST C4 c4 IS NOT NULL 4 rows selected.
As with TO_LOB, we have identified all mandatory columns, but this time without having to materialise the NULLABILITY_VIEW data. To demonstrate that we have converted the LONG to a CLOB, we'll create and describe a table of the resultset.
SQL> CREATE TABLE nullability_pipelined_snapshot 2 AS 3 SELECT * 4 FROM TABLE(nullability_pipelined);
Table created.
SQL> DESC nullability_pipelined_snapshot
Name Null? Type --------------------------------------- -------- -------------- TABLE_NAME VARCHAR2(30) COLUMN_NAME VARCHAR2(30) SEARCH_CONDITION CLOB
As expected, Oracle has determined the datatypes from the pipelined function resultset (they are therefore the same as the source NULLABILITY_OT object type) and SEARCH_CONDITION is now a CLOB.
PL/SQL has a LONG datatype of its own, but this actually a subtype defined as a VARCHAR2(32767). Despite its name, therefore, it is not the same as the troublesome SQL LONG datatype that has prompted this article.
method three: dbms_xmlgen
Still within PL/SQL, the third method uses the built-in DBMS_XMLGEN package. This package has a number of XML-generating functions and procedures that convert a relational resultset to XML format. In the process of doing this, it also converts LONG data to VARCHAR2 and we can exploit this to search for mandatory columns.
The DBMS_XMLGEN function we will use is named GETXMLTYPE and to see how it works, we will build up our solution in stages. First, we will see the XML output from the GETXMLTYPE function, as follows.
SQL> SELECT DBMS_XMLGEN.GETXMLTYPE('SELECT * FROM nullability_view') AS xml
2 FROM dual;
XML
------------------------------------------------------------------------------
<ROWSET>
<ROW>
<TABLE_NAME>NULLABILITY_TEST</TABLE_NAME>
<COLUMN_NAME>C1</COLUMN_NAME>
<CONSTRAINT_NAME>SYS_C0021312</CONSTRAINT_NAME>
<SEARCH_CONDITION>"C1" IS NOT NULL</SEARCH_CONDITION>
</ROW>
<ROW>
<TABLE_NAME>NULLABILITY_TEST</TABLE_NAME>
<COLUMN_NAME>C2</COLUMN_NAME>
<CONSTRAINT_NAME>SYS_C0021313</CONSTRAINT_NAME>
<SEARCH_CONDITION>"C2" IS NOT NULL</SEARCH_CONDITION>
</ROW>
<ROW>
<TABLE_NAME>NULLABILITY_TEST</TABLE_NAME>
<COLUMN_NAME>C3</COLUMN_NAME>
<CONSTRAINT_NAME>COLUMN_LEVEL_CHECK</CONSTRAINT_NAME>
<SEARCH_CONDITION>c3 IS NOT NULL</SEARCH_CONDITION>
</ROW>
<ROW>
<TABLE_NAME>NULLABILITY_TEST</TABLE_NAME>
<COLUMN_NAME>C4</COLUMN_NAME>
<CONSTRAINT_NAME>TABLE_LEVEL_CHECK</CONSTRAINT_NAME>
<SEARCH_CONDITION>c4 IS NOT NULL</SEARCH_CONDITION>
</ROW>
<ROW>
<TABLE_NAME>NULLABILITY_TEST</TABLE_NAME>
<COLUMN_NAME>C5</COLUMN_NAME>
</ROW>
</ROWSET>
1 row selected.
Oracle generates a canonical XML document from the resultset. Column/expression/alias names are converted to tags within each ROW sequence and the entire resultset of repeating ROWs is enclosed within ROWSET tags. Once we have an XML document, we can use standard Oracle functions to convert it back to a relational format. There are two ways to do this, depending on Oracle version, and we'll see both below. First, we'll use the older method, available from Oracle 9i Release 2, as follows.
SQL> WITH xml AS (
2 SELECT XMLTYPE(
3 DBMS_XMLGEN.GETXML('SELECT * FROM nullability_view')
4 ) AS xml
5 FROM dual
6 )
7 SELECT extractValue(xs.object_value, '/ROW/TABLE_NAME') AS table_name
8 , extractValue(xs.object_value, '/ROW/COLUMN_NAME') AS column_name
9 , extractValue(xs.object_value, '/ROW/CONSTRAINT_NAME') AS constraint_name
10 , extractValue(xs.object_value, '/ROW/SEARCH_CONDITION') AS search_condition
11 FROM xml x
12 , TABLE(XMLSEQUENCE(EXTRACT(x.xml, '/ROWSET/ROW'))) xs;
TABLE_NAME COLUMN_NAME CONSTRAINT_NAME SEARCH_CONDITION -------------------- --------------- -------------------- ------------------------------ NULLABILITY_TEST C1 SYS_C0011316 "C1" IS NOT NULL NULLABILITY_TEST C2 SYS_C0011317 "C2" IS NOT NULL NULLABILITY_TEST C3 COLUMN_LEVEL_CHECK c3 IS NOT NULL NULLABILITY_TEST C4 TABLE_LEVEL_CHECK c4 IS NOT NULL NULLABILITY_TEST C5 5 rows selected.
With the XMLSEQUENCE and EXTRACT functions, we have generated a number of smaller XML fragments from the rowset. The extractValue function enables us to access particular scalar attributes of each fragment, based on their XPath expressions. Therefore, we have simply turned the XML document back into a relational resultset, but this time the SEARCH_CONDITION column has been converted to VARCHAR2. This means, of course, that we are now able to solve our sample problem, as follows.
SQL> WITH xml AS (
2 SELECT XMLTYPE(
3 DBMS_XMLGEN.GETXML('SELECT * FROM nullability_view')
4 ) AS xml
5 FROM dual
6 )
7 , parsed_xml AS (
8 SELECT extractValue(xs.object_value, '/ROW/TABLE_NAME') AS table_name
9 , extractValue(xs.object_value, '/ROW/COLUMN_NAME') AS column_name
10 , extractValue(xs.object_value, '/ROW/CONSTRAINT_NAME') AS constraint_name
11 , extractValue(xs.object_value, '/ROW/SEARCH_CONDITION') AS search_condition
12 FROM xml x
13 , TABLE(XMLSEQUENCE(EXTRACT(x.xml, '/ROWSET/ROW'))) xs
14 )
15 SELECT *
16 FROM parsed_xml
17 WHERE UPPER(search_condition) LIKE '%IS NOT NULL%';
TABLE_NAME COLUMN_NAME CONSTRAINT_NAME SEARCH_CONDITION -------------------- --------------- -------------------- ------------------------------ NULLABILITY_TEST C1 SYS_C0011316 "C1" IS NOT NULL NULLABILITY_TEST C2 SYS_C0011317 "C2" IS NOT NULL NULLABILITY_TEST C3 COLUMN_LEVEL_CHECK c3 IS NOT NULL NULLABILITY_TEST C4 TABLE_LEVEL_CHECK c4 IS NOT NULL 4 rows selected.
Finally, we will see what datatypes are returned from this example by creating and describing a snapshot table, as follows.
SQL> CREATE TABLE nullability_xmlseq_snapshot
2 AS
3 WITH xml AS (
4 SELECT XMLTYPE(
5 DBMS_XMLGEN.GETXML('SELECT * FROM nullability_view')
6 ) AS xml
7 FROM dual
8 )
9 SELECT extractValue(xs.object_value, '/ROW/TABLE_NAME') AS table_name
10 , extractValue(xs.object_value, '/ROW/COLUMN_NAME') AS column_name
11 , extractValue(xs.object_value, '/ROW/CONSTRAINT_NAME') AS constraint_name
12 , extractValue(xs.object_value, '/ROW/SEARCH_CONDITION') AS search_condition
13 FROM xml x
14 , TABLE(XMLSEQUENCE(EXTRACT(x.xml, '/ROWSET/ROW'))) xs;
Table created.
SQL> DESC nullability_xmlseq_snapshot
Name Null? Type ---------------------------------- -------- -------------------- TABLE_NAME VARCHAR2(4000) COLUMN_NAME VARCHAR2(4000) CONSTRAINT_NAME VARCHAR2(4000) SEARCH_CONDITION VARCHAR2(4000)
We can see that all columns are returned as VARCHAR2(4000), so for completeness we would need to use the CAST function on each column in our resultset to correct their lengths. However, we should be more concerned that the SEARCH_CONDITION column is only 4,000 bytes. This means that LONGs over this size cannot be converted with this method (we will re-visit this later in the article).
The newer method for converting XML into a relational format (available from Oracle 10g Release 2 onwards) is to use XMLTABLE, as follows.
SQL> WITH xml AS (
2 SELECT DBMS_XMLGEN.GETXMLTYPE(
3 'SELECT * FROM nullability_view'
4 ) AS xml
5 FROM dual
6 )
7 SELECT xs.table_name
8 , xs.column_name
9 , xs.constraint_name
10 , xs.search_condition
11 FROM xml x
12 , XMLTABLE('/ROWSET/ROW'
13 PASSING x.xml
14 COLUMNS table_name VARCHAR2(30) PATH 'TABLE_NAME',
15 column_name VARCHAR2(30) PATH 'COLUMN_NAME',
16 constraint_name VARCHAR2(30) PATH 'CONSTRAINT_NAME',
17 search_condition VARCHAR2(4000)) xs;
TABLE_NAME COLUMN_NAME CONSTRAINT_NAME SEARCH_CONDITION -------------------- --------------- -------------------- ------------------------------ NULLABILITY_TEST C1 SYS_C0021312 "C1" IS NOT NULL NULLABILITY_TEST C2 SYS_C0021313 "C2" IS NOT NULL NULLABILITY_TEST C3 COLUMN_LEVEL_CHECK c3 IS NOT NULL NULLABILITY_TEST C4 TABLE_LEVEL_CHECK c4 IS NOT NULL NULLABILITY_TEST C5 5 rows selected.
The XMLTABLE function is much richer in functionality because it supports XQuery and is Oracle's supported method from 11g onwards. We can see that it has the added benefit of being able to strongly-type the columns that we parse from the XML document and we do not need to use the extractValue function to access them.
We can simply add a predicate to the SQL to solve our sample problem, as follows.
SQL> WITH xml AS (
2 SELECT DBMS_XMLGEN.GETXMLTYPE(
3 'SELECT * FROM nullability_view'
4 ) AS xml
5 FROM dual
6 )
7 SELECT xs.table_name
8 , xs.column_name
9 , xs.constraint_name
10 , xs.search_condition
11 FROM xml x
12 , XMLTABLE('/ROWSET/ROW'
13 PASSING x.xml
14 COLUMNS table_name VARCHAR2(30) PATH 'TABLE_NAME',
15 column_name VARCHAR2(30) PATH 'COLUMN_NAME',
16 constraint_name VARCHAR2(30) PATH 'CONSTRAINT_NAME',
17 search_condition VARCHAR2(4000)) xs
18 WHERE UPPER(xs.search_condition) LIKE '%IS NOT NULL%';
TABLE_NAME COLUMN_NAME CONSTRAINT_NAME SEARCH_CONDITION -------------------- --------------- -------------------- ------------------------------ NULLABILITY_TEST C1 SYS_C0021312 "C1" IS NOT NULL NULLABILITY_TEST C2 SYS_C0021313 "C2" IS NOT NULL NULLABILITY_TEST C3 COLUMN_LEVEL_CHECK c3 IS NOT NULL NULLABILITY_TEST C4 TABLE_LEVEL_CHECK c4 IS NOT NULL 4 rows selected.
We can shorten the XMLTABLE example by including the DBMS_XMLGEN call in the PASSING clause of the XQuery, as follows.
SQL> SELECT xs.table_name
2 , xs.column_name
3 , xs.constraint_name
4 , xs.search_condition
5 FROM XMLTABLE('/ROWSET/ROW'
6 PASSING (SELECT DBMS_XMLGEN.GETXMLTYPE(
7 'SELECT * FROM nullability_view'
8 ) FROM dual)
9 COLUMNS table_name VARCHAR2(30) PATH 'TABLE_NAME',
10 column_name VARCHAR2(30) PATH 'COLUMN_NAME',
11 constraint_name VARCHAR2(30) PATH 'CONSTRAINT_NAME',
12 search_condition VARCHAR2(4000)) xs
13 WHERE UPPER(xs.search_condition) LIKE '%IS NOT NULL%';
TABLE_NAME COLUMN_NAME CONSTRAINT_NAME SEARCH_CONDITION -------------------- --------------- -------------------- ------------------------------ NULLABILITY_TEST C1 SYS_C0021312 "C1" IS NOT NULL NULLABILITY_TEST C2 SYS_C0021313 "C2" IS NOT NULL NULLABILITY_TEST C3 COLUMN_LEVEL_CHECK c3 IS NOT NULL NULLABILITY_TEST C4 TABLE_LEVEL_CHECK c4 IS NOT NULL 4 rows selected.
Finally, to demonstrate how XMLTABLE is strongly-typed, we can create and describe a snapshot table as before.
SQL> CREATE TABLE nullability_xmltab_snapshot
2 AS
3 SELECT xs.table_name
4 , xs.column_name
5 , xs.constraint_name
6 , xs.search_condition
7 FROM XMLTABLE('/ROWSET/ROW'
8 PASSING (SELECT DBMS_XMLGEN.GETXMLTYPE(
9 'SELECT * FROM nullability_view'
10 ) FROM dual)
11 COLUMNS table_name VARCHAR2(30) PATH 'TABLE_NAME',
12 column_name VARCHAR2(30) PATH 'COLUMN_NAME',
13 constraint_name VARCHAR2(30) PATH 'CONSTRAINT_NAME',
14 search_condition VARCHAR2(4000)) xs;
Table created.
SQL> DESC nullability_xmltab_snapshot
Name Null? Type --------------------- -------- ---------------- TABLE_NAME VARCHAR2(30) COLUMN_NAME VARCHAR2(30) CONSTRAINT_NAME VARCHAR2(30) SEARCH_CONDITION VARCHAR2(4000)
method four: dictionary long application
The fourth and final method we will examine is a follow-on from the PL/SQL workaround and uses the Dictionary Long Application (DLA). This is an oracle-developer.net utility developed to solve the problem of LONGs in dictionary views. The DLA has two versions:
- static version: this is for Oracle versions 9i and 10g Release 1 and creates a number of static pipelined functions (one per dictionary view) and is based on the PL/SQL LONG-to-CLOB method we demonstrated earlier;
- dynamic version: this is for Oracle 10g Release 2 onwards and creates a single generic pipelined function using an Oracle Data Cartridge and the ANYDATASET type. The dynamic DLA is able to take any query and generate a resultset with LONGs converted to CLOBs.
Links for the source code and further reading about the dynamic DLA can be found at the end of this article. The following is an example of how we can solve our sample problem with the dynamic version of the DLA.
SQL> SELECT * 2 FROM TABLE( 3 dla_pkg.query_view( 4 'SELECT * FROM nullability_view') 5 ) 6 WHERE UPPER(search_condition) LIKE '%IS NOT NULL%';
TABLE_NAME COLUMN_NAME CONSTRAINT_NAME SEARCH_CONDITION -------------------- --------------- -------------------- ------------------------ NULLABILITY_TEST C1 SYS_C0011323 "C1" IS NOT NULL NULLABILITY_TEST C2 SYS_C0011324 "C2" IS NOT NULL NULLABILITY_TEST C3 COLUMN_LEVEL_CHECK c3 IS NOT NULL NULLABILITY_TEST C4 TABLE_LEVEL_CHECK c4 IS NOT NULL 4 rows selected.
The dynamic DLA has a single pipelined function named QUERY_VIEW (remember this utility was intended as a LONG-to-CLOB converter for Oracle's dictionary views). As we can see above, we pass in a dynamic query string to the function and it returns a resultset of the correct columns and format. Using this, we have easily solved our problem of finding mandatory columns.
As with previous examples, we will create and describe a table of the DLA's resultset below.
SQL> CREATE TABLE nullability_dla_snapshot 2 AS 3 SELECT * 4 FROM TABLE( 5 dla_pkg.query_view( 6 'SELECT * FROM nullability_view') 7 );
Table created.
SQL> DESC nullability_dla_snapshot
Name Null? Type ---------------------------------- -------- ------------------- TABLE_NAME VARCHAR2(30) COLUMN_NAME VARCHAR2(30) CONSTRAINT_NAME VARCHAR2(30) SEARCH_CONDITION CLOB
For convenience, the DLA installation includes a number of views over some of the dictionary views that have LONG columns. These convenience views have the call to the QUERY_VIEW pipelined function encoded within them. A V_DBA_CONSTRAINTS view over DBA_CONSTRAINTS is included and we can use this as an alternative to our first example above, as follows.
SQL> SELECT table_name 2 , constraint_name 3 , search_condition 4 FROM v_dba_constraints 5 WHERE owner = USER 6 AND table_name = 'NULLABILITY_TEST' 7 AND UPPER(search_condition) LIKE '%IS NOT NULL%';
TABLE_NAME CONSTRAINT_NAME SEARCH_CONDITION -------------------- -------------------- ------------------------------ NULLABILITY_TEST SYS_C0011323 "C1" IS NOT NULL NULLABILITY_TEST SYS_C0011324 "C2" IS NOT NULL NULLABILITY_TEST COLUMN_LEVEL_CHECK c3 IS NOT NULL NULLABILITY_TEST TABLE_LEVEL_CHECK c4 IS NOT NULL 4 rows selected.
When using the convenience views, we can preset a number of filters to make our DLA searches much more efficient. For example, we are interested in mandatory columns in the NULLABILITY_TEST table, so we can preset filters for OWNER and TABLE_NAME and re-query V_DBA_CONSTRAINTS, as follows.
SQL> BEGIN
2 dla_pkg.set_filter('OWNER', USER);
3 dla_pkg.set_filter('TABLE_NAME', 'NULLABILITY_TEST');
4 END;
5 /
PL/SQL procedure successfully completed.
SQL> SELECT table_name 2 , constraint_name 3 , search_condition 4 FROM v_dba_constraints 5 WHERE UPPER(search_condition) LIKE '%IS NOT NULL%';
TABLE_NAME CONSTRAINT_NAME SEARCH_CONDITION -------------------- -------------------- ------------------------------ NULLABILITY_TEST SYS_C0011323 "C1" IS NOT NULL NULLABILITY_TEST SYS_C0011324 "C2" IS NOT NULL NULLABILITY_TEST COLUMN_LEVEL_CHECK c3 IS NOT NULL NULLABILITY_TEST TABLE_LEVEL_CHECK c4 IS NOT NULL 4 rows selected.
Note that no performance output is included to demonstrate the efficiency of the filtered method, but this will push the OWNER and TABLE_NAME predicates into the underlying dynamic query of DBA_CONSTRAINTS, meaning that only the rows of interest will be piped by the QUERY_VIEW function. By contrast, the unfiltered example will return all data from DBA_CONSTRAINTS before any predicates are applied and this can be very wasteful.
Finally, DLA filters can be cleared either by name or together in a single call, as follows.
SQL> BEGIN
2 dla_pkg.clear_filter('ALL');
3 END;
4 /
PL/SQL procedure successfully completed.
working with longs over 32kb
Now that we have seen our four methods working, we have a final consideration: that is, LONGs over 32Kb in size. Our sample problem involved very short SEARCH_CONDITION values, so we will now test the methods with "long LONGs". For this, we will contrive a simple technical problem. We will create a view of over 32Kb in size and attempt to find the position of the FROM clause in the view's underlying query.
setup
We will begin by creating a view (named LARGE_VIEW) with a large underlying query, as follows.
SQL> DECLARE
2 v_sql CLOB;
3 BEGIN
4 v_sql := 'CREATE VIEW large_view AS SELECT ';
5 FOR i IN 1 .. 100 LOOP
6 v_sql := v_sql || '''' || RPAD('x',400,'x') || ''' AS c' || TO_CHAR(i) || ',';
7 END LOOP;
8 v_sql := RTRIM(v_sql, ',') || ' FROM dual';
9 EXECUTE IMMEDIATE v_sql;
10 END;
11 /
PL/SQL procedure successfully completed.
We now have the following views in our schema.
SQL> SELECT view_name 2 , text_length 3 FROM user_views 4 WHERE view_name LIKE '%VIEW';
VIEW_NAME TEXT_LENGTH -------------------- ----------- LARGE_VIEW 41008 NULLABILITY_VIEW 576 2 rows selected.
As we can see, the query text for the new LARGE_VIEW view is approximately 40Kb in size. If we try to query the position of the FROM clause in the view's text, we hit the usual LONG restriction, as follows.
SQL> SELECT view_name 2 , INSTR(text, 'FROM ') AS from_position 3 FROM user_views 4 WHERE view_name LIKE '%VIEW';
, INSTR(text, 'FROM ') AS from_position
*
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG
We will therefore test each of our methods with this problem below.
method one: to_lob and longs over 32kb
We will begin with the TO_LOB method, as follows.
SQL> CREATE TABLE user_views_snapshot 2 AS 3 SELECT view_name 4 , TO_LOB(text) AS text 5 FROM user_views;
Table created.
SQL> SELECT view_name 2 , INSTR(text, 'FROM ') AS from_position 3 FROM user_views_snapshot 4 WHERE view_name LIKE '%VIEW';
VIEW_NAME FROM_POSITION -------------------- ------------- LARGE_VIEW 41000 NULLABILITY_VIEW 112 2 rows selected.
We can see that this method works as before without any additional effort.
method two: pl/sql and longs over 32kb
We will now try the PL/SQL method below.
SQL> DECLARE 2 v_from_position PLS_INTEGER; 3 BEGIN 4 FOR r IN (SELECT view_name, text 5 FROM user_views 6 WHERE view_name LIKE '%VIEW') 7 LOOP 8 9 v_from_position := INSTR(r.text, 'FROM '); 10 11 DBMS_OUTPUT.PUT_LINE( 12 'View = ' || r.view_name || 13 '; From position = ' || TO_CHAR(v_from_position) 14 ); 15 16 END LOOP; 17 END; 18 /
DECLARE * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind ORA-06512: at line 4
This time Oracle has raised an exception (highlighted above). As noted earlier, PL/SQL can only deal with VARCHAR2 values up to 32767 bytes. Therefore, Oracle raises the VALUE_ERROR exception when trying to fetch the 41,008-byte text for LONG_VIEW. (Readers can ignore the Bulk Bind: Truncated Bind message in this context. This is raised because Oracle attempted to silently bulk collect 100 rows from our cursor before hitting the VALUE_ERROR exception for the LONG_VIEW row. This behaviour is a result of the PL/SQL optimising compiler.)
To fetch LONG data over the PL/SQL limit, we can use the DBMS_SQL package. This built-in package has APIs for fetching LONG values piecewise. We will use these APIs to create a function that converts a LONG to a CLOB by fetching it in manageable chunks. We will put this function in a LONG2CLOB package, starting with the specification as follows.
SQL> CREATE PACKAGE long2clob AS 2 3 TYPE args_ntt IS TABLE OF VARCHAR2(4000); 4 5 FUNCTION convert( p_source IN VARCHAR2, 6 p_long_col IN VARCHAR2, 7 p_cols IN args_ntt, 8 p_vals IN args_ntt ) RETURN CLOB; 9 END long2clob; 10 /
Package created.
Our package contains a single function named CONVERT and a supporting collection type. The logic of the CONVERT function is contained in the package body below.
SQL> CREATE PACKAGE BODY long2clob AS 2 3 FUNCTION convert( p_source IN VARCHAR2, 4 p_long_col IN VARCHAR2, 5 p_cols IN args_ntt, 6 p_vals IN args_ntt ) RETURN CLOB IS 7 8 v_csr BINARY_INTEGER; 9 v_sql VARCHAR2(32767) := 'select %l% from %v% where 1=1 '; 10 v_pred VARCHAR2(32767) := ' and %c% = :bv%n%'; 11 v_piece VARCHAR2(32767); 12 v_clob CLOB; 13 v_plen INTEGER := 32767; 14 v_tlen INTEGER := 0; 15 v_rows INTEGER; 16 17 BEGIN 18 19 /* Build the SQL statement used to fetch the single long... */ 20 v_sql := REPLACE(REPLACE(v_sql, '%l%', p_long_col), '%v%', p_source); 21 FOR i IN 1 .. p_cols.COUNT LOOP 22 v_sql := v_sql || REPLACE(REPLACE(v_pred, '%c%', p_cols(i)), '%n%', TO_CHAR(i)); 23 END LOOP; 24 25 /* Parse the cursor and bind the inputs... */ 26 v_csr := DBMS_SQL.OPEN_CURSOR; 27 DBMS_SQL.PARSE(v_csr, v_sql, DBMS_SQL.NATIVE); 28 FOR i IN 1 .. p_vals.COUNT LOOP 29 DBMS_SQL.BIND_VARIABLE(v_csr, ':bv'||i, p_vals(i)); 30 END LOOP; 31 32 /* Fetch the long column piecewise... */ 33 DBMS_SQL.DEFINE_COLUMN_LONG(v_csr, 1); 34 v_rows := DBMS_SQL.EXECUTE_AND_FETCH(v_csr); 35 LOOP 36 DBMS_SQL.COLUMN_VALUE_LONG(v_csr, 1, 32767, v_tlen, v_piece, v_plen); 37 v_clob := v_clob || v_piece; 38 v_tlen := v_tlen + 32767; 39 EXIT WHEN v_plen < 32767; 40 END LOOP; 41 42 /* Finish... */ 43 DBMS_SQL.CLOSE_CURSOR(v_csr); 44 RETURN v_clob; 45 46 END convert; 47 48 END long2clob; 49 /
Package body created.
The CONVERT function creates a dynamic SQL statement from the input parameters and uses DBMS_SQL to fetch the LONG column in 32767-byte chunks. The parameters are as follows:
- p_source: the source view or table containing the LONG column;
- p_long_col: the name of the LONG column in the source;
- p_cols: a collection of columns in the source used as predicates to identify the source row of interest;
- p_vals: a corresponding collection of values for the p_cols predicate columns.
Some further points to note about this function are as follows:
- Lines 20-23: the dynamic SQL is built from the function parameters and a simple template. Only the LONG column is selected. Predicates are treated as bind variables that are named according to their offsets in the columns collection;
- Lines 28-30: once the dynamic SQL is parsed, the values in the p_vals collection are bound to the SQL, using their offsets to derive their corresponding bind variable name;
- Lines 33-40: the LONG column is defined and fetched in chunks of 32767 bytes. Each chunk is appended to the return CLOB;
- Line 44: a CLOB of the original LONG text is returned from the function.
We will now repeat our PL/SQL test for fetching larger LONGs as follows. Some commentary follows the example.
SQL> DECLARE
2
3 CURSOR c_views
4 IS
5 SELECT view_name, text
6 FROM user_views
7 WHERE view_name LIKE '%VIEW';
8
9 r_views c_views%ROWTYPE;
10 v_clob_text CLOB;
11 v_from_position PLS_INTEGER;
12
13 BEGIN
14 OPEN c_views;
15 LOOP
16
17 /* Fetch a row. If it fails, drop into a piecewise fetch... */
18 BEGIN
19 FETCH c_views INTO r_views;
20 EXIT WHEN c_views%NOTFOUND;
21 v_clob_text := r_views.text;
22 EXCEPTION
23 WHEN VALUE_ERROR THEN
24 v_clob_text := long2clob.convert(
25 p_source => 'USER_VIEWS',
26 p_long_col => 'TEXT',
27 p_cols => long2clob.args_ntt('VIEW_NAME'),
28 p_vals => long2clob.args_ntt(r_views.view_name)
29 );
30 END;
31
32 /* Resume normal processing... */
33 v_from_position := INSTR(v_clob_text, 'FROM ');
34
35 DBMS_OUTPUT.PUT_LINE(
36 'View=[' || r_views.view_name ||
37 ']; From position=[' || TO_CHAR(v_from_position) || ']'
38 );
39
40 END LOOP;
41 CLOSE c_views;
42 END;
43 /
View=[LARGE_VIEW]; From position=[41000] View=[NULLABILITY_VIEW]; From position=[112] PL/SQL procedure successfully completed.
We can see that, with the inclusion of a LONG2CLOB function, our example succeeds in finding the FROM clause in the LARGE_VIEW text. Some notes about this are as follows.
- Lines 3-9: we have used an explicit cursor and record as we need more control over how we fetch the data;
- Lines 18-30: we fetch each record within an anonymous block. This enables us to capture any VALUE_ERROR exceptions that happen at a row level. This would not be possible with a cursor-for-loop;
- Lines 23-29: when we hit the VALUE_ERROR exception for a row, we invoke the LONG2CLOB function with the relevant inputs from the current source row. We are able to supply these inputs because all attributes of the current record (except TEXT) are conveniently still populated for us. LONG2CLOB fetches the TEXT value into a CLOB variable;
- Line 33: now that we have the long text in a CLOB, we are able to solve our problem.
Note that there are already a couple of LONG2CLOB APIs in Oracle, as the following query demonstrates.
SQL> SELECT owner 2 , object_name 3 , procedure_name 4 FROM dba_procedures 5 WHERE procedure_name LIKE '%LONG2CLOB%';
OWNER OBJECT_NAME PROCEDURE_NAME ------- --------------------- -------------- SYS DBMS_METADATA_UTIL LONG2CLOB SYS UTL_XML LONG2CLOB 2 rows selected.
Unfortunately, these packages are both undocumented so are not supported for general use. Furthermore, they both take a ROWID to locate the row with the LONG column to be converted, which means that they can only be used with tables or views that expose ROWIDs.
method three: dbms_xmlgen and longs over 32kb
As we noted in the earlier section on DBMS_XMLGEN, the extractValue function returns all strings as VARCHAR2(4000) so it is unlikely that we will be able to convert LONGs over this size with this method. However, we also saw how the newer XMLTABLE method is much richer in functionality and allows us to strongly-type the columns we parse from the XML (and therefore we might be more successful with longer LONGs). We will test both methods below, starting with the older XMLSEQUENCE functionality.
SQL> WITH xml AS ( 2 SELECT DBMS_XMLGEN.GETXMLTYPE( 3 'SELECT view_name, text FROM user_views' 4 ) AS xml 5 FROM dual 6 ) 7 , parsed_xml AS ( 8 SELECT extractValue(xs.object_value, '/ROW/VIEW_NAME') AS view_name 9 , extractValue(xs.object_value, '/ROW/TEXT') AS text 10 FROM xml x 11 , TABLE(XMLSEQUENCE(EXTRACT(x.xml, '/ROWSET/ROW'))) xs 12 ) 13 SELECT view_name 14 , INSTR(text, 'FROM ') AS from_position 15 , LENGTH(text) AS text_length 16 FROM parsed_xml 17 WHERE view_name LIKE '%VIEW';
VIEW_NAME FROM_POSITION TEXT_LENGTH -------------------- ------------- ----------- LARGE_VIEW 0 4000 NULLABILITY_VIEW 112 576 2 rows selected.
As we can see, the query has returned 0 for the position of the FROM clause and 4000 for the length of the TEXT column. Oracle hasn't raised an exception when trying to convert the view text for LARGE_VIEW, but it hasn't solved our problem either, as the larger column is clearly truncated to 4000 characters.
We will repeat the test but using the newer XMLTABLE/XQuery method, as follows.
SQL> SELECT view_name
2 , INSTR(text, 'FROM ') AS from_position
3 , LENGTH(text) AS text_length
4 FROM XMLTABLE('/ROWSET/ROW'
5 PASSING (SELECT DBMS_XMLGEN.GETXMLTYPE(
6 'SELECT view_name, text FROM user_views'
7 ) FROM dual)
8 COLUMNS view_name VARCHAR2(30) PATH 'VIEW_NAME',
9 text CLOB PATH 'TEXT')
10 WHERE view_name LIKE '%VIEW';
VIEW_NAME FROM_POSITION TEXT_LENGTH -------------------- ------------- ----------- LARGE_VIEW 0 4000 NULLABILITY_VIEW 112 576 2 rows selected.
There is no difference with this method, despite the fact that we defined the datatype of TEXT as a CLOB in our XQuery. This suggests of course that the issue lies with the DBMS_XMLGEN.GETXMLTYPE function, which we'll test by viewing its outputs (to see if this function has truncated the column when generating the XML).
SQL> SELECT DBMS_XMLGEN.GETXMLTYPE( 2 q'[SELECT view_name, text 3 FROM user_views 4 WHERE view_name LIKE '%VIEW']' 5 ) AS xml 6 FROM dual;
XML
--------------------------------------------------------------------------------
<ROWSET>
<ROW>
<VIEW_NAME>NULLABILITY_VIEW</VIEW_NAME>
<TEXT>SELECT utc.table_name
, utc.column_name
, ucc.constraint_name
, uc.search_condition
FROM user_tab_columns utc
LEFT OUTER JOIN
user_cons_columns ucc
ON ( utc.table_name = ucc.table_name
AND utc.column_name = ucc.column_name)
LEFT OUTER JOIN
user_constraints uc
ON (ucc.constraint_name = uc.constraint_name)
WHERE utc.table_name = 'NULLABILITY_TEST'
AND ( uc.constraint_type = 'C'
OR uc.constraint_type IS NULL)
ORDER BY
utc.column_id</TEXT>
</ROW>
<ROW>
<VIEW_NAME>LARGE_VIEW</VIEW_NAME>
<TEXT>SELECT 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxx' AS c1,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' AS c2,'xxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' AS c3,'
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
' AS c4,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxx' AS c5,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' AS c6,'xxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' AS c7,'xxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx&apo
s; AS c8,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxx' AS c9,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxx</TEXT>
</ROW>
</ROWSET>
1 row selected.
We can clearly see that the TEXT tag for LARGE_VIEW has been truncated, meaning that converting LONGs over 4000 bytes is not possible with the DBMS_XMLGEN technique.
method four: dictionary long application and longs over 32kb
The Dictionary Long Application (DLA) described earlier includes a LONG2CLOB function to explicitly cater for LONG values over 32Kb. We will test it with the LARGE_VIEW text, as follows.
SQL> SELECT view_name 2 , INSTR(text, 'FROM ') AS from_position 3 FROM TABLE( 4 dla_pkg.query_view( 5 'SELECT * FROM user_views') 6 ) 7 WHERE view_name LIKE '%VIEW';
VIEW_NAME FROM_POSITION -------------------- ------------- LARGE_VIEW 41000 NULLABILITY_VIEW 112 2 rows selected.
As demonstrated earlier, DLA already has a view named V_DBA_VIEWS that we can use instead of the query above. In the following example, we will repeat the test but set a filter to restrict the DLA's output to our own schema's views, as follows.
SQL> BEGIN
2 dla_pkg.set_filter('OWNER', USER);
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> SELECT view_name 2 , INSTR(text, 'FROM ') AS from_position 3 FROM v_dba_views 4 WHERE view_name LIKE '%VIEW';
VIEW_NAME FROM_POSITION -------------------- ------------- LARGE_VIEW 41000 NULLABILITY_VIEW 112 2 rows selected.
Because V_DBA_VIEWS queries DBA_VIEWS, the inclusion of the filter reduces its workload to be comparable to our previous "long-hand" example that uses the QUERY_VIEW function with USER_VIEWS. The answer is of course the same whichever method we choose to use.
summary
In this article, we've seen four techniques for working around the restrictions of LONG columns, by converting LONGs to either VARCHAR2s or CLOBs. When manipulating LONG values under 4000 bytes, any of the methods will suffice but will be dependant on various factors such as the following:
- whether the database is read-only (can use the anonymous PL/SQL or DBMS_XMLGEN methods);
- whether any code or objects can be created (can use the TO_LOB, PL/SQL pipelined function or Dictionary Long Application methods);
- personal preference or perceived ease-of-use (developer's choice);
- performance (will depend on the specific requirements).
The DBMS_XMLGEN method will not work with LONG values over 32Kb but at least one of the alternatives will suffice in all cases.
acknowledgements
Thanks to Timo Raitalaakso for recommending the DBMS_XMLGEN.GETXMLTYPE function over the XMLTYPE(DBMS_XMLGEN.GETXML) method I used in the original version of this article.
further reading
For more information on the TO_LOB function, see the SQL Reference. More information on DBMS_XMLGEN and the DBMS_SQL LONG APIs can be found in the PL/SQL Packages and Types Reference. Information on XMLTABLE can be found in the SQL Reference. The Dictionary Long Application can be downloaded from the Utilities page of this site and a detailed description of how it works can be found in this oracle-developer.net article.
source code
The source code for the examples in this article can be downloaded from here.
Adrian Billington, July 2010 (updated March 2012)
Back to Top