2 Replies Latest reply: Jul 5, 2012 5:39 AM by GregStef0 RSS

    update georaster over dblink (SDO_GEOR.COPY)

    GregStef0
      Hi all

      Is it possible to update a georaster object using as source another georaster from a remote database?

      I'm on 11g2
      BANNER
      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
      PL/SQL Release 11.2.0.1.0 - Production
      CORE 11.2.0.1.0 Production
      TNS for Linux: Version 11.2.0.1.0 - Production
      NLSRTL Version 11.2.0.1.0 - Production

      I tried:
      -----
      DECLARE
      cnt number :=0;
      gr1 sdo_georaster;
      gr2 sdo_georaster;
      BEGIN
      for gr in ( select georid, from ortho.gr_ortho1@newaspdb )

      loop

      INSERT INTO GR_ORTHO1
      VALUES ( gr.georid, gr.map_id, SDO_GEOR.init ('GR_ORTHO1_RDT',gr.georid) )
      RETURNING geor INTO gr2;

      select geor into gr1 from ortho.gr_ortho1@newaspdb where georid=gr.georid;

      --> here I get the error

      SDO_GEOR.COPY (gr1, gr2);

      UPDATE GR_ORTHO1
      SET geor = gr2
      WHERE georid = gr.georid;

      COMMIT;
      end loop;

      END;
      /
      -----
      I get the error:

      ORA-13475: Μη αποδεκτό αντικείμενο GeoRaster εξόδου
      ORA-00942: πίνακας ή όψη δεν υπάρχει
      ORA-06512: σε "MDSYS.MD", γραμμή 1723
      ORA-06512: σε "MDSYS.MDERR", γραμμή 8
      ORA-06512: σε "MDSYS.SDO_GEOR", γραμμή 451
      ORA-06512: σε "MDSYS.SDO_GEOR", γραμμή 1055
      ORA-06512: σε γραμμή 21

      But, when I use a local source table, the code runs just fine...

      Thanks in advance for your time,
      Greg

      Edited by: user12065678 on May 23, 2012 4:50 PM
        • 1. Re: update georaster over dblink (SDO_GEOR.COPY)
          Jeffrey Xie
          Hi Greg,

          Database link is not supported by the GeoRaster routines. Therefore, you'll have to use either the Data Pump Export and Import utilities or the original Export and Import utilities to transfer GeoRaster data between databases.

          In addition, you may use gdal_translate (with the georaster driver) to directly transport a georaster from one database to another - not very easy to script the commandline though if you have many georaster objects.

          regards,

          Jeffrey
          • 2. Re: update georaster over dblink (SDO_GEOR.COPY)
            GregStef0
            Hi Jeffrey, sorry for the late response...

            I'm sorry to hear that dblink is not supported for georaster opperations (I'm also sorry that I'm growing more and more accustomed in Oracle's lack of functionality in spatial subjects)...

            My task was to update 2869 georasters, over 105 databases.
            I've already used the expdp/impdp approach (I'm more familiar with Oracle than with gdal) and it worked - it's not something I\d like to repeat, though.