8 Replies Latest reply: Dec 18, 2012 5:31 PM by Simon Greener RSS

    procedure

    don123
      hi, please help me to write the following proceudre
      ===============================

      create or replace procedure getco (i out number, x out number, y out number) is
      begin
      select a.idnumber, t.x, t.y from holdingposition a, table(sdo_util.getvertices(a.geometry)) t into i, x, y;
      dbms_output.put_line(i||x||y);
      end;
      =========================================
      Warning: Procedure created with compilation errors.

      SQL>show errors
      Errors for PROCEDURE GETCO:

      LINE/COL ERROR
      -------- ---------------------------------------------------
      3/1 PL/SQL: SQL Statement ignored
      3/95 PL/SQL: ORA-00933: SQL command not properly ended
      SQL>



      regards
      smb
        • 1. Re: procedure
          FlyingGuy
          create or replace procedure getco (i out number, x out number, y out number) is
          begin
          select a.idnumber, t.x, t.y from holdingposition a, table(sdo_util.getvertices(a.geometry)) t into i, x, y;
          dbms_output.put_line(i||x||y);
          end;
          Your code above...
          ===============
          Mine below
          create or replace procedure getco (i out number, x out number, y out number) is
          begin
          select 
          a.idnumber, 
          t.x, 
          t.y 
          into 
          i, x, y
          from 
          holdingposition a, 
          table(sdo_util.getvertices(a.geometry)) ;
          dbms_output.put_line(i||x||y);
          end;
          Since I have no table definitions or data samples I really cannot be sure this will work, but I think you might find that it does.
          Cheers!
          • 2. Re: procedure
            _jum
            The INTO parameters have to come directly after the SELECT and you missed the alias t:
            CREATE OR REPLACE PROCEDURE test_getco (i OUT NUMBER, x OUT NUMBER, y OUT NUMBER) IS
            BEGIN
              SELECT a.idnumber, t.x, t.y t
                INTO i, x, y
                FROM holdingposition a, TABLE (SDO_UTIL.getvertices (a.geometry)) t;
            
              DBMS_OUTPUT.put_line (i || x || y);
            END;
            But keep in mind, that you have to ensure, that there is only one row in the SELECT/TABLE or you'll get
            ORA-01422: exact fetch returns more than requested number of rows
            • 3. Re: procedure
              don123
              Thanks Jum, But my table has many number of rows, do i need to create cursor ??

              regards
              smb
              • 4. Re: procedure
                Mahir M. Quluzade
                Hi,

                How many rows return following query?
                Must be return rows only 1 row.
                SELECT a.idnumber, t.x, t.y 
                    FROM holdingposition a, TABLE (SDO_UTIL.getvertices (a.geometry)) t;
                Regards
                Mahir M. Quluzade
                http://www.mahir-quluzade.com
                • 5. Re: procedure
                  don123
                  hi,

                  the table has line geometry, there are many lines, one line will have two or more vertices, so all X, Y coordinates will be retrieved.

                  regards
                  smb
                  • 6. Re: procedure
                    Mahir M. Quluzade
                    I understand, what is you need now ?
                    What is a procedure return ?

                    Every time in oracle get value with function.
                    • 7. Re: procedure
                      _jum
                      This is nearly as unclear as the last post ;-(
                      What do you exactly want do do with the procedure/function (may be pipelined function) ?
                      As a start for you here a complete example, that you can modify:
                      SET SERVEROUTPUT ON SIZE 900000;
                      
                      --DROP   TABLE holdingposition; 
                      
                      CREATE TABLE holdingposition 
                      (
                        IDNUMBER  NUMBER,
                        GEOMETRY  MDSYS.SDO_GEOMETRY
                      );
                      
                      INSERT INTO holdingposition 
                         (IDNUMBER, GEOMETRY)
                       VALUES
                         (101, SDO_GEOMETRY(2002,8307,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(-87.900836, 42.001688)));
                      
                      INSERT INTO holdingposition 
                         (IDNUMBER, GEOMETRY)
                       VALUES
                         (102, SDO_GEOMETRY(2002,8307,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(-87.925747, 41.969833)));
                      
                      INSERT INTO holdingposition 
                         (IDNUMBER, GEOMETRY)
                       VALUES
                         (103, SDO_GEOMETRY(2002,8307,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(-87.912084, 41.970584)));
                      
                      --this procedure can only catch one row
                      CREATE OR REPLACE PROCEDURE test_getco (i OUT NUMBER, x OUT NUMBER, y OUT NUMBER) IS
                      BEGIN
                        SELECT a.idnumber, t.x, t.y t
                          INTO i, x, y
                          FROM holdingposition  a, TABLE (SDO_UTIL.getvertices (a.geometry)) t
                         WHERE rownum=1;
                      
                        DBMS_OUTPUT.put_line ('ID='||i||' X='||x ||' Y='|| y);
                      END;
                      
                      SHOW ERRORS
                      
                      --Procedure created.
                      --No errors.
                      
                      --run the procedure to catch one row
                      DECLARE
                        vi NUMBER;
                        vx NUMBER;
                        vy NUMBER;
                      BEGIN
                        test_getco(vi,vx,vy);
                      END;
                      
                      ID=101 X=-87,900836 Y=42,001688
                      
                      --this procedure uses a cursor to show all rows
                      CREATE OR REPLACE PROCEDURE test1_getco IS
                        CURSOR cRows IS
                        SELECT a.idnumber, t.x, t.y 
                          FROM holdingposition  a, TABLE (SDO_UTIL.getvertices (a.geometry)) t;
                      
                        fRows cRows%ROWTYPE;
                      BEGIN
                        OPEN cRows;
                        
                        LOOP 
                          FETCH cRows INTO fRows; 
                          EXIT WHEN crows%NOTFOUND;
                          DBMS_OUTPUT.put_line ('ID='||fRows.idnumber||' X='||fRows.x ||' Y='||fRows.y);
                        END LOOP;
                      
                      END;
                      
                      SHOW ERRORS
                      
                      Procedure created.
                      No errors.
                      
                      --run the procedure to show all rows
                      BEGIN
                        test1_getco;
                      END;
                      
                      ID=101 X=-87,900836 Y=42,001688
                      ID=102 X=-87,925747 Y=41,969833
                      ID=103 X=-87,912084 Y=41,970584
                      Edited by: _jum on 18.12.2012 17:28                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
                      • 8. Re: procedure
                        Simon Greener
                        This request is an exact copy of this one:

                        function to get x, y

                        In this post his function is called getco in the other post it is getxy. They do the same thing.

                        Our requester got pretty well what he wanted in the related post. But in the end he simply would not provide information that would have helped us help him.

                        IMHO In this post he has trespassed on the good will and intentions of all that have responded to both forum posts.

                        He seems not to understand that you can't construct a bearing from a single point.

                        He introduced the bearing requirement late in his first post after not even telling us that the data that was to go into his initial procedure was linestrings and not single point.
                        In addition, he also indicated in his interactions that the source table only had a single line - or row. His lack of a where clause on his SQL indicated that he thought the table
                        only had a single row. However, given his later exposure of linestring data one must say that he probably meant that his table contained linestrings (ie many linestrings).
                        So, in this post where he said:
                        Thanks Jum, But my table has many number of rows, do i need to create cursor ??
                        I rather got a bit angry because this was all teased out of him in the first post and he should know the answer as we showed him what he should do, which was to use a where clause using the provided idnumber input parameter.

                        He stopped interacting in the last post when asked for provide more accurate function signatures to implement what he wants.

                        In fact, if you accept what he said:
                        (1) generate X, Y from one line table
                        (2) pass these X, Y to compute bearing
                        Then the final solution I offered does exactly what he wants:
                        create or replace function (p_idnumber in number,
                                                    p_tolerance in number default 0.05)
                        Because:

                        1. bearing_for_points gets two XY points from a nominated linestring in his source table which implements the first bit of what he wanted.

                        He seems not to understand that a function that returns the XY coordinates required to define a bearing
                        can only be done in either a procedure with 4 out parameters:
                        Create Procedure get_xy(p_idnumber in number,  X1 out number, Y1 out number, X2 out number, Y2 out number)
                        or via a function that returns a compound object type such as:
                        CREATE TYPE vector AS OBJECT (X1 out number, Y1 out number, X2 out number, Y2 out number);
                        Create Function get_xy(p_idnumber) returns vector;
                        2. bearing_for_points then takes those two points and calls the standard Oracle function BEARING_TILT_FOR_POINTS to compute a bearing.
                        No second function is needed.

                        But, apparently, because what I provided was not what he thinks he actually wants (though he wouldn't give us the names of the functions he thinks he needs),
                        he stops interacting in one forum post when a solution is within his fingertips and starts this post wasting the time of a lot of other people with the same questions etc
                        that were answered previously.

                        If I have gotten all this completely and utterly wrong, then I apologise, but this post looks like a cross-post to me.

                        regards
                        Simon