Oracle stored functions

[HarryT]

Re: Oracle stored functions

yes, i know the above stored function REFCURSOR_EMP runs in SQLPLUS with:

var refcur refcursor
exec :refcur := REFCURSOR_EMP(1000, 10000);
print refcur

[Peter.Wayne]

Re: Oracle stored functions

How about trying this, then?

sqlStatement = <<%sql%
SELECT COUNT_EMP(:from, :to)
%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("l_count")

I think it's just a matter of figuring out how to call the function. How is it done in php for Oracle?

[HarryT]

Re: Oracle stored functions

Oracle's syntax to execute only a function in a select list is:
sqlStatement = <<%sql%
SELECT COUNT_EMP(:from, :to) FROM DUAL
%sql%

the new error is:
"
Script:anzahl line:32
employeeCount=rs.data("l_count")
Column not found while getting result set data column.'
"

Got this coding running with Oracle 10g and AlphaFive V10:
----
dim conn as SQL::Connection
dim sql as C
dim vEmpNo as C
dim args as SQL::Arguments
vEmpNo = ui_get_text("vEmpNo", "Show Emp")
sql = "select * from emp where empno >= :empno"
if .not. conn.open("::Name::MyOracleConnect")
ui_msg_box("Error", conn.CallResult.text)
end
end if
if .not. args.Set("empno", vEmpNo)
end
end if
if .not. conn.execute(sql, args)
ui_msg_box("Error", conn.CallResult.text)
end
end if
sql_resultset_preview(conn.resultset)
---

changed SQL to:
sql = "select count_emp(:empno,10000) from dual"
And this was als running.

But how to EXECUTE/CALL an Oracle "stored function/procedure"
with IN/OUT parameters, RETURN values and REFCURSOR?

e.g. "return:= count_emp(:from_IN,:to_IN,:ename_OUT)"
for "return" values of type NUMBER,CHAR,REFCURSOR

[Peter.Wayne]

Re: Oracle stored functions

I just checked syntax for calling an Oracle function via php, and it looks like the syntax is much the same as the Alpha syntax:

$query =
 "SELECT employee_id,
         substr(first_name,1,1) || '. '|| last_name as employee_name,
         hire_date,
         to_char(salary, '9999G999D99') as salary,
         nvl(commission_pct,0) as commission_pct,
         to_char([COLOR="Red"]calc_remuneration[/COLOR](salary, commission_pct),'9999G999D99')
           [COLOR="Red"]as remuneration[/COLOR]
  FROM employees
  WHERE department_id = :did
  ORDER BY employee_id ASC";

In this code (from Oracle.uk), calc_remuneration() is an Oracle function, ans the result is redefined "as renumeration" for PHP.
So your function should work for
SELECT EMP_COUNT(1,100) as employeeCount

As I wrote before, I don't have Oracle to test, but stored functions work fine in MySQL, and I can't think why they should not work in Oracle.

[HarryT]

Re: Oracle stored functions

Peter,
please see my last post.

You are right:
"sql = "select count_emp(:empno,10000) from dual"
is running, but with "FROM DUAL" for correct Oracle syntax.

This is a solution by calling the function in a "select list".
But there must be also a solution with a "direct CALL" like
return := called_function(p_IN,p_OUT)

with IN/OUT parameters and return type of NUMBER,CHAR,BOOLEAN,REFCURSOR

[HarryT]

Re: Oracle stored functions

Please note,
a function can return only one value but can take more than one IN parameter but no OUT parameters.

If you use:
sql = "select ename_emp(:empno,:ename) from dual"
with :empno as IN and :ename as OUT parameter
Alpha Five errors with:
'ORA-06572: Function ENAME_EMP has out arguments'

Explanation of the Oracle error:
Error: ORA 6572
Text: function <name> has out arguments
--------------------------------------------------------------------------------
Cause: A SQL statement references either a packaged or a stand-alone PL/SQL function that contains an OUT parameter in its argument list.
PL/SQL functions referenced by SQL statements must not contain the OUT parameter.
Action: Recreate the PL/SQL function without the OUT parameter in the argument list.

That's why you need a call like:
SQLstatement = <<%sql%
:return_value := called_function(:IN_param,:OUT_param,...)
%sql%

How could you do it in Xbasic with AlphaFive V10?

[HarryT]

Re: Oracle stored functions

Sorry, it should read:
a function "IN A SELECT STATEMENT" can return only one value but can take more than one IN parameter but no OUT parameters.

How to call a Oracle "stored function" with IN/OUT parameters in Alpha Five V10?
sqlStatment = ":return_value := called_function :IN_param,:OUT_param,...)"

Also for :return_value of type REFCURSOR.

No one?