i made the following changes to varchar but still get the same exception.
oracle function (ops$sqltime.pa_new_job_no_fn) definition:
(p_project_no IN varchar2) return varchar2 is
-- ***************************************************************************
-- DESCRIPTION: This procedure is called by the rollout process. Given a
project no,
-- it will determine a unique job_no for that project.
-- Version Date Who Description of Change
-- ------- -------- --- --------------------------------
-- 5.1.3 11/08/04 DL-JJT Changed f(x) to return '101' instead
-- of '1' if l_return is null
-- 51.1.02 08/24/98 GSB :6702: Check pa_job for duplicates
-- before returning the job_no.
-- 8.01.XX 07/10/98 GSB :3121: Initial Creation
l_count number;
l_last_job_no number := 0;
l_this_job_no number;
l_return pa_job.job_no%type;
cursor c1 is
select job_no
from pa_job
where project_no = p_project_no
order by job_no desc;
r1 c1%rowtype;
--Main
BEGIN
open c1;
loop
fetch c1 into r1;
exit when c1%notfound or l_return is not null;
--This will test if the job_no returned is numeric. If it isn't
--skip on to the next one.
begin
l_this_job_no := NULL;
l_this_job_no := TO_NUMBER(r1.job_no);
exception
when others then
null;
end;
if l_this_job_no is not null
and l_this_job_no != 9999 then
--We have a job that is entirely numeric. So let's add 1 to it, and
--test for the existence of the new job no. We do this by comparing
--to the last job_no we looked at. If it passes that
if l_this_job_no + 1 != l_last_job_no
and l_this_job_no != l_last_job_no
then
--Now check the database:
select count(*)
into l_count
from pa_job
where project_no = p_project_no
and job_no = to_char(l_this_job_no + 1);
if l_count = 0
then
l_return := TO_CHAR(l_this_job_no + 1);
else
l_last_job_no := l_this_job_no;
end if;
else
l_last_job_no := l_this_job_no;
end if;
end if;
end loop;
close c1;
--If l_return is null, we know this project has no numeric jobs, so we are
--safe returning a 1.
l_return := NVL(l_return,'101');
RETURN(l_return);
END
------------------------------------------------------------------------------------------
my code:
OracleCommand cmd = new OracleCommand();
cmd.Connection = oraConn;
cmd.CommandText = "ops$sqltime.pa_new_job_no_fn";
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter par1 = new OracleParameter("RETURN_VALUE",
OracleType.VarChar);
par1.Direction = ParameterDirection.ReturnValue;
par1.Size = 4000;
cmd.Parameters.Add(par1);
par1 = new OracleParameter("PROJECT_NO", OracleType.VarChar);
par1.Value = projectNo;
par1.Direction = ParameterDirection.Input;
par1.Size = 4000;
cmd.Parameters.Add(par1);
int affectedRows = (int) cmd.ExecuteScalar();
ljn = (int) cmd.Parameters["RETURN_VALUE"].Value;
Bjorn Abelli said:
...
the real name of the input parameter is project_no which
is a string.
So why don't you name the parameter as such (though with capital letters)?
As you're sure it's a String, I suppose the variable "projectNo" in your
program is also a String.
That would mean that "PROJECT_NO" is a CHAR or VARCHAR2 field, most likely
with a specified length, but I doubt that it has a length of 4000...
the return value should be the job_no.
Of what type? If "PROJECT_NO" is a CHAR/VARCHAR2, my first guess would be
that the return type also is of type CHAR/VARCHAR, i.e. a String!
i believe there is only 1 input and 1 return parameter.
i made some changes and now i'm getting a different exception:
ORA-06550: line 1, column 14: PLS-00306: wrong number or
types of arguments in call to 'PA_NEW_JOB_NO_FN' ORA-06550:
line 1, column 7: PL/SQL: Statement ignored
So the call went through, but the number or types of parameters are wrong.
Do you have a better description of the stored function with the number and
types of arguments and the type of the return value?
OracleCommand cmd = new OracleCommand();
cmd.Connection = oraConn;
cmd.CommandText = "ops$sqltime.pa_new_job_no_fn";
cmd.CommandType = CommandType.StoredProcedure;
// I would still insist on using named parameters. Otherwise
// you'll probably not be able to fetch the return value...
// Though possibly with the type and size more accurate,
// so check with your DBA what the types really are...
OracleParameter par1 =
new OracleParameter("RETURN_VALUE", OracleType.Char, 20);
par1.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(par1);
// ...both for the return value, as well as for the input
// argument...
par1 = new OracleParameter("PROJECT_NO", OracleType.Char, 20);
par1.Value = projectNo;
par1.Direction = ParameterDirection.Input;
cmd.Parameters.Add(par1);
int affectedRows = (int) cmd.ExecuteNonQuery();
// Possibly this hence would be a String as well?
string ljn = cmd.Parameters["RETURN_VALUE"].Value.ToString();
// Bjorn A
