ORA-30926
A reader, June 19, 2003 - 9:16 am UTC
tom,
Please explain me why i am getting this error. frankly i did not understand much with the above explanation - sorry about that.
what should i do to avoid this error
17:44:38 ge$matrix@links.us.oracle.com>create table tb_today2 as select * from tb_today;
17:46:32 ge$matrix@links.us.oracle.com>create table tb_today3 as select * from tb_today;
18:27:56 ge$matrix@links.us.oracle.com>sta c:\ge\links\proc\practice\merge1.sql;
begin
*
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables
ORA-06512: at line 2
18:27:58 ge$matrix@links.us.oracle.com>select count(*) from tb_today2 e1, tb_today3 e2 where
18:28:44 2 e1.customer_index_num=e2.customer_index_num and e1.requisition_num=e2.requisition_num and e1.system_origin_nam=e2.system_origin_nam and e1.line_num=e2.line_num and e1.sequence_num=e2.sequence_num;
COUNT(*)
==========
9873
18:28:55 ge$matrix@links.us.oracle.com>alter table tb_today3 add flag varchar2(2);
--merge1.sql--
begin
merge into tb_today2 e1 using tb_today3 e2 on (e1.customer_index_num=e2.customer_index_num and e1.requisition_num=e2.requisition_num and e1.system_origin_nam=e2.system_origin_nam and e1.line_num=e2.line_num and e1.sequence_num=e2.sequence_num)
when matched then
update set e1.flag='U'
when not matched then
insert (customer_index_num) values (e2.customer_index_num);
commit;
end;
Thanks for your consideration.
June 19, 2003 - 1:02 pm UTC
consider:
ops$tkyte@ORA920LAP> create table t1 ( x int, y int );
Table created.
ops$tkyte@ORA920LAP> create table t2 ( x int, y int );
Table created.
ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> insert into t1 values ( 1, 1 );
1 row created.
ops$tkyte@ORA920LAP> insert into t2 values ( 1, 2 );
1 row created.
ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> merge into t1 using t2
2 on ( t1.x = t2.x )
3 when matched then update set t1.y = t2.y
4 when not matched then insert (x,y) values ( t2.x, t2.y);
1 row merged.
<b>Ok, here it is clear what will happen right -- we'll do an update of Y from 1 to 2. BUT</b>
ops$tkyte@ORA920LAP> insert into t2 values (1, 3 );
1 row created.
<b>Now tell me -- what SHOULD happen now? should y be 2 or 3 at the end? </b>
ops$tkyte@ORA920LAP> merge into t1 using t2
2 on ( t1.x = t2.x )
3 when matched then update set t1.y = t2.y
4 when not matched then insert (x,y) values ( t2.x, t2.y);
merge into t1 using t2
*
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables
<b>that is what this means. It means that your merge key of
(e1.customer_index_num=e2.customer_index_num and
e1.requisition_num=e2.requisition_num and
e1.system_origin_nam=e2.system_origin_nam and e1.line_num=e2.line_num and
e1.sequence_num=e2.sequence_num)
has DUPLICATES in tb_today3 e2 and we don't know WHICH of the N rows in e2 should be used to update the row in e1.
</b>
Your "on" clause should be "on to a unique key"
A reader, June 19, 2003 - 9:20 am UTC
tom,
please ignore this line in the above message..
18:28:55 ge$matrix@links.us.oracle.com>alter table tb_today3 add flag
varchar2(2);
both the tables are of same strucutre.
thanks again
Excellent - yet an another thankyou!
A reader, June 19, 2003 - 1:51 pm UTC
Merge : output
arjun, July 03, 2003 - 2:12 pm UTC
hi: tom,
excellent as always:
I tried examples using merge : I get
"Operation 189 succeeded." instead of "n rows merged"
following is my version:
BANNER
------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
PL/SQL Release 9.2.0.3.0 - Production
CORE 9.2.0.3.0 Production
TNS for Solaris: Version 9.2.0.3.0 - Production
NLSRTL Version 9.2.0.3.0 - Production
please let me know.
regards
arjun
July 03, 2003 - 8:10 pm UTC
but what is your sqlplus version -- oh, that is 8i or before....
ok, sqlplus at that release level has no clue what "merge" is, hence is just says "whatever you typed was amazing and it worked"
excellent explanation
A reader, October 01, 2003 - 6:53 am UTC
hi tom,
hats off to your amazing explanations and solution. The above error as u explained is caused mainly due to having multiple rows in the source table and if we use MERGE statement.
We are facing a strange situation. We have an oracle stored PROCEDURE that has simple update
statements AS follows......
UPDATE BAK_LYENTITYPROPERTY
SET keyname = 'CLASSNAME'
WHERE UPPER(name) IN ( 'GENERALCLASSKEY','LIMITEDCLASSKEY','LLCCLASSKEY',
'DEFAULTNONRECOURSEPARTNERCLASSKEY','DEFAULTOTHERPARTNERCLASSKEY',
'DEFAULTQUALIFIEDPARTNERCLASSKEY','DEFAULTSCHKPARTNERCLASSKEY');
-- END 003
--update LYENTITY related keyvalues
UPDATE BAK_LYENTITYPROPERTY T
SET keyvalue = ( SELECT F.entitykey
FROM LYENTITY F
WHERE NVL(RTRIM(F.dcorp), ' ') = NVL(RTRIM(T.value), ' ') --002
AND F.enterprisekey = v_enterprisekey
AND T.keyname = 'DCORP' ) --002
WHERE EXISTS ( SELECT 1
FROM LYENTITY F
WHERE NVL(RTRIM(F.dcorp), ' ') = NVL(RTRIM(T.value), ' ') --002
AND F.enterprisekey = v_enterprisekey
AND T.keyname = 'DCORP' ); --002
..............
some how, we get the following error message.
.... ORA-30926: unable to get a stable set of rows in the source tables
reading ur explanations i could conclude that
the above error msg IS meant FOR merge statements WHERE the join condition
returns multiple ROWS FROM the source table. But our PROCEDURE uses no MERGE statment
AND we are working ON oracle version 8.1.7 IN which merge STATEMENT IS NOT available AT all.
Can u let me know the cause of this error in a situation other than the MERGE statement. Or can we replicate the behaviour of a MERGE statment , using simple updates/inserts/selects ???
thanx ,nikhil
October 01, 2003 - 8:44 am UTC
be there a bitmap index involved?
Merge strange behaviour
karma, December 11, 2003 - 8:10 pm UTC
here i have simple merger but its not doing as expected:
MERGE INTO a_acct xx
USING
(
(SELECT a.firm_c,a.brch_c,a.base_c,sysdate,a.citz_c
FROM a_acct_mstr a
WHERE (a.firm_c,a.brch_c,a.base_c) NOT IN (SELECT firm_c,brch_c,base_c FROM i_acct_mstr)
)
UNION
(SELECT a.firm_c,a.brch_c,a.base_c,sysdate cycle_d,a.citz_c
FROM i_acct_mstr i, a_acct_mstr a
WHERE a.firm_c = i.firm_c
AND a.brch_c = i.brch_c
AND a.base_c = i.base_c
AND (a.citz_c <> i.citz_c or i.citz_c IS NULL)
AND a.citz_c IN ('3', '4','B','C','D')
)
) yy
ON (xx.firm_c = yy.firm_c and xx.brch_c = yy.brch_c and xx.base_c = yy.base_c)
WHEN matched THEN
UPDATE SET xx.eff_date = sysdate+10
WHEN not matched THEN
INSERT (firm_c,brch_c,base_c,eff_date,doc_category,doc_code,doc_status,doc_short_name,
doc_code_desc,source_sys,prtn_id,insert_tmst,update_tmst)
VALUES (yy.firm_c,yy.brch_c,yy.base_c,sysdate,'XXX','-',yy.citz_c,'-',
'-','BVVV','RRRR',sysdate,sysdate) ;
Here is the scoop:
select before union return 1 row I.e i got new row
select after union returns no rows i.e. nothing changed
A_acct is EMPTY.
First time i ran merge it says succesfully completed not rows affected. Now since i got 1 NEW row and there is nothing in a_acct table should'nt it insert ? It does not.
So, I changed data so that 2nd part of SQL ( after Union) gets me 1 row.
Now i ran merge stmt. and it says 2 rows affected. It inserted 2 rows and this is correct.
Now i changed back data so that 2nd part of union returns no data and first part of union returns 1 row and ran merge again and it should update 1 row since this row do exist in the a_acct table now because of 2nd run but merge says no rows affected.
I am going crazy and wondering if i am doing something wrong?
December 13, 2003 - 10:23 am UTC
hows about a 100% complete, yet as concise as possible example. including little create tables, the inserts needed, etc -- you know, just like I do in my examples!
remember -- CONCISE is as important as COMPLETE. get rid of everything that is not relevant to the example, use as few columns as possible. I find many (most) times that in the course of making a tiny example -- I find the errors of my ways (I find the mistake i was making whilst in the course of trying to reproduce it with as few moving pieces as possible)
malcolm, February 23, 2004 - 7:26 am UTC
Tom, this thread has been very useful me. Thanks.
I like the way that Oracle can automagically detect whether the result of a merge will be ambiguous, and throws a ORA-30926 if it's not.
But why can't it do a similar thing with updates of views? What I mean is that it would be nice to tell Oracle that a view is key-preserved, even if it's not provable to the server.
Something like this...
update /*+is_key_preserved_hint*/
(select t1.y t1_y, t2.y t2_y
from t1, t2
where t1.x = t2.x)
set t1_y = t2_y
... and Oracle tries the update, but it fails if the view really isn't key preserved.
Then we wouldn't need to use the merge workaround.
February 23, 2004 - 7:53 am UTC
there is a hint, hint is undocumented, therefore I don't talk about bypass_ujvc
bypass updatable join view check?
malcolm, February 23, 2004 - 10:44 am UTC
I notice this hint is used for MV refreshes, so it must be reasonably safe.
Of course I won't be using it for any production code, but it will be useful for ad-hoc updates.
Since the functionality is there, it would be nice if Oracle fully supported it.
February 23, 2004 - 10:49 am UTC
MERGE does it.
Especially in 10g when we can skip the INSERT portion of the MERGE all together.
ORA-01733: virtual column not allowed here
A reader, March 18, 2009 - 12:10 pm UTC
Dear Tom,
I did the following update on a collection (table of object)
UPDATE ( SELECT *
FROM TABLE ( my_collection) ) dd
SET dd.aa = 5
, dd.bb = 6
, dd.cc = 7
, dd.ee = 8
, dd.ff = 9
WHERE dd.id = 100
AND dd.yy = 200;
And I get ORA-01733: virtual column not allowed here.
Isn't possible to update a collection like above?
Thanks a lot for your help
March 19, 2009 - 10:10 am UTC
no, it is not.
you could
ops$tkyte%ORA10GR2> create type myScalarType as object
2 ( a number, b number, c number, x number, y number )
3 /
Type created.
ops$tkyte%ORA10GR2> create type myTableType as table of myScalarType
2 /
Type created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> declare
2 l_collection1 myTableType := myTableType( myScalarType( 100, 200, 300, 100, 200 ),
3 myScalarType( 100, 200, 300, 10, 20 ) ) ;
4 l_collection2 myTableType;
5 begin
6 select myScalarType( case when x = 100 and y = 200 then 5 else a end,
7 case when x = 100 and y = 200 then 6 else b end,
8 case when x = 100 and y = 200 then 7 else c end,
9 x, y )
10 bulk collect into l_collection2
11 from table( l_collection1 );
12
13 for i in 1 .. l_collection2.count
14 loop
15 dbms_output.put_line( l_collection2(i).a );
16 dbms_output.put_line( l_collection2(i).b );
17 dbms_output.put_line( l_collection2(i).c );
18 dbms_output.put_line( l_collection2(i).x );
19 dbms_output.put_line( l_collection2(i).y );
20 dbms_output.put_line( '------------------' );
21 end loop;
22 end;
23 /
5
6
7
100
200
------------------
100
200
300
10
20
------------------
PL/SQL procedure successfully completed.
but it is likely easier just to iterate over the collection elements.
Manipulate Collection in Loop before inserting into table
Anand Singh, October 22, 2011 - 6:30 am UTC
1. Created four data structure
==============================
create type EMAIL_LIST as object
(
NAME Varchar2(300),
EMAIL Varchar2(300)
)
/
CREATE TYPE EMAIL_ADDRESS IS TABLE OF EMAIL_LIST
/
create type EMAIL_ARRAYTYPE as object
(
COL1 Varchar2(300),
COL2 Varchar2(300)
)
/
CREATE TYPE EMAIL_ARRAY IS TABLE OF EMAIL_ARRAYTYPE
/
2. Created a tables using above data structure
==============================
CREATE TABLE EMAIL_DATA_DETAIL
(
EMAILDATAID NUMBER(8),
emailformatid VARCHAR2(8),
POPTIME DATE,
PUSHTIME DATE,
PUSHED VARCHAR2(1),
ERRMSG VARCHAR2(4000),
FROM_LIST EMAIL_ADDRESS,
TO_LIST EMAIL_ADDRESS,
NUM_COL1 NUMBER(18,2),
NUM_COL2 NUMBER(18,2),
INT_COL1 NUMBER(8),
PERCENT_COL1 NUMBER(10,7),
VCHAR_COL1 VARCHAR2(300),
VCHAR_COL2 VARCHAR2(300),
VCHAR_COL3 VARCHAR2(300),
ARRAY_COL1 EMAIL_ARRAY,
ARRAY_COL2 EMAIL_ARRAY
)
NESTED TABLE FROM_LIST STORE AS EMAIL_DATA_DETAIL_FROM_LIST,
NESTED TABLE TO_LIST STORE AS EMAIL_DATA_DETAIL_TO_LIST,
NESTED TABLE ARRAY_COL1 STORE AS EMAIL_DATA_DETAIL_ARRAY_COL1,
NESTED TABLE ARRAY_COL2 STORE AS EMAIL_DATA_DETAIL_ARRAY_COL2
/
3. Want to populate a tables with all details
==============================================
begin
for i in (select username, email
from user_master
where <<some condition>>
loop
1. want to populate some collection with username and email
end loop;
2. Want to create a single record in email_data_detail table wherein TO_LIST having all username and email reterived in step1
commit;
end;
/
Is there any way in PL/SQL wherein i can populate some data structure (PL/SQL tables etc.) in the loop and can
insert single record into a tables for further use.
Thanks In advance for your help and managing time from your busy schedule.
October 22, 2011 - 8:18 pm UTC
yes there is, have you read the plsql language guide? It pretty much covers collections in great detail.
ops$tkyte%ORA11GR2> CREATE TABLE EMAIL_DATA_DETAIL
2 (
3 EMAILDATAID NUMBER(8),
4 FROM_LIST EMAIL_ADDRESS,
5 TO_LIST EMAIL_ADDRESS
6 )
7 NESTED TABLE FROM_LIST STORE AS EMAIL_DATA_DETAIL_FROM_LIST,
8 NESTED TABLE TO_LIST STORE AS EMAIL_DATA_DETAIL_TO_LIST
9 /
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> declare
2 l_rec email_data_detail%rowtype;
3 begin
4 for x in (select rownum r from all_users where rownum <= 2)
5 loop
6 l_rec.emaildataid := x.r;
7 l_rec.from_list := email_address();
8 l_rec.from_list.extend(3);
9 for i in 1 .. 3
10 loop
11 l_rec.from_list(i) := email_list( 'name_' || i, 'email_'||i );
12 end loop;
13
14 select email_list( username, to_char(created) ) bulk collect into l_rec.to_list from all_users where rownum < 5;
15
16 insert into email_data_detail values L_REC;
17 end loop;
18 end;
19 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select * from email_data_detail;
EMAILDATAID
-----------
FROM_LIST(NAME, EMAIL)
-------------------------------------------------------------------------------
TO_LIST(NAME, EMAIL)
-------------------------------------------------------------------------------
1
EMAIL_ADDRESS(EMAIL_LIST('name_1', 'email_1'), EMAIL_LIST('name_2', 'email_2'),
EMAIL_LIST('name_3', 'email_3'))
EMAIL_ADDRESS(EMAIL_LIST('SYS', '05-SEP-10'), EMAIL_LIST('SYSTEM', '05-SEP-10')
, EMAIL_LIST('OUTLN', '05-SEP-10'), EMAIL_LIST('DIP', '05-SEP-10'))
2
EMAIL_ADDRESS(EMAIL_LIST('name_1', 'email_1'), EMAIL_LIST('name_2', 'email_2'),
EMAIL_LIST('name_3', 'email_3'))
EMAIL_ADDRESS(EMAIL_LIST('SYS', '05-SEP-10'), EMAIL_LIST('SYSTEM', '05-SEP-10')
, EMAIL_LIST('OUTLN', '05-SEP-10'), EMAIL_LIST('DIP', '05-SEP-10'))
HOWEVER, I would strongly encourage you to NOT DO THIS. Just use regular tables, rows and columns, joins.. I strongly encourage you to NOT DO THIS.
Merge & Update
Rajeshwaran Jeyabal, October 22, 2011 - 9:13 pm UTC
Tom:
I was exercising your example on First post and ended up with some doubts.
rajesh@ORA10GR2> declare
2 l_type myobjntt := myobjntt(myobj(1,1,1,1), myobj(2,2,2,2));
3 begin
4 update
5 (
6 select t.col1 as t_col1,
7 t.col2 as t_col2,
8 t.col3 as t_col3,
9 t.col4 as t_col4,
10 my.col1 as my_col1,
11 my.col2 as my_col2,
12 my.col3 as my_col3,
13 my.col4 as my_col4
14 from t, table(l_type) my
15 where t.col1 = my.col1
16 )
17 set t_col4 = my_col4;
18 end;
19 /
declare
*
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table
ORA-06512: at line 4
Elapsed: 00:00:00.14
rajesh@ORA10GR2>
rajesh@ORA10GR2> declare
2 l_type myobjntt := myobjntt(myobj(1,1,1,1), myobj(2,2,2,2));
3 begin
4 merge into t using
5 (select * from table(l_type)) t1
6 on (t.col1 = t1.col1)
7 when matched then
8 update set t.col4 = t1.col4;
9 end;
10 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.09
rajesh@ORA10GR2>
rajesh@ORA10GR2> select * from t;
COL1 COL2 COL3 COL4
---------- ---------- ---------- ----------
1 1 1 1
2 2 2 2
3 3 3
Elapsed: 00:00:00.12
rajesh@ORA10GR2>
rajesh@ORA10GR2> Questions:
1) Is that MERGE and UPDATE performs differently? Can you help me to understand why UPDATE returns ORA-01779?
(Here is the script I used for this example)
drop table t purge;
create table t(
col1 number,
col2 number,
col3 number,
col4 number);
insert into t values(1,1,1,null);
insert into t values(2,2,2,null);
insert into t values(3,3,3,null);
create or replace type
myobj as object(col1 number,
col2 number,
col3 number,
col4 number);
/
create or replace type myobjntt is table of myobj;
/
alter table t add constraint t_pk primary key(col1);
October 24, 2011 - 4:42 am UTC
What this is saying is that
"the rows in T, as produced by the selection in the update, could come out more than once."
"a single row in T might appear many times, not just once, in the output"
Since that can happen (there is nothing preventing it, I know your example does not have it happen, but the fact is - IT CAN happen), we do not permit the update - since the value to which t_col4 would be set to would ultimately depend on the order the rows were processed in (it would not be deterministic).
The merge can also suffer from this issue - but it does the check at runtime by design. Not compile time.
(Hint to you Rajesh, if you are going to change my names, supply the ENTIRE TEST CASE, the entire thing, just like I always do. Don't make us guess what your structures might look like. Put is all together in one concise location - remove the ambiguity)
ops$tkyte%ORA11GR2> create type my_scalar_type as object
2 ( col1 number, col2 number, col3 number, col4 number )
3 /
Type created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create type my_collection_type as table of my_scalar_type
2 /
Type created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create table t
2 ( col1 number, col2 number, col3 number, col4 number,
3 primary key(col1,col2,col3) )
4 /
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into t values(1,2,3,4);
1 row created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> declare
2 l_type my_collection_type := my_collection_type(my_scalar_type(1,1,1,1), my_scalar_type(2,2,2,2));
3 begin
4 merge into t using
5 (select * from table(l_type)) t1
6 on (t.col1 = t1.col1)
7 when matched then
8 update set t.col4 = t1.col4;
9 end;
10 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> select * from t;
COL1 COL2 COL3 COL4
---------- ---------- ---------- ----------
1 2 3 1
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> declare
2 l_type my_collection_type := my_collection_type(my_scalar_type(1,1,1,3), my_scalar_type(1,2,2,1));
3 begin
4 merge into t using
5 (select * from table(l_type)) t1
6 on (t.col1 = t1.col1)
7 when matched then
8 update set t.col4 = t1.col4;
9 end;
10 /
declare
*
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables
ORA-06512: at line 4
the merge failed the second time around because the database realized the modification was "flaky" - not deterministic.
Merge & Update
Rajeshwaran Jeyabal, October 24, 2011 - 1:08 pm UTC
Yep, This is what i needed Tom, Thanks.
The merge can also suffer from this issue - but it does the check at runtime by design. Not compile time
Error while updating collection
Girish, January 25, 2013 - 4:28 am UTC
Hi Tom,
I am doing following steps to update why the error is occuring
SQL> create or replace type myTableType as table of number;
/
Type created.
SQL> create or replace function str2tbl( p_str in varchar2 ) return myTableType
as
l_str long default p_str || ',';
l_n number;
l_data myTableType := myTabletype();
begin
loop
l_n := instr( l_str, ',' );
exit when (nvl(l_n,0) = 0);
l_data.extend;
l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
l_str := substr( l_str, l_n+1 );
end loop;
return l_data;
end;
/
1 merge into test_2013 a
2 using table(cast( str2tbl('ABC,DEF,XYZ,PQR') as mytableType )) b
3 on (1=1)
4 when matched then
5* update set c2=b.column_value
TEST@orcl@25-JAN-13> /
merge into test_2013 a
*
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables
Regards,
Girish
January 30, 2013 - 1:43 pm UTC
it wouldn't matter if you used a real table, the collection is a red herring here (not relevant)
you are merging on "1=1" so every row in test_2013 is being joined with every row in B
you are trying to update the first row in test_2013 to one of ABC, DEF, XYZ, PQR - and then trying to update the FIRST ROW (again) to one of ABC, DEF, XYZ, PQR (minus whatever you just set it to) and so on.
so the result of the merge is not deterministic. depending on the arbitrary ordering of the rows in the join - you'd get different answers each time you ran it.
hence the error.
ops$tkyte%ORA11GR2> create table t ( c2 varchar2(10) );
Table created.
ops$tkyte%ORA11GR2> insert into t values ( null );
1 row created.
ops$tkyte%ORA11GR2> create table b
2 as
3 select 'ABC' column_value from dual union all
4 select 'DEF' column_value from dual union all
5 select 'XYZ' column_value from dual union all
6 select 'PQR' column_value from dual;
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> merge into t
2 using b
3 on (1=1)
4 when matched then update set c2 = b.column_value;
using b
*
ERROR at line 2:
ORA-30926: unable to get a stable set of rows in the source tables
Sending email in tablular format
A reader, December 27, 2014 - 1:10 am UTC
PROCEDURE P_SEND_LOAD_DETAILS(
i_component_name IN VARCHAR2 DEFAULT 'RAM' ,
I_HEADER IN VARCHAR2 ,
I_sQL_STATEMENT IN VARCHAR2 )
IS
ctxh DBMS_XMLGEN.ctxHandle;
queryresult XMLTYPE;
xslt_tranfsorm XMLTYPE;
l_text CLOB;
L_FRM VARCHAR2( 200 ) ;
l_col_count NUMBER;
l_col_descs dbms_sql.desc_tab;
l_cursor NUMBER;
l_table_name VARCHAR2( 50 );
L_ALL_COL_DESC VARCHAR2( 4000 );
L_ALL_COL_STYLE VARCHAR2( 4000 );
BEGIN
-- Define the table name
l_table_name := 'tab';
--
-- Build the SQL statement
l_cursor := dbms_sql.open_cursor;
--
-- Parse the SQL statement
dbms_sql.parse( c => l_cursor , STATEMENT => I_sql_statement , language_flag
=> dbms_sql.native );
--
-- Describe the columns of the cursor
dbms_sql.describe_columns( c => l_cursor , col_cnt => l_col_count , desc_t =>
l_col_descs );
--
-- Output the results: Header
dbms_output.put_line( 'Description of table ' || l_table_name || ':' ) ;
--
-- Output the results: Column names
FOR i IN 1..l_col_count
LOOP
L_ALL_COL_DESC := L_ALL_COL_DESC ||' <th>' || l_col_descs( i ).col_name ||
' </th>'||CHR( 13 );
L_ALL_COL_STYLE := L_ALL_COL_STYLE||
' <td style="text-align:left;"> <xsl:value-of select="'||l_col_descs( i )
.col_name||'"/> </td> '||CHR( 13 );
END LOOP;
-- SQL Query :
ctxh := DBMS_XMLGEN.newContext( I_sqL_statement ) ;
-- XSLT Transformation to HTML :
xslt_tranfsorm := NEW XMLTYPE(
'
<xsl:stylesheet xmlns:xsl="
http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:template match="/ROWSET">
<table>
<tr>'
||L_ALL_COL_DESC || '</tr>
<xsl:for-each select="ROW">
<tr>' ||
L_ALL_COL_STYLE||
'
</tr>
</xsl:for-each>
</table>
</xsl:template>
</xsl:stylesheet>'
) ;
queryresult := DBMS_XMLGEN.getXMLType( ctxh ) .transform( xslt_tranfsorm ) ;
L_TEXT := '<h3><center>'||i_header||'</center></h3> ' ||
queryresult.getClobVal( ) ;
FOR C1 IN
(
SELECT
*
FROM
EMAIL_CMPNT_LKUP ,
GLOBAL_NAME
WHERE
COMPONENT_NAME = I_COMPONENT_NAME
)
LOOP
L_FRM := C1.GLOBAL_NAME||'_DATABASE ';
send_mail( L_FRM , C1.EMAIL_ADDRESS , i_header , l_text ) ;
END LOOP;
end p_send_load_details;
I have query on collections if they can be used to replace a table
Mohammed Imran, August 06, 2015 - 2:39 pm UTC
Hi Tom,
I am analyzing a performance issue which includes a function having logic which loops through(collection) table records(around 1 million). In each iteration updates(no inserts/deletes) are done on few other records of the same table based on some validations. I believe multiple select and update statements on the table in loop are cause of performance issue.
1st question, Is it possible to rewrite the code to fetch all table data into a collection and do select’s and update’s on the collection in loop rather than on table?
2nd question, will the performance improve by doing so? Please suggest.
function algorithm.
CREATE OR REPLACE TYPE "TYPE_R" IS OBJECT(col1,col2…);
CREATE OR REPLACE TYPE " type_l " IS TABLE OF TYPE_R;
Function F1()
l_line type_l ;
l_line2 type_l ;
Begin
select bulk collect into l_line from lines_tab; --around 1Million records.
For var_l IN l_line LOOP
select * into l_line2 from TABLE (CAST (l_line2 AS *Table Object)) where col1 < -100 and col2 = :2 and nvl(col3,’~’) = :3 and col4 IN (‘X’,’Y’,’Z’);
--Some Validations
Update table … ---need to figure how to update a collection
End loop;
END;
Awaiting for your response.
Thanks,
Imran.
I have query on collections if they can be used to replace a table
Mohammed Imran, August 07, 2015 - 4:08 am UTC
Sorry I missed to mention about stats in my previous post above. The function algorithm mentioned above is new proposed algorithm and with the existing logic it takes around 75 minutes to process to 1 million records.
DB version - 11.2.0.3.0 - 64bit Production
DB Server OS – Linux.
Explanation with small demo will be more helpful. Thanks.