Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004
    Posts
    2

    Angry Unanswered: OLEDB Calling Stored Procedure via DB Link

    Hello,

    Using Oracle and OLEDB I am trying to execute a stored procedure (actually a function) via a database link. When I do this OLEDB throws an "Unspecified error". The SP is fine, I can exec it via sqlplus through the link as well as in some other java code. VB.NET code below that I can't get to work. Any insight would be helpfull (and no I can't change providers...). I think the error is due to the DB Link syntax (@SMSTRANSFER.WORLD), I actually put a local copy of the schema and sp to my connection and this also works fine.



    Dim smscmd As New OleDb.OleDbCommand("SUBMIT_TRANSACTION@SMSTRANSFER .WORLD", Me.twrDB, Me.transaction)
    smscmd.CommandType = CommandType.StoredProcedure

    'Return Value
    smscmd.Parameters.Add("RETURN_VALUE", OleDb.OleDbType.VarChar, 100).Direction = ParameterDirection.ReturnValue

    'Input Parameters
    smscmd.Parameters.Add("limsIds", OleDb.OleDbType.VarChar, 100).Value = inBarcodes
    smscmd.Parameters.Add("parameterIds", OleDb.OleDbType.VarChar, 100).Value = "1"
    smscmd.Parameters.Add("parameterValues", OleDb.OleDbType.VarChar, 100).Value = inSMSParentIDs
    smscmd.Parameters.Add("applicationId", OleDb.OleDbType.VarChar, 100).Value = "3"
    smscmd.Parameters.Add("userLoginId", OleDb.OleDbType.VarChar, 100).Value = inMpersonID

    smscmd.ExecuteNonQuery()

  2. #2
    Join Date
    May 2004
    Posts
    2

    Talking Did it the "hard" way

    Solved my own problem, but had to do as SQL, couldn't use the CommandType.StoredProcedure:

    smscmd.CommandType = CommandType.Text
    smscmd.CommandText = "BEGIN ? := SUBMIT_TRANSACTION@SMSTRANSFER.WORLD(?,?,?,?,?); END;"

    'Return Value
    smscmd.Parameters.Add("RETURN_VALUE", OleDb.OleDbType.VarChar, 100).Direction = ParameterDirection.Output

    'Input(Parameters)
    smscmd.Parameters.Add("limsIds", OleDb.OleDbType.VarChar, 100).Value = inBarcodes
    smscmd.Parameters.Add("parameterIds", OleDb.OleDbType.VarChar, 100).Value = "1"
    smscmd.Parameters.Add("parameterValues", OleDb.OleDbType.VarChar, 100).Value = inSMSParentIDs
    smscmd.Parameters.Add("applicationId", OleDb.OleDbType.VarChar, 100).Value = "3"
    smscmd.Parameters.Add("userLoginId", OleDb.OleDbType.VarChar, 100).Value = inMpersonID

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •