Solved

How to check whether insert was successful in a PL/SQL procedure?

Posted on 2014-12-27
1
344 Views
Last Modified: 2014-12-27
Dear Experts,

I would like to run TRUNCATE TABLE statement in below code IF ONLY previous insert was successful. How can I implement this? It would be great if you offer me a piece of complemantary code.

BR


DECLARE
.......
BEGIN
.......
INSERT INTO MAIN_TABLE SELECT * FROM TEMP_TABLE;
COMMIT;
EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_TABLE DROP STORAGE';
END;
0
Comment
Question by:GurcanK
1 Comment
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40519654
If your insert or the commit fails then you'll raise an exception and the block will end (or jump to an exception handler if you declare one) so, your truncate is already in the correct place to ensure it only executes after a successful insert and commit
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question