Link to home
Start Free TrialLog in
Avatar of Biju1149
Biju1149

asked on

Run-time error '-2147217900(80040e14)'

I am getting following error while running my VB application:

Run-time error '-2147217900(80040e14)':

ORA-06550: line 1, column 60:
PLS-00103: Encountered the symbol "1" when expecting one of the following:
.(),*@%|=-+</> at in is mod remainder not range rem+>..<an exponent(**)> <> or!+ or ~=>
>= <= <> and or like LIKE2 LIKE4 LIKEC between}}


Here's the package and stored proc:

CREATE OR REPLACE PACKAGE ARB_Facsimile_Signature
is
Procedure Invoices_BU( NumYear in Number, NumMon in Number, VarBuUnit in Varchar, p_cursor  out types.cursorType );

Procedure Invoices_ALL ( NumYear in Number, NumMon in Number, p_cursor  out types.cursorType );

END ARB_Facsimile_Signature;
/


CREATE OR REPLACE PACKAGE BODY ARB_Facsimile_Signature
is
Procedure Invoices_ALL( NumYear in Number, NumMon in Number, p_cursor out types.cursorType )is
begin
      open p_cursor for Select IM_INVOICE_NO AS Invoice_No,UM_USER_NAME Approver_Name,BM_NAME as Business_Unit,IM_APPR_DATe as Approved_Date,
ID_COUNTRY_CODE as Country,(select sum(ID_AMOUNT) as Amount
 from bill_invoice_dtl where
 id_invoice_no =im_invoice_no and  ID_INVOICE_VER = IM_INVOICE_VER) as Invoice_Amount,
 CASE ID_Country_code
  WHEN 'IND' THEN 'INR'
  WHEN 'NEP' THEN 'INR'
  ELSE 'USD'
END as Currency from Bill_Invoice_Mst
INNER JOIN BILL_INVOICE_DTL ON IM_INVOICE_NO=ID_INVOICE_NO
INNER JOIN BILL_BU_MST ON IM_BU_CODE=BM_CODE
INNER JOIN BILL_USER_MST ON IM_APPR_BY = UM_LOGIN_ID
where IM_MONTH= NumMon and IM_YEAR= NumYear;
end Invoices_ALL;
Procedure Invoices_BU ( NumYear in Number, NumMon in Number, VarBuUnit in Varchar, p_cursor out types.cursorType ) AS
begin
      open p_cursor for Select IM_INVOICE_NO AS Invoice_No,UM_USER_NAME Approver_Name,BM_NAME as Business_Unit,IM_APPR_DATe as Approved_Date,
ID_COUNTRY_CODE as Country,(select sum(ID_AMOUNT) as Amount
 from bill_invoice_dtl where
 id_invoice_no =im_invoice_no and  ID_INVOICE_VER = IM_INVOICE_VER) as Invoice_Amount,
 CASE ID_Country_code
  WHEN 'IND' THEN 'INR'
  WHEN 'NEP' THEN 'INR'
  ELSE 'USD'
END as Currency from Bill_Invoice_Mst
INNER JOIN BILL_INVOICE_DTL ON IM_INVOICE_NO=ID_INVOICE_NO
INNER JOIN BILL_BU_MST ON IM_BU_CODE=BM_CODE
INNER JOIN BILL_USER_MST ON IM_APPR_BY = UM_LOGIN_ID
where IM_BU_CODE = VarBuUnit and
IM_MONTH= NumMon and IM_YEAR= NumYear;
end Invoices_BU;
END ARB_Facsimile_Signature;
/

Here's the vb code:

Private Sub imgGO_Click()
Dim CmdStr As ADODB.Command
Dim adrsBuDtl As New ADODB.Recordset ' Recordset for storing business unit details.
Dim SqlStr As String
Dim ParYear As New ADODB.Parameter
Dim ParMonth As New ADODB.Parameter
Dim ParBu As New ADODB.Parameter
Provider  
    If Check_values Then
        If Not ConnTest Then
            MsgBox "Database connection error", vbOKOnly, "Go Image Click"
        Else
      OraConn.Open "Provider=ORAOLEDB.Oracle;User ID=arbilling1;password=biju1;Data Source=hpdev;Persist Security Info=False"
            OraConn.CommandTimeout = 10
            OraConn.CursorLocation = adUseClient
            If CboBusinessUnit.ListIndex = 1 Then ' Query for all Business unit details
                'SqlStr = "ARB_Facsimile_Signature.Invoices_ALL"
                SqlStr = "{Call ARB_Facsimile_Signature.Invoices_ALL(?,?,resultset 1)}"
                Set CmdStr = New ADODB.Command
                With CmdStr
                        .ActiveConnection = OraConn
                        .CommandText = SqlStr
                        .CommandType = adCmdText
                End With
                Set ParYear = CmdStr.CreateParameter("In_Year", adInteger, adParamInput, 1, TxtYear.Text)
                CmdStr.Parameters.Append ParYear
                ParYear = TxtYear.Text
                Set ParMonth = CmdStr.CreateParameter("In_Month", adInteger, adParamInput, 1, CboMonth.ListIndex)
                CmdStr.Parameters.Append ParMonth
                ParMonth = CboMonth.ListIndex
            ElseIf CboBusinessUnit.ListIndex > 1 Then
                SqlStr = "ARB_Facsimile_Signature.Invoices_BU"
                'SqlStr = "{Call ARB_Facsimile_Signature.Invoices_BU(?,?,?,{resultset 1000, fname})}"
                Set CmdStr = New ADODB.Command
                With CmdStr
                    Set .ActiveConnection = OraConn
                        .CommandText = SqlStr
                        .CommandType = adCmdStoredProc
                End With
                Set ParYear = CmdStr.CreateParameter("In_Year", adInteger, adParamInput, 1)
                CmdStr.Parameters.Append ParYear
                Set ParMonth = CmdStr.CreateParameter("In_Month", adInteger, adParamInput, 1)
                CmdStr.Parameters.Append ParMonth
                Set ParBu = CmdStr.CreateParameter("In_Bu", adVarChar, adParamInput, 3)
                CmdStr.Parameters.Append ParBu
                ParYear.value = CInt(TxtYear.Text)
                ParMonth.value = CInt(CboMonth.ListIndex)
                ParBu.value = maBUCode(CboBusinessUnit.ListIndex)
            End If
        End If
           
        Set adrsBuDtl = New ADODB.Recordset
        'If Not adrsBuDtl.State Then
            Set adrsBuDtl = CmdStr.Execute
            MsgBox adrsBuDtl.RecordCount
        'End If
    End If
End Sub
Avatar of Sujith
Sujith
Flag of United Kingdom of Great Britain and Northern Ireland image

Are you sure this call is correct.
>> SqlStr = "{Call ARB_Facsimile_Signature.Invoices_ALL(?,?,resultset 1)}"

"resultset 1" - looks not right
Avatar of Biju1149
Biju1149

ASKER

Sujith,

I am not sure. Can you help me to resolve the issue? Thanks in advance.

I am not familiar with VB.

To me it looks like that call is not proper. Probably you have to declare a resultset variable and use it in the call. Or bind the parameter to the variable.
"{Call ARB_Facsimile_Signature.Invoices_ALL(?,?,resultset 1)}"  is a call to the oracle stored procedure.

Thats where the problem is.  I agree that   resultset 1 doesnt look look like a parameter to send to the stored procedure.
SQL>variable c refcursor;
SQL>exec ARB_Facsimile_Signature. Invoices_BU (2006,01,HRC,:C);
SQL>print c;

In oracle above command is used to execute and retrive data.
I need to execute this procedure using VB code.

Please help me to resolve the issue. thanks in advance
ASKER CERTIFIED SOLUTION
Avatar of Robberbaron (robr)
Robberbaron (robr)
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial