cant get returnvalue of storeproc

[adshocker]

hi all,

i'm having a problem getting the returnvalue of a storedproc. i'm using oracle database and created an oracle function that returns a clob data. unfortunately in vb.net, it won't accept clob format. if i change the returnvalue to varchar2 format it works fine.

is there a way to convert clob into string?

thanks.

 

[cjard]

nope, you have to read from it as though it were a stream. I'll look up some code example for you when I get into work because the home projects I do dont gerneally have clobs

 

[adshocker]

nope, you have to read from it as though it were a stream. I'll look up some code example for you when I get into work because the home projects I do dont gerneally have clobs

thanks.

p.s.

i'm using clob datatype because i need to return a large amount of characters (basically DDL statements like Creating tables with about a hundred partition more or less, also for creating a view with a very large text). please let me know if there are other ways to do this besides using clob as datatype.

thanks again.

 

[cjard]

One of the other things you'll run into is, I think, that stored procedures dont acquire rights to create views or tables from roles.. Though your user might be in role that allows it to create tables directly, the table creation right has to be granted explicitly to be used from PLSQL

This way reads from a cursor full of clobs:

    Public Function GetProcessData(ByVal processId As Long, ByVal processDataType As String) As String

        Dim command As OracleCommand = Nothing
        Dim processData As String = Nothing

        Try

            Me.OpenConnection()

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

            command.Parameters.Add("Id", OracleType.Number).Value = processId
            command.Parameters.Add("DType", OracleType.VarChar).Value = processDataType
            command.Parameters.Add("curProcessData", OracleType.Cursor).Direction = ParameterDirection.Output

            Dim reader As OracleDataReader = command.ExecuteReader()

            If reader.Read Then

                'fields are returned in this order:
                '(0) process_data

                If Not reader.IsDBNull(0) Then
                    processData = reader.GetOracleLob(0).Value.ToString
                End If

            End If

            reader.Close()

        Catch ex As Exception
            Throw

        Finally

            command.Dispose()
            Me.CloseConnection()

        End Try

        Return processData

    End Function

 

[adshocker]

thanks very much.

i'll try this as soon as i can.

 

[cjard]

I just remembered another thing.. if youre only returning the clob on its own, not part of a cursor you have to do something like:

Dim oc as New OracleCommand("PROC_NAME", "CONNECTION STIRNG")
oc.CommandType = StoredProcedure
oc.Parameters.Add(...whatever..)
oc.Parameters.Add("out_clob", OracleType.Lob? Clob?)
oc.ExecuteNonQuery()

Now put a breakpoint on executeNonQuery
Run the code, step over the Exec so that it runs
Now use the locals window to investigate the oc object.. parameters collection, the out_clob parameter I *think* has a type of OracleLob, and it can be .Value or .ToStringed to get your clob data.. i cant accurately remember and I cant find the cod eI wrote for this once.. Use the debugger to investigate

 

[adshocker]

thanks...

i tried your both suggestions...

in the first approach, i get an error saying that the procedure is not a procedure or is undefined. the procedure i'm calling is a part of a package... not sure if this has anything to do with it.
here's my code.

        Dim tmpConn As New OracleConnection(My.Settings.ConnectionString)
        Dim tmpComm As New OracleCommand
        Dim tmpLob As OracleLob
        Dim tmpStr As String = Nothing

        tmpConn.Open()
        With tmpComm
            .CommandType = CommandType.StoredProcedure
            .CommandText = "dbm_metadata.table_ddl"
            .Connection = tmpConn
            .Parameters.Add("p_table_name", OracleType.VarChar).Value = TextBox2.Text
            '.Parameters.Add("v_output", OracleType.Clob).Direction = ParameterDirection.ReturnValue

            Dim tmpRead As OracleDataReader = .ExecuteReader
            While tmpRead.Read
                tmpStr = tmpRead.GetOracleLob(0).Value.ToString
            End While
            tmpRead.Close()
        End With
        tmpConn.Close()

on the second approach, i don't get an error but it returns a different data.. "System.Data.OracleClient.OracleLob".

here's my code

        Dim tmpConn As New OracleConnection(My.Settings.ConnectionString)
        Dim tmpComm As New OracleCommand
        Dim tmpLob As OracleLob

        tmpConn.Open()
        With tmpComm
            .CommandType = CommandType.StoredProcedure
            .CommandText = "dbm_metadata.table_ddl"
            .Connection = tmpConn
            .Parameters.Add("p_table_name", OracleType.VarChar).Value = TextBox2.Text
            .Parameters.Add("v_output", OracleType.Clob).Direction = ParameterDirection.ReturnValue
            .ExecuteNonQuery()
            tmpLob = .Parameters("v_output").Value
            TextBox1.Text = tmpLob.ToString
        End With
        tmpConn.Close()

am i doing something wrong?

thanks.

 

[cjard]

Go the second approach. it looks like ToString wont return the value of the clob, it returns the type of the object. Look afor another property to get the value (read the documentation?)