How to insert large text into Oracle database columns?

[stephy]

Hi everyone,

I'm trying to insert into a table which has a column of type LONG that is used to contain large texts, by executing a stored procedure. However i'm only able to execute it for the first few times only. Subsequent times when i hit this error message - "Ora-01460 Unreasonable or Unimplemented conversion requested", i would have to exit my .NET application and run it again.

I have tried some solution recommended by installing ODP.NET for such large data types, but to no avail. Have i missed out anything? Could someone shed some light?

Here's what i have written in my .NET application:

Public Shared Sub InsertQueue(ByVal identity As String, _
ByVal processFlag As Integer, _
Optional ByVal strInputXML As String = Nothing _
)
Dim conn As OleDbConnection
Dim strSp As String = clsConnection.DBOwner & ".USP_INS_KIF_QUEUE"
Dim prmParam(3) As OleDbParameter

prmParam(0) = New OleDbParameter("@returnvalue", OleDbType.Integer)
prmParam(0).Direction = ParameterDirection.Output
prmParam(1) = New OleDbParameter("@inputxml", OleDbType.LongVarChar, 0, ParameterDirection.Input, _
True, Nothing, Nothing, Nothing, DataRowVersion.Current, strInputXML)
prmParam(2) = New OleDbParameter("@identity", identity)
prmParam(3) = New OleDbParameter("@processflag", processFlag)

Try
conn = New OleDbConnection(clsConnection.ConnectionString)

conn.Open()

OleDbHelper.ExecuteNonQuery(clsConnection.ConnectionString, CommandType.StoredProcedure, strSp, prmParam)
clsCommon.ParseException(prmParam(0).Value)
Catch ex As Exception
Throw ex
Finally
conn.Close()
conn.Dispose()
End Try
End Sub

And the stored procedure goes like this:

CREATE OR REPLACE PROCEDURE EKRISMGR.USP_INS_KIF_QUEUE -- Stored Procedure for table KIF_QUEUE
(
v_returnvalue OUT NUMBER,
v_inputxml IN LONG,
v_identity IN varchar2,
v_processflag IN NUMBER
)
AS
recCount INTEGER;
BEGIN
INSERT INTO EKRISMGR.KIF_QUEUE (INPUT_XML, IDENTITY, PROCESSFLAG, STARTDATETIME)
VALUES (v_inputxml, v_identity, v_processflag, SYSDATE);

recCount := SQL%ROWCOUNT;

IF (recCount > 0) THEN
v_returnvalue := 0;
ELSE
v_returnvalue := 1000;
END IF;

RETURN;
END;

Any help would be very much appreciated.

 

[cjard]

Oh yuck. LONG is deprecated in oracle in favour of clob or blob.. Try making your SPROC param into a clob, edit your procedure to do the same..


and be aware that you might have to obtain a stream from that parameter and write to it sequentially.. Here is some code I have from a .net 1.1 project that writes clobs to oracle.. I didnt write it, but it works:

    Public Sub InsertCreditOpinion(ByVal opinion As CreditOpinion)

        Const chunkSize As Integer = 32000
        Dim command As OracleCommand = Nothing
        Dim commandA As OracleCommand
        Dim chunk As String
        Dim index As Integer

        'Using the MS Oracle provider we need to write CLOBs in 32K chunks
        'The initial InsertCreditOpinion() call will insert the record and the first 32K chunk
        'If the CLOB (xmlData) is larger than 32K then we use AppendCreditOpinionData() to add the
        'remaining 32K chunks - the whole sequence is enclosed in a DB Transaction so in this
        'case we need to handle the connection and transaction explicitly

        Try

            Me.OpenConnection()
            Me.BeginTransaction()

            command = New OracleCommand
            command.Connection = Me.Connection
            command.Transaction = Me.Transaction
            command.CommandText = cPackageName & ".InsertCreditOpinion"
            command.CommandType = CommandType.StoredProcedure

            command.Parameters.Add("customerId", OracleType.VarChar).Value = opinion.CustomerId
            command.Parameters.Add("productId", OracleType.VarChar).Value = opinion.ProductId

            If opinion.Data.Length < chunkSize Then
                chunk = opinion.Data
                index = 0
            Else
                chunk = opinion.Data.Substring(0, chunkSize)
                index = chunkSize
            End If

            command.Parameters.Add("creditData", OracleType.Clob).Value = chunk
            command.Parameters.Add("creditOpinionId", OracleType.Number).Direction = ParameterDirection.Output

            'insert the record and write the first chunk
            MyBase.ExecuteCommand(command)

            'get the unique Id for the record
            opinion.Id = CType(command.Parameters("creditOpinionId").Value, Long)

            'now write all the other chunks
            Do While index > 0

                If (opinion.Data.Length - index) > chunkSize Then
                    'next chunk
                    chunk = opinion.Data.Substring(index, chunkSize)
                    index += chunkSize
                Else
                    'last chunk
                    chunk = opinion.Data.Substring(index)
                    index = 0
                End If

                commandA = Nothing

                Try

                    commandA = New OracleCommand
                    commandA.Connection = Me.Connection
                    commandA.Transaction = Me.Transaction
                    commandA.CommandText = cPackageName & ".AppendCreditOpinion"
                    commandA.CommandType = CommandType.StoredProcedure
                    commandA.Parameters.Add("creditOpinionId", OracleType.Number).Value = opinion.Id
                    commandA.Parameters.Add("chunk", OracleType.Clob).Value = chunk

                    MyBase.ExecuteCommand(commandA)

                Finally
                    commandA.Dispose()
                End Try

            Loop

            Me.CommitTransaction()

        Catch ex As Exception

            Me.RollbackTransaction()
            Throw

        Finally

            command.Dispose()
            Me.CloseConnection()

        End Try

    End Sub