Oracle stored functions

[HarryT]

I'm evaluating AlphaFiveV10 with the 30 day trial.
How to use this small Oracle 10g "stored function" with AlphaDAO scripting?

create or replace FUNCTION COUNT_EMP
(
p_from IN NUMBER,
p_to IN NUMBER
)
RETURN number AS
l_count number;
BEGIN
select count(*)
into l_count
from emp
where empno between p_from and p_to;
return (l_count);
END COUNT_EMP;

Tried with:
"Execute Select Query on a SQL database"
Connection string "MyOracleConnect" (tested with succeed)
SQL Statement type "Stored Procedure"
Stored Procedure Name "COUNT_EMP"
Arguments: p_from,p_to

Button "Run" says:
"ORA-00900 invalid SQL statement"

"View XbBasic" output:

DIM cn as SQL::Connection
dim flagResult as l
flagResult = cn.open("::Name::MyOracleConnect")
if flagResult = .f. then
ui_msg_box("Error","Could not connect to database. Error reported was: " + crlf() + cn.CallResult.text)
end
end if
cn.PortableSQLEnabled = .f.
dim sqlStatement as c
sqlStatement = <<%sql%
COUNT_EMP
%sql%
'Execute the Query
flagResult = cn.Execute(sqlStatement)
if flagResult = .f. then
ui_msg_box("Error",cn.CallResult.text)
cn.close()
end
end if
dim rs as sql::resultset
rs = cn.ResultSet
sql_resultset_preview(rs,1000,"Preview Query Results",cn)
'Now, close the connection
cn.close()

Any solution?

Thanks

[Keith Hubert]

Re: Oracle stored functions

Hi Harry,

Welcome to Alpha.

I'm not an Oracle expert but my question back to you is, did you write the Stored Function or did you let Alpha write it for you?

[Stan Mathews]

Re: Oracle stored functions

See this thread on running stored procedures.

[HarryT]

Re: Oracle stored functions

Thanks,

changed coding to:

p_from=1
p_to=100
sqlStatement = "COUNT_EMP" + "'"+p_from+"','"+p_to+"'"
flagResult = cn.Execute(sqlStatement)

Same error
"ORA-00900 invalid SQL statement"

Please see above, COUNT_EMP is a Oracle STORED FUNCTION with a return value!

[brackley]

Re: Oracle stored functions

You have not defined your return value

[brackley]

Re: Oracle stored functions

What about an extra space after the function name and before the first param?

[Peter.Wayne]

Re: Oracle stored functions

I think you should have

DIM cn as SQL::Connection
dim flagResult as l
flagResult = cn.open("::Name::MyOracleConnect")
if flagResult = .f. then
ui_msg_box("Error","Could not connect to database. Error reported was: " + crlf() + cn.CallResult.text)
end
end if
cn.PortableSQLEnabled = .f.
dim sqlStatement as c
sqlStatement = <<%sql%
SELECT COUNT_EMP(:from, :to) AS employeeCount
%sql%
' these employee numbers are just examples
p_from="1"
p_to="100"  

dim args as sql::arguments
args.set("from",p_from)
args.set("to",p_to)
'Execute the Query
flagResult = cn.Execute(sqlStatement,args)
if flagResult = .f. then
ui_msg_box("Error",cn.CallResult.text)
cn.close()
end
end if
dim rs as sql::resultset
rs = cn.ResultSet
employeeCount=rs.data("employeeCount")
'Now, close the connection
cn.close()

[HarryT]

Re: Oracle stored functions

Thanks Brent,
- the first coding was made automatically by AlphaFive (not me)
- is there a problem with extra spaces ?

[HarryT]

Re: Oracle stored functions

Many thanks Peter,
tried your code and get the error:
"ORA-00923: FROM keyword not found where expected"

with Oracle there is no:
"SELECT COUNT_EMP(1, 100) AS employeeCount"

changed to:
"SELECT COUNT_EMP(1, 100) employeeCount from dual"
but now there is no answer/message after "Run"

Want to change the code by "Design" and the error is now:
Script:IU_DLG_BOX:@=a5_script_editor_internal line:700
Named modeless dialog not found
???
After restart of Alpha the error disappeared.

Dissapointing, i have only a few days left to evaluate AlphaFive V10 and couldn't get a small Oracle "stored function" running.
Don't forget i'm new to (codeless) Alpha and XBasic.

We use Oracle Forms 6i Client/Server (> 1000 Forms/Reports) and have a lot of PL/SQL coding in our Oracle 10g Database.
Without a well documented and stable interface to Oracle it is hard to do anything with Alpha Five. It's essential for us.

I won't give up yet, because Alpha Five V10 seems to be as productive as Oracle Forms.

[brackley]

Re: Oracle stored functions

Sorry HarryT, I saw:

sqlStatement = "COUNT_EMP" + "'"+p_from+"','"+p_to+"'"


And that would have translated to COUNT plus whatever was in p_from, you need a space or as it was pointed out a "(" , which I forgot :(

I am evaluating Alpha as a front end to Oracle as well, a tad frustrating. I actually prefer Oracle forms 6 (before JInitiator etc) and actually Forms 4.5 to be honest (Yes, I am dating myself)

But I like what Alpha is trying to accomplish, have you tried REF CURSOR's yet returning a result set?

[Peter.Wayne]

Re: Oracle stored functions

I don't have Oracle but I wonder whether your stored function is syntactically correct. It is strange to me that it says
RETURN number as
l_count number

in the definition.
Anyhow, I have a few stored functions in MySQL that work fine.
Here is one:

DELIMITER $$

DROP FUNCTION IF EXISTS `wms`.`age` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `age`(dob date) RETURNS int(11)
    DETERMINISTIC
begin
declare y1 integer;
declare m1 integer;
declare d1 integer;
declare y2 integer;
declare m2 integer;
declare d2 integer;
declare age2 integer;

set y1=year(dob);
set m1=month(dob);
set d1=day(dob);

set y2=year(current_date());
set m2=month(current_date());
set d2=day(current_date());

set age2=y2-y1;
if m2<m1 then
  set age2=age2-1;
end if;

if m1=m2 and d2<d1 then
  set age2=age2-1;
end if;

return age2;


end $$

DELIMITER ;

and then to use it from Alpha Five:

dim cnw as sql::connetion
cnw.open("::name::wms")
sql="SELECT age('19800101') as result"

? cnw.execute(sql)
= .T.

rs=cnw.ResultSet 

? rs.data("result")
= 30

[brackley]

Re: Oracle stored functions

It is, I compiled it on my Oracle db here

[HarryT]

Re: Oracle stored functions

Hi Brent,
we stay still at Oracle Forms6i because it's Client/Server, very productice, stable and fast.
No need for Web Forms 9i-11g with a full blown App Server!
Till today Forms6i C/S with MS Windows Terminal Server or Citrix MetaFrame is for us the better solution.

> have you tried REF CURSOR's yet returning a result set

No, but refcursor would have been my next question to Xbasic and Alpha:
(also what's about OUT parameters?)
------- PL/SQL stored function
create or replace
FUNCTION REFCURSOR_EMP
(p_from in number,
p_to in number)
RETURN SYS_REFCURSOR
AS
l_rc SYS_REFCURSOR;
BEGIN
open l_rc for
select *
from emp
where empno between p_from and p_to;
return l_rc;
END REFCURSOR_EMP;
-------

Can any AlphaFive+Xbasic+Oracle specialist help?

[HarryT]

Re: Oracle stored functions

Peter,
the stored function with
"RETURN number AS
l_count integer;"
is correct Oracle Syntax.

"RETURN number AS" declares the return TYPE
and
"l_count number" the return VARIABLE

[brackley]

Re: Oracle stored functions

You lost me..

But here goes ;)

The out parameter that is returned by the function must be defined in alpha somewhere to accept it....me thinks.

If you wish to use your ref cursor in sql plus you would have to define it first then call it

variable v1 refcursor.reftype
execute :v1 := getfuncblahblah(parms)
print v1