nicemanish
asked on
stored procedure issue with package
issue : if i am calling stored procedure from "Package1.PROCEDURE1"; then its works.but if i am using only storedprocedure then its not works
(if put procedure in package its work but without package its not works when i passed array from c# code.
Wrong number or type of arguments
try
{
objCommand = new OracleCommand();
objCommand.Connection = GetConnectionString();
objCommand.CommandText = "Package1.PROCEDURE1";
objCommand.CommandType = CommandType.StoredProcedur e;
OracleParameter param =new OracleParameter();
param.OracleDbType=OracleD bType.Varc har2;
param.CollectionType=Oracl eCollectio nType.PLSQ LAssociati veArray;
param.Value=new string [2] {"4","3"};
param.Size=20;
objCommand.Parameters.Add( param);
//Output parameter to the stored procedure
//objCommand.Parameters.Ad d("po_outp ut_cnt", OracleDbType.Varchar2, 10).Direction = ParameterDirection.Output;
objCommand.ExecuteNonQuery ();
//Close the database connection
CloseConnection();
//converting return parameter into boolean.
//if (string.IsNullOrWhiteSpace (returnSta tus) || returnStatus.Equals("0"))
//returnValue = false;
//else
//returnValue = true;
}
--------------------
Prcodeure
create or replace package package1
as
type name is table of varchar2(20) index by pls_integer;
PROCEDURE PROCEDURE1(param in name);
end package1;
create or replace package body package1 as
PROCEDURE PROCEDURE1(param in name) is
BEGIN
for i in param.first..param.last loop
insert into jobs(jobid) values(param(i));
end loop;
END PROCEDURE1;
end package1;
Error :- Wrong number or type of arguments
(if put procedure in package its work but without package its not works when i passed array from c# code.
Wrong number or type of arguments
try
{
objCommand = new OracleCommand();
objCommand.Connection = GetConnectionString();
objCommand.CommandText = "Package1.PROCEDURE1";
objCommand.CommandType = CommandType.StoredProcedur
OracleParameter param =new OracleParameter();
param.OracleDbType=OracleD
param.CollectionType=Oracl
param.Value=new string [2] {"4","3"};
param.Size=20;
objCommand.Parameters.Add(
//Output parameter to the stored procedure
//objCommand.Parameters.Ad
objCommand.ExecuteNonQuery
//Close the database connection
CloseConnection();
//converting return parameter into boolean.
//if (string.IsNullOrWhiteSpace
//returnValue = false;
//else
//returnValue = true;
}
--------------------
Prcodeure
create or replace package package1
as
type name is table of varchar2(20) index by pls_integer;
PROCEDURE PROCEDURE1(param in name);
end package1;
create or replace package body package1 as
PROCEDURE PROCEDURE1(param in name) is
BEGIN
for i in param.first..param.last loop
insert into jobs(jobid) values(param(i));
end loop;
END PROCEDURE1;
end package1;
Error :- Wrong number or type of arguments
It should not be that way unless some other code changes were done for the standalone procedure and the packaged procedure code.
ASKER
could you plz more elobarate ....if put procedure in package its work but without package its not works when i passed array from c# code.
how can i achive this functionality if passes array and using only stored procedure
how can i achive this functionality if passes array and using only stored procedure
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.