TED_P1026

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;

​

TED_P1026

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;

​