4 Replies Latest reply: Jan 14, 2013 10:16 AM by magicliver RSS

    Keep getting string literal when using CLOBs

    magicliver
      Hi, I am using Oracle 11.2.0.3 on Windows 2003 R2 and I have a procedure to extract various bits of XML and GML from an XMLType column. The GML for a record can be one or more geometries and I need to derive a single SDO_GEOMETRY from and convert to WGS84. I created a function called MULTI_GML_TO_SDOGEOM in which I parse my GML as a CLOB. I then add it to a SQL_STMT variable which is also a CLOB. In processing 10,000 records, this function worked fine for 8000 but then failed when it hit a record that had over 4000 characters in the GML (seven geometries) with a ORA-01704 string literal too long. I entered debug on every line of the function and found the function failed on the open cursor statement
      OPEN c_geoms FOR sql_stmt;
      I can't understand why I am getting this error as the total length of sql_stmt for the record that failed was about 7500 characters and I am using CLOBs which should be able handle that length. I am not sure if I have not used the CLOBs correctly or perhaps I need to use something from DBMS_LOB package but I cannot find any decent examples and I am not really sure why this doesn't work anyway.
      Here is the function:
      CREATE OR REPLACE FUNCTION MULTI_GML_TO_SDOGEOM (
         geometry_components IN CLOB)
         RETURN sdo_geometry
      IS
      v_count             NUMBER;
      v_gml               XMLType;
      v_gml_rec           XMLType;
      v_gml_clob          CLOB;
      v_gml_clob_rec      CLOB;
      sql_stmt            CLOB;
      v_sdogeom           SDO_GEOMETRY;
      v_sdogeom_all       SDO_GEOMETRY;
      varray_sdogeom      SDO_GEOMETRY_ARRAY;
      
      
      TYPE t_ref_cursor  IS REF CURSOR;
      c_geoms         t_ref_cursor;
      
      BEGIN
      
      varray_sdogeom := SDO_GEOMETRY_ARRAY();
      
      IF geometry_components is not null THEN
      
        v_gml := XMLType ('<GeometryComponents xmlns:gml="http://www.opengis.net/gml/3.2">'||geometry_components||'</GeometryComponents>');
      
        v_gml_clob := v_gml.getClobVal();
      
        SELECT count(*) INTO v_count FROM XMLTable ('declare namespace gml="http://www.opengis.net/gml/3.2"; (: :)
                                                   //polygon' PASSING v_gml);
      
        If v_count > 0 THEN
      
          sql_stmt := 'WITH gml_input AS (SELECT XMLType ('''||v_gml_clob||''') as gmldata from dual)
                       select poly.spatial_location from gml_input,
                                                       xmltable (xmlnamespaces (''http://www.opengis.net/gml/3.2'' as "gml"),
                                                                ''GeometryComponents/polygon/gml:Polygon''
                                                                 PASSING gmldata
                                                                 COLUMNS
                                                                 spatial_location XMLTYPE PATH ''//gml:Polygon'') poly
                       UNION ALL
                       select point.spatial_location from gml_input,
                                                       xmltable (xmlnamespaces (''http://www.opengis.net/gml/3.2'' as "gml"),
                                                                ''GeometryComponents/polygon/gml:Point''
                                                                 PASSING gmldata
                                                                 COLUMNS
                                                                 spatial_location XMLTYPE PATH ''//gml:Point'') point';
      --    dbms_output.put_line (sql_stmt);
      
      
          OPEN c_geoms FOR sql_stmt;
      
          LOOP
      
            FETCH c_geoms INTO v_gml_rec;
            EXIT WHEN c_geoms%NOTFOUND;
      
            v_gml_clob_rec := v_gml_rec.getClobVal;
      
            sql_stmt := 'SELECT SDO_CS.TRANSFORM(SDO_UTIL.FROM_GML311GEOMETRY ('''||v_gml_clob_rec||'''), 8307) FROM dual';
      
            EXECUTE IMMEDIATE sql_stmt INTO v_sdogeom;
       
            varray_sdogeom.EXTEND;
      
            varray_sdogeom(varray_sdogeom.COUNT) := v_sdogeom;
      
          END LOOP;   -- c_geoms fetch
      
          CLOSE c_geoms;
      
          select SDO_AGGR_SET_UNION(varray_sdogeom, 0.005) INTO v_sdogeom_all from dual;
      
        END IF;  -- v_count > 0
      
      RETURN v_sdogeom_all;
      
      END IF;
      
      END MULTI_GML_TO_SDOGEOM;
      /
      
      show errors
      Unfortunately I cannot add the data I am processing as it is classified but here is a dummy sample of the type of GML I am parsing though this is short enough that it works:
      <GeometryComponents xmlns:gml="http://www.opengis.net/gml/3.2">
      <polygon xmlns:gmd="http://www.isotc211.org/2005/gmd" xmlns:srv="http://www.isotc211.org/2005/srv"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:gco="http://www.isotc211.org/2005/gco" xmlns:gml="http://www.opengis.net/gml/3.2" xmlns:mgmp="http://www.mod.uk/mgmp" xmlns:smr="http://www.mod.uk/smr"
      xmlns:xlink="http://www.w3.org/1999/xlink"><gml:Polygon gml:id="bp2" srsName="EPSG:4326">
      <gml:exterior>
      <gml:LinearRing>
      <gml:posList srsDimension="2">175 -40 176 -40 176 -39 175 -39 175 -40</gml:posList>
      </gml:LinearRing>
      </gml:exterior>
      </gml:Polygon>
      </polygon>
      </GeometryComponents>
      And although this function is normally called from a procedure, here is a call from dual
       select MULTI_GML_TO_SDOGEOM ('<GeometryComponents xmlns:gml="http://www.opengis.net/gml/3.2">
      <polygon xmlns:gmd="http://www.isotc211.org/2005/gmd" xmlns:srv="http://www.isotc211.org/2005/srv"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:gco="http://www.isotc211.org/2005/gco" xmlns:gml="http://www.opengis.net/gml/3.2" xmlns:mgmp="http://www.mod.uk/mgmp" xmlns:smr="http://www.mod.uk/smr"
      xmlns:xlink="http://www.w3.org/1999/xlink"><gml:Polygon gml:id="bp2" srsName="EPSG:4326">
      <gml:exterior>
      <gml:LinearRing>
      <gml:posList srsDimension="2">175 -40 176 -40 176 -39 175 -39 175 -40</gml:posList>
      </gml:LinearRing>
      </gml:exterior>
      </gml:Polygon>
      </polygon>
      </GeometryComponents>') from dual;
      Thanks in advance.
        • 1. Re: Keep getting string literal when using CLOBs
          ckrig-Oracle
          Hi, what if you bind with :1 instead of concatenate ..'''||v_gml_clob||'''.. and ..'''||v_gml_clob_rec||'''.. respectively;
          * OPEN c_geoms FOR sql_stmt USING v_gml_clob; -- or, even better, bind directly to v_gml skipping conversion back and forth
          * EXECUTE IMMEDIATE sql_stmt INTO v_sdogeom USING v_gml_clob_rec; -- or, even better, bind directly to v_gml_rec skipping conversion back and forth

          Simplified example;
          DECLARE
          v scott.emp.empno%TYPE := 7934;
          vv INTEGER;
          BEGIN
          EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM scott.emp WHERE empno = :1' INTO vv USING v;
          END;

          --cb

          Edited by: 969592 on 2013-jan-08 08:19
          Fixing typo
          • 2. Re: Keep getting string literal when using CLOBs
            odie_63
            Hi,

            I fail to see why you use dynamic SQL here.
            As said above, you're doing a lot of bad and unnecessary stuff, first of which being not using bind variables.
            Then I see a lot serializing/constructing on XMLType which just adds more overhead.

            Basically, the function can be simplified down to :
            create or replace function multi_gml_to_sdogeom (
              geometry_components in clob
            )
            return sdo_geometry
            is
            
              v_sdogeom_all       SDO_GEOMETRY;
            
            begin
            
              select SDO_AGGR_SET_UNION(
                       cast(
                         collect(
                           SDO_CS.TRANSFORM(SDO_UTIL.FROM_GML311GEOMETRY(spatial_location), 8307)
                         )
                         as sdo_geometry_array
                       )
                     , .005
                     )
              into v_sdogeom_all
              from (
                select xmlserialize(content x.column_value) as spatial_location
                from xmltable(
                       xmlnamespaces ('http://www.opengis.net/gml/3.2' as "gml")
                     , '/GeometryComponents/polygon/(gml:Polygon|gml:Point)'
                       passing xmlparse(document geometry_components)
                     ) x
              ) ;
            
              return v_sdogeom_all;
            
            end;
            SQL> select multi_gml_to_sdogeom('<GeometryComponents xmlns:gml="http://www.opengis.net/gml/3.2">
              2  <polygon xmlns:gmd="http://www.isotc211.org/2005/gmd" xmlns:srv="http://www.isotc211.org/2005/srv"
              3  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:gco="http://www.isotc211.org/2005/gco" xmlns:gml="http://www.opengis.net/gml/3.2" xmlns:mgmp="http://www.mod.uk/mgmp" xmlns:smr="http://www.mod.uk/smr"
              4  xmlns:xlink="http://www.w3.org/1999/xlink"><gml:Polygon gml:id="bp2" srsName="EPSG:4326">
              5  <gml:exterior>
              6  <gml:LinearRing>
              7  <gml:posList srsDimension="2">175 -40 176 -40 176 -39 175 -39 175 -40</gml:posList>
              8  </gml:LinearRing>
              9  </gml:exterior>
             10  </gml:Polygon>
             11  <gml:Point gml:id="p21" srsName="EPSG:4326">
             12      <gml:coordinates>45.67, 88.56</gml:coordinates>
             13    </gml:Point>
             14  </polygon>
             15  </GeometryComponents>')
             16  from dual ;
             
            MULTI_GML_TO_SDOGEOM('<GEOMETR
            ------------------------------
            <Object>
             
            Edited by: odie_63 on 8 janv. 2013 18:02
            • 3. Re: Keep getting string literal when using CLOBs
              magicliver
              Hey I just want to say both answers I have had are excellent. Yes I should use bind variables more, yes my dynamic sql looks clumsy and I am extremely greatful for odie's much more elegant solution. I have not been able to successfully apply these ideas yet to my code and I am glad in a way that I didn't post the procedure I built that calls this function as I expect you'd both be dismayed. I am trying to improve my knowledge of SQL and answers like this are extremely helpful. I have had some other database issues this week but hope to try and implement your suggestions asap and then I can award a 'helpful'/'correct' as soon as I have implemented these improvements. Thanks to you both though in the meantime.
              • 4. Re: Keep getting string literal when using CLOBs
                magicliver
                Again, thanks odie for a solution far superior and elegant to my function. I had not seen the XMLparse (document <variable>) notation before or used the cast/collect or even the pipe as an 'OR' for the xpath, so all of that was very useful to learn. Cheers