PL/SQL
INSERT statement, Oracle development, Oracle PL/SQL, PL/SQL, PL/SQL block, SQL, SQL Timestamp, Timestamp, UPDATE statement
Hi Experts,
I would like to mentione here i was trying to test one package and found the package has a procedure named SET() which hav UPDATE on Report table and INSERT on A_R table in same transaction
Both the above mentioned tables hav a tim_upt column which s given as systimestamp
and strange it was the second insert reflects the correct timestamp whereas the UPDATE (Report table) s not reflecting the tim_upt
Though it is like this..
procedure set(tim_upt -- parameter varaible)
BEGIN
UPDATE Report
set different attributes,
tim_upt = systimestamp
where
some condition;
Insert into A_R (......,tim_upt)values(....systimestamp);
Exception
when others then
raise_appl_err(.........);
END;
It compiles without any error ,on run of test for that set of tables it is not throwing any exception and but the 1st table is not reflected with time of update ...
pls guide me on this
Software/Hardware used:
Software/Hardware used:
ASKED:
February 17, 2009 10:47 AM
UPDATED:
February 20, 2009 10:12 AM
Answer Wiki:
Updates don't throw any exception when no row is affected. Maybe there is no row matching the update criteria.
You might want to check the value of sql%rowcount after the update.
Something like this:
<pre>UPDATE Report
set different attributes,
tim_upt = systimestamp
where
some condition;
if sql%rowcount < 1 then
dbms_output.put_line('No rows afected');
end if;</pre>
To see all answers submitted to the Answer Wiki: View Answer History.
Remember to set serveroutput on if you run your procedure from sql*plus.
Hello,
Yes i checked with sqlcount — its don’t hav rows to update,but the update column and INSERT column on second table are same,defeintly if record Insert happening,UPDATEshould also happen
It looks strange
Any table creation criteria stops from this update??
Inserts don’t include a condition. Anytime you execute an insert, the desired row will be inserted unless an error occurs, on the other hand updates will affect only rows satisfying the WHERE clause (if it is present). So, the fact that the insert is being done doesn’t mean the update will for sure affect some row, because it have a WHERE clause that needs to be satisfied.
The WHERE clause of the update statement is key here.
Yes!! what carlos saying is perfect can you send check the Where Clause criteria. Are you finding any rows with this condition??
yes it s the problem,where clause causing the problem.i am looking into my code.