Hi All,
I am trying to get sql%rowcount working in my procedure and I must be something very simple but I'm completely stumped.
Here is my test data:
TABLES:
create table MY_JOBS (job_name varchar2(60), language varchar2(3));
create table DOCUMENTS (user_name varchar2(60), language varchar2(3));
create table TRANSLATIONS (table_name varchar2(30), column_name varchar2(30), english_value varchar2(4000), translated_value varchar2(4000), completion_status varchar2(1), language varchar2(3));
VALUES:
insert into my_jobs (job_name, language) values ('MANAGER', 'US');
insert into my_jobs (job_name, language) values ('MANAGER', 'ZHS');
insert into my_jobs (job_name, language) values ('MANAGER', 'JA');
insert into my_jobs (job_name, language) values ('MANAGER', 'F');
Insert into my_jobs (job_name, language) values ('CLERK', 'US');
Insert into my_jobs (job_name, language) values ('CLERK', 'ZHS');
Insert into my_jobs (job_name, language) values ('CLERK', 'JA');
Insert into my_jobs (job_name, language) values ('CLERK', 'F');
commit;
insert into translations (table_name, column_name, english_value, translated_value, language, completion_status)
values ('MY_JOBS', 'JOB_NAME', 'MANAGER', '经理', 'ZHS', 'Y');
insert into translations (table_name, column_name, english_value, translated_value, language, completion_status)
values ('MY_JOBS', 'JOB_NAME', 'MANAGER', 'マネージャー', 'JA', 'Y');
insert into translations (table_name, column_name, english_value, translated_value, language, completion_status)
values ('MY_JOBS', 'JOB_NAME', 'MANAGER', 'responsable', 'F', 'Y');
insert into translations (table_name, column_name, english_value, translated_value, language, completion_status)
values ('MY_JOBS', 'JOB_NAME', 'CLERK', '书记', 'ZHS', 'Y');
insert into translations (table_name, column_name, english_value, translated_value, language, completion_status)
values ('MY_JOBS', 'JOB_NAME', 'CLERK', '店員', 'JA', 'Y');
insert into translations (table_name, column_name, english_value, translated_value, language, completion_status)
values ('MY_JOBS', 'JOB_NAME', 'CLERK', 'employé', 'F', 'Y');
Insert into TRANSLATIONS (TABLE_NAME,COLUMN_NAME,ENGLISH_VALUE,TRANSLATED_VALUE,COMPLETION_STATUS,LANGUAGE) values ('DOCUMENTS','USER_NAME','Images','Images','Y','F');
Insert into TRANSLATIONS (TABLE_NAME,COLUMN_NAME,ENGLISH_VALUE,TRANSLATED_VALUE,COMPLETION_STATUS,LANGUAGE) values ('DOCUMENTS','USER_NAME','Video','Vidéo','Y','F');
Insert into TRANSLATIONS (TABLE_NAME,COLUMN_NAME,ENGLISH_VALUE,TRANSLATED_VALUE,COMPLETION_STATUS,LANGUAGE) values ('DOCUMENTS','USER_NAME','Data Sheet','データシート','Y','JA');
Insert into TRANSLATIONS (TABLE_NAME,COLUMN_NAME,ENGLISH_VALUE,TRANSLATED_VALUE,COMPLETION_STATUS,LANGUAGE) values ('DOCUMENTS','USER_NAME','Images','イメージ','Y','JA');
Insert into TRANSLATIONS (TABLE_NAME,COLUMN_NAME,ENGLISH_VALUE,TRANSLATED_VALUE,COMPLETION_STATUS,LANGUAGE) values ('DOCUMENTS','USER_NAME','Data Sheet','데이터 시트','Y','KO');
Insert into TRANSLATIONS (TABLE_NAME,COLUMN_NAME,ENGLISH_VALUE,TRANSLATED_VALUE,COMPLETION_STATUS,LANGUAGE) values ('DOCUMENTS','USER_NAME','Images','이미지','Y','KO');
Insert into TRANSLATIONS (TABLE_NAME,COLUMN_NAME,ENGLISH_VALUE,TRANSLATED_VALUE,COMPLETION_STATUS,LANGUAGE) values ('DOCUMENTS','USER_NAME','Price List','가격 목록','Y','KO');
Insert into TRANSLATIONS (TABLE_NAME,COLUMN_NAME,ENGLISH_VALUE,TRANSLATED_VALUE,COMPLETION_STATUS,LANGUAGE) values ('DOCUMENTS','USER_NAME','Video','비디오','Y','KO');
Insert into TRANSLATIONS (TABLE_NAME,COLUMN_NAME,ENGLISH_VALUE,TRANSLATED_VALUE,COMPLETION_STATUS,LANGUAGE) values ('DOCUMENTS','USER_NAME','Video','ビデオ','Y','JA');
Insert into TRANSLATIONS (TABLE_NAME,COLUMN_NAME,ENGLISH_VALUE,TRANSLATED_VALUE,COMPLETION_STATUS,LANGUAGE) values ('DOCUMENTS','USER_NAME','Price List','Liste de prix','Y','F');
Insert into TRANSLATIONS (TABLE_NAME,COLUMN_NAME,ENGLISH_VALUE,TRANSLATED_VALUE,COMPLETION_STATUS,LANGUAGE) values ('DOCUMENTS','USER_NAME','Price List','価格リスト','Y','JA');
Insert into TRANSLATIONS (TABLE_NAME,COLUMN_NAME,ENGLISH_VALUE,TRANSLATED_VALUE,COMPLETION_STATUS,LANGUAGE) values ('DOCUMENTS','USER_NAME','Data Sheet','Fiche produit','Y','F');
commit;
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Data Sheet','US');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Images','US');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Price List','US');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Video','US');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Data Sheet','ZHS');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Images','ZHS');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Price List','ZHS');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Video','ZHS');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Data Sheet','F');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Images','F');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Price List','F');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Video','F');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Data Sheet','E');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Images','E');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Price List','E');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Video','E');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Data Sheet','JA');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Images','JA');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Price List','JA');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Video','JA');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Data Sheet','D');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Images','D');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Price List','D');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Video','D');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Data Sheet','NL');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Images','NL');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Price List','NL');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Video','NL');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Data Sheet','AR');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Images','AR');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Price List','AR');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Video','AR');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Data Sheet','PTB');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Images','PTB');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Price List','PTB');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Video','PTB');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Data Sheet','KO');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Images','KO');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Price List','KO');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Video','KO');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Data Sheet','ZHT');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Images','ZHT');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Price List','ZHT');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Video','ZHT');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Data Sheet','I');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Images','I');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Price List','I');
Insert into DOCUMENTS (USER_NAME,LANGUAGE) values ('Video','I');
commit;
PROCEDURE:
create or replace
procedure language_update (p_table IN varchar2, p_column IN varchar2)
IS
l_sql VARCHAR2(4000);
l_rows_updated number :=0;
l_rows_processed number := 0;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
l_sql := 'Update '||p_table||' set ' ||p_column|| ' = :newval where '||p_column|| ' = :engval and language = :lang';
for c2 IN (SELECT TABLE_NAME, COLUMN_NAME, TRANSLATED_VALUE, ENGLISH_VALUE, LANGUAGE FROM TRANSLATIONS WHERE COMPLETION_STATUS = 'Y' and table_name = p_table and column_name = p_column)
LOOP
BEGIN
EXECUTE IMMEDIATE l_sql USING c2.translated_value, c2.english_value, c2.language;
l_rows_updated := l_rows_updated + sql%rowcount;
l_rows_processed := (l_rows_processed + 1);
exception
when others then null;
end;
end loop;
commit;
dbms_output.put_line ('Records processed: '||l_rows_processed);
dbms_output.put_line ('Records updated: '||l_rows_updated);
END;
My first call to the procedure is as follows:
set serveroutput ON
begin
language_update( p_table=>'MY_JOBS', p_column=>'JOB_NAME');
end;
anonymous block completed
Records processed: 6
Records updated: 6
This is what I expect. My second call yields the following:
anonymous block completed
Records processed: 6
Records updated: 0
Again what I expect since I just ran these parameters and there is nothing left to update.
Next I change my input parameters:
set serveroutput ON
begin
language_update( p_table=>'DOCUMENTS', p_column=>'USER_NAME');
end;
anonymous block completed
Records processed: 12
Records updated: 12
So far so good. But the second time I run these parameteres, I get this:
anonymous block completed
Records processed: 12
Records updated: 1
I can't figure out why its returning that one record was updated. I'm expecting zero since it's the same parameters I just ran. Can anyone shed light on what I'm doing wrong? In my production instance I'm also seeing some strange results in the number of rows processed. In this case I'm seeing 10 instead of 12, but I am unable to reproduce that issue. I'm sure the db isn't actually updating one record but I'm stumped as why my code is returning that result.
Any ideas or clues are very much appreciated,
john