Hi all,
I've just created a function in sqldeveloper Version 18.1.0.095 wich is part of a package :
FUNCTION get\_apms\_rpm (
p\_armo\_id IN NUMBER,
p\_rpm\_h\_l IN VARCHAR2
) RETURN NUMBER AS
v\_rpm1 NUMBER;
v\_rpm2 NUMBER;
BEGIN
BEGIN
WITH apms\_xml AS (
SELECT
amps\_armo\_id,
amps\_polarity\_xml
FROM
(
SELECT
amps\_armo\_id,
amps\_polarity,
amps\_rpm
FROM
arem\_motor\_polarity\_spec
WHERE
amps\_armo\_id = p\_armo\_id
)
PIVOT XML ( MAX ( amps\_rpm )
FOR ( amps\_polarity )
IN ( ANY )
)
) SELECT
extractvalue(amps\_polarity\_xml,'/PivotSet/item\[1\]/column\[2,
extractvalue(amps\_polarity\_xml,'/PivotSet/item\[2\]/column\[2\]')
INTO
v\_rpm1,
v\_rpm2
FROM
apms\_xml;
END;
IF
v\_rpm2 IS NULL
THEN
v\_rpm1 := NULL;
ELSE
CASE
p\_rpm\_h\_l
WHEN 'H' THEN
RETURN v\_rpm1;
WHEN 'L' THEN
RETURN v\_rpm2;
ELSE
RETURN NULL;
END CASE;
END IF;
END get\_apms\_rpm;
When I compile an try to debug it I get this error :
and also I can not see the package body in the explorer
Note that when i do :
select PKAREM_FANSLCTR.GET_APMS_RPM(66,'H') ,
PKAREM\_FANSLCTR.GET\_APMS\_RPM(66,'L')
from dual;
the function works and returns me 985 for H and 491 fo L
When I put some part of the code (the with amps_xml part) in comment it works again :
FUNCTION get\_apms\_rpm (
p\_armo\_id IN NUMBER,
p\_rpm\_h\_l IN VARCHAR2
) RETURN NUMBER AS
v\_rpm1 NUMBER;
v\_rpm2 NUMBER;
BEGIN
/\*BEGIN
WITH apms\_xml AS (
SELECT
amps\_armo\_id,
amps\_polarity\_xml
FROM
(
SELECT
amps\_armo\_id,
amps\_polarity,
amps\_rpm
FROM
arem\_motor\_polarity\_spec
WHERE
amps\_armo\_id = p\_armo\_id
)
PIVOT XML ( MAX ( amps\_rpm )
FOR ( amps\_polarity )
IN ( ANY )
)
) SELECT
extractvalue(amps\_polarity\_xml,'/PivotSet/item\[1\]/column\[2,
extractvalue(amps\_polarity\_xml,'/PivotSet/item\[2\]/column\[2\]')
INTO
v\_rpm1,
v\_rpm2
FROM
apms\_xml;
END;\*/
IF
v\_rpm2 IS NULL
THEN
v\_rpm1 := NULL;
ELSE
CASE
p\_rpm\_h\_l
WHEN 'H' THEN
RETURN v\_rpm1;
WHEN 'L' THEN
RETURN v\_rpm2;
ELSE
RETURN NULL;
END CASE;
END IF;
END get\_apms\_rpm;
and the body is visible again
What is wrong with my function or is it an sqldeveloper bug ?
Is there a workaround ?
Regards
Jean-Yves