ODP.NET, Managed reading LONG column results in ORA-01009

Question

I'm trying to get the source of a view in my .net app. To do this, I query DBA_VIEWS: it has a column TEXT with exactly what I need. The type is LONG.

If I do it using the Unmanaged driver, everything works as expected. The same code with Managed driver results in ORA-01009: missing mandatory parameter. Adding a transaction around the command and using breakpoint and "slow" steps sometimes results in the same code working.

ODP.NET version is 19, Oracle DB is 18c Express Edition. Strangely enough, the same code works just fine with Oracle Database 12c regardless of driver type.

Is there maybe some setting I need to change on the database or in code? I'm completely lost here.

Code I'm using for testing:

Imports System.Data.Common

Imports Oracle.ManagedDataAccess
'Imports Oracle.DataAccess

Module Views
    Function CreateCommand(Connection As DbConnection) As System.Data.Common.DbCommand
        Dim cmd As Data.Common.DbCommand = Connection.CreateCommand()
        With CType(cmd, Client.OracleCommand)
            .BindByName = True
            .FetchSize = &H100000 '1 Mb
            .InitialLONGFetchSize = -1 'the entire LONG or LONG RAW data is prefetched and stored in the fetch array.
            .InitialLOBFetchSize = -1 'the entire LOB data is prefetched and stored in the fetch array. 
        End With
        Return cmd
    End Function

    Sub query()
        Try
            Using DBConnection = New Client.OracleConnection("User ID=TESTUSER;Password=TESTPWD;Data Source=TESTDB;Pooling=True")
                DBConnection.Open()

                Using DBConnection.BeginTransaction()

                    Using cmdSQL = CType(CreateCommand(DBConnection), Client.OracleCommand)
                        cmdSQL.CommandText = "select TEXT from DBA_VIEWS where VIEW_NAME = :0"

                        Dim p = cmdSQL.CreateParameter()
                        p.ParameterName = "0"
                        p.Value = "TEST_VIEW"
                        cmdSQL.Parameters.Add(p)

                        Dim sw = Stopwatch.StartNew

                        Using rdr = cmdSQL.ExecuteReader
                            rdr.FetchSize = 2 ^ 20
                            While rdr.Read
                                Dim row(rdr.FieldCount - 1) As Object
                                rdr.GetProviderSpecificValues(row)
                                Dim x = row(0)
                                Console.WriteLine($"{x.ToString.Length} bytes")
                            End While
                        End Using
                        Console.WriteLine($"{sw.ElapsedMilliseconds} ms")
                    End Using
                End Using
            End Using
        Catch ex As Exception
            Console.WriteLine(ex.ToString)
        End Try
    End Sub
End Module

Answer 1

It's a pity, Oracle deprecated LONG data type for ages but LONG data is still used many times for internal data.

You could write a function and then get the data by calling the function:

create or replace function GetViewText(v in varchar2) return clob is     
 ret CLOB;      
BEGIN

    FOR aRow IN (SELECT TEXT FROM DBA_VIEWS WHERE VIEW_NAME = v) LOOP
        ret := aRow.TEXT;
        -- or ret := TO_CLOB(aRow.TEXT);
    END LOOP;  
    RETURN ret;    
END;

Answer 2

You can use an anonymous block with output parameter and a call to ExecuteNonQuery. Your command text will be

"begin select TEXT into :0 from DBA_VIEWS where VIEW_NAME = :1; end;"

Add 2 parameters. Make sure that

' Parameter #1 has
p.Direction = ParameterDirection.Output
p.OracleDbType = OracleDbType.Long
p.Size = 1000000

And use command cmd.ExecuteNonQuery(). Then, when parameter is retrieved, just use its value

Dim txt As String = cmd.Parametersp[0].Value.ToString()

Answer 3

Yet another way from this answer is to (ab)use dbms_xmlgen.getxml. We can either use it to query a single view's code (as in my original question)

with input as (
 select
 :0 as VIEW_NAME
 from dual
)
SELECT
substr(
    text_xml,
    instr(text_xml, '<LONGCOL>') + length('<LONGCOL>'),
    instr(text_xml, '</LONGCOL>', -1) - (instr(text_xml, '<LONGCOL>') + length('<LONGCOL>'))
) as TEXT
from
(
    -- getxml can return malformed xml (which is good in this case)
    -- while getxmltype can not.
    select dbms_xmlgen.getxml(q'{
    SELECT TEXT as LONGCOL
    FROM SYS.DBA_VIEWS
    WHERE VIEW_NAME = '}' || input.VIEW_NAME || q'{'
}') as text_xml
from input
)

or create our own DBA_VIEWS version.

create or replace view APP_SCHEMA.DBA_VIEWS
as
select
    OWNER, VIEW_NAME, TEXT_LENGTH, 
    case
        when (TEXT_VC is not null and TEXT_LENGTH <= 4000)
            then to_clob(TEXT_VC)
        when TEXT is NULL
            then NULL
        else (
             SELECT
               substr(
                    text_xml,
                    instr(text_xml, '<LONGCOL>') + length('<LONGCOL>'),
                    --instr(text_xml, '</LONGCOL>', -1) - (instr(text_xml, '<LONGCOL>') + length('<LONGCOL>'))
                    TEXT_LENGTH
               ) as TEXT
              from
               (
                -- getxml can return malformed xml (which is good in this case)
                -- while getxmltype can not.
                select dbms_xmlgen.getxml(q'{
                SELECT TEXT as LONGCOL
                FROM SYS.DBA_VIEWS
                WHERE OWNER = '}' || OWNER || q'{'
                and VIEW_NAME = '}' || VIEW_NAME || q'{'
                }') as text_xml
                from dual
               )
        )
    end as TEXT,
    TEXT_VC, TYPE_TEXT_LENGTH, TYPE_TEXT, OID_TEXT_LENGTH, OID_TEXT, VIEW_TYPE_OWNER, VIEW_TYPE, SUPERVIEW_NAME, EDITIONING_VIEW, READ_ONLY, CONTAINER_DATA, BEQUEATH, ORIGIN_CON_ID, DEFAULT_COLLATION, CONTAINERS_DEFAULT, CONTAINER_MAP, EXTENDED_DATA_LINK, EXTENDED_DATA_LINK_MAP, HAS_SENSITIVE_COLUMN
from sys.dba_views
;