2 Replies Latest reply: Jun 17, 2011 4:34 PM by NoelKhan RSS

    Problem with loading sdo_geometry with sql loader

    867745
      Hi All,

      I i'm trying to load some geometries, and i'm using sql loader for that.
      example entry of my data file looks like this:
      1,01030000000100000011000000af8916eafaf7284014c8917307f54540645ddc4603f82840d7b7dd150bf5454084ab4dad08f828401cfc0e8f0ef54540c987eaf70ef828404469143713f54540c987eaf70ef828404469143713f54540d13131a715f82840d44c52f41bf54540e148fb7a19f82840e2e1e24d23f54540e148fb7a19f82840e2e1e24d23f545403abcd6941af828400b13a16c25f5454091c71d801ef82840b8efac3830f54540c58cf0f620f8284039cbd1883ef54540c58cf0f620f8284039cbd1883ef545406659e6632df82840eb80351834f545403bf9991f24f828404bf37d271cf54540e3c281902cf8284012633ec516f54540b66a323e27f82840181d35cb0af54540af8916eafaf7284014c8917307f54540


      Geometry is stored in wkb format. To be able to add SRID information to geometry i created the function in database which looks like this:

      create or replace function sdo_geom_form_wkb_text(wkb_text IN VARCHAR2) RETURN
      sdo_geometry as
      SRID_VALUE NUMBER := 8307;
      BEGIN
      return sdo_geometry(to_blob(HEXTORAW(wkb_text)), SRID_VALUE);
      end sdo_geom_form_wkb_text;


      and i would like to invoke this method during load. To do this i look in some forums where there were examples with using from_wkt function and i made my own ctl file:

      OPTIONS (SKIP=0,BINDSIZE=20000000,ROWS=10000,ERRORS=500,DIRECT=true)
      LOAD DATA
      INFILE 'c:\geometry\face2.dat'
      BADFILE 'c:\geometry\face2.bad'
      TRUNCATE
      CONTINUEIF NEXT(1:1) = '#'
      INTO TABLE TEST_TABLE
      FIELDS TERMINATED BY ','
      TRAILING NULLCOLS (
      ID INTEGER EXTERNAL,
      string_geom BOUNDFILLER,
      GEOMETRY EXPRESSION "sdo_geom_form_wkb_text(:string_geom)"
      )

      when i invoke sqlloader i'm getting:


      SQL*Loader-951: Error calling once/load initialization
      ORA-26052: Unsupported type 121 for SQL expression on column GEOMETRY.

      Could anyone tell what i'm doing wrong?


      Thanks,