Home » SQL & PL/SQL » SQL & PL/SQL » Create Function
Create Function [message #2180] Wed, 26 June 2002 05:21 Go to next message
Nitin Jain
Messages: 7
Registered: October 2001
Junior Member
I want to use previously created function in a new function.
For Example:

CREATE OR REPLACE FUNCTION nodeExists (nodeId NUMBER) RETURN BOOLEAN IS
nOfNodesFound NUMBER := 0;
BEGIN
SELECT COUNT (id)
INTO nOfNodesFound
FROM t_Node
WHERE id = nodeId;

IF nOfNodesFound = 0 THEN
raise_application_error (-20201, 'Node ' || nodeId || ' does not exist');
RETURN FALSE;

IF nOfNodesFound > 1 THEN
raise_application_error (-20202, 'Corrupted data in the database: Node ' || nodeId || ' is found ' || nOfNodesFound || 'times; expected only once');
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;

END nodeExists;

CREATE OR REPLACE FUNCTION nodeTypeMatches (nodeId NUMBER, expectedNodeType NUMBER) RETURN BOOLEAN IS
actualNodeType Number;
BEGIN
IF NOT nodeExists (nodeId) THEN
RETURN FALSE;

ELSE
SELECT nodeType
INTO actualNodeType
FROM t_Node
WHERE id = nodeId;

IF expectedNodeType != actualNodeType THEN
raise_applictiopn_error (-20203, 'Type mismatch: Node is of the type ' || actualNodeType || ' ,expected ' || expectedNodeType);
RETURN FALSE;
ELSE
RETURN TRUE;

END IF;
END IF;

END nodeTypeMatches;

I get the following error:

Errors for FUNCTION NODETYPEMATCHES:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/2 PL/SQL: Statement ignored
4/9 PLS-00201: identifier 'NODEEXISTS' must be declared
Re: Create Function [message #2183 is a reply to message #2180] Wed, 26 June 2002 06:41 Go to previous message
Balaji
Messages: 102
Registered: October 2000
Senior Member
substitute the statement
if not nodeexists(nodeid) with
select nodeexists(nodeid)into some_variable from dual

if some_variable = true then
else

end if
or take the argument in to a variable and pass the variable when calling the function.

balaji

rgds
Balaji
Previous Topic: Update Statement
Next Topic: Ref Cursors
Goto Forum:
  


Current Time: Fri Oct 18 14:49:30 CDT 2019