Solved

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

Posted on 2008-10-29
9
1,281 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
  • 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now