Stored Procedure Oracle + Clob + Vb.Net

Discussion in 'Microsoft ADO .NET' started by jbolanos@co.soluziona.com, Jul 29, 2004.

  1. Guest

    I want to call a stored procedure with input parameter of Clob type
    from Vb.net, I have had many problems and looks for in google and
    locate a solution that Tomas Westhed gave in 2002-05-28. I cosay that
    it did was:

    Dim tx As OracleTransaction
    tx = cn.BeginTransaction()

    Dim cmd As New OracleCommand()
    cmd = cn.CreateCommand()

    cmd.Transaction = tx

    cmd.CommandText = "declare xx Clob; begin " & _
    "dbms_lob.createtemporary(xx, false, 0); :tempClob := xx;
    end;"
    cmd.Parameters.Add(New OracleParameter("tempClob",
    OracleType.Clob)).Direction = ParameterDirection.Output
    Try
    cmd.ExecuteNonQuery()

    Dim tempLob As OracleLob
    tempLob = cmd.Parameters(0).Value

    clobstring = <Any XML String>

    cmd.Parameters.Clear()
    cmd.CommandText = "dbms_lob.write"
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.Add(New OracleParameter("lob_loc",
    OracleType.Clob)).Direction = ParameterDirection.Input
    cmd.Parameters("lob_loc").Value = tempLob
    cmd.Parameters.Add(New OracleParameter("amount",
    OracleType.Byte)).Direction = ParameterDirection.Input
    cmd.Parameters("amount").Value = clobstring.Length
    cmd.Parameters.Add(New OracleParameter("offset",
    OracleType.Number)).Direction = ParameterDirection.Input
    cmd.Parameters("offset").Value = 1
    cmd.Parameters.Add(New OracleParameter("buffer",
    OracleType.VarChar)).Direction = ParameterDirection.Input
    cmd.Parameters("buffer").Value = clobstring

    We then use cmd.Parameters(0).Value as a in-parameter for our stored
    procedure.

    I have done the same but I do not understand like calling the stored
    procedure that is like using cmd.Parameters(0).Value.

    I thank for any aid
     
    , Jul 29, 2004
    #1
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Andrew

    Oracle CLOB parameter limit?

    Andrew, Aug 29, 2003, in forum: Microsoft ADO .NET
    Replies:
    2
    Views:
    14,493
    Andrew
    Sep 3, 2003
  2. A B
    Replies:
    0
    Views:
    2,471
  3. Jeff Washburn

    Insert CLOB into Oracle using ADO.NET

    Jeff Washburn, Jul 30, 2004, in forum: Microsoft ADO .NET
    Replies:
    5
    Views:
    5,609
    Jeff Washburn
    Aug 2, 2004
  4. Replies:
    0
    Views:
    533
  5. jobs
    Replies:
    1
    Views:
    2,760
    Paul Clement
    Jan 10, 2007
Loading...

Share This Page