get the value of oracle function

Thread view:

chsadaki@hotmail.com - 16 Dec 2005 11:52 GMT

Hi ...
I am writing an application using jbuilderx and oracle9i, i wrote a
function in oracle using pl/sql that returns a number, and i tested it
in oracle sql and everything is ok and it returns a value.
The problem is that i cant get the value from jbuilder, i wrote this
code :

QueryProvider qp = new QueryProvider();
qp.executeStatement(dbname,"{:xx = call
funcname(:nn,:jj)}",parameterRowName);
double d = parameterRowName.getDouble("xx");

but each time i test the d variable it is =0.0

and the remaining code of the function is executed well , I just need
to get the return value

please anybody can help me in this???

thanx
shameram

Reply to this Message

Bjorn Abelli - 16 Dec 2005 13:25 GMT

<chsadaki@hotmail.com> wrote...

> I am writing an application using jbuilderx and oracle9i,
> i wrote a function in oracle using pl/sql that returns a
> number, and i tested it in oracle sql and everything is ok
> and it returns a value. The problem is that i cant get the
> value from jbuilder, i wrote this code :

1. It shouldn't be JBuilder that gets the value,
it should be your Java program...

JBuilder is "only" an IDE, which makes it possible for you to write, compile
and run/debug programs.

// Where's the code for "QueryProvider"?
// If it's what I guess, a class provided by Borland in JBuilder,
// I also guess that you really don't want to use that, but the
// ProcedureProvider instead.

> QueryProvider qp = new QueryProvider();

As it looks, that class has encapsulated all the normally used JDBC
routines, so I guess that "dbname" is a DataSource, and "parameterRowName"
is an array of parameters to the procedure?

> qp.executeStatement(
> dbname,
> "{:xx = call funcname(:nn,:jj)}",
> parameterRowName);

AFAIK, even if ":var" is the standard way to deal with local variables in
Oracle, not all JDBC-drivers does it, but all of them conforms to the old
"ODBC"-way with simple question marks, i.e.

"{? = call funcname(?, ?)}",

But as this isn't even standard JDBC, but a Borland class, maybe you should
read more closely in that documentation, to see what form should be used. If
I read Borland documentation right, it maybe looks more like:

"{:1.xx = call funcname(:2.nn, :3.jj)}",

As you're calling a stored function, it should be doing a prepareCall before
executing.

I don't think that really is happening inside a QueryProvider? From the name
I would rather guess that it doesn't deal with other things than ordinary
queries...

It's more likely that the ProcedureDriver does that...

> double d = parameterRowName.getDouble("xx");

If the driver really works with the Oracle standard of variable naming (:),
that should also be reflected when fetching values after execution:

parameterRowName.getDouble(":xx");

...or if you use the name setting indicated of Borland pages:

parameterRowName.getDouble(":1.xx");

However, it seems also essential that the parameter list is initiated
properly, i.e. that "parameterRowName" contains all three parameters (return
value, arg 1 and arg 2).

> please anybody can help me in this???

You need to provide more information, but you would possibly get better
answers in a specific JBuilder group as you're using Borlands class
libraries, e.g.:

news://newsgroups.borland.com/borland.public.jbuilder.database

// Bjorn A

Reply to this Message

chsadaki@hotmail.com - 19 Dec 2005 16:39 GMT

First of all thanx for ur answer.
I tried to use some of ur information in may application ,
but I found another way to get the value , it is not very clever but it
is working.
I wrote a procedure instead the function and i use an in out parameter
donc taraaa....
it is not very clever I know
thanx anyway

Reply to this Message

get the value of oracle function

Free Magazines