This discussion is archived
4 Replies Latest reply: Jan 14, 2013 8:16 AM by magicliver RSS

Keep getting string literal when using CLOBs

magicliver Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points