Solved

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

Posted on 2008-10-29
9
1,293 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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.

829 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