PL/SQL

[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.

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;

 

[sem]

Try to enclose your 'problem' statement into begin..end and catch an error:

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

 

[NitinJain]

Hello Sem,
Sorry to tell you that I am still getting the same error.

Can you suggest me something else?

Regards

 

[sem]

You CAN NOT get the SAME error if you enclosed ALL the suspicious statements. Try to wrap each select statement (changing, probably, exception handler)