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  |
 |
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  |
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 |
 |
Barbara Boehmer
Messages: 9101
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 #507769 is a reply to message #507766] |
Tue, 17 May 2011 16:31 |
|
Barbara Boehmer
Messages: 9101
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 #508185 is a reply to message #508037] |
Thu, 19 May 2011 14:40 |
|
Barbara Boehmer
Messages: 9101
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 |
|
|
|
Goto Forum:
Current Time: Sun Dec 22 02:25:01 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
