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.