| how can I test this function that compiles but returns boolean type? [message #38193] |
Sat, 30 March 2002 16:27  |
Sid
Messages: 38
Registered: May 1999
|
Member |
|
|
I have created a function that returns a boolean value. I want the function to search a department table and find if deptno exist in the table. If the department exist then boolean true is returned else boolean false value is returned.
Here is the function:
CREATE OR REPLACE FUNCTION valid_deptno
(v_deptno IN dept.deptno%TYPE)
RETURN BOOLEAN
IS
CURSOR c_deptno IS
SELECT deptno from dept;
BEGIN
FOR i IN c_deptno LOOP
IF i.deptno = v_deptno THEN
RETURN (TRUE);
END IF;
END LOOP;
-- WILL ONLY GET HERE IF DEPTNO IS NOT FOUND.
RETURN (FALSE);
END valid_deptno;
/
When I go to test the function.
SQL> VARIABLE g_boolean BOOLEAN
Usage: VAR[[IABLE]] [[ variable [[ NUMBER | CHAR | CHAR (n) | VARCHAR2 (n)
NCHAR | NCHAR (n) | NVARCHAR2 (n)
CLOB | NCLOB | REFCURSOR ]] ]]
SQL reminds me that BOOLEAN value is not supported.
I would like to keep the same shell if possible for the valid_deptno function, that is maintain the return value of BOOLEAN simply because itr seems logically correct.
I have tried assigning the return value of the fucntion to another data type for example:
SQL> VARIABLE g_string VARCHAR2
SQL> EXECUTE :g_string := valid_deptno(10);
begin :g_string := valid_deptno(10); end;
*
ERROR at line 1:
ORA-06550: line 1, column 20:
PLS-00382: expression is of wrong type
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
This result in wrong type error.
What can I do??
|
|
|
|
| Re: how can I test this function that compiles but returns boolean type? [message #38195 is a reply to message #38193] |
Sat, 30 March 2002 21:30  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
First of all, you shouldn't loop through every row in department looking for a match - just look specifically for the one deptno.
CREATE OR REPLACE FUNCTION valid_deptno
(v_deptno IN dept.deptno%TYPE)
RETURN BOOLEAN
IS
v_result dept.deptno%type;
BEGIN
select deptno
into v_result
from dept
where deptno = v_deptno;
return (true);
exception
when no_data_found then
return (false);
end valid_deptno;
Now, as you know, you won't be able to use this function in a SQL statement. But, if you want to test this function in PL/SQL:
begin
if valid_deptno(99) then
dbms_output.put_line('Valid');
else
dbms_output.put_line('Invalid');
end if;
end; |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
