PL/SQL

Inprise

610 pts.

Tags:

INSERT statement

Oracle development

Oracle PL/SQL

PL/SQL

PL/SQL block

SQL

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

Asked: February 17, 2009 10:47 AM Last updated: February 20, 2009 10:12 AM

Answer Wiki

Last updated: February 17, 2009 1:39 PM GMT

carlosdl84,495 pts.
History
Contributors
- Ordered by most recent
- carlosdl84,495 pts.

Thanks. We'll let you know when a new response is added.

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>

Discuss This Question: 5 Replies

carlosdl Feb 17, 2009 1:41 PM GMT

Remember to set serveroutput on if you run your procedure from sql*plus.

84,495 pointsBadges:

report
Inprise Feb 18, 2009 9:06 AM GMT

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??

610 pointsBadges:

report
carlosdl Feb 18, 2009 8:56 PM GMT

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.

84,495 pointsBadges:

report
Sivu Feb 20, 2009 7:12 AM GMT

Yes!! what carlos saying is perfect can you send check the Where Clause criteria. Are you finding any rows with this condition??

105 pointsBadges:

report
Inprise Feb 20, 2009 10:12 AM GMT

yes it s the problem,where clause causing the problem.i am looking into my code.

610 pointsBadges:

report