CODEWITHSUNDEEP
oracle-databaseprocedure
Joao Heleno
Joao Heleno

Reputation: 370

Invalid identifier in Oracle SQL procedure

I'm getting Error(8,3): PL/SQL: SQL Statement ignored and Error(8,10): PL/SQL: ORA-00904: "XMLELEMENT": invalid identifier in the following procedure:

    CREATE OR REPLACE PROCEDURE GETXMLTREE
    (
      p_ESTABELECIMENTOID IN NUMBER  
    , MYXML OUT VARCHAR2  
    )
    AS
    BEGIN
    SELECT XMLELEMENT --line 8
    into MYXML
        ("root",
          (select dbms_xmlgen.getxmltype 
            (dbms_xmlgen.newcontextfromhierarchy
              ('SELECT level,
                XMLElement("item",XMLAttributes(osusr_kfa_estabele.id as "id"),
                  XMLElement("content", XMLElement("name", nomecompleto)))          
                  from osusr_kfa_estabele
                  start with osusr_kfa_estabele.id = p_ESTABELECIMENTOID 
                  connect by nocycle prior osusr_kfa_estabele.id = osusr_kfa_estabele.estabelecimetnopaiid' ))
          from dual))
    from dual;

    END GETXMLTREE;

Any clues to why this is happening? Thanks

Upvotes: 0

Views: 2959

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132580

Your INTO is in the wrong place. Try:

SELECT XMLELEMENT --line 8
        ("root",
          (select dbms_xmlgen.getxmltype 
            (dbms_xmlgen.newcontextfromhierarchy
              ('SELECT level,
                XMLElement("item",XMLAttributes(osusr_kfa_estabele.id as "id"),
                  XMLElement("content", XMLElement("name", nomecompleto)))          
                  from osusr_kfa_estabele
                  start with osusr_kfa_estabele.id = p_ESTABELECIMENTOID 
                  connect by nocycle prior osusr_kfa_estabele.id = osusr_kfa_estabele.estabelecimetnopaiid' ))
          from dual))
    into MYXML
    from dual;

Upvotes: 6

Related Questions