Lilib9
asked on
Asp.net Oracle stored procedure error
Hi,
I are using ASP.net 2008 and microsoft entreprise Application block 4.0 for Accesing Oracle 10g Express Edition.
while trying to insert a new record in oracle using stored procedure i face the error ..
ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'PR_INSERT_TEST' ORA-06550: line 1, column 7: PL/SQL: Statement ignored
i have even tried for a table with one column and a stored procedure for the same , but i still get the error ...
Please Help me solve the error
Regards,
Lilib
I are using ASP.net 2008 and microsoft entreprise Application block 4.0 for Accesing Oracle 10g Express Edition.
while trying to insert a new record in oracle using stored procedure i face the error ..
ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'PR_INSERT_TEST' ORA-06550: line 1, column 7: PL/SQL: Statement ignored
i have even tried for a table with one column and a stored procedure for the same , but i still get the error ...
Please Help me solve the error
Regards,
Lilib
' -----------------------------------Form DAL layer ---------------------------
Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports System.Data
Imports System.Data.Common
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports Microsoft.Practices.EnterpriseLibrary.Data.Configuration
Imports Microsoft.Practices.EnterpriseLibrary.Data.Oracle
Imports Microsoft.Practices.EnterpriseLibrary.Data.Instrumentation
Imports JM.BMS.DS
Public Class BIN_TYPEDAL
Inherits BaseDAL
Private Const JM_BMS_pr_insert_bin_type As String = "PR_INSERT_BIN_TYPE"
Public Function ViewBinType(ByVal dataSet As DSBinType) As DSBinType
dataSet.EnforceConstraints = False
Dim db As Database = DatabaseFactory.CreateDatabase("BMS_DB_CONNECTION")
Dim cmdInsert As DbCommand = PrepareSPParameters(db, dataSet.TBL_BIN_TYPES, JM_BMS_pr_insert_bin_type)
Dim i As Int32 = db.UpdateDataSet(dataSet, dataSet.TBL_BIN_TYPES.TableName, cmdInsert, Nothing, Nothing, UpdateBehavior.Standard)
Return dataSet
End Function
End Class
'----------------------------------------- Base DAL code---------------------------------------
Imports System
Imports System.Collections.Generic
Imports System.Text
Imports System.Data
Imports System.Data.Common
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports Microsoft.Practices.EnterpriseLibrary.Data.Configuration
Imports Microsoft.Practices.EnterpriseLibrary.Data.Oracle
Imports Microsoft.Practices.EnterpriseLibrary.Data.Instrumentation
Imports System.Web
Imports System.Web.Caching
Imports System.Collections
Imports System.Linq
Public Class BaseDAL
'Static variable
Private Shared paramCache As Hashtable = Hashtable.Synchronized(New Hashtable())
''' <summary>
''' The function creates a command object with parameters based on the passed on stored procedure
''' name.
''' </summary>
''' <param name="db">The database object where the stored procedure resides.</param>
''' <param name="procedureName">Stored procedure name.</param>
''' <returns>DBCommand object filled in with parameter information (except the values).</returns>
Protected Overridable Function PrepareSPParameters(ByVal db As Database, ByVal procedureName As String) As DbCommand
Dim databaseCommand As DbCommand
databaseCommand = db.GetStoredProcCommand(procedureName)
If CheckParametersInCache(db, databaseCommand) Is Nothing Then
' Discover parameters required for dbcommand and store them in cache
SetCacheParameter(db, databaseCommand)
Else
Dim cachedParameters As DbParameter() = CheckParametersInCache(db, databaseCommand)
For Each Dbpara As DbParameter In cachedParameters
' create clone copy from cache
Dim CloneDbpara As DbParameter = DirectCast(DirectCast(Dbpara, ICloneable).Clone(), DbParameter)
db.AddParameter(databaseCommand, CloneDbpara.ToString(), CloneDbpara.DbType, CloneDbpara.Direction, CloneDbpara.SourceColumn, CloneDbpara.SourceVersion, _
CloneDbpara.Value)
Next
End If
Return databaseCommand
End Function
''' <summary>
''' The function creates a command object with parameters based on the passed on stored procedure
''' name. The table also maps dataset values to stored procedure parameter values. The function
''' expects stored procedure parameter names to be same as data table column names. The function
''' also assumes a single row in the table and ignore any additional rows.
''' </summary>
''' <param name="db">The database object where the stored procedure resides.</param>
''' <param name="dataStore">The table from which data needs to be mapped to the stored procedure.
''' </param>
''' <param name="procedureName">Stored procedure name.</param>
''' <returns>DBCommand object filled in with parameter information (including the values).</returns>
Protected Overridable Function PrepareSPParameters(ByVal db As Database, ByVal dataStore As DataTable, ByVal procedureName As String) As DbCommand
Dim databaseCommand As DbCommand
If dataStore.Rows.Count <= 0 Then
Throw New Exception("No rows in datatable")
End If
databaseCommand = PrepareSPParameters(db, procedureName)
Return databaseCommand
End Function
''' <summary>
''' The function creates a command object with parameters based on the passed on stored procedure
''' name. The table also maps dataset columns to stored procedure parameter names. The function
''' expects stored procedure parameter names to be same as data table column names. Use
''' this function for the UpdateDataSet() commands parameters list.
''' </summary>
''' <param name="db">The database object where the stored procedure resides.</param>
''' <param name="dataStore">The table from which data needs to be mapped to the stored procedure.
''' </param>
''' <param name="procedureName">Stored procedure name.</param>
''' <returns>DBCommand object filled in with parameter information (including the values).</returns>
Protected Overridable Function PrepareSPParameters(ByVal db As Database, ByVal dataStore As DataTable, ByVal procedureName As String, ByVal rowVersion As DataRowVersion) As DbCommand
Dim databaseCommand As DbCommand
If dataStore.Rows.Count <= 0 Then
Throw New Exception("No rows in datatable")
End If
databaseCommand = PrepareSPParameters(db, procedureName)
For Each para As DbParameter In databaseCommand.Parameters
Dim colName As String = para.ParameterName.Replace("@", "")
If dataStore.Columns.Contains(colName) Then
para.SourceColumn = colName
para.SourceVersion = rowVersion
End If
Next
Return databaseCommand
End Function
''' <summary>
''' The function retrieves the parameter collection for a stored procedure. This will reduce
''' the need to scatter parameter names all around in data access.
''' </summary>
''' <param name="db">The database object where the stored procedure resides.</param>
''' <param name="databaseCommand">Command object for which parameters are needed.</param>
Private Function CheckParametersInCache(ByVal db As Database, ByVal databaseCommand As DbCommand) As DbParameter()
Dim hashKey As String = databaseCommand.CommandText
Dim cachedParameters As DbParameter() = DirectCast((HttpContext.Current.Cache(hashKey)), DbParameter())
Return cachedParameters
End Function
''' <summary>
''' Add DBparameterCollection to the cache
''' </summary>
''' <param name="db">The database object where the stored procedure resides.</param>
''' <param name="databaseCommand">Command object for which parameters are needed.</param>
Public Shared Sub SetCacheParameter(ByVal db As Database, ByVal databaseCommand As DbCommand)
db.DiscoverParameters(databaseCommand)
Dim hashKey As String = databaseCommand.CommandText
Dim DBpara As DbParameter() = New DbParameter(databaseCommand.Parameters.Count - 1) {}
For i As Integer = 0 To databaseCommand.Parameters.Count - 1
' create a clone copy
DBpara(i) = DirectCast(DirectCast(databaseCommand.Parameters(i), ICloneable).Clone(), DbParameter)
Next
paramCache(hashKey) = DBpara
HttpContext.Current.Cache.Insert(hashKey, paramCache(hashKey))
End Sub
End Class
'------------------------------------ Web Config --------------------------
<connectionStrings>
<add name="BMS_DB_CONNECTION" connectionString="Data Source=172.16.129.67;User ID=jmp;Password=jmp;Unicode=True" providerName="System.Data.OracleClient"/>
</connectionStrings>
'---------------------------------Stored Procedure--------------------------------
create or replace procedure pr_insert_bin_type
(BIN_TYPE_NAME IN tbl_bin_types.bin_type_name%type ,
QUANTITY IN tbl_bin_types.quantity%type ,
REMARK IN tbl_bin_types.remark%type)
as
Begin
insert into TBL_BIN_TYPES (ID,bin_type_name,quantity,remark)
values (s_bin_type_id.nextval,BIN_TYPE_NAME,QUANTITY,REMARK);
commit;
End;
'-------------------------------------- Table Structure ---------------------
ID NOT NULL VARCHAR2(16)
BIN_TYPE_NAME VARCHAR2(30)
QUANTITY NUMBER(10,2)
REMARK VARCHAR2(200)
AUDIT_USERID VARCHAR2(16)
CREATION_DATE VARCHAR2(15)
sequence create on ID
error.JPG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Appologies Lilib. The EE system is malfunctioning i guess because i do not receive emails for many of the comments now a days. Here are some links re the n tier architecture.
http://www.developerfusion.com/article/3058/boosting-your-net-application-performance/2/
http://www.15seconds.com/issue/011023.htm
http://msdn.microsoft.com/en-us/library/bb384398.aspx
https://www.experts-exchange.com/questions/24342091/Approach-for-Application-Layers-n-tier-for-Web-Application.html
http://www.articlesbase.com/web-design-articles/ntier-architecture-in-aspnet-559345.html
http://www.developerfusion.com/article/3058/boosting-your-net-application-performance/2/
http://www.15seconds.com/issue/011023.htm
http://msdn.microsoft.com/en-us/library/bb384398.aspx
https://www.experts-exchange.com/questions/24342091/Approach-for-Application-Layers-n-tier-for-Web-Application.html
http://www.articlesbase.com/web-design-articles/ntier-architecture-in-aspnet-559345.html
ASKER
Thanks for going through the code and helping out with a suggestion.
The fact is that i am new to 3 -tier Architecture in ASP.Net (VB) using microsoft entreprise Application block 4.0.
If possible, can you provide me with the code to interact with Dataset, Data Access layer(DAL),Business Access Layer(BAL) and Businnes Facade layer.
Any kind of help would be grateful.
Thanks and Regards,
Lilib