Home » Server Options » Spatial » how to use collections and forall insert (windows xp,oracle 11g)
how to use collections and forall insert [message #507472] Mon, 16 May 2011 02:08 Go to next message
swapnabpnn
Messages: 96
Registered: December 2010
Member
Hi ALL,
I had written the following procedure to insert values into log table by taking from source and target tables which are passed as parameters in procedure and it is working fine

SQL> CREATE OR REPLACE PROCEDURE sp_cordcount_log(tname1 VARCHAR2,tname2 VARCHAR2)
  2  AS
  3  v_coord1               SDO_ORDINATE_ARRAY;
  4  v_coord2               SDO_ORDINATE_ARRAY;
  5  v_srccount        NUMBER(22);
  6  v_targcount               NUMBER(22);
  7  cur_1                     SYS_REFCURSOR;
  8  v_fno                     NUMBER (5);
  9  v_fid                  NUMBER(10);
 10  v_cno                     NUMBER(5);
 11  v_cid                  NUMBER(5);
 12  v_sqlstr               VARCHAR2(2000);
 13
 14  BEGIN
 15  OPEN cur_1 FOR ' SELECT a.GEOM_SOURCE.SDO_ORDINATES,a.G3E_FNO,a.G3E_FID,a.G3E_CNO,a.G3E_CID,b.GEOM_TARGET.SDO_ORDINATES from '||tname1|
|' a,'||tname2||' b where  a.G3E_FID=b.G3E_FID ';
 16  LOOP
 17             FETCH cur_1 INTO v_coord1,v_fno,v_fid,v_cno,v_cid,v_coord2;
 18             EXIT WHEN cur_1%NOTFOUND;
 19             v_srccount := v_coord1.COUNT;
 20             v_targcount := v_coord2.COUNT;
 21             v_sqlstr:=' insert into INFO_PROJECTED_ERROR_LOG(G3E_FNO,G3E_FID,G3E_CNO,G3E_CID,SOURCE_COUNT,TARGET_COUNT)values( '||v_fno|
|','||v_fid||','||v_cno||','||v_cid||','||v_srccount||','||v_targcount||' ) ';
 22             EXECUTE IMMEDIATE v_sqlstr;
 23  END LOOP;
 24  COMMIT;
 25  EXCEPTION WHEN OTHERS THEN
 26     RAISE_APPLICATION_ERROR(-20010,SQLERRM);
 27
 28  END sp_cordcount_log;


The above one is working fine and giving expected results.But the issue is performance when records are in large number,so they suggested to use collections and forall insert.I tried to write it as follows,but it is throwing error.

Could anyone please help me out how to write the first stated procedure using collections..
SQL> CREATE OR REPLACE PROCEDURE sp_cordcounts_log
  2   IS
  3      TYPE infodata_type IS RECORD (
  4      v_coord1  INFO_PROJECTED_DATA.GEOM_SOURCE.SDO_ORDINATES%TYPE,
  5      v_fno     INFO_PROJECTED_DATA.G3E_FNO%TYPE,
  6      v_fid     INFO_PROJECTED_DATA.G3E_FID%TYPE,
  7           v_cno     INFO_PROJECTED_DATA.G3E_CNO%TYPE,
  8           v_cid     INFO_PROJECTED_DATA.G3E_CID%TYPE,
  9     v_coord2   INFO_PROJECTED_DATA_TMP.GEOM_TARGET.SDO_ORDINATES%TYPE
 10      );
 11      TYPE tab_infodata IS TABLE OF infodata_type INDEX BY PLS_INTEGER;
 12      v_info_tab             tab_infodata;
 13      BEGIN
 14
 15      SELECT a.GEOM_SOURCE.SDO_ORDINATES,a.G3E_FNO,a.G3E_FID,a.G3E_CNO,a.G3E_CID,b.GEOM_TARGET.SDO_ORDINATES
 16      BULK COLLECT INTO v_info_tab FROM INFO_PROJECTED_DATA a,INFO_PROJECTED_DATA_TMP b WHERE a.G3E_FID=b.G3E_FID;
 17
 18      FORALL v_indx IN 1..v_info_tab.COUNT
 19      INSERT INTO INFO_PROJECTED_ERROR_LOG(G3E_FNO,G3E_FID,G3E_CNO,G3E_CID,SOURCE_COUNT,TARGET_COUNT)values( v_info_tab(v_indx).v_fno,v_i
nfo_tab(v_indx).v_fid,v_info_tab(v_indx).v_cno,v_info_tab(v_indx).v_cid,
 20      v_info_tab(v_indx).v_coord1.count,v_info_tab(v_indx).v_coord2.count);
 21
 22   END sp_cordcounts_log;
 23  /

Warning: Procedure created with compilation errors.

Elapsed: 00:00:00.54
SQL> sho err
Errors for PROCEDURE SP_CORDCOUNTS_LOG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
19/5     PL/SQL: SQL Statement ignored
19/108   PL/SQL: ORA-22806: not an object or REF
19/108   PLS-00382: expression is of wrong type
19/108   PLS-00436: implementation restriction: cannot reference fields of
         BULK In-BIND table of records

19/133   PLS-00382: expression is of wrong type
19/133   PLS-00436: implementation restriction: cannot reference fields of
         BULK In-BIND table of records

19/158   PLS-00382: expression is of wrong type



Please help me out........
Re: how to use collections and forall insert [message #507484 is a reply to message #507472] Mon, 16 May 2011 03:00 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
Hi,

the error itself is described here: http://pls-00436.ora-code.com/ Quote:
PLS-00436:
implementation restriction: cannot reference fields of BULK In-BIND table of records
Cause: table(bulk_index).field is not supported at run-time yet.
Action: Use FOR loop with plain FORALL DML statement (SELECT/INSERT/DELETE/UPDATE) instead

Changing INFODATA_TYPE and TAB_INFODATA types to SQL types (using CREATE TYPE statement) should overcome this. Anyway, storing a large result set in local variable could lead to performance issue (memory) as well.

Maybe a simple change in the original procedure from dynamic to static SQL could help. There is nothing dynamic in INSERT statement and hardcoding variables into dynamic string is degrading performance. By the way, the new procedure SELECTs data from static tables and does not dynamically take them by provided names as the original one. Is it really needed in the old one?
As simple cursor loop is internally (at least from 10g) fetching 100 rows in one step (the same as BULK COLLECT LIMIT 100 would do), it could not perform bad. There is a post/article about this feature on AskTom, however I am unable to find it now.

Or, better, you may change it to a single INSERT SELECT statement
insert into INFO_PROJECTED_ERROR_LOG(G3E_FNO,G3E_FID,G3E_CNO,G3E_CID,SOURCE_COUNT,TARGET_COUNT)
SELECT <corresponding columns/functions on them from source tables>
FROM INFO_PROJECTED_DATA a,INFO_PROJECTED_DATA_TMP b
WHERE a.G3E_FID=b.G3E_FID;
Re: how to use collections and forall insert [message #507542 is a reply to message #507484] Mon, 16 May 2011 14:00 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11gR2> CREATE TABLE info_projected_data
  2    (geom_source  SDO_GEOMETRY,
  3  	g3e_fno      NUMBER,
  4  	g3e_fid      NUMBER,
  5  	g3e_cid      NUMBER,
  6  	g3e_cno      NUMBER)
  7  /

Table created.

SCOTT@orcl_11gR2> INSERT INTO info_projected_data VALUES
  2    (SDO_GEOMETRY
  3  	 (2003, NULL, NULL,
  4  	  SDO_ELEM_INFO_ARRAY (1, 1003, 1),
  5  	  SDO_ORDINATE_ARRAY (5, 1, 8, 1, 8, 6, 5, 7, 5, 1)),
  6  	1, 2, 3, 4)
  7  /

1 row created.

SCOTT@orcl_11gR2> INSERT INTO info_projected_data VALUES
  2    (SDO_GEOMETRY
  3  	 (2003, NULL, NULL,
  4  	  SDO_ELEM_INFO_ARRAY (1,1003,3),
  5  	  SDO_ORDINATE_ARRAY (1, 1, 5, 7)),
  6    4, 3, 2, 1)
  7  /

1 row created.

SCOTT@orcl_11gR2> CREATE TABLE info_projected_data_tmp
  2    (geom_target  SDO_GEOMETRY,
  3  	g3e_fid      NUMBER)
  4  /

Table created.

SCOTT@orcl_11gR2> INSERT INTO info_projected_data_tmp VALUES
  2    (SDO_GEOMETRY
  3  	 (2003, NULL, NULL,
  4  	  SDO_ELEM_INFO_ARRAY (1, 1003, 1),
  5  	  SDO_ORDINATE_ARRAY (5, 1, 8, 1, 8, 6, 5, 7, 5, 1)),
  6  	2)
  7  /

1 row created.

SCOTT@orcl_11gR2> INSERT INTO info_projected_data_tmp VALUES
  2    (SDO_GEOMETRY
  3  	 (2003, NULL, NULL,
  4  	  SDO_ELEM_INFO_ARRAY (1,1003,3),
  5  	  SDO_ORDINATE_ARRAY (1, 1, 5, 7)),
  6    3)
  7  /

1 row created.

SCOTT@orcl_11gR2> CREATE TABLE info_projected_error_log
  2    (G3E_FNO       NUMBER,
  3  	G3E_FID       NUMBER,
  4  	G3E_CNO       NUMBER,
  5  	G3E_CID       NUMBER,
  6  	SOURCE_COUNT  NUMBER,
  7  	TARGET_COUNT  NUMBER)
  8  /

Table created.

SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE sp_cordcounts_log
  2  IS
  3  	TYPE tab_infodata IS TABLE OF info_projected_error_log%ROWTYPE
  4  				      INDEX BY PLS_INTEGER;
  5  	v_info_tab	     tab_infodata;
  6  BEGIN
  7    SELECT a.G3E_FNO, a.G3E_FID, a.G3E_CNO, a.G3E_CID,
  8  	      a.source_count, b.target_count
  9    BULK   COLLECT INTO v_info_tab
 10    FROM   (SELECT g3e_fno, g3e_fid, g3e_cno, g3e_cid,
 11  		      COUNT (s.COLUMN_VALUE) source_count
 12  	       FROM   INFO_PROJECTED_DATA x,
 13  		      TABLE (x.geom_source.sdo_ordinates) s
 14  	       GROUP  BY g3e_fno, g3e_fid, g3e_cno, g3e_cid) a,
 15  	      (SELECT g3e_fid,
 16  		      COUNT (t.COLUMN_VALUE) target_count
 17  	       FROM   INFO_PROJECTED_DATA_TMP y,
 18  		      TABLE (y.geom_target.sdo_ordinates) t
 19  	       GROUP  BY g3e_fid) b
 20    WHERE  a.G3E_FID = b.G3E_FID;
 21    --
 22    FORALL v_indx IN 1 .. v_info_tab.COUNT
 23  	 INSERT INTO INFO_PROJECTED_ERROR_LOG VALUES v_info_tab (v_indx);
 24  END sp_cordcounts_log;
 25  /

Procedure created.

SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> EXEC sp_cordcounts_log

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> SELECT * FROM info_projected_error_log
  2  /

   G3E_FNO    G3E_FID    G3E_CNO    G3E_CID SOURCE_COUNT TARGET_COUNT
---------- ---------- ---------- ---------- ------------ ------------
         1          2          4          3           10           10
         4          3          1          2            4            4

2 rows selected.

SCOTT@orcl_11gR2>

Re: how to use collections and forall insert [message #507656 is a reply to message #507542] Tue, 17 May 2011 08:04 Go to previous messageGo to next message
swapnabpnn
Messages: 96
Registered: December 2010
Member
Thank you..

could u please provide any links for collections and forall concepts
Re: how to use collections and forall insert [message #507666 is a reply to message #507656] Tue, 17 May 2011 08:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Application Developer's Guide - Fundamentals

Regards
Michel
Re: how to use collections and forall insert [message #507766 is a reply to message #507666] Tue, 17 May 2011 16:02 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
The primary problem that required a workaround was due to trying to use the pL/SQL count collection method ".count" such as "v_info_tab(v_indx).v_coord1.count" in a SQL expression of any kind, such as an insert or select. ".count" can only be used in PL/SQL expressions, not SQL statements. Your problem had more to do with that than collections or forall. I have provided a link to information on ".count" below.

Oracle® Database PL/SQL Language Reference
11g Release 2 (11.2)
PL/SQL Collections and Records
COUNT Collection Method
http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/composites.htm#LNPLS99967

Here are some links for bulk collect and forall:

Oracle® Database PL/SQL Language Reference
11g Release 2 (11.2)
PL/SQL Collections and Records:
http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/composites.htm#LNPLS005

Oracle® Database PL/SQL Language Reference
11g Release 2 (11.2)
FORALL Statement:
http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/forall_statement.htm#LNPLS01321



Re: how to use collections and forall insert [message #507769 is a reply to message #507766] Tue, 17 May 2011 16:31 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
The following demonstrates that the usage of ".count" in SQL was the problem, first by showing that if you remove that from the values clause of the SQL insert statement in the original code, replacing it with "0" that the code runs, then by doing a different, but longer, workaround than what I provided that is more similar to the original code.

SCOTT@orcl_11gR2> CREATE TABLE info_projected_data
  2    (geom_source  SDO_GEOMETRY,
  3  	g3e_fno      NUMBER,
  4  	g3e_fid      NUMBER,
  5  	g3e_cid      NUMBER,
  6  	g3e_cno      NUMBER)
  7  /

Table created.

SCOTT@orcl_11gR2> INSERT INTO info_projected_data VALUES
  2    (SDO_GEOMETRY
  3  	 (2003, NULL, NULL,
  4  	  SDO_ELEM_INFO_ARRAY (1, 1003, 1),
  5  	  SDO_ORDINATE_ARRAY (5, 1, 8, 1, 8, 6, 5, 7, 5, 1)),
  6  	1, 2, 3, 4)
  7  /

1 row created.

SCOTT@orcl_11gR2> INSERT INTO info_projected_data VALUES
  2    (SDO_GEOMETRY
  3  	 (2003, NULL, NULL,
  4  	  SDO_ELEM_INFO_ARRAY (1,1003,3),
  5  	  SDO_ORDINATE_ARRAY (1, 1, 5, 7)),
  6    4, 3, 2, 1)
  7  /

1 row created.

SCOTT@orcl_11gR2> CREATE TABLE info_projected_data_tmp
  2    (geom_target  SDO_GEOMETRY,
  3  	g3e_fid      NUMBER)
  4  /

Table created.

SCOTT@orcl_11gR2> INSERT INTO info_projected_data_tmp VALUES
  2    (SDO_GEOMETRY
  3  	 (2003, NULL, NULL,
  4  	  SDO_ELEM_INFO_ARRAY (1, 1003, 1),
  5  	  SDO_ORDINATE_ARRAY (5, 1, 8, 1, 8, 6, 5, 7, 5, 1)),
  6  	2)
  7  /

1 row created.

SCOTT@orcl_11gR2> INSERT INTO info_projected_data_tmp VALUES
  2    (SDO_GEOMETRY
  3  	 (2003, NULL, NULL,
  4  	  SDO_ELEM_INFO_ARRAY (1,1003,3),
  5  	  SDO_ORDINATE_ARRAY (1, 1, 5, 7)),
  6    3)
  7  /

1 row created.

SCOTT@orcl_11gR2> CREATE TABLE info_projected_error_log
  2    (G3E_FNO       NUMBER,
  3  	G3E_FID       NUMBER,
  4  	G3E_CNO       NUMBER,
  5  	G3E_CID       NUMBER,
  6  	SOURCE_COUNT  NUMBER,
  7  	TARGET_COUNT  NUMBER)
  8  /

Table created.

SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE sp_cordcounts_log
  2  IS
  3    TYPE infodata_type IS RECORD
  4  	 (v_coord1 INFO_PROJECTED_DATA.GEOM_SOURCE.SDO_ORDINATES%TYPE,
  5  	  v_fno     INFO_PROJECTED_DATA.G3E_FNO%TYPE,
  6  	  v_fid     INFO_PROJECTED_DATA.G3E_FID%TYPE,
  7  	  v_cno     INFO_PROJECTED_DATA.G3E_CNO%TYPE,
  8  	  v_cid     INFO_PROJECTED_DATA.G3E_CID%TYPE,
  9  	  v_coord2  INFO_PROJECTED_DATA_TMP.GEOM_TARGET.SDO_ORDINATES%TYPE);
 10  	TYPE tab_infodata IS TABLE OF infodata_type INDEX BY PLS_INTEGER;
 11  	v_info_tab	       tab_infodata;
 12  BEGIN
 13    SELECT a.GEOM_SOURCE.SDO_ORDINATES, a.G3E_FNO, a.G3E_FID, a.G3E_CNO, a.G3E_CID,
 14  	      b.GEOM_TARGET.SDO_ORDINATES
 15    BULK   COLLECT INTO v_info_tab
 16    FROM   INFO_PROJECTED_DATA a, INFO_PROJECTED_DATA_TMP b
 17    WHERE  a.G3E_FID = b.G3E_FID;
 18    --
 19    FORALL v_indx IN 1 .. v_info_tab.COUNT
 20  	 INSERT INTO INFO_PROJECTED_ERROR_LOG
 21  	   (G3E_FNO, G3E_FID, G3E_CNO, G3E_CID, SOURCE_COUNT, TARGET_COUNT)
 22  	 values
 23  	   (v_info_tab(v_indx).v_fno,
 24  	    v_info_tab(v_indx).v_fid,
 25  	    v_info_tab(v_indx).v_cno,
 26  	    v_info_tab(v_indx).v_cid,
 27  	    0,
 28  	    0);
 29  END sp_cordcounts_log;
 30  /

Procedure created.

SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> EXEC sp_cordcounts_log

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> SELECT * FROM info_projected_error_log
  2  /

   G3E_FNO    G3E_FID    G3E_CNO    G3E_CID SOURCE_COUNT TARGET_COUNT
---------- ---------- ---------- ---------- ------------ ------------
         1          2          4          3            0            0
         4          3          1          2            0            0

2 rows selected.

SCOTT@orcl_11gR2> TRUNCATE TABLE info_projected_error_log
  2  /

Table truncated.

SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE sp_cordcounts_log
  2  IS
  3    TYPE infodata_type IS RECORD
  4  	 (v_coord1  NUMBER,
  5  	  v_fno     INFO_PROJECTED_DATA.G3E_FNO%TYPE,
  6  	  v_fid     INFO_PROJECTED_DATA.G3E_FID%TYPE,
  7  	  v_cno     INFO_PROJECTED_DATA.G3E_CNO%TYPE,
  8  	  v_cid     INFO_PROJECTED_DATA.G3E_CID%TYPE,
  9  	  v_coord2  NUMBER);
 10  	TYPE tab_infodata IS TABLE OF infodata_type INDEX BY PLS_INTEGER;
 11  	v_info_tab	       tab_infodata;
 12  BEGIN
 13    SELECT a.source_count, a.G3E_FNO, a.G3E_FID, a.G3E_CNO, a.G3E_CID, b.target_count
 14    BULK   COLLECT INTO v_info_tab
 15    FROM   (SELECT g3e_fno, g3e_fid, g3e_cno, g3e_cid,
 16  		      COUNT (s.COLUMN_VALUE) source_count
 17  	       FROM   INFO_PROJECTED_DATA x,
 18  		      TABLE (x.geom_source.sdo_ordinates) s
 19  	       GROUP  BY g3e_fno, g3e_fid, g3e_cno, g3e_cid) a,
 20  	      (SELECT g3e_fid,
 21  		      COUNT (t.COLUMN_VALUE) target_count
 22  	       FROM   INFO_PROJECTED_DATA_TMP y,
 23  		      TABLE (y.geom_target.sdo_ordinates) t
 24  	       GROUP  BY g3e_fid) b
 25    WHERE  a.G3E_FID = b.G3E_FID;
 26    --
 27    FORALL v_indx IN 1 .. v_info_tab.COUNT
 28  	 INSERT INTO INFO_PROJECTED_ERROR_LOG
 29  	   (G3E_FNO, G3E_FID, G3E_CNO, G3E_CID, SOURCE_COUNT, TARGET_COUNT)
 30  	 values
 31  	   (v_info_tab(v_indx).v_fno,
 32  	    v_info_tab(v_indx).v_fid,
 33  	    v_info_tab(v_indx).v_cno,
 34  	    v_info_tab(v_indx).v_cid,
 35  	    v_info_tab(v_indx).v_coord1,
 36  	    v_info_tab(v_indx).v_coord2);
 37  END sp_cordcounts_log;
 38  /

Procedure created.

SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> EXEC sp_cordcounts_log

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> SELECT * FROM info_projected_error_log
  2  /

   G3E_FNO    G3E_FID    G3E_CNO    G3E_CID SOURCE_COUNT TARGET_COUNT
---------- ---------- ---------- ---------- ------------ ------------
         1          2          4          3           10           10
         4          3          1          2            4            4

2 rows selected.

SCOTT@orcl_11gR2>

[Updated on: Tue, 17 May 2011 16:33]

Report message to a moderator

Re: how to use collections and forall insert [message #508037 is a reply to message #507769] Thu, 19 May 2011 02:33 Go to previous messageGo to next message
swapnabpnn
Messages: 96
Registered: December 2010
Member
Thanks for the reply,COuld you please explain me about the usage of TABLE (x.geom_source.sdo_ordinates).What is the use of this predefined function.......
Re: how to use collections and forall insert [message #508185 is a reply to message #508037] Thu, 19 May 2011 14:40 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Table() accepts a collection as a parameter and converts it to a table, so that you can then apply anything to it that you would apply to a table. For example, you can then use "count()", since you cannot use ".count".

When you are trying to understand what something does, it helps to break it down one piece at a time, starting with the simplest part of the innermost sub-query, viewing the results, then adding the modifications of any outer queries one at a time, viewing the results at each stage. I have provided something similar below.

-- If you have a table and data like so:
SCOTT@orcl_11gR2> CREATE TABLE info_projected_data
  2    (geom_source  SDO_GEOMETRY,
  3  	g3e_fno      NUMBER,
  4  	g3e_fid      NUMBER,
  5  	g3e_cid      NUMBER,
  6  	g3e_cno      NUMBER)
  7  /

Table created.

SCOTT@orcl_11gR2> INSERT INTO info_projected_data VALUES
  2    (SDO_GEOMETRY
  3  	 (2003, NULL, NULL,
  4  	  SDO_ELEM_INFO_ARRAY (1, 1003, 1),
  5  	  SDO_ORDINATE_ARRAY (5, 1, 8, 1, 8, 6, 5, 7, 5, 1)),
  6  	1, 2, 3, 4)
  7  /

1 row created.

SCOTT@orcl_11gR2> INSERT INTO info_projected_data VALUES
  2    (SDO_GEOMETRY
  3  	 (2003, NULL, NULL,
  4  	  SDO_ELEM_INFO_ARRAY (1,1003,3),
  5  	  SDO_ORDINATE_ARRAY (1, 1, 5, 7)),
  6    4, 3, 2, 1)
  7  /

1 row created.


-- you can select the data like so, with the sdo_ordinate_array displayed as a collection:
SCOTT@orcl_11gR2> SELECT g3e_fno, g3e_fid, g3e_cno, g3e_cid,
  2  	    x.geom_source.sdo_ordinates
  3  FROM   info_projected_data x
  4  /

   G3E_FNO    G3E_FID    G3E_CNO    G3E_CID
---------- ---------- ---------- ----------
GEOM_SOURCE.SDO_ORDINATES
--------------------------------------------------------------------------------
         1          2          4          3
SDO_ORDINATE_ARRAY(5, 1, 8, 1, 8, 6, 5, 7, 5, 1)

         4          3          1          2
SDO_ORDINATE_ARRAY(1, 1, 5, 7)


2 rows selected.


-- If you display the contents of the rows above without the collection, you get:
SCOTT@orcl_11gR2> SELECT g3e_fno, g3e_fid, g3e_cno, g3e_cid
  2  FROM   info_projected_data x
  3  /

   G3E_FNO    G3E_FID    G3E_CNO    G3E_CID
---------- ---------- ---------- ----------
         1          2          4          3
         4          3          1          2

2 rows selected.


-- If you apply TABLE() to the collection, you get the following. Notice that it provides a default column name of "column_value":
SCOTT@orcl_11gR2> SELECT s.*
  2  FROM   info_projected_data x,
  3  	    TABLE (x.geom_source.sdo_ordinates) s
  4  /

COLUMN_VALUE
------------
           5
           1
           8
           1
           8
           6
           5
           7
           5
           1
           1
           1
           5
           7

14 rows selected.


-- If you join the two above together, then you get:
SCOTT@orcl_11gR2> SELECT g3e_fno, g3e_fid, g3e_cno, g3e_cid,
  2  	    s.*
  3  FROM   info_projected_data x,
  4  	    TABLE (x.geom_source.sdo_ordinates) s
  5  /

   G3E_FNO    G3E_FID    G3E_CNO    G3E_CID COLUMN_VALUE
---------- ---------- ---------- ---------- ------------
         1          2          4          3            5
         1          2          4          3            1
         1          2          4          3            8
         1          2          4          3            1
         1          2          4          3            8
         1          2          4          3            6
         1          2          4          3            5
         1          2          4          3            7
         1          2          4          3            5
         1          2          4          3            1
         4          3          1          2            1
         4          3          1          2            1
         4          3          1          2            5
         4          3          1          2            7

14 rows selected.


-- Then you can use count and group by like any other table:
SCOTT@orcl_11gR2> SELECT g3e_fno, g3e_fid, g3e_cno, g3e_cid,
  2  	    COUNT (s.COLUMN_VALUE) source_count
  3  FROM   info_projected_data x,
  4  	    TABLE (x.geom_source.sdo_ordinates) s
  5  GROUP  BY g3e_fno, g3e_fid, g3e_cno, g3e_cid
  6  /

   G3E_FNO    G3E_FID    G3E_CNO    G3E_CID SOURCE_COUNT
---------- ---------- ---------- ---------- ------------
         1          2          4          3           10
         4          3          1          2            4

2 rows selected.

SCOTT@orcl_11gR2>


In the larger problem, this was done with both tables and collections, forming two inline views (sub-queries in the from clause) which were then joined to obtain the final results, so that there was a source_count in one column and a target_count in another column.

[Updated on: Thu, 19 May 2011 14:47]

Report message to a moderator

Previous Topic: Assigning geometry to variable
Next Topic: oracle spatial coordinates to relational (2 threads merged by bb)
Goto Forum:
  


Current Time: Fri Mar 29 00:05:14 CDT 2024