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?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Andrew

    Oracle CLOB parameter limit?

    Andrew, Aug 29, 2003, in forum: Microsoft ADO .NET
    Replies:
    2
    Views:
    15,536
    Andrew
    Sep 3, 2003
  2. ewen mcdonald

    clob into oracle

    ewen mcdonald, Sep 15, 2003, in forum: Microsoft ADO .NET
    Replies:
    4
    Views:
    3,721
    Paul Clement
    Sep 15, 2003
  3. Lenny Woodbridge

    Oracle CLOB Support

    Lenny Woodbridge, Jan 6, 2004, in forum: Microsoft ADO .NET
    Replies:
    1
    Views:
    518
    Angel Saenz-Badillos[MS]
    Jan 6, 2004
  4. Guest
    Replies:
    0
    Views:
    890
    Guest
    Feb 16, 2004
  5. A B
    Replies:
    0
    Views:
    4,299
  6. Jeff Washburn

    Insert CLOB into Oracle using ADO.NET

    Jeff Washburn, Jul 30, 2004, in forum: Microsoft ADO .NET
    Replies:
    5
    Views:
    7,013
    Jeff Washburn
    Aug 2, 2004
  7. Replies:
    0
    Views:
    703
  8. jobs
    Replies:
    1
    Views:
    3,638
    Paul Clement
    Jan 10, 2007
Loading...