Hello! I have been facing followin

[NitinJain]

Hello!
I have been facing following problem. The setAttributeNode function gets created successfully, but while testing if the parameter values are passed, I get the listed error:
ORA-01403: no data found
for the following select statement:

SELECT child_node_id
INTO currAttr
FROM t_arch
WHERE parent_node_id = thisNode AND
child_node_id IN (
SELECT id
FROM t_Attr
WHERE name_id = (
SELECT name_id
FROM t_Attr
WHERE id = newAttr
) );

I have found out that this statement doesn't retrieves any row, due to which this error is thrown. I was facing same problem in other select statements in this function. I could
solve those by using Count(*) as the retrieved value was not used further. But in the above case "currAttr" is further used in the function.


I was suggested by Sem to add Begin--End as shown:
function ...
...
begin
....
begin

select .. into ..;
exception
when no_data_found then currAttr := null;
when others then ...;

end;
...
end;
I have also found out in one of the Oracle documentations:

"However, if no rows are returned, PL/SQL raises NO_DATA_FOUND unless the SELECT statement called a SQL aggregate function such as AVG or SUM. (SQL aggregate functions always return a value or a null. So, a SELECT INTO statement that calls an aggregate function never raises NO_DATA_FOUND.) "

Following is the full function.


CREATE OR REPLACE FUNCTION setAttributeNode
(thisNode NUMBER, newAttr NUMBER )
RETURN NUMBER IS


CURSOR cr_archId IS

SELECT sq_arch.NEXTVAL FROM DUAL;


nodeIsElement BOOLEAN;
nodeIsAttr BOOLEAN;
docId NUMBER;--BOOLEAN;
archId NUMBER;
parentId NUMBER;
currAttr NUMBER; -- current Attribute of
thisNode that
-- has the same name as newAttr, if
-- one exists
BEGIN

nodeIsElement := nodeTypeMatches (thisNode, 1);

--INVALID_ACCESS_ERR

IF NOT nodeIsElement THEN

DOMException(15);

ELSIF nodeIsElement IS NULL THEN

raise_application_error (-20212, 'Node does not
exists');

END IF;

nodeIsAttr := nodeTypeMatches (newAttr, 2);

-- INVALID_ACCESS_ERR

IF NOT nodeIsAttr THEN

DOMEXception(15);

ELSIF nodeIsAttr IS NULL THEN

raise_application_error (-20212, 'Node does not
exists');

END IF;

-- WRONG_DOCUMENT_ERR: Raised if newAttr
was created from a different
document than the one that created the element.


SELECT COUNT(*) --ownerDocumentId
INTO docId
FROM t_Node element, t_Node attr
WHERE element.ownerDocumentId =
attr.ownerDocumentId AND
element.id = thisNode AND
attr.id = newAttr;
IF docId = 0 THEN

DOMException(4);

END IF;

-- INUSE_ATTRIBUTE_ERR: Raised if newAttr is
already an attribute of
another Element object. The DOM user must explicitly
clone Attr nodes to re-use
them in other elements.



SELECT COUNT(*) --parent_node_id
INTO parentId
FROM t_arch
WHERE parent_node_id != thisNode AND
child_node_id = newAttr;

IF parentId != 0 THEN

DOMException(10);

END IF;


-- see if thisNode has an attribute with newAttr''s
name

SELECT child_node_id
INTO currAttr
FROM t_arch
WHERE parent_node_id = thisNode AND
child_node_id IN (
SELECT id
FROM t_Attr
WHERE name_id = (
SELECT name_id
FROM t_Attr
WHERE id = newAttr
)
);



IF currAttr = newAttr THEN

-- nothing to do - newAttr is already an attribute of
thisNode
RETURN newAttr;

ELSIF currAttr IS NULL THEN
-- thisNode doesn''t have an attribute with this name
-- newAttr has no parent - INUSE_ERROR would
have been thrown
-- create new arch

OPEN cr_archId;
FETCH cr_archId INTO archId;
CLOSE cr_archId;

INSERT INTO t_arch
( id,parent_node_id,
child_node_id, parent_ord_no, child_ord_no,
type_id,specified_in_dom)
(SELECT archId, thisNode, newAttr,
1, 0, id, 1
FROM t_arch_type
WHERE parent_node_type_id = 1 AND
child_node_type_id = 2);

RETURN newAttr;


ELSE

-- if none of the above is true, thisNode has an attribute
with this
name
-- replace the attribute
-- consider garbage-collecting currAttr - it is now
completely
unreachable

UPDATE t_arch
SET child_node_id = newAttr
WHERE parent_node_id = thisNode AND
child_node_id = currAttr;

RETURN newAttr;

END IF;

END setAttributeNode;