SAVE EXCEPTIONS with example

SAVE EXCEPTIONS:-

Since version 9i it is possible to do bulk DML using FORALL and use the SAVE EXCEPTIONS clause. It makes sure that all invalid rows – the exceptions – are saved into the sql%bulk_exceptions array, while all valid rows are still processed. This array stores a record for each invalid row, containing an ERROR_INDEX which is the iteration number during the FORALL statement and an ERROR_CODE which corresponds with the Oracle error code.

DECLARE
USER_EXCE EXCEPTION;
PRAGMA EXCEPTION_INIT(USER_EXCE,-24381);
TYPE T1 IS TABLE OF ALL_OBJECTS%ROWTYPE;
T2 T1;
CURSOR C1 IS SELECT * FROM ALL_OBJECTS WHERE ROWNUM<=1000;
BEGIN
OPEN C1;
LOOP
FETCH C1 BULK COLLECT INTO T2 LIMIT 100;
FORALL I IN 1..T2.COUNT SAVE EXCEPTIONS
INSERT INTO XXC05_OBJECT VALUES T2(I);
EXIT WHEN T2.COUNT=0;
END LOOP;
EXCEPTION
WHEN USER_EXCE
THEN
FOR I IN 1..SQL%BULK_EXCEPTIONS.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(SQL%BULK_EXCEPTIONS(I).ERROR_CODE);
END LOOP;
END;

DECLARE
TYPE T1 IS TABLE OF ALL_OBJECTS%ROWTYPE;
T2 T1;
CURSOR C1 IS SELECT * FROM ALL_OBJECTS WHERE ROWNUM<=1000;
BEGIN
OPEN C1;
LOOP
FETCH C1 BULK COLLECT INTO T2 LIMIT 100;
FORALL I IN 1..T2.COUNT SAVE EXCEPTIONS
INSERT INTO XXC05_OBJECT VALUES T2(I);
EXIT WHEN T2.COUNT=0;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
FOR I IN 1..SQL%BULK_EXCEPTIONS.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(SQL%BULK_EXCEPTIONS(I).ERROR_CODE);
END LOOP;
END;

Advertisements

2 comments

  1. I have been browsing online more than 4 hours today, yet I never found
    any interesting article like yours. It’s pretty worth enhough ffor me.
    In my view, if all webmasters and bloggers made good
    content as you did, the net will bbe a lot more useful han ever before.|
    I couldn’t refrain from commenting. Perfectly written!|
    I will rivht away clutch your rss as I can not in finding your e-mail subscription link or e-newsletter service.
    Do you’ve any? Please let me realize sso that I
    mayy just subscribe. Thanks.|
    It is appropriate time tto make some plans for the fture and it’s timee to be happy.
    I’ve read tgis post and if I could I wish to suggest you
    few interesting things or tips. Perhaps you can write next articles referring to
    this article. I want to read more things abouht it!|
    It’s the best time to make some plans for the future and it’s time to be happy.
    I’ve learn this post and if I may I desire to recommend you some attention-grabbing issues or
    tips. Maybe you could write next articles relating to
    this article. I wish to learn more thgings aplproximately it!|
    I have been browsing on-line greater than three hours lately, yet I by no means found
    any interesting article lkke yours. It’s pretty value sufficient
    for me. In my view, if alll web owners and bloggers made just right content
    material as you probably did, the web might bbe much more helpful than ver
    before.|
    Ahaa, its fastidious conversatijon about this post here at this weblog,
    I have read all that, so now me also commenting at
    this place.|
    I am sure this paragraph has touched all the internet people, its really really nicxe piece of writing on building up
    new webpage.|
    Wow, this paragraph is nice, my younger sister is analyzing these kinds
    of things, so Iam going tto let know her.|
    Saved as a favorite, I really like your website!|
    Way cool! Some very valid points! I appreciate you
    penning this write-up and also the rest of the site is
    very good.|
    Hi, I do think this is an excellent web site. I stumbledupon iit 😉 I may return once again sihce
    I book marked it. Money annd freedom is the greatest
    way to change, may you be rich and continue to guide others.|
    Woah! I’m really digging the template/theme of this website.
    It’s simple, yet effective. A lot of times it’s very
    difficult to get that “perfect balance” between usability and visual appeal.

    I must say you have done a superb job with this. Also, the blog loads extremely quick for
    me on Opera. Exceptional Blog!|
    These are really enormous ideas in about blogging.
    You have touched some pleasant factors here.

    Any way keep up wrinting.|
    Everyone loves what you guys tewnd tto be up too.
    This kind of clever work and reporting! Keep up the terrific works guys I’ve
    you guys to my blogroll.|
    Hi there! Someone in my Facebook group shared this ste wiith us so I
    came to give it a look. I’m definitely loving the information.
    I’m bookmarking and will be tweeting this tto my followers!

    Excellent blog and terrific design.|
    Everyoone loves what you guys are up too. This type off clever work and reporting!
    Keep up the superb works guys I’ve included youu guys tto my own blogroll.|
    Hi wouild you mind sharing which blog platform you’re working with?
    I’m planning to start my ownn blog soon but I’m having a difficult time
    deciding between BlogEngine/Wordpress/B2evolution annd Drupal.
    The reason I ask is because your design annd style seems different then most blogs and I’m looking for something completely
    unique. P.S Apologies for
    getting off-topic but I had to ask!|
    Howdy would you mind letting me know which hosting company you’re using?
    I’ve loaded your blog in 3 completely different web browsers
    and I must say this blog loads a lot quickker then most.
    Can you suggest a good hosting ptovider at a honest
    price? Thank you, I appreciate it!|
    Everyone loves it when individuals get together and share thoughts.
    Great site, keep it up!|
    Thank you for the good writeup. It in fact was a amusement account it.

    Look advanced to far added avreeable from you! By the way, howw could we communicate?|
    Hello just wanted to give you a quick heads up. The text in your content seem to bee running off the screen in Chrome.

    I’m not sure if this is a formatting issue or something to
    do with web browser compatibility but I thought I’d post to lett you know.
    The layout look great though! Hope you get the issue fixed soon. Thanks|
    This is a topic that’s cloose to myy heart… Cheers!
    Exactly where aree your contact details though?|
    It’s very simple to find out any matter on web as compared to
    books, as I found this paragralh at thgis site.|
    Does your blog have a contact page? I’m having trouble locating it but, I’d
    like to shoot you an email. I’ve got some recommendations
    for your blog yoou might be interested in hearing.
    Either way, great blog and I look forward to seeing it grow over time.|
    Greetings! I’ve been following your weblog for a while now
    and finally got the coujrage to go ahead and give you a shout out from New Caney Tx!
    Just wanted to say keep up the excellent work!|
    Greetings from Los angeles! I’m bored at work so I
    decided too browse your website on myy iphone during lunch break.
    I reaslly like the information you provide here and can’t wait to take a look when I get
    home. I’m surprised at how fast your blkg loaded on my mobile
    .. I’m not even uskng WIFI, just 3G .. Anyhow, great site!|
    Its such as you learn my mind! You appear to undersyand so much
    about this, like youu wrote the e-book in it or
    something. I believe that you simply can do with a few %
    to power the message home a bit, however other than that, that iis excellent
    blog. A great read. I’ll definiyely be back.|
    I visited several sites but thhe audio feature for audko songs current at this web page is iin fact excellent.|
    Howdy, i reead your blog from time to time and i own a
    similar one and i was just wondering if you get a lot of spam responses?
    If soo how ddo you reduce it, any plugin or anything you can advise?
    I get so much lately it’s driving me mad so any assistance iss very much appreciated.|
    Greetings! Very helpful advice within this post!
    It’s the little chwnges that produce the largest changes. Thanks a lot for sharing!|
    I really love your site.. Great colors & theme. Did you maake this website yourself?
    Please reply back as I’m planning to create my own bllog and would like to know where you got this from or exactly
    what the theme is named. Apprecate it!|
    Hello there! This post couldn’t be written any
    better! Reading through this pokst reminds me oof my prvious roommate!He continually kept preacching
    about this. I wijll send this information to him. Pretty sure
    he wiull have a great read. Many thanks for sharing!|
    Whoa! Thiss blog looks ust like my old one! It’s on a totally
    different subhect but it has prfetty much the same
    layout and design. Wonderful choice of colors!|
    There’s definately a lot to find out about this subject.
    I really like all the points you have made.|
    You have made some really good points there.
    I looked on the web to lwarn more about the issue and found most individuals will go along
    with your views on this site.|
    Hi there, I read your blog on a regular basis.
    Your humoristic style is witty, keep up the good work!|
    I simjply couldn’t leave your sitre before suggesting that I really loved the usual information an individual provide
    on your guests? Is gonna be back continuously in order to check up on new posts|
    I needed to thank you for this great read!! I certainly
    loved every little bit of it. I’ve got you booknarked to check out
    new stuff yyou post…|
    Hi, just wanted to mention, I enjoyed this blog post. It was
    practical. Keep on posting!|
    Hello, I enjoy reaading through your article post.
    I like to wrute a little comment to support you.|
    I constantly spent my half an hour to read this website’s articles every
    day along with a cup of coffee.|
    I every time emailed tuis webpage post page to all my contacts, for the reason that
    if like to read it after thyat my links will too.|
    My coder is tring to convince me to move to .net from PHP.

    I have always disliked the idea becauae of tthe costs.
    Buut he’s tryiong none the less. I’ve been using WordPress on numrrous websites for about a year and aam concerned about switching to another platform.
    I have hard excellent things about blogengine.net.
    Is there a wayy I can import all my wordpress posts into it?
    Any kind of help would bbe greatly appreciated!|
    Good day! I could have sworn I’ve been to tuis site before but after
    looking at some of the pists I realized it’s new to me. Anyways,
    I’m definitely happy I came across it and I’ll
    be bookmarking itt and checking back often!|
    Wonderful article! That is the kind of infomation that are supposed to be shared around the
    web. Disgrace on Google for not positioning tis submit higher!

    Come on over and discuss with my website . Thanks =)|
    Heya i’m for the first time here. I came across tbis board and I find It really useful & it helpeed me out
    much. I hope to give something back and hep
    others like you helped me.|
    Hello there, I believe your web site mmay be having web
    browser compatibility issues. Whenever I look
    at your website in Safari, itt looks fine but when opening in Internet
    Explorer, it’s ggot some overlapping issues. I merely wanted to provide you with a quick heads up!
    Apart from that, great blog!|
    A person necessarily assist to make severely posts I would state.That is the
    first timme I frequented your website page and up to now?
    I amazed with the research you made to makke this actual
    post extraordinary. Fantatic process!|
    Heya i am for the primary time here. I camme across this board and
    I to find It really useful & it helped me out a lot.
    I’m hoping to present something back and aid others such as you helped me.|
    Hi there! I simply would like to giive you a huuge thumbs up for the greatt information yoou have got here on this post.
    I will bee returning to your site for more soon.|
    I all the time used to read post in news papers but
    now as I am a user oof web so from now I am using net for articles or reviews, thanks to web.|
    Your means of telling thhe whole thing in this piece of writong is truly fastidious, all be ccapable of simply be aware of it,
    Thanks a lot.|
    Hello there,I discovered yor web site by thhe use of Google even as looking
    for a similar topic, your website got here up, it appears to be like great.
    I’ve bookmarked it in my google bookmarks.

    Hello there, simply changed into aware of your blog thru
    Google, and located that it’s really informative.
    I am going to be careful for brussels. I will be gratefil if you continue this in future.

    Numedous folks will likely be benefited ffrom your
    writing. Cheers!|
    I am curious to find out what blog system you
    are utilizing? I’m having some small security problems with my latest site
    and I’d ike to find something more risk-free. Do youu have any recommendations?|
    I am really impressed with your writing skills as well as with
    the layout on your weblog. Is this a paid theme orr did you
    customize it yourself? Either way keep up the excellent quality writing, it’s rare too see a great blog like
    this one today.|
    I’m exremely impressed alonbg with your writging abilities and alo wioth the format for our weblog.
    Is that this a paid subject or did youu modify it yourself?
    Either way keep up the nijce high quality writing, it is
    rare to peer a nice weblog like this one nowadays..|
    Hi, Neat post. Thewre is an issue along with your site in internet explorer,
    maay check this? IE nonetheless is the marketplace leader and a big part of folks will leave out your great writing because of this problem.|
    I’m not surre where you’re getting your info, but good topic.
    I needs tto spend some time learning moee or understanding more.
    Thanks forr magnificent info I was looking for this information for my mission.|
    Hi, i think that i saw you visited my blog so i came to “return the favor”.I amm trying too find
    things to enhancee my site!I suppose its ok to usee
    some of your ide\

    Like

Comments are closed.