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_N O
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_N O
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=bij u1;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.I nvoices_AL L"
SqlStr = "{Call ARB_Facsimile_Signature.In voices_ALL (?,?,resul tset 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.I nvoices_BU "
'SqlStr = "{Call ARB_Facsimile_Signature.In voices_BU( ?,?,?,{res ultset 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.L istIndex)
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
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
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_N
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
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_N
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;
OraConn.CommandTimeout = 10
OraConn.CursorLocation = adUseClient
If CboBusinessUnit.ListIndex = 1 Then ' Query for all Business unit details
'SqlStr = "ARB_Facsimile_Signature.I
SqlStr = "{Call ARB_Facsimile_Signature.In
Set CmdStr = New ADODB.Command
With CmdStr
.ActiveConnection = OraConn
.CommandText = SqlStr
.CommandType = adCmdText
End With
Set ParYear = CmdStr.CreateParameter("In
CmdStr.Parameters.Append ParYear
ParYear = TxtYear.Text
Set ParMonth = CmdStr.CreateParameter("In
CmdStr.Parameters.Append ParMonth
ParMonth = CboMonth.ListIndex
ElseIf CboBusinessUnit.ListIndex > 1 Then
SqlStr = "ARB_Facsimile_Signature.I
'SqlStr = "{Call ARB_Facsimile_Signature.In
Set CmdStr = New ADODB.Command
With CmdStr
Set .ActiveConnection = OraConn
.CommandText = SqlStr
.CommandType = adCmdStoredProc
End With
Set ParYear = CmdStr.CreateParameter("In
CmdStr.Parameters.Append ParYear
Set ParMonth = CmdStr.CreateParameter("In
CmdStr.Parameters.Append ParMonth
Set ParBu = CmdStr.CreateParameter("In
CmdStr.Parameters.Append ParBu
ParYear.value = CInt(TxtYear.Text)
ParMonth.value = CInt(CboMonth.ListIndex)
ParBu.value = maBUCode(CboBusinessUnit.L
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
ASKER
Sujith,
I am not sure. Can you help me to resolve the issue? Thanks in advance.
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.
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.In voices_ALL (?,?,resul tset 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.
Thats where the problem is. I agree that resultset 1 doesnt look look like a parameter to send to the stored procedure.
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> SqlStr = "{Call ARB_Facsimile_Signature.In
"resultset 1" - looks not right