By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,715 Members | 1,773 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,715 IT Pros & Developers. It's quick & easy.

how to call an oracle function

P: n/a
how can i call an oracle function to get data without using a select
statement or stored procedures?

given a project_no, i need to call the function:
ops$sqltime.pa_new_job_no_fn
which will return the next job_no

thanks in advance.
Nov 17 '05 #1
Share this Question
Share on Google+
12 Replies


P: n/a
is it a proc or a function?
The Oracle proc should call the function.

I'm not sure if you can call an actual function straight from code without
calling it from a proc (stored procedure) and then exposing it that way to
out side code.

"Newbie" <Ne****@discussions.microsoft.com> wrote in message
news:7C**********************************@microsof t.com...
how can i call an oracle function to get data without using a select
statement or stored procedures?

given a project_no, i need to call the function:
ops$sqltime.pa_new_job_no_fn
which will return the next job_no

thanks in advance.

Nov 17 '05 #2

P: n/a

"Newbie" wrote...
how can i call an oracle function to get data without using a select
statement or stored procedures?
That statement was confusing, as a stored function as "a kind of" stored
procedure, from ADO.NET's point of view...
given a project_no, i need to call the function:
ops$sqltime.pa_new_job_no_fn
which will return the next job_no

In short, I'd guess something like this should work...

private int NextJobNo(int project_no)
{
OracleCommand cmd = new OracleCommand();
cmd.Connection = cn; // You have the connection somewhere, don't
you..."
cmd.CommandText = "ops$sqltime.pa_new_job_no_fn";
cmd.CommandType = CommandType.StoredProcedure;

OracleParameter par1 =
new OracleParameter("RETURN_VALUE", OracleDbType.Int32);
par1.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(par1);

par1 = new OracleParameter("PROJECT_NO", OracleDbType.Int32);
par1.Value = project_no;
par1.Direction = ParameterDirection.Input;
cmd.Parameters.Add(par1);

int affectedRows = (int) cmd.ExecuteNonQuery();
int job_no = (int) cmd.Parameters["RETURN_VALUE"].Value;
return job_no;
}
// Bjorn A
Nov 17 '05 #3

P: n/a
i'm not sure but i think it's a function. if i have to use a proc (stored
procedure), then how do i implement everything?

"microsoft.news.com" wrote:
is it a proc or a function?
The Oracle proc should call the function.

I'm not sure if you can call an actual function straight from code without
calling it from a proc (stored procedure) and then exposing it that way to
out side code.

"Newbie" <Ne****@discussions.microsoft.com> wrote in message
news:7C**********************************@microsof t.com...
how can i call an oracle function to get data without using a select
statement or stored procedures?

given a project_no, i need to call the function:
ops$sqltime.pa_new_job_no_fn
which will return the next job_no

thanks in advance.


Nov 17 '05 #4

P: n/a
i'm getting the exception:

Parameter 'p1': No size set for variable length data type: String.

my code follows:

OracleCommand cmd = new OracleCommand();
cmd.Connection = oraConn;
cmd.CommandText = "ops$sqltime.pa_new_job_no_fn";
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter par1 = new OracleParameter();
//i couldnt find the namespace required forOracleDbType.Int32
par1.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(par1);
par1 = new OracleParameter();
par1.Value = projectNo;
par1.Direction = ParameterDirection.Input;
cmd.Parameters.Add(par1);
int affectedRows = (int) cmd.ExecuteNonQuery();
ljn = (int) cmd.Parameters["RETURN_VALUE"].Value;

"Bjorn Abelli" wrote:

"Newbie" wrote...
how can i call an oracle function to get data without using a select
statement or stored procedures?


That statement was confusing, as a stored function as "a kind of" stored
procedure, from ADO.NET's point of view...
given a project_no, i need to call the function:
ops$sqltime.pa_new_job_no_fn
which will return the next job_no

In short, I'd guess something like this should work...

private int NextJobNo(int project_no)
{
OracleCommand cmd = new OracleCommand();
cmd.Connection = cn; // You have the connection somewhere, don't
you..."
cmd.CommandText = "ops$sqltime.pa_new_job_no_fn";
cmd.CommandType = CommandType.StoredProcedure;

OracleParameter par1 =
new OracleParameter("RETURN_VALUE", OracleDbType.Int32);
par1.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(par1);

par1 = new OracleParameter("PROJECT_NO", OracleDbType.Int32);
par1.Value = project_no;
par1.Direction = ParameterDirection.Input;
cmd.Parameters.Add(par1);

int affectedRows = (int) cmd.ExecuteNonQuery();
int job_no = (int) cmd.Parameters["RETURN_VALUE"].Value;
return job_no;
}
// Bjorn A

Nov 17 '05 #5

P: n/a
"Newbie" wrote...

//i couldnt find the namespace required for OracleDbType.Int32

That depends on which provider you're using!

If you don't use Oracle's provider (where the types are defined in
Oracle.DataAccess.Client.OracleDbType), you should be able to use
System.Data.OracleClient.OracleType instead (in Microsoft's provider for
Oracle).

I haven't run any stored functions with it myself (I've only used Oracle's
own provider), so I'm not sure about the behaviour from Microsoft's
provider...

But my guess is that you simply can change OracleDbType to OracleType in
your code... :-)

i'm getting the exception:

Parameter 'p1': No size set for variable length data type: String.


Well, you don't set any types or names for any parameters at all, so I
believe it defaults to String, which *must* be defined with a length (just
as CHAR and VARCHAR2 in Oracle must have lengths)...

With the small changes I mentioned above, you'll probably at least come one
step further...

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.Int32);
par1.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(par1);

// It's best to check what the input parameter *really* is
// named in the stored function, and to use that name...

par1 = new OracleParameter("PROJECT_NO", OracleType.Int32);
par1.Value = projectNo;
par1.Direction = ParameterDirection.Input;
cmd.Parameters.Add(par1);

int affectedRows = (int) cmd.ExecuteNonQuery();

ljn = (int) cmd.Parameters["RETURN_VALUE"].Value;

// Bjorn A
Nov 17 '05 #6

P: n/a
already tried changing OracleDbType.Int32 to OracleType.Int32 but still
getting the same exception

"Bjorn Abelli" wrote:
"Newbie" wrote...

//i couldnt find the namespace required for OracleDbType.Int32

That depends on which provider you're using!

If you don't use Oracle's provider (where the types are defined in
Oracle.DataAccess.Client.OracleDbType), you should be able to use
System.Data.OracleClient.OracleType instead (in Microsoft's provider for
Oracle).

I haven't run any stored functions with it myself (I've only used Oracle's
own provider), so I'm not sure about the behaviour from Microsoft's
provider...

But my guess is that you simply can change OracleDbType to OracleType in
your code... :-)

i'm getting the exception:

Parameter 'p1': No size set for variable length data type: String.


Well, you don't set any types or names for any parameters at all, so I
believe it defaults to String, which *must* be defined with a length (just
as CHAR and VARCHAR2 in Oracle must have lengths)...

With the small changes I mentioned above, you'll probably at least come one
step further...

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.Int32);
par1.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(par1);

// It's best to check what the input parameter *really* is
// named in the stored function, and to use that name...

par1 = new OracleParameter("PROJECT_NO", OracleType.Int32);
par1.Value = projectNo;
par1.Direction = ParameterDirection.Input;
cmd.Parameters.Add(par1);

int affectedRows = (int) cmd.ExecuteNonQuery();

ljn = (int) cmd.Parameters["RETURN_VALUE"].Value;

// Bjorn A

Nov 17 '05 #7

P: n/a

"Newbie" wrote...
already tried changing OracleDbType.Int32 to OracleType.Int32
but still getting the same exception


Did you name the parameters?

What is the *real* name of the input parameter?

Did you use that name?

Are there *more* parameters in the stored function than the input parameter
and the return value?

Did you get the "no length on String" error, even when you used
OracleType.Int32?

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.Int32);
par1.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(par1);

// It's best to check what the input parameter *really* is
// named in the stored function, and to use that name...

par1 = new OracleParameter("PROJECT_NO", OracleType.Int32);
par1.Value = projectNo;
par1.Direction = ParameterDirection.Input;
cmd.Parameters.Add(par1);

int affectedRows = (int) cmd.ExecuteNonQuery();

ljn = (int) cmd.Parameters["RETURN_VALUE"].Value;


// Bjorn A

Nov 17 '05 #8

P: n/a
the real name of the input parameter is project_no which is a string. the
return value should be the job_no. 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

my code follows:

OracleCommand cmd = new OracleCommand();
cmd.Connection = oraConn;
cmd.CommandText = "ops$sqltime.pa_new_job_no_fn";
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter par1 = new OracleParameter();
par1.Direction = ParameterDirection.ReturnValue;
par1.Size = 4000;
cmd.Parameters.Add(par1);
par1 = new OracleParameter();
par1.Value = projectNo;
par1.Direction = ParameterDirection.Input;
par1.Size = 4000;
cmd.Parameters.Add(par1);
int affectedRows = (int) cmd.ExecuteNonQuery();
int ljn = (int) cmd.Parameters["RETURN_VALUE"].Value;
"Bjorn Abelli" wrote:

"Newbie" wrote...
already tried changing OracleDbType.Int32 to OracleType.Int32
but still getting the same exception


Did you name the parameters?

What is the *real* name of the input parameter?

Did you use that name?

Are there *more* parameters in the stored function than the input parameter
and the return value?

Did you get the "no length on String" error, even when you used
OracleType.Int32?

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.Int32);
par1.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(par1);

// It's best to check what the input parameter *really* is
// named in the stored function, and to use that name...

par1 = new OracleParameter("PROJECT_NO", OracleType.Int32);
par1.Value = projectNo;
par1.Direction = ParameterDirection.Input;
cmd.Parameters.Add(par1);

int affectedRows = (int) cmd.ExecuteNonQuery();

ljn = (int) cmd.Parameters["RETURN_VALUE"].Value;


// Bjorn A

Nov 17 '05 #9

P: n/a

"Newbie" wrote...
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
Nov 17 '05 #10

P: n/a
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" wrote:

"Newbie" wrote...
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

Nov 17 '05 #11

P: n/a

"Newbie" wrote...
i made the following changes to varchar but still
get the same exception.
I noticed a small difference between what you have said, and the definition
of the function.
(p_project_no IN varchar2) return varchar2 is


....which in consequence should make the following change in your code for
the input parameter (Oracle can be picky about the parameter names...)

par1 = new OracleParameter("P_PROJECT_NO", OracleType.VarChar);

Hopefully your variable "projectNo" really is a string.

Note that the function also is defined to *return* a string, not a number,
so you should also make that change in your code:

string ljn = cmd.Parameters["RETURN_VALUE"].Value.ToString();

If it still doesn't work, I'd try the following.

- Reduce the parameters' sizes to something less
than 4000 (e.g. 200)

If it still won't work, I'd try Oracle's own Provider instead of
Microsoft's...

// Bjorn A
Nov 17 '05 #12

P: n/a
problem finally fixed. i changed
par1 = new OracleParameter("PROJECT_NO", OracleType.VarChar);
to
par1 = new OracleParameter("P_PROJECT_NO", OracleType.VarChar);
and now it's working. thanks a lot, Bjorn.
"Bjorn Abelli" wrote:

"Newbie" wrote...
i made the following changes to varchar but still
get the same exception.


I noticed a small difference between what you have said, and the definition
of the function.
(p_project_no IN varchar2) return varchar2 is


....which in consequence should make the following change in your code for
the input parameter (Oracle can be picky about the parameter names...)

par1 = new OracleParameter("P_PROJECT_NO", OracleType.VarChar);

Hopefully your variable "projectNo" really is a string.

Note that the function also is defined to *return* a string, not a number,
so you should also make that change in your code:

string ljn = cmd.Parameters["RETURN_VALUE"].Value.ToString();

If it still doesn't work, I'd try the following.

- Reduce the parameters' sizes to something less
than 4000 (e.g. 200)

If it still won't work, I'd try Oracle's own Provider instead of
Microsoft's...

// Bjorn A

Nov 17 '05 #13

This discussion thread is closed

Replies have been disabled for this discussion.