CODEWITHSUNDEEP
sqlxmloracle-databaseplsql
ADTC
ADTC

Reputation: 10086

XmlRoot, XmlElement, InsertChildXml in PL/SQL function gives PLS-00201 identifier must be declared

Note: Kindly do not flag this as a duplicate of a similar question. I am only asking this because the other questions do not address the exact same situtaion, and their answers do not provide a working solution.

If I use an XML related function like INSERTCHILDXML in a select statement like the following, it works completely fine:

SELECT INSERTCHILDXML( ... ) FROM DUAL;

But if I try to use this in PL/SQL as the following, it gives error PLS-00201: identifier 'INSERTCHILDXML' must be declared.

CREATE FUNCTION ...
    RETURN XMLType
AS
BEGIN
    RETURN INSERTCHILDXML( ... );
EXCEPTION
    ...
END;

Functions having this problem: XMLROOT, XMLELEMENT, INSERTCHILDXML

There are scores of results when searching the web about the error. Many of them tell me to CREATE PUBLIC SYNONYM and GRANT EXECUTE on the problematic identifier. But it doesn't work.

Public synonym can be created for same name, but granting execute (on any schema) fails with:

ORA-01775: looping chain of synonyms

If I try to use a different synonym name, upon trying to grant execute, I get:

ORA-00980: synonym translation is no longer valid

How can I resolve the issue and successfully use the listed functions in my new PL/SQL function?

Upvotes: 0

Views: 2464

Answers (1)

ADTC
ADTC

Reputation: 10086

After taking a short break (and looking at some more of similar questions), I reached an epiphany.

Apparently the reason it did not work is because I was trying to RETURN the function output:

RETURN INSERTCHILDXML( ... );

What I should really be doing is push the output into a variable, then return the variable:

CREATE FUNCTION ...
    RETURN XMLType
AS
    temp XMLType;
BEGIN
    SELECT INSERTCHILDXML( ... ) INTO temp FROM DUAL;
    RETURN temp;
EXCEPTION
    ...
END;

Note: There is no need to create synonyms or grant execute permissions. If you have performed any of that, you can undo it.

Additional findings:

You cannot assign the function output to a variable. It must be pushed using SELECT INTO. You can however use the XMLType type and its methods.

temp := XMLType( ... ) -- works fine
temp := XMLELEMENT ( ... ) -- does not work

Usage in conditionals is flaky. CASE WHEN tolerates it while IF doesn't.

CASE WHEN EXISTSNODE( ... ) = 1 THEN -- works fine

IF EXISTSNODE( ... ) = 1 THEN -- does not work

You can circumvent the problem with IF by using a variable:

SELECT EXISTSNODE( ... ) INTO exists_node FROM DUAL;
IF exist_node = 1 THEN -- works

Upvotes: 3

Related Questions