Solved

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

Posted on 2008-10-29
9
1,295 Views
Last Modified: 2013-12-19
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
0
Comment
Question by:Biju1149
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
9 Comments
 
LVL 27

Expert Comment

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

"resultset 1" - looks not right
0
 

Author Comment

by:Biju1149
ID: 22838641
Sujith,

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

0
 
LVL 27

Expert Comment

by:sujith80
ID: 22838702
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.
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 23104271
"{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.
0
 

Author Comment

by:Biju1149
ID: 23104682
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
0
 
LVL 32

Accepted Solution

by:
Robberbaron (robr) earned 500 total points
ID: 23162835
http://support.microsoft.com/kb/176086    '<<<very much on target

http://support.microsoft.com/kb/174679/EN-US/

 
I know little of stored proceedures but following M$ example....

SqlStr = "{Call ARB_Facsimile_Signature.Invoices_ALL(?,?,{resultset 1, p_cursor})}"
'returns max 1 record !

SqlStr = "{Call ARB_Facsimile_Signature.Invoices_BU(?,?,?,{resultset 1000, p_cursor})}"
'returns max 1000 records

ive changed the out variable name to match what is in the SP.

ms example attached for others to search by.




  ''QSQL = "{call packperson.oneperson(?,{resultset 2, ssn, fname, lname})}"
  'return 2 records max
 
  Set CPw2 = New ADODB.Command
  With CPw2
      Set .ActiveConnection = Cn
      .CommandText = QSQL
      .CommandType = adCmdText
      .Parameters.Append .CreateParameter(, adInteger, adParamInput)
  End With
 
  Set Rs = New ADODB.Recordset
  With Rs
      .CursorType = adOpenStatic
      .LockType = adLockReadOnly
  End With
 
  CPw2(0) = inputssn  '<<< user input data
 
  Rs.Open
 
  MsgBox "Person data: " & Rs(0) & ", " & Rs(1) & ", " & Rs(2)
 
  Rs.Close

Open in new window

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question