Convert LONG to CLOB
A friend asked me how to get an Oracle view definition out of a LONG
column and into a web application. I thought it was an interesting question because I ran into a similar problem when writing the Oracle Database 12c PL/SQL Programming book.
One of the new Oracle 12c features is the DBMS_UTILITY
‘s new EXPAND_SQL_TEXT
procedure. It lets you expand a view’s definition to include any views that the master view uses. It produces a single queries with all the base tables that support the view. Clearly, it’s an effective tool when it comes to understanding how those large ERP views work in the E-Business Suite.
LONG
to CLOB
Data Type
Here’s a version of the function that converts the LONG
data type into a CLOB
data type:
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 | CREATE OR REPLACE FUNCTION long_to_clob ( pv_view_name VARCHAR2 , pv_column_length INTEGER ) RETURN CLOB AS /* Declare local variables. */ lv_cursor INTEGER := dbms_sql.open_cursor; lv_feedback INTEGER; -- Acknowledgement of dynamic execution lv_length INTEGER; -- Length of string lv_return CLOB; -- Function output lv_stmt VARCHAR2(2000); -- Dynamic SQL statement lv_string VARCHAR2(32760); -- Maximum length of LONG data type BEGIN /* Create dynamic statement. */ lv_stmt := 'SELECT text'||CHR(10) || 'FROM user_views'||CHR(10) || 'WHERE view_name = '''||pv_view_name||''''; /* Parse and define a long column. */ dbms_sql.parse(lv_cursor, lv_stmt, dbms_sql.native); dbms_sql.define_column_long(lv_cursor,1); /* Only attempt to process the return value when fetched. */ IF dbms_sql.execute_and_fetch(lv_cursor) = 1 THEN dbms_sql.column_value_long( lv_cursor , 1 , pv_column_length , 0 , lv_string , lv_length); END IF; /* Check for an open cursor. */ IF dbms_sql.is_open(lv_cursor) THEN dbms_sql.close_cursor(lv_cursor); END IF; /* Create a local temporary CLOB in memory: - It returns a constructed lv_return_result. - It disables a cached version. - It set the duration to 12 (the value of the dbms_lob.call package-level variable) when the default is 10. */ dbms_lob.createtemporary(lv_return, FALSE, dbms_lob.call); /* Append the Long to the empty temporary CLOB. */ dbms_lob.write(lv_return, pv_column_length, 1, lv_string); RETURN lv_return; END long_to_clob; / |
This wraps the conversion of a LONG
to CLOB
, which is necessary to pre-size the LONG
data type. Pre-sizing avoids reading the LONG
column’s value character-by-character.
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 | CREATE OR REPLACE FUNCTION expand_view ( pv_view_name VARCHAR2 ) RETURN CLOB IS /* Declare containers for views. */ lv_input_view CLOB; lv_output_view CLOB; /* Declare a target variable, because of the limit of SELECT-INTO. */ lv_long_view LONG; /* Declare a dynamic cursor. */ CURSOR c (cv_view_name VARCHAR2) IS SELECT text FROM user_views WHERE view_name = cv_view_name; BEGIN /* Open, fetch, and close cursor to capture view text. */ OPEN c(pv_view_name); FETCH c INTO lv_long_view; CLOSE c; /* Convert a LONG return type to a CLOB. */ lv_input_view := long_to_clob(pv_view_name, LENGTH(lv_long_view)); /* Send in the view text and receive the complete text. */ dbms_utility.expand_sql_text(lv_input_view, lv_output_view); /* Return the output CLOB value. */ RETURN lv_output_view; END; / |
LONG
to VARCHAR2
Data Type
Here’s a version of the function that converts the LONG
data type into a VARCHAR2
data type:
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 | CREATE OR REPLACE FUNCTION long_to_varchar2 ( pv_view_name VARCHAR2 , pv_column_length INTEGER ) RETURN VARCHAR2 AS /* Declare local variables. */ lv_cursor INTEGER := dbms_sql.open_cursor; lv_feedback INTEGER; -- Acknowledgement of dynamic execution lv_length INTEGER; -- Length of string lv_return VARCHAR2(32767); -- Function output lv_stmt VARCHAR2(2000); -- Dynamic SQL statement lv_string VARCHAR2(32760); -- Maximum length of LONG data type BEGIN /* Create dynamic statement. */ lv_stmt := 'SELECT text'||CHR(10) || 'FROM user_views'||CHR(10) || 'WHERE view_name = '''||pv_view_name||''''; /* Parse and define a long column. */ dbms_sql.parse(lv_cursor, lv_stmt, dbms_sql.native); dbms_sql.define_column_long(lv_cursor,1); /* Only attempt to process the return value when fetched. */ IF dbms_sql.execute_and_fetch(lv_cursor) = 1 THEN dbms_sql.column_value_long( lv_cursor , 1 , pv_column_length , 0 , lv_string , lv_length); END IF; /* Check for an open cursor. */ IF dbms_sql.is_open(lv_cursor) THEN dbms_sql.close_cursor(lv_cursor); END IF; /* Convert the long length string to a maximum size length. */ lv_return := lv_string; RETURN lv_return; END long_to_varchar2; / |
This wraps the conversion of a LONG
to VARCHAR2
, which is necessary to pre-size the LONG
data type. Pre-sizing avoids reading the LONG
column’s value character-by-character.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | CREATE OR REPLACE FUNCTION return_view_text ( pv_view_name VARCHAR2 ) RETURN VARCHAR2 IS /* Declare a target variable, because of the limit of SELECT-INTO. */ lv_long_view LONG; /* Declare a dynamic cursor. */ CURSOR c (cv_view_name VARCHAR2) IS SELECT text FROM user_views WHERE view_name = cv_view_name; BEGIN /* Open, fetch, and close cursor to capture view text. */ OPEN c(pv_view_name); FETCH c INTO lv_long_view; CLOSE c; /* Return the output CLOB value. */ RETURN long_to_varchar2(pv_view_name, LENGTH(lv_long_view)); END; / |
Wrapper to DBMS_UTILITY
‘s EXPAND_SQL_TEXT
Procedure
As a response to somebody who simply wants a wrapper to the new dbms_utility
‘s expand_sql_text
procedure, I wrote the wrapper. Although, my reflection on this is why does a new procedure require a new wrapper to be useful? Did the use case get stated incorrectly. Anyway, here’s the wrapper:
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 | -- Converts a long column to a CLOB data type. CREATE OR REPLACE FUNCTION expand_sql_text ( pv_view_name VARCHAR2 ) RETURN CLOB AS /* Declare containers for views. */ lv_input_view CLOB; lv_output_view CLOB; /* Declare a target variable, because of the limit of SELECT-INTO. */ lv_long_view LONG; /* Declare local variables for dynamic SQL. */ lv_cursor INTEGER := dbms_sql.open_cursor; lv_feedback INTEGER; -- Acknowledgement of dynamic execution lv_length INTEGER; -- Length of string lv_return CLOB; -- Function output lv_stmt VARCHAR2(2000); -- Dynamic SQL statement lv_string VARCHAR2(32760); -- Maximum length of LONG data type /* Declare user-defined exception. */ invalid_view_name EXCEPTION; PRAGMA EXCEPTION_INIT(invalid_view_name, -20001); /* Declare a dynamic cursor. */ CURSOR c (cv_view_name VARCHAR2) IS SELECT text FROM user_views WHERE view_name = cv_view_name; FUNCTION verify_view_name ( pv_view_name VARCHAR2 ) RETURN BOOLEAN AS /* Default return value. */ lv_return_result BOOLEAN := FALSE; /* Declare cursor to check view name. */ CURSOR c (cv_view_name VARCHAR2) IS SELECT NULL FROM user_views WHERE view_name = cv_view_name; BEGIN FOR i IN c (pv_view_name) LOOP lv_return_result := TRUE; END LOOP; RETURN lv_return_result; END verify_view_name; BEGIN /* Throw exception when invalid view name. */ IF NOT verify_view_name(pv_view_name) THEN RAISE invalid_view_name; END IF; /* Open, fetch, and close cursor to capture view text. */ OPEN c(pv_view_name); FETCH c INTO lv_long_view; CLOSE c; /* Create dynamic statement. */ lv_stmt := 'SELECT text'||CHR(10) || 'FROM user_views'||CHR(10) || 'WHERE view_name = '''||pv_view_name||''''; /* Parse and define a long column. */ dbms_sql.parse(lv_cursor, lv_stmt, dbms_sql.native); dbms_sql.define_column_long(lv_cursor,1); /* Only attempt to process the return value when fetched. */ IF dbms_sql.execute_and_fetch(lv_cursor) = 1 THEN dbms_sql.column_value_long( lv_cursor , 1 , LENGTH(lv_long_view) , 0 , lv_string , lv_length); END IF; /* Check for an open cursor. */ IF dbms_sql.is_open(lv_cursor) THEN dbms_sql.close_cursor(lv_cursor); END IF; /* Create a local temporary CLOB in memory: - It returns a constructed lv_return_result. - It disables a cached version. - It set the duration to 12 (the value of the dbms_lob.call package-level variable) when the default is 10. */ dbms_lob.createtemporary(lv_input_view, FALSE, dbms_lob.call); /* Append the Long to the empty temporary CLOB. */ dbms_lob.write(lv_input_view, LENGTH(lv_long_view), 1, lv_string); /* Send in the view text and receive the complete text. */ dbms_utility.expand_sql_text(lv_input_view, lv_output_view); /* Return the output CLOB value. */ RETURN lv_output_view; EXCEPTION WHEN invalid_view_name THEN RAISE_APPLICATION_ERROR(-20001,'Invalid View Name.'); WHEN OTHERS THEN RETURN NULL; END expand_sql_text; / |
As always, I hope this provides folks with a leg up on tricky syntax.