---

Oracle / PLSQL: Retrieve the value of a LONG field

Question: I have a table in Oracle that contains a field with the data type of LONG. How can I extract the contents of this LONG field?

If I run the SQL statement below, it just returns <Long>.

SELECT event_details FROM vlts_event_data;

Answer: In Oracle, LONG fields are a bit tricky. However, you can use PLSQL code to determine the value of a LONG field.

Here is an example of a function that returns the value of a LONG field called SEARCH_CONDITION. This function accepts the primary key values (owner and constraint_name fields) and returns the value of the SEARCH_CONDITION field for the selected record.

CREATE or REPLACE function Find_Value
   ( av_owner varchar2, av_cname varchar2)
   RETURN varchar2

IS
   long_var LONG;

BEGIN
   SELECT SEARCH_CONDITION INTO long_var
   FROM ALL_CONSTRAINTS
   WHERE owner = av_owner
   AND constraint_name = av_cname;

   return long_var;

END;

Once the above function has been created, you can reference this function in your SQL statement. For example,

SELECT owner, constraint_name, Find_Value(owner, constraint_name)
FROM ALL_CONSTRAINTS;

This SQL statement would retrieve the owner and constraint_name fields from the ALL_CONSTRAINTS table, as well as the value stored within the SEARCH_CONDITION field.

You can modify the Find_Value function to reference your own table and field names. You will also need to modify the function parameters to pass in your own primary key values.