5 Replies Latest reply: May 7, 2013 3:30 PM by NoelKhan RSS

    Sdo_Geometry in cursor

    1007002
      Hello, I have this function:

      CREATE OR REPLACE
      FUNCTION Area_Convenio
      RETURN VARCHAR2
      IS
      v_datos VARCHAR2(32767);
      v_nombre VARCHAR2(32767);
      v_geometria SDO_GEOMETRY;
      CURSOR c_DatosSolicitud
      IS
      SELECT idsolicitud,
      direccion, geometria
      FROM solicitudes
      WHERE direccion IS NOT NULL
      AND geometria IS NOT NULL
      ORDER BY idsolicitud DESC;
      v_DatosSolicitud c_DatosSolicitud%ROWTYPE;
      BEGIN
      /*
      Desarrollador : Ingrid Alejandra Salazar Echeverri
      Fecha : 2012-12-18
      Descripcion : Actualiza el campo rotación.
      Parámetros:
      varRadians -> Valor en Radianes a convertir a Grados.
      */
      OPEN c_DatosSolicitud;
      LOOP
      FETCH c_DatosSolicitud INTO v_DatosSolicitud;
      EXIT
      WHEN c_DatosSolicitud%NOTFOUND;
      BEGIN
      SELECT C.Nombre
      INTO v_nombre
      FROM Convenios C
      INNER JOIN Areasconvenio A
      ON C.Idareaconvenio = A.Idareaconvenio
      INNER JOIN Trabconfig_Convenio T
      ON C.Idconvenio = T.Idconvenio
      WHERE Sdo_Relate(A.Geometria, v_DatosSolicitud.geometria ,'mask=ANYINTERACTION') = 'TRUE';
      END;
      IF v_nombre IS NOT NULL THEN
      v_datos := v_datos ||' '||v_DatosSolicitud.direccion|| ' ' || v_DatosSolicitud.idsolicitud;
      END IF;
      END LOOP;
      RETURN v_datos;
      END Area_Convenio;

      When I put a break point in the function, I can see that the field "geometria" inside the cursor has a type that say "opaque", so the function returns "no data found", so, how do I use the geometry type field for comparing inside a cursor, is it posible?
        • 1. Re: Sdo_Geometry in cursor
          NoelKhan
          Yes it is possible to compare and manipulate geometries with PL/SQL.

          The function isn't returning "no data found;" rather that's a condition cascaded from your inner BEGIN block containing the statement "SELECT C.Nombre INTO v_nombre..."
          CREATE OR REPLACE FUNCTION Area_Convenio RETURN VARCHAR2
          IS
               v_datos      VARCHAR2(32767);     -- "...really? Is the text really that long?"
               v_nombre      VARCHAR2(32767);
               v_geometria SDO_GEOMETRY;           -- "This variable isn't being used"
               CURSOR c_DatosSolicitud     IS
                    SELECT 
                         idsolicitud, direccion, geometria
                    FROM solicitudes
                    WHERE direccion IS NOT NULL
                    AND geometria IS NOT NULL
                    ORDER BY idsolicitud DESC;
               v_DatosSolicitud c_DatosSolicitud%ROWTYPE;
          BEGIN
          
          OPEN c_DatosSolicitud;
               LOOP
                    FETCH c_DatosSolicitud INTO v_DatosSolicitud;
                    EXIT WHEN c_DatosSolicitud%NOTFOUND;
          
                    v_nombre := NULL; -- "reset this value"
                    IF v_DatosSolicitud.geometria IS NOT NULL THEN     -- "you can manipulate/compare geometry in pl/sql"
                         BEGIN
                              SELECT C.Nombre INTO v_nombre
                              FROM Convenios C
                              INNER JOIN Areasconvenio A
                              ON C.Idareaconvenio = A.Idareaconvenio
                              INNER JOIN Trabconfig_Convenio T
                              ON C.Idconvenio = T.Idconvenio
                              WHERE Sdo_Relate(A.Geometria, v_DatosSolicitud.geometria ,'mask=ANYINTERACTION') = 'TRUE';
                         EXCEPTION WHEN NO_DATA_FOUND THEN
                              NULL; -- "ignore or provide feedback here"
                         END;
                    
                         IF v_nombre IS NOT NULL THEN
                              v_datos := v_datos ||' '||v_DatosSolicitud.direccion|| ' ' || v_DatosSolicitud.idsolicitud;
                         END IF;
                    END IF;
               END LOOP;
               RETURN v_datos;
          END Area_Convenio;
          • 2. Re: Sdo_Geometry in cursor
            1007002
            Hi,

            I replace the function with you reply, but the geometry still being null or empty, the geometry doesnt have any value, the type is opaque, what can i do?
            • 3. Re: Sdo_Geometry in cursor
              NoelKhan
              Hi,

              Run your queries outside of that function to get a better look at your data. For example, check whether your SELECT has results and if so, whether those results have not-NULL geometries.

              SQL Developer doesn't know how to display objects like SDO_GEOMETRY, so it says it's opaque. For debugging purposes, translate the SDO_GEOMETRY type into text using [ SDO_UTIL.TO_WKTGEOMETRY | http://docs.oracle.com/cd/B19306_01/appdev.102/b14255/sdo_util.htm#BJEBJEGJ ]

              Regards,
              Noel
              • 4. Re: Sdo_Geometry in cursor
                1007002
                I do the select outside the function and i works, the geometry has data. If i do the traslate, how it works?
                • 5. Re: Sdo_Geometry in cursor
                  NoelKhan
                  Hi,

                  Your cursor excludes rows where "geometria IS NOT NULL" so focus on your inner SELECT statement. It's likely there are no rows when inner-joining your 3 tables. If the following query doesn't work, focus on the data and joins between your 3 tables.
                  SELECT 
                       C.Nombre INTO v_nombre
                  FROM
                       (
                            (Convenios C INNER JOIN Areasconvenio A ON C.Idareaconvenio = A.Idareaconvenio)
                            INNER JOIN Trabconfig_Convenio T ON C.Idconvenio = T.Idconvenio
                       ),
                       (
                            SELECT SDO_AGGR_MBR( geometria ) mbr
                            FROM solicitudes
                            WHERE direccion IS NOT NULL
                            AND geometria IS NOT NULL
                       ) v
                  WHERE Sdo_Relate(A.Geometria, v.mbr,'mask=ANYINTERACTION') = 'TRUE';
                  As the documentation says, pass your geometry into SDO_UTIL.TO_WKTGEOMETRY and get a string as a result. For example:
                  ...
                  v_nombre := NULL;
                  DBMS_OUTPUT.PUT_LINE('To WKT geometry result = ' || SDO_UTIL.TO_WKTGEOMETRY(v_DatosSolicitud.geometria) );  --"Add this line"
                  BEGIN
                        SELECT C.Nombre INTO v_nombre
                  ...
                  Regards,
                  Noel