Home » SQL & PL/SQL » SQL & PL/SQL » How to get return from out parameter of function
How to get return from out parameter of function [message #35646] Wed, 03 October 2001 18:09 Go to next message
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 Go to previous message
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

----------------------------------------------------------------------
Previous Topic: DBMS_SHARED_POOL.UNKEEP
Next Topic: how to pass varchar2 in sp
Goto Forum:
  


Current Time: Thu Mar 28 16:52:58 CDT 2024