how to call an oracle function

[Newbie]

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.

[microsoft.news.com]

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****@discuss ions.microsoft. com> wrote in message
news:7C******** *************** ***********@mic rosoft.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.

[Bjorn Abelli]

"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.Sto redProcedure;

OracleParameter par1 =
new OracleParameter ("RETURN_VALUE" , OracleDbType.In t32);
par1.Direction = ParameterDirect ion.ReturnValue ;
cmd.Parameters. Add(par1);

par1 = new OracleParameter ("PROJECT_NO ", OracleDbType.In t32);
par1.Value = project_no;
par1.Direction = ParameterDirect ion.Input;
cmd.Parameters. Add(par1);

int affectedRows = (int) cmd.ExecuteNonQ uery();
int job_no = (int) cmd.Parameters["RETURN_VAL UE"].Value;
return job_no;
}
// Bjorn A

[Newbie]

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****@discuss ions.microsoft. com> wrote in message
news:7C******** *************** ***********@mic rosoft.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.

[Newbie]

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.Sto redProcedure;
OracleParameter par1 = new OracleParameter ();
//i couldnt find the namespace required forOracleDbType .Int32
par1.Direction = ParameterDirect ion.ReturnValue ;
cmd.Parameters. Add(par1);
par1 = new OracleParameter ();
par1.Value = projectNo;
par1.Direction = ParameterDirect ion.Input;
cmd.Parameters. Add(par1);
int affectedRows = (int) cmd.ExecuteNonQ uery();
ljn = (int) cmd.Parameters["RETURN_VAL UE"].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.Sto redProcedure;

OracleParameter par1 =
new OracleParameter ("RETURN_VALUE" , OracleDbType.In t32);
par1.Direction = ParameterDirect ion.ReturnValue ;
cmd.Parameters. Add(par1);

par1 = new OracleParameter ("PROJECT_NO ", OracleDbType.In t32);
par1.Value = project_no;
par1.Direction = ParameterDirect ion.Input;
cmd.Parameters. Add(par1);

int affectedRows = (int) cmd.ExecuteNonQ uery();
int job_no = (int) cmd.Parameters["RETURN_VAL UE"].Value;
return job_no;
}
// Bjorn A

[Bjorn Abelli]

"Newbie" wrote...

//i couldnt find the namespace required for OracleDbType.In t32

That depends on which provider you're using!

If you don't use Oracle's provider (where the types are defined in
Oracle.DataAcce ss.Client.Oracl eDbType), you should be able to use
System.Data.Ora cleClient.Oracl eType 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.Sto redProcedure;

OracleParameter par1 =
new OracleParameter ("RETURN_VALUE" , OracleType.Int3 2);
par1.Direction = ParameterDirect ion.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.Int3 2);
par1.Value = projectNo;
par1.Direction = ParameterDirect ion.Input;
cmd.Parameters. Add(par1);

int affectedRows = (int) cmd.ExecuteNonQ uery();

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

// Bjorn A

[Newbie]

already tried changing OracleDbType.In t32 to OracleType.Int3 2 but still
getting the same exception

"Bjorn Abelli" wrote:

"Newbie" wrote...

//i couldnt find the namespace required for OracleDbType.In t32

That depends on which provider you're using!

If you don't use Oracle's provider (where the types are defined in
Oracle.DataAcce ss.Client.Oracl eDbType), you should be able to use
System.Data.Ora cleClient.Oracl eType 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.Sto redProcedure;

OracleParameter par1 =
new OracleParameter ("RETURN_VALUE" , OracleType.Int3 2);
par1.Direction = ParameterDirect ion.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.Int3 2);
par1.Value = projectNo;
par1.Direction = ParameterDirect ion.Input;
cmd.Parameters. Add(par1);

int affectedRows = (int) cmd.ExecuteNonQ uery();

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

// Bjorn A

[Bjorn Abelli]

"Newbie" wrote...

already tried changing OracleDbType.In t32 to OracleType.Int3 2
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.Int3 2?

OracleCommand cmd = new OracleCommand() ;
cmd.Connection = oraConn;
cmd.CommandText = "ops$sqltime.pa _new_job_no_fn" ;
cmd.CommandType = CommandType.Sto redProcedure;

OracleParameter par1 =
new OracleParameter ("RETURN_VALUE" , OracleType.Int3 2);
par1.Direction = ParameterDirect ion.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.Int3 2);
par1.Value = projectNo;
par1.Direction = ParameterDirect ion.Input;
cmd.Parameters. Add(par1);

int affectedRows = (int) cmd.ExecuteNonQ uery();

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

// Bjorn A

[Newbie]

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.Sto redProcedure;
OracleParameter par1 = new OracleParameter ();
par1.Direction = ParameterDirect ion.ReturnValue ;
par1.Size = 4000;
cmd.Parameters. Add(par1);
par1 = new OracleParameter ();
par1.Value = projectNo;
par1.Direction = ParameterDirect ion.Input;
par1.Size = 4000;
cmd.Parameters. Add(par1);
int affectedRows = (int) cmd.ExecuteNonQ uery();
int ljn = (int) cmd.Parameters["RETURN_VAL UE"].Value;
"Bjorn Abelli" wrote:

"Newbie" wrote...

already tried changing OracleDbType.In t32 to OracleType.Int3 2
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.Int3 2?

OracleCommand cmd = new OracleCommand() ;
cmd.Connection = oraConn;
cmd.CommandText = "ops$sqltime.pa _new_job_no_fn" ;
cmd.CommandType = CommandType.Sto redProcedure;

OracleParameter par1 =
new OracleParameter ("RETURN_VALUE" , OracleType.Int3 2);
par1.Direction = ParameterDirect ion.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.Int3 2);
par1.Value = projectNo;
par1.Direction = ParameterDirect ion.Input;
cmd.Parameters. Add(par1);

int affectedRows = (int) cmd.ExecuteNonQ uery();

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

// Bjorn A

[Bjorn Abelli]

"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.Sto redProcedure;

// 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 = ParameterDirect ion.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 = ParameterDirect ion.Input;
cmd.Parameters. Add(par1);

int affectedRows = (int) cmd.ExecuteNonQ uery();

// Possibly this hence would be a String as well?

string ljn = cmd.Parameters["RETURN_VAL UE"].Value.ToString ();
// Bjorn A