(PL)SQL - Long

Function to return a long column value as a clob. Based in part on the code from the link below, but modified to accept a table/column/where-clause instead of a query/bind-variable.

More background info & original code: https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:665224430110.

Package

Package
	Name	TED_P1026
	Source	package ted_p1026 is function column_long_to_clob ( p_table_or_view_name in varchar2 , p_column_name in varchar2 , p_where_clause in varchar2 -- e.g. 'id = 10', make sure the where clause results in only 1 (or 0) result(s) ) return clob ; end ted_p1026;

Package Body

Package Body
	Name	TED_P1026
	Source	package body ted_p1026 is function column_long_to_clob ( p_table_or_view_name in varchar2 , p_column_name in varchar2 , p_where_clause in varchar2 -- e.g. 'id = 10', make sure the where clause results in only 1 (or 0) result(s) ) return clob is -- Function returns part (max 32760 bytes) of a long value in a table -- The 32760 bytes limitation is due to dbms_sql.column_value_long v_stmt varchar2(2000); -- Dynamic statement v_cursor integer := dbms_sql.open_cursor; v_value_length pls_integer; -- Number of bytes actually returned in value (v_value) v_fetch_offset pls_integer := 0; -- Offset into the long field for start of fetch v_fetch_bytes pls_integer := 32000; -- Max 32760 - limitation is due to dbms_sql.column_value_long v_fetch_chunk varchar2(32000); -- Same length as v_fetch_bytes v_result clob; begin dbms_lob.createtemporary(lob_loc => v_result, cache => true); -- create dynamic statement v_stmt := 'select ' \|\| p_column_name \|\| chr(10) \|\| 'from ' \|\| p_table_or_view_name \|\| chr(10) \|\| 'where ' \|\| nvl(p_where_clause, '1=1') ; -- parse dynamic statement dbms_sql.parse ( c => v_cursor -- ID number of the cursor in which to parse the statement , statement => v_stmt -- SQL statement to be parsed , language_flag => dbms_sql.native -- Determines how Oracle handles the SQL statement (NATIVE (or 1) specifies normal behavior for the database to which the program is connected) ) ; -- define the long column dbms_sql.define_column_long ( c => v_cursor -- ID number of the cursor for the row being defined to be selected , position => 1 -- Relative position of the column in the row being defined (The first column in a statement has position 1) ) ; -- process and fetch (if there is a result to fetch) if dbms_sql.execute_and_fetch(v_cursor) = 1 then loop -- get a chunk of bytes dbms_sql.column_value_long ( c => v_cursor -- Cursor ID number of the cursor from which to get the value , position => 1 -- Position of the column of which to get the value , length => v_fetch_bytes -- Number of bytes of the long value to fetch , offset => v_fetch_offset -- Offset into the long field for start of fetch , value => v_fetch_chunk -- Value of the column as a VARCHAR2 , value_length => v_value_length -- Number of bytes actually returned in value ) ; -- exit the loop if no bytes were fetched if v_value_length = 0 then exit; end if; -- append the fetched bytes to v_result dbms_lob.append(dest_lob => v_result, src_lob => v_fetch_chunk); -- update fetch offset v_fetch_offset := v_fetch_offset + v_fetch_bytes; end loop; end if; -- close the open cursor if needed if dbms_sql.is_open(v_cursor) then dbms_sql.close_cursor(v_cursor); end if; return v_result; end column_long_to_clob; end ted_p1026;

Column Long to Clob

Code

Package

Package Body