Another LONG conversion

A

Another way how to query LONG columns with SQL is to create function (or packaged function) for each column you need to convert.

    FUNCTION get_user_views_text (
        in_view_name        user_views.view_name%TYPE
    )
    RETURN VARCHAR2 AS
        out_text            user_views.text%TYPE;
    BEGIN
        SELECT v.text INTO out_text
        FROM user_views v
        WHERE v.view_name = in_view_name;
        --
        RETURN out_text;
    END;

Use function in SQL:

SELECT v.view_name, v.text_length, get_user_views_text(v.view_name) AS text
FROM user_views v
ORDER BY 1;

Or you can create dynamic function:

    FUNCTION get_long_string (
        in_table_name       VARCHAR2,
        in_column_name      VARCHAR2,
        in_where_col1_name  VARCHAR2,
        in_where_val1       VARCHAR2,
        in_where_col2_name  VARCHAR2    := NULL,
        in_where_val2       VARCHAR2    := NULL
    )
    RETURN VARCHAR2 AS
        l_query         VARCHAR2(4000);
        l_cursor        INTEGER         := DBMS_SQL.OPEN_CURSOR;
        l_buflen        PLS_INTEGER     := 4000;
        l_result        PLS_INTEGER;
        --
        out_value       VARCHAR2(4000);
        out_value_len   PLS_INTEGER;
    BEGIN
        l_query :=
            'SELECT ' || in_column_name ||
            ' FROM '  || in_table_name ||
            ' WHERE ' || in_where_col1_name || ' = :val1';
        --
        IF in_where_col2_name IS NOT NULL THEN
            l_query := l_query || ' AND ' || in_where_col2_name || ' = :val2';
        END IF;
        --
        DBMS_SQL.PARSE(l_cursor, l_query, DBMS_SQL.NATIVE);
        DBMS_SQL.BIND_VARIABLE(l_cursor, ':val1', in_where_val1);
        --
        IF in_where_col2_name IS NOT NULL THEN
            DBMS_SQL.BIND_VARIABLE(l_cursor, ':val2', in_where_val2);
        END IF;
        --
        DBMS_SQL.DEFINE_COLUMN_LONG(l_cursor, 1);
        --
        l_result := DBMS_SQL.EXECUTE(l_cursor);
        IF DBMS_SQL.FETCH_ROWS(l_cursor) > 0 THEN
            DBMS_SQL.COLUMN_VALUE_LONG(l_cursor, 1, l_buflen, 0, out_value, out_value_len);
        END IF;
        DBMS_SQL.CLOSE_CURSOR(l_cursor);
        --
        RETURN out_value;
    END;

And use this function in SQL:

SELECT v.view_name, v.text_length, get_long_string('USER_VIEWS', 'TEXT', 'VIEW_NAME', v.view_name) AS text
FROM user_views v
ORDER BY 1;

You can query Oracle Dictionary and get a list of all LONG columns:

SELECT t.table_name, t.column_name
FROM all_tab_columns t
WHERE t.owner           = 'SYS'
    AND t.table_name    LIKE 'USER%'
    AND t.data_type     IN ('LONG', 'LONG RAW')
ORDER BY 1, 2;