Solved

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

Posted on 2014-12-27
1
351 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Suggested Solutions

Title # Comments Views Activity
migration MS SQL database to Oracle 30 71
oracle DR - data guard failover. 18 60
how to find out the count of records based on the subfolders paths 11 39
oracle forms question 22 48
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

726 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