This content has been marked as final. Show 8 replies
Your code above...
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;
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.
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;
The INTO parameters have to come directly after the SELECT and you missed the alias t:
But keep in mind, that you have to ensure, that there is only one row in the SELECT/TABLE or you'll get
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;
ORA-01422: exact fetch returns more than requested number of rows
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:
Edited by: _jum on 18.12.2012 17:28
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
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 tableThen the final solution I offered does exactly what he wants:
(2) pass these X, Y to compute bearing
create or replace function (p_idnumber in number, p_tolerance in number default 0.05)
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:
or via a function that returns a compound object type such as:
Create Procedure get_xy(p_idnumber in number, X1 out number, Y1 out number, X2 out number, Y2 out number)
2. bearing_for_points then takes those two points and calls the standard Oracle function BEARING_TILT_FOR_POINTS to compute a bearing.
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;
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.