0 Replies Latest reply on Jul 25, 2002 9:20 PM by 7435

    Problems with calling a procedure

      I have the following procedure defined:

      CREATE or replace Procedure Extract
      (inQuery_no In NUMBER, inStart_Date In DATE, inEnd_Date In DATE, inFiscal_Period In varCHAR2) is
      Insert into labor_activity
      (Select inQuery_no, L.SON_NUMBER, ACTIVITY_CODE, HOURS, Equip_Type_In, Labor_Rate, COGNIZANT_ORG, CLOSE_DATE, i.invc_nmbr
      From b406.labor_activity_record L, b406.X408_ORDER_RECORD X, b406.NEW_SB_ALPHA_RECORD s, b406.invoice_record I
      where L.SON_NUMBER = X.SON_NUMBER and s.SB_SON_NMBR = X.SON_NUMBER and i.son_nmbr = x.son_number and CLOSE_DATE >= inStart_Date and CLOSE_DATE <= inEnd_Date And x.overhaul_flag = 'N' and s.New_Alpha_sb = 'NIL' and EQUIP_TYPE_IN In (Select Equipment_Type From Controls Where EXPIRATION_DATE > inFiscal_Period and EFFECTIVE_DATE <= inFiscal_Period));

      Which runs successfully from SQL*Plus when I execute the following line:

      begin extract(1, to_date(2002-02-02,YYYY-MM-DD), to_date(2002-03-01,YYYY-MM-DD), 2002-05); end;

      However, when I attempt to run it from VB with the following code:

      OraDatabase.Parameters.Add "QUERYNO", "1", ORAPARM_INPUT
      OraDatabase.Parameters("QUERYNO").serverType = ORATYPE_NUMBER

      OraDatabase.Parameters.Add "STARTDATE", "02/02/2002", ORAPARM_INPUT
      OraDatabase.Parameters("STARTDATE").serverType = ORATYPE_DATE

      OraDatabase.Parameters.Add "ENDDATE", "03/01/2002", ORAPARM_INPUT
      OraDatabase.Parameters("ENDDATE").serverType = ORATYPE_DATE

      OraDatabase.Parameters.Add "FISCALPERIOD", "2002-05", ORAPARM_INPUT
      OraDatabase.Parameters("FISCALPERIOD").serverType = ORATYPE_VARCHAR2

      Set OraSqlStmt = OraDatabase.CreateSql("Begin vpBOSS_Extract(:QUERYNO,:STARTDATE,:ENDDATE,:FISCALPERIOD); end;", ORASQL_FAILEXEC)

      No data is transferred and no error is raised. Any suggestions?