Prev: Requirement:SAp JNET
Next: Empty pages marked corrupt
From: MJSobol on 21 May 2010 14:13
Environment: Oracle 10g2
Windows 32-bit, Standard Edition

I have created a Trigger which compiles OK.
Here is the Trigger:
CREATE or replace TRIGGER au_s1_contract_misc_columnitem
AFTER UPDATE ON s1_contract_misc_column_item
FOR EACH ROW
DECLARE
c_misc_column_description varchar2(50);
quote char(1);

BEGIN
quote := chr(39);

c_misc_column_description :=
CASE
WHEN :new.misc_column_nbr = 1 THEN
'misc_column_description1'
WHEN :new.misc_column_nbr = 2 THEN
'misc_column_description2'
WHEN :new.misc_column_nbr = 3 THEN
'misc_column_description3'
WHEN :new.misc_column_nbr = 4 THEN
'misc_column_description4'
WHEN :new.misc_column_nbr = 5 THEN
'misc_column_description5'
WHEN :new.misc_column_nbr = 6 THEN
'misc_column_description6'
WHEN :new.misc_column_nbr = 7 THEN
'misc_column_description7'
WHEN :new.misc_column_nbr = 8 THEN
'misc_column_description8'
END;

IF (:new.description <> :old.description) THEN
BEGIN
EXECUTE IMMEDIATE 'UPDATE s1_contract SET ' ||
c_misc_column_description || ' = ' || Quote || :new.description ||
Quote || 'WHERE ' || :new.misc_column_item_nbr || ' =
s1_contract.misc_column_item_nbr1';
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Updating misc_column_description1');
dbms_output.put_line('SQLCODE= ' || SQLCODE);
dbms_output.put_line('SQLERRM= ' || SQLERRM);
END;

BEGIN
EXECUTE IMMEDIATE 'UPDATE s1_contract SET ' ||
c_misc_column_description || ' = ' || Quote || :new.description ||
Quote || 'WHERE ' || :new.misc_column_item_nbr || ' =
s1_contract.misc_column_item_nbr2';
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Updating misc_column_description2');
dbms_output.put_line('SQLCODE= ' || SQLCODE);
dbms_output.put_line('SQLERRM= ' || SQLERRM);
END;

BEGIN
EXECUTE IMMEDIATE 'UPDATE s1_contract SET ' ||
c_misc_column_description || ' = ' || Quote || :new.description ||
Quote || 'WHERE ' || :new.misc_column_item_nbr || ' =
s1_contract.misc_column_item_nbr3';
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Updating misc_column_description3');
dbms_output.put_line('SQLCODE= ' || SQLCODE);
dbms_output.put_line('SQLERRM= ' || SQLERRM);
END;

BEGIN
EXECUTE IMMEDIATE 'UPDATE s1_contract SET ' ||
c_misc_column_description || ' = ' || Quote || :new.description ||
Quote || 'WHERE ' || :new.misc_column_item_nbr || ' =
s1_contract.misc_column_item_nbr4';
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Updating misc_column_description4');
dbms_output.put_line('SQLCODE= ' || SQLCODE);
dbms_output.put_line('SQLERRM= ' || SQLERRM);
END;

BEGIN
EXECUTE IMMEDIATE 'UPDATE s1_contract SET ' ||
c_misc_column_description || ' = ' || Quote || :new.description ||
Quote || 'WHERE ' || :new.misc_column_item_nbr || ' =
s1_contract.misc_column_item_nbr5';
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Updating misc_column_description5');
dbms_output.put_line('SQLCODE= ' || SQLCODE);
dbms_output.put_line('SQLERRM= ' || SQLERRM);
END;

BEGIN
EXECUTE IMMEDIATE 'UPDATE s1_contract SET ' ||
c_misc_column_description || ' = ' || Quote || :new.description ||
Quote || 'WHERE ' || :new.misc_column_item_nbr || ' =
s1_contract.misc_column_item_nbr6';
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Updating misc_column_description6');
dbms_output.put_line('SQLCODE= ' || SQLCODE);
dbms_output.put_line('SQLERRM= ' || SQLERRM);
END;

BEGIN
EXECUTE IMMEDIATE 'UPDATE s1_contract SET ' ||
c_misc_column_description || ' = ' || Quote || :new.description ||
Quote || 'WHERE ' || :new.misc_column_item_nbr || ' =
s1_contract.misc_column_item_nbr7';
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Updating misc_column_description7');
dbms_output.put_line('SQLCODE= ' || SQLCODE);
dbms_output.put_line('SQLERRM= ' || SQLERRM);
END;

BEGIN
EXECUTE IMMEDIATE 'UPDATE s1_contract SET ' ||
c_misc_column_description || ' = ' || Quote || :new.description ||
Quote || 'WHERE ' || :new.misc_column_item_nbr || ' =
s1_contract.misc_column_item_nbr8';
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Updating misc_column_description8');
dbms_output.put_line('SQLCODE= ' || SQLCODE);
dbms_output.put_line('SQLERRM= ' || SQLERRM);
END;

END IF;

END;
/

Here is some sample data:
select * from s1_contract_misc_column_item;
MISC_COLUMN_ITEM_NBR MISC_COLUMN_NBR SORT_ORDER DESCRIPTION
-------------------- --------------- ----------
--------------------------------------------------
1 1 7 Ayr
2 1 1 Arthur
3 1 3 Acton
4 1 4 Alma
5 1 2 Ariss
6 1 5 Arkell
7 1 6 Aberfoyle
8 2 1 Bayfield
9 3 1 Columbia
10 4 1 Decatur-1
11 5 1 Elmira
12 6 1 Flesherton

12 rows selected.

Here is the UPDATE statement I am issuing:
update s1_contract_misc_column_item
set description = 'Decatur'
where misc_column_item_nbr = 10;

and here is the error I am getting:
Updating misc_column_description4
SQLCODE= 100
SQLERRM= ORA-01403: no data found
ORA-01403: no data found
ORA-06512: at "T1310.BU_S1_CONTRACT", line 1
ORA-04088: error during execution of trigger 'T1310.BU_S1_CONTRACT'

1 row updated.

I do not understand this error since there is data that SHOULD be
updated:
CONTRACT_NBR MISC_COLUMN_ITEM_NBR1 MISC_COLUMN_ITEM_NBR2
MISC_COLUMN_ITEM_NBR3 MISC_COLUMN_ITEM_NBR4 MISC_COLUMN_ITEM_NBR5
MISC_COLUMN_ITEM_NBR6
MISC_COLUMN_DESCRIPTION1
MISC_COLUMN_DESCRIPTION2
MISC_COLUMN_DESCRIPTION3
MISC_COLUMN_DESCRIPTION4
MISC_COLUMN_DESCRIPTION5
MISC_COLUMN_DESCRIPTION6
------------ --------------------- ---------------------
--------------------- --------------------- ---------------------
---------------------
--------------------------------------------------
--------------------------------------------------
--------------------------------------------------
--------------------------------------------------
--------------------------------------------------
--------------------------------------------------
754 5
8 9 10
11 12
Ariss
Bayfield
Columbia
Decatur
Elmira Flesherton
7639
1
Ayr
7640
1
Ayr
7670
3
Acton
7661
5
Ariss
7919 5
8
Ariss Bayfield
7922 5
8
Ariss Bayfield
7923 5
8
Ariss Bayfield
7973
5
Ariss
7911 3
8
Acton Bayfield
7912
5
Ariss
7920 1
8 9 10
11 12
Ayr
Bayfield
Columbia
Decatur
Elmira Flesherton
7803 1
8 9 10
11 12
Ayr
Bayfield
Columbia
Decatur
Elmira Flesherton
7804 5
8
Ariss Bayfield
8022 5
8 9 10
11 12
Ariss
Bayfield
Columbia
Decatur
Elmira Flesherton
8028 5
8 9 10
11 12
Ariss
Bayfield
Columbia
Decatur
Elmira Flesherton
8249 5
8 9 10
11 12
Ariss
Bayfield
Columbia
Decatur
Elmira Flesherton
8138 5
8 9
10
Ariss
Bayfield
Columbia Decatur
8139 5
8
Ariss Bayfield
8266 5
8
Ariss Bayfield
8140 3
8
Acton Bayfield
8151 5
8 9 10
11 12
Ariss
Bayfield
Columbia
Decatur
Elmira Flesherton
8212 5
8 9 10
11 12
Ariss
Bayfield
Columbia
Decatur
Elmira Flesherton
8268 5
8
Ariss Bayfield
8269 5
8
Ariss Bayfield
8518
5
Ariss
8519
5
Ariss
8525
5
Ariss
8538
5
Ariss
8539
5
Ariss
8540
5
Ariss
8544
5
Ariss
8546
5
Ariss
8588
2
Arthur
8626 2
8 9 10
11 12
Arthur
Bayfield
Columbia
Decatur
Elmira Flesherton
8627 2
8 9 10
11 12
Arthur
Bayfield
Columbia
Decatur
Elmira Flesherton
8677 2
8
Arthur Bayfield
8693 2
8
Arthur Bayfield
8698 2
8 9 10
11 12
Arthur
Bayfield
Columbia
Decatur
Elmira Flesherton
8699 2
8 9 10
11 12
Arthur
Bayfield
Columbia
Decatur
Elmira Flesherton
8700 2
8 9 10
11 12
Arthur
Bayfield
Columbia
Decatur
Elmira Flesherton
8701 2
8 9 10
11 12
Arthur
Bayfield
Columbia
Decatur
Elmira Flesherton
8702 2
8 9 10
11 12
Arthur
Bayfield
Columbia
Decatur
Elmira Flesherton
8703 2
8 9 10
11 12
Arthur
Bayfield
Columbia
Decatur
Elmira Flesherton
8687 2
8
Arthur Bayfield

45 rows selected.

I.E. any value of ‘Decatur’ in column MISC_COLUMN_DESCRIPTION4 should
have been changed to ‘Decatur-1’.

When I re-select the data in s1_contract_misc_column_item table, I
get these results;
MISC_COLUMN_ITEM_NBR MISC_COLUMN_NBR SORT_ORDER DESCRIPTION
-------------------- --------------- ----------
--------------------------------------------------
1 1 7 Ayr
2 1 1 Arthur
3 1 3 Acton
4 1 4 Alma
5 1 2 Ariss
6 1 5 Arkell
7 1 6 Aberfoyle
8 2 1 Bayfield
9 3 1 Columbia
10 4 1 Decatur-1
11 5 1 Elmira
12 6 1 Flesherton

12 rows selected.

I am confused, also frustrated with working with DYNAMIC SQL, it can
be such a PITA!!!!

Any assistance would be appreciated.

Murray Sobol
dbcSMARTsoftware inc.
From: Vladimir M. Zakharychev on 21 May 2010 17:37
On May 21, 10:13 pm, MJSobol <murray_so...(a)dbcsmartsoftware.com>
wrote:
> Environment: Oracle 10g2
> Windows 32-bit, Standard Edition
>
> I have created a Trigger which compiles OK.
> Here is the Trigger:

[skip]

>
> I am confused, also frustrated with working with DYNAMIC SQL, it can
> be such a PITA!!!!
>

Then why are you using it??? Dynamic SQL approach you chose is not
appropriate here and even if it was, it's not used properly (hint:
read about bind variables and USING clause of EXECUTE IMMEDIATE - as
it is, your trigger will only help in creating completely unscalable
application.)

I didn't completely understand what that code is assumed to do as it's
hardly readable and you didn't present the data model (at least in the
scope of the trigger,) but it seems to me that (1) the data model is
utterly wrong and (2) you are trying to work around that wrong model
with trigger code that is no better.

Now let's 'trace' the trigger. You issue

update s1_contract_misc_column_item
set description = 'Decatur'
where misc_column_item_nbr = 10;

In the trigger:

:new.misc_column_item_nbr = 10
:new.misc_column_nbr = 4
:new.description = 'Decatur'
c_misc_column_description = 'misc_column_description4'

This triggers the following statements to be executed in order of
appearance:

UPDATE s1_contract SET misc_column_description4 = 'Decatur' WHERE 10 =
s1_contract.misc_column_item_nbr1
UPDATE s1_contract SET misc_column_description4 = 'Decatur' WHERE 10 =
s1_contract.misc_column_item_nbr2
UPDATE s1_contract SET misc_column_description4 = 'Decatur' WHERE 10 =
s1_contract.misc_column_item_nbr3
UPDATE s1_contract SET misc_column_description4 = 'Decatur' WHERE 10 =
s1_contract.misc_column_item_nbr4
UPDATE s1_contract SET misc_column_description4 = 'Decatur' WHERE 10 =
s1_contract.misc_column_item_nbr5
UPDATE s1_contract SET misc_column_description4 = 'Decatur' WHERE 10 =
s1_contract.misc_column_item_nbr6
UPDATE s1_contract SET misc_column_description4 = 'Decatur' WHERE 10 =
s1_contract.misc_column_item_nbr7
UPDATE s1_contract SET misc_column_description4 = 'Decatur' WHERE 10 =
s1_contract.misc_column_item_nbr8

Assuming that query output after "I do not understand this error since
there is data that SHOULD be updated:" is from "SELECT * FROM
s1_contract", I don't see at least two columns that the trigger would
attempt to reference in update statements above (that are only
misc_column_item_nbr's from 1 to 6 while there are 8 update
statements,) so it will fail anyway as it reaches 7th update.

Second point: UPDATE itself does NOT generate NO_DATA_FOUND exception
if it didn't find any rows to update, but triggers firing on it can
throw exceptions as they please, which is the case. The exception
comes from your BEFORE UPDATE ON S1_CONTRACT trigger BU_S1_CONTRACT so
that's what you need to check.

Though I'd recommend you to rethink and reimplement your data model
first. Normalize it to BCNF or 3NF and things will become much easier.
And don't be afraid of joins - they are not really that evil.

Hth,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com


From: Mark D Powell on 22 May 2010 10:37
On May 21, 5:37 pm, "Vladimir M. Zakharychev"
<vladimir.zakharyc...(a)gmail.com> wrote:
> On May 21, 10:13 pm, MJSobol <murray_so...(a)dbcsmartsoftware.com>
> wrote:
>
> > Environment: Oracle 10g2
> > Windows 32-bit, Standard Edition
>
> > I have created a Trigger which compiles OK.
> > Here is the Trigger:
>
> [skip]
>
>
>
> > I am confused, also frustrated with working with DYNAMIC SQL, it can
> > be such a PITA!!!!
>
> Then why are you using it??? Dynamic SQL approach you chose is not
> appropriate here and even if it was, it's not used properly (hint:
> read about bind variables and USING clause of EXECUTE IMMEDIATE - as
> it is, your trigger will only help in creating completely unscalable
> application.)
>
> I didn't completely understand what that code is assumed to do as it's
> hardly readable and you didn't present the data model (at least in the
> scope of the trigger,) but it seems to me that (1) the data model is
> utterly wrong and (2) you are trying to work around that wrong model
> with trigger code that is no better.
>
> Now let's 'trace' the trigger. You issue
>
> update s1_contract_misc_column_item
> set description = 'Decatur'
> where misc_column_item_nbr = 10;
>
> In the trigger:
>
> :new.misc_column_item_nbr = 10
> :new.misc_column_nbr = 4
> :new.description = 'Decatur'
> c_misc_column_description = 'misc_column_description4'
>
> This triggers the following statements to be executed in order of
> appearance:
>
> UPDATE s1_contract SET misc_column_description4 = 'Decatur' WHERE 10 =
> s1_contract.misc_column_item_nbr1
> UPDATE s1_contract SET misc_column_description4 = 'Decatur' WHERE 10 =
> s1_contract.misc_column_item_nbr2
> UPDATE s1_contract SET misc_column_description4 = 'Decatur' WHERE 10 =
> s1_contract.misc_column_item_nbr3
> UPDATE s1_contract SET misc_column_description4 = 'Decatur' WHERE 10 =
> s1_contract.misc_column_item_nbr4
> UPDATE s1_contract SET misc_column_description4 = 'Decatur' WHERE 10 =
> s1_contract.misc_column_item_nbr5
> UPDATE s1_contract SET misc_column_description4 = 'Decatur' WHERE 10 =
> s1_contract.misc_column_item_nbr6
> UPDATE s1_contract SET misc_column_description4 = 'Decatur' WHERE 10 =
> s1_contract.misc_column_item_nbr7
> UPDATE s1_contract SET misc_column_description4 = 'Decatur' WHERE 10 =
> s1_contract.misc_column_item_nbr8
>
> Assuming that query output after "I do not understand this error since
> there is data that SHOULD be updated:" is from "SELECT * FROM
> s1_contract", I don't see at least two columns that the trigger would
> attempt to reference in update statements above (that are only
> misc_column_item_nbr's from 1 to 6 while there are 8 update
> statements,) so it will fail anyway as it reaches 7th update.
>
> Second point: UPDATE itself does NOT generate NO_DATA_FOUND exception
> if it didn't find any rows to update, but triggers firing on it can
> throw exceptions as they please, which is the case. The exception
> comes from your BEFORE UPDATE ON S1_CONTRACT trigger BU_S1_CONTRACT so
> that's what you need to check.
>
> Though I'd recommend you to rethink and reimplement your data model
> first. Normalize it to BCNF or 3NF and things will become much easier.
> And don't be afraid of joins - they are not really that evil.
>
> Hth,
>    Vladimir M. Zakharychev
>    N-Networks, makers of Dynamic PSP(tm)
>    http://www.dynamicpsp.com

One thing I noticed about a couple of the dynamic SQL update statement
is that the use of dynamic SQL is not needed to begin with. A series
of static UPDATE statements could be coded. All of the base table
target row columns are available for use as bind variables to the
trigger SQL.

Non-normalized data as Vladimir noted almost always ends up causing
DML data value update issues. Data shold generally be fully
normailized for data integrity and SQL statement performance reasons.

HTH -- Mark D Powell --


 | 
Pages: 1
Prev: Requirement:SAp JNET
Next: Empty pages marked corrupt