Call Oracle Procedure using adcmdtext

[cockeyed]

Hello,
I have been tackling this for a while with no avail, and I am desperate at this point. The following is vb.net code used to execute an Oracle procedure. It simply hangs forever on the Execute command, and does not return an error.

I have the the exact same code calling a different procedure which is working without any issues. That procedure is also available below.
Any and all comments or opinions are very welcome.

Thank you all so much in advance,


VB Code:

       Dim sSQL As String = ""
        Dim dbCmd As ADODB.Command = New ADODB.Command
        gbl_strProvider = "provider=OraOLEDB.Oracle;Data Source=" & Trim(strConnect) & "; User id=" & Trim(strUserID) & "; Password=" & Trim(strPassword)
 gbl_dbConn.Open(gbl_strProvider)
        dbCmd.ActiveConnection = gbl_dbConn
        dbCmd.CommandTimeout = 30

        dbCmd.CommandType = ADODB.CommandTypeEnum.adCmdText
                 dbCmd.Parameters.Append(dbCmd.CreateParameter("in_compno", ADODB.DataTypeEnum.adNumeric, ADODB.ParameterDirectionEnum.adParamInput, , CompNo))
                dbCmd.Parameters.Append(dbCmd.CreateParameter("in_stateid", ADODB.DataTypeEnum.adChar, ADODB.ParameterDirectionEnum.adParamInput, 2, strNewStateID))
                dbCmd.Parameters.Append(dbCmd.CreateParameter("in_versno", ADODB.DataTypeEnum.adNumeric, ADODB.ParameterDirectionEnum.adParamInput, , VersNo))
                sSQL = "{ Call ups.UPDATECOMPONENTSTATEID(?, ?, ?) }"
                dbCmd.CommandText = sSQL
        dbCmd.Execute()

Oracle Procedure:

create or replace
PROCEDURE UPDATECOMPONENTSTATEID
  (
    in_compno  IN NUMBER,
    in_stateid IN CHAR,
    in_versno  IN NUMBER)
AS
  owntype VARCHAR2(10);
  transno NUMBER;
  stateid CHAR(2);
BEGIN

   SELECT ownertype,
    customerno     ,
    componentstateid
     INTO owntype,
    transno      ,
    stateid
     FROM ups.component
    WHERE componentno = in_compno;
   UPDATE ups.component
  SET componentstateid = in_stateid,
    versno             = in_versno
    WHERE componentno  = in_compno;
  IF owntype           = 'TRANSACT' AND stateid = '60' THEN
    -- Check if we can close the transaction
    UPS.CLOSETRANSACTION(transno);
  END IF;
END;


Oracle Procedure that is working without issues:
create or replace PROCEDURE UPDATECOMPONENT(in_compno IN NUMBER, in_owntyp IN VARCHAR2,
   in_custno IN NUMBER, in_type IN VARCHAR2, in_catg IN VARCHAR2, in_prod IN VARCHAR2,
   in_manu IN VARCHAR2, in_serial IN VARCHAR2, in_lic IN VARCHAR2, in_vend IN VARCHAR2,
   in_pudt IN DATE, in_warrdt IN DATE, in_maint IN NUMBER, in_cpuspdcode IN NUMBER,
   in_connvers IN VARCHAR2, in_hd IN VARCHAR2, in_ram IN VARCHAR2, in_stateid IN CHAR,
   in_user1 IN VARCHAR2, in_user2 IN VARCHAR2, in_user3 IN VARCHAR2, in_user4 IN VARCHAR2,
   in_notes IN VARCHAR2, in_versno IN NUMBER) AS

BEGIN
  UPDATE ups.component SET ownertype = in_owntyp, customerno = in_custno,
    componenttype = in_type, componentcategory = in_catg, productdesc = in_prod,
    manufacturer = in_manu, serialno = in_serial, licenseno = in_lic,
    vendor = in_vend, purchasedt = in_pudt, warrantyexpiredt =  in_warrdt,
    annualmaintenance = in_maint, cpuspeedcode = in_cpuspdcode,
    connectversion = in_connvers, cpuharddrive = in_hd, cpumemory = in_ram,
    componentstateid = in_stateid, userref1 = in_user1, userref2 = in_user2,
    userref3 = in_user3, userref4 = in_user4, notes = in_notes, versno = in_versno
    WHERE componentno = in_compno;
END;

 

[cjard]

Tell me, why are you using classic ADO?

 

[cockeyed]

Hi,
Upgrading an existing program to .Net and requires the use of the MSHierachicalFlexGrid Control. The VB6 version works without a problem when it comes to calling the procedures in Oracle, and for some reason this on procedure is clocking.

 

[cjard]

Mmm.. even in ADO I wouldnt have called it like that..

I'd have set the CommandText to just the name of the sproc, and set the type to adCmdStoredProcedure.. Parameters as usual

Tell me, does the sproc complete when you run it in sqlplus/toad ?

 

[cockeyed]

Found the problem, it was caused by the DB connection set to BeginTrans, once I set it to RollBackTans, it works without issue. Thank you