4 Replies Latest reply: Jun 20, 2013 4:26 PM by Andylk RSS

    XMLELEMENT

    Andylk

      I have the following:

      DECLARE

       

       

         CURSOR c_xml

         IS

            SELECT XMLELEMENT ("CHECKINOUT"

                              ,xmlattributes (property_id AS "PROPERTY"

                                             ,msgid AS "ID"

                                             ,status AS "STATUS"

                                             ,lead_name AS "OCCUPANT"

                                             ,email AS "email"

                                             ,unit_ext AS "EXTENSION"

                                             ,folio_id AS "FOLIO")) AS tosend

            FROM   TABLE (pm_pbx_systems.checkinout ('PHR'));

       

       

        TYPE records IS TABLE OF c_xml%rowtype;

        charges        records;

        v_content     varchar2(32000);

      BEGIN

       

       

      open c_xml;

      fetch c_xml bulk collect into charges;

      close c_xml;

       

      dbms_output.put_line(charges(1).tosend);

       

      for i in charges.first .. charges.last loop

      v_content := v_content||charges(i).tosend;

      end loop;

       

       

      end;

       

      When I try to access charges(1).tosend I get

      [Error] Execution (25: 1): ORA-06550: line 24, column 1:

      PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'

      ORA-06550: line 24, column 1:

      PL/SQL: Statement ignored

       

      ORA-06550: line 25, column 14:

      PLS-00306: wrong number or types of arguments in call to '||'

      ORA-06550: line 25, column 1:

      PL/SQL: Statement ignored

       

      How do I access this information? I want to create an object that can be used to send via UTL_HTTP.write_text ( r         => v_req, DATA => v_content );

       

      Any help would be appreciated.

      Thank you

        • 1. Re: XMLELEMENT
          ranit B

          What is "tosend"? Any user-defined function?

           

          I'm not sure but in both the error-ed lines this seems to be the cause. Does "tosend" accept any parameters?

          • 2. Re: XMLELEMENT
            Andylk

            tosend is the alias for the query.

            • 3. Re: XMLELEMENT
              Jason_(A_Non)

              XMLElement returns an XMLType data type.  DBMS_OUTPUT has no clue how to handle that data type.  In order for DBMS_OUTPUT to be able to do what you want, you would need something like

              DECLARE
              
                 CURSOR c_xml
                 IS
                    SELECT XMLELEMENT ("CHECKINOUT"
                                      ,xmlattributes ('1' AS "PROPERTY"
                                                     ,'2' AS "ID"
                                                     ,'3' AS "STATUS"
                                                     ,'4' AS "OCCUPANT"
                                                     ,'5' AS "email"
                                                     ,'6' AS "EXTENSION"
                                                     ,'7' AS "FOLIO")) AS tosend
                    FROM   dual;
              
                TYPE records IS TABLE OF c_xml%rowtype;
                charges        records;
                v_content     varchar2(32000);
              BEGIN
              
                 open c_xml;
                 fetch c_xml bulk collect into charges;
                 close c_xml;
              
                 dbms_output.put_line(charges(1).tosend.getStringVal());
              
                 for i in charges.first .. charges.last loop
                    v_content := v_content||charges(i).tosend.getStringVal();
                 end loop;
              
              end;
              

               

              I used .getStringVal() to convert the XMLType into a VARCHAR2 value.  That works for up to 4k worth of data.  There is also a .getClobVal().  Depending upon your version, you could wrap an XMLSerialize around the XMLElement and use XMLSerialize to convert the XMLType into VARCHAR2 instead of using .getStringVal().  That would be the preferred method.

               

              Can you use XMLAgg in your query against the DB?  Not sure what your final v_content needs to look like so just throwing out options.

              • 4. Re: XMLELEMENT
                Andylk

                Thanks. What I want to do is to send the v_content as part of the utl_http.write.