| How to get return from out parameter of function [message #35646] |
Wed, 03 October 2001 18:09  |
George
Messages: 68
Registered: April 2001
|
Member |
|
|
I have a function like below, how should I call the function and catch the out parameter and return value?
CREATE OR REPLACE function totalNo2 (
cnt OUT NUMBER,
name IN VARCHAR2)
RETURN INTEGER IS
totalemp NUMBER(10);
sql_stmt VARCHAR2(100);
BEGIN
sql_stmt := 'SELECT count(*) FROM emp e, dept d where d.dname = '''||name||''' ';
EXECUTE IMMEDIATE sql_stmt INTO totalemp;
cnt := totalemp;
RETURN totalemp;
END ;
SQL> EXEC :cv := totalNo2( 'SALES');
BEGIN :cv := totalNo2( 'SALES'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 14:
PLS-00306: wrong number or types of arguments in call to 'TOTALNO2'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Thanks
----------------------------------------------------------------------
|
|
|
|
| Re: How to get return from out parameter of function [message #35647 is a reply to message #35646] |
Wed, 03 October 2001 18:33  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Several things we need to change here.
1) You should not use OUT parameters with functions - they should simply return a value, and that value should not be passed through a parameter.
2) Why use dynamic SQL in the function? Your literal SQL will be parsed over and over - no execution plan will be reused.
3) Your SQL statement needs to join emp and dept.
So, here's a reworked version:
create or replace function TotalNo2
(p_dept_name in dept.dname%type)
return integer
is
v_count pls_integer;
begin
select count(*)
into v_count
from dept d, emp e
where d.dname = p_dept_name
and e.dept_id = d.dept_id;
return (v_count);
end;
/
Then, in SQL*Plus:
user@gdla.world> var x number;
user@gdla.world> exec :x := totalno2('SALES');
PL/SQL procedure successfully completed.
user@gdla.world> print x
X
---------
8
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
