PL/SQL

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: Last updated:
Related Questions
1

Answer Wiki

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

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • carlosdl
    Remember to set serveroutput on if you run your procedure from sql*plus.
    85,885 pointsBadges:
    report
  • Inprise
    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
    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.
    85,885 pointsBadges:
    report
  • Sivu
    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
    yes it s the problem,where clause causing the problem.i am looking into my code.
    610 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: