PL/SQL
Home > IT Answers > Oracle > PL/SQL
Inprise
540 pts.
0
Q:
PL/SQL
PL/SQL block, SQL Timestamp, UPDATE statement, INSERT statement, SQL, Timestamp, PL/SQL, Oracle PL/SQL, Oracle development
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: Feb 17 2009  10:47 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Carlosdl
29855 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
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:

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;
Last Answered: Feb 17 2009  1:39 PM GMT by Carlosdl   29855 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Carlosdl   29855 pts.  |   Feb 17 2009  1:41PM GMT

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

 

Inprise   540 pts.  |   Feb 18 2009  9:06AM 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??

 

Carlosdl   29855 pts.  |   Feb 18 2009  8:56PM 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.

 

Sivu   105 pts.  |   Feb 20 2009  7:12AM GMT

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

 

Inprise   540 pts.  |   Feb 20 2009  10:12AM GMT

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

 
0