Oracle PL/SQL/PL SQL/FORALL
Содержание
An exception will stop the bulk insert.
<source lang="sql">
SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> SQL> DECLARE
2 TYPE t_Strings IS TABLE OF MyTable.char_col%TYPE
3 INDEX BY BINARY_INTEGER;
4 TYPE t_Numbers IS TABLE OF MyTable.num_col%TYPE
5 INDEX BY BINARY_INTEGER;
6 v_Strings t_Strings;
7 v_Numbers t_Numbers;
8 BEGIN
9 DELETE FROM MyTable;
10 FOR v_Count IN 1..10 LOOP
11 v_Strings(v_Count) := "123456789012345678901234567890";
12 v_Numbers(v_Count) := v_Count;
13 END LOOP;
14
15 FORALL v_Count IN 1..10
16 INSERT INTO MyTable (num_col, char_col)
17 VALUES (v_Numbers(v_Count), v_Strings(v_Count));
18
19 v_Strings(6) := v_Strings(6) || "a";
20
21 FORALL v_Count IN 1..10
22 UPDATE MyTable
23 SET char_col = char_col || v_Strings(v_Count)
24 WHERE num_col = v_Numbers(v_Count);
25 EXCEPTION
26 WHEN OTHERS THEN
27 DBMS_OUTPUT.PUT_LINE("Got exception: " || SQLERRM);
28 COMMIT;
29 END;
30 /
Got exception: ORA-12899: value too large for column "sqle"."MYTABLE"."CHAR_COL" (actual: 61, maximum: 60) PL/SQL procedure successfully completed. SQL> SQL> drop table MyTable; Table dropped. SQL>
</source>
forall from 1 to 50
<source lang="sql">
SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> DECLARE
2 TYPE t_Numbers IS TABLE OF MyTable.num_col%TYPE 3 INDEX BY BINARY_INTEGER; 4 TYPE t_Chars IS TABLE OF MyTable.char_col%TYPE 5 INDEX BY BINARY_INTEGER; 6 v_Numbers t_Numbers; 7 v_Chars t_Chars; 8 BEGIN 9 -- Fill up the arrays with 50 rows. 10 FOR v_Count IN 1..50 LOOP 11 v_Numbers(v_Count) := v_Count; 12 v_Chars(v_Count) := "Row number " || v_Count; 13 END LOOP; 14 15 -- And insert them into the database using bulk binds. 16 FORALL v_Count IN 1..50 17 INSERT INTO MyTable VALUES 18 (v_Numbers(v_Count), v_Chars(v_Count)); 19 END; 20 /
PL/SQL procedure successfully completed. SQL> SQL> select * from MyTable;
NUM_COL CHAR_COL
------------------------------------------------------------
1.00 Row number 1 2.00 Row number 2 3.00 Row number 3 4.00 Row number 4 5.00 Row number 5 6.00 Row number 6 7.00 Row number 7 8.00 Row number 8 9.00 Row number 9 10.00 Row number 10 11.00 Row number 11 NUM_COL CHAR_COL
------------------------------------------------------------
12.00 Row number 12 13.00 Row number 13 14.00 Row number 14 15.00 Row number 15 16.00 Row number 16 17.00 Row number 17 18.00 Row number 18 19.00 Row number 19 20.00 Row number 20 21.00 Row number 21 22.00 Row number 22 NUM_COL CHAR_COL
------------------------------------------------------------
23.00 Row number 23 24.00 Row number 24 25.00 Row number 25 26.00 Row number 26 27.00 Row number 27 28.00 Row number 28 29.00 Row number 29 30.00 Row number 30 31.00 Row number 31 32.00 Row number 32 33.00 Row number 33 NUM_COL CHAR_COL
------------------------------------------------------------
34.00 Row number 34 35.00 Row number 35 36.00 Row number 36 37.00 Row number 37 38.00 Row number 38 39.00 Row number 39 40.00 Row number 40 41.00 Row number 41 42.00 Row number 42 43.00 Row number 43 44.00 Row number 44 NUM_COL CHAR_COL
------------------------------------------------------------
45.00 Row number 45 46.00 Row number 46 47.00 Row number 47 48.00 Row number 48 49.00 Row number 49 50.00 Row number 50
50 rows selected. SQL> SQL> drop table MyTable; Table dropped. SQL> SQL>
</source>
Insert all 1000 elements using a single FORALL statement
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> SQL> set serveroutput on format wrapped SQL> SQL> DECLARE
2 TYPE t_Numbers IS TABLE OF MyTable.num_col%TYPE; 3 TYPE t_Strings IS TABLE OF MyTable.char_col%TYPE; 4 v_Numbers t_Numbers := t_Numbers(1); 5 v_Strings t_Strings := t_Strings(1); 6 7 BEGIN 8 v_Numbers.EXTEND(1000); 9 v_Strings.EXTEND(1000); 10 FOR v_Count IN 1..1000 LOOP 11 v_Numbers(v_Count) := v_Count; 12 v_Strings(v_Count) := "Element #" || v_Count; 13 END LOOP; 14 15 16 FORALL v_Count IN 1..1000 17 INSERT INTO MyTable VALUES 18 (v_Numbers(v_Count), v_Strings(v_Count)); 19 20 21 END; 22 /
PL/SQL procedure successfully completed. SQL> SQL> select * from MyTable;
NUM_COL CHAR_COL
------------------------------------------------------------
1 Element #1
2 Element #2
3 Element #3
4 Element #4
5 Element #5
6 Element #6
7 Element #7
8 Element #8
9 Element #9
10 Element #10
11 Element #11
NUM_COL CHAR_COL
------------------------------------------------------------
12 Element #12
13 Element #13
14 Element #14
15 Element #15
16 Element #16
17 Element #17
18 Element #18
19 Element #19
20 Element #20
21 Element #21
22 Element #22
NUM_COL CHAR_COL
------------------------------------------------------------
23 Element #23
24 Element #24
25 Element #25
26 Element #26
27 Element #27
28 Element #28
29 Element #29
30 Element #30
31 Element #31
32 Element #32
33 Element #33
NUM_COL CHAR_COL
------------------------------------------------------------
34 Element #34
35 Element #35
36 Element #36
37 Element #37
38 Element #38
39 Element #39
40 Element #40
41 Element #41
42 Element #42
43 Element #43
44 Element #44
NUM_COL CHAR_COL
------------------------------------------------------------
45 Element #45
46 Element #46
47 Element #47
48 Element #48
49 Element #49
50 Element #50
51 Element #51
52 Element #52
53 Element #53
54 Element #54
55 Element #55
NUM_COL CHAR_COL
------------------------------------------------------------
56 Element #56
57 Element #57
58 Element #58
59 Element #59
60 Element #60
61 Element #61
62 Element #62
63 Element #63
64 Element #64
65 Element #65
66 Element #66
NUM_COL CHAR_COL
------------------------------------------------------------
67 Element #67
68 Element #68
69 Element #69
70 Element #70
71 Element #71
72 Element #72
73 Element #73
74 Element #74
75 Element #75
76 Element #76
77 Element #77
NUM_COL CHAR_COL
------------------------------------------------------------
78 Element #78
79 Element #79
80 Element #80
81 Element #81
82 Element #82
83 Element #83
84 Element #84
85 Element #85
86 Element #86
87 Element #87
88 Element #88
NUM_COL CHAR_COL
------------------------------------------------------------
89 Element #89
90 Element #90
91 Element #91
92 Element #92
93 Element #93
94 Element #94
95 Element #95
96 Element #96
97 Element #97
98 Element #98
99 Element #99
NUM_COL CHAR_COL
------------------------------------------------------------
100 Element #100
101 Element #101
102 Element #102
103 Element #103
104 Element #104
105 Element #105
106 Element #106
107 Element #107
108 Element #108
109 Element #109
110 Element #110
NUM_COL CHAR_COL
------------------------------------------------------------
111 Element #111
112 Element #112
113 Element #113
114 Element #114
115 Element #115
116 Element #116
117 Element #117
118 Element #118
119 Element #119
120 Element #120
121 Element #121
NUM_COL CHAR_COL
------------------------------------------------------------
122 Element #122
123 Element #123
124 Element #124
125 Element #125
126 Element #126
127 Element #127
128 Element #128
129 Element #129
130 Element #130
131 Element #131
132 Element #132
NUM_COL CHAR_COL
------------------------------------------------------------
133 Element #133
134 Element #134
135 Element #135
136 Element #136
137 Element #137
138 Element #138
139 Element #139
140 Element #140
141 Element #141
142 Element #142
143 Element #143
NUM_COL CHAR_COL
------------------------------------------------------------
144 Element #144
145 Element #145
146 Element #146
147 Element #147
148 Element #148
149 Element #149
150 Element #150
151 Element #151
152 Element #152
153 Element #153
154 Element #154
NUM_COL CHAR_COL
------------------------------------------------------------
155 Element #155
156 Element #156
157 Element #157
158 Element #158
159 Element #159
160 Element #160
161 Element #161
162 Element #162
163 Element #163
164 Element #164
165 Element #165
NUM_COL CHAR_COL
------------------------------------------------------------
166 Element #166
167 Element #167
168 Element #168
169 Element #169
170 Element #170
171 Element #171
172 Element #172
173 Element #173
174 Element #174
175 Element #175
176 Element #176
NUM_COL CHAR_COL
------------------------------------------------------------
177 Element #177
178 Element #178
179 Element #179
180 Element #180
181 Element #181
182 Element #182
183 Element #183
184 Element #184
185 Element #185
186 Element #186
187 Element #187
NUM_COL CHAR_COL
------------------------------------------------------------
188 Element #188
189 Element #189
190 Element #190
191 Element #191
192 Element #192
193 Element #193
194 Element #194
195 Element #195
196 Element #196
197 Element #197
198 Element #198
NUM_COL CHAR_COL
------------------------------------------------------------
199 Element #199
200 Element #200
201 Element #201
202 Element #202
203 Element #203
204 Element #204
205 Element #205
206 Element #206
207 Element #207
208 Element #208
209 Element #209
NUM_COL CHAR_COL
------------------------------------------------------------
210 Element #210
211 Element #211
212 Element #212
213 Element #213
214 Element #214
215 Element #215
216 Element #216
217 Element #217
218 Element #218
219 Element #219
220 Element #220
NUM_COL CHAR_COL
------------------------------------------------------------
221 Element #221
222 Element #222
223 Element #223
224 Element #224
225 Element #225
226 Element #226
227 Element #227
228 Element #228
229 Element #229
230 Element #230
231 Element #231
NUM_COL CHAR_COL
------------------------------------------------------------
232 Element #232
233 Element #233
234 Element #234
235 Element #235
236 Element #236
237 Element #237
238 Element #238
239 Element #239
240 Element #240
241 Element #241
242 Element #242
NUM_COL CHAR_COL
------------------------------------------------------------
243 Element #243
244 Element #244
245 Element #245
246 Element #246
247 Element #247
248 Element #248
249 Element #249
250 Element #250
251 Element #251
252 Element #252
253 Element #253
NUM_COL CHAR_COL
------------------------------------------------------------
254 Element #254
255 Element #255
256 Element #256
257 Element #257
258 Element #258
259 Element #259
260 Element #260
261 Element #261
262 Element #262
263 Element #263
264 Element #264
NUM_COL CHAR_COL
------------------------------------------------------------
265 Element #265
266 Element #266
267 Element #267
268 Element #268
269 Element #269
270 Element #270
271 Element #271
272 Element #272
273 Element #273
274 Element #274
275 Element #275
NUM_COL CHAR_COL
------------------------------------------------------------
276 Element #276
277 Element #277
278 Element #278
279 Element #279
280 Element #280
281 Element #281
282 Element #282
283 Element #283
284 Element #284
285 Element #285
286 Element #286
NUM_COL CHAR_COL
------------------------------------------------------------
287 Element #287
288 Element #288
289 Element #289
290 Element #290
291 Element #291
292 Element #292
293 Element #293
294 Element #294
295 Element #295
296 Element #296
297 Element #297
NUM_COL CHAR_COL
------------------------------------------------------------
298 Element #298
299 Element #299
300 Element #300
301 Element #301
302 Element #302
303 Element #303
304 Element #304
305 Element #305
306 Element #306
307 Element #307
308 Element #308
NUM_COL CHAR_COL
------------------------------------------------------------
309 Element #309
310 Element #310
311 Element #311
312 Element #312
313 Element #313
314 Element #314
315 Element #315
316 Element #316
317 Element #317
318 Element #318
319 Element #319
NUM_COL CHAR_COL
------------------------------------------------------------
320 Element #320
321 Element #321
322 Element #322
323 Element #323
324 Element #324
325 Element #325
326 Element #326
327 Element #327
328 Element #328
329 Element #329
330 Element #330
NUM_COL CHAR_COL
------------------------------------------------------------
331 Element #331
332 Element #332
333 Element #333
334 Element #334
335 Element #335
336 Element #336
337 Element #337
338 Element #338
339 Element #339
340 Element #340
341 Element #341
NUM_COL CHAR_COL
------------------------------------------------------------
342 Element #342
343 Element #343
344 Element #344
345 Element #345
346 Element #346
347 Element #347
348 Element #348
349 Element #349
350 Element #350
351 Element #351
352 Element #352
NUM_COL CHAR_COL
------------------------------------------------------------
353 Element #353
354 Element #354
355 Element #355
356 Element #356
357 Element #357
358 Element #358
359 Element #359
360 Element #360
361 Element #361
362 Element #362
363 Element #363
NUM_COL CHAR_COL
------------------------------------------------------------
364 Element #364
365 Element #365
366 Element #366
367 Element #367
368 Element #368
369 Element #369
370 Element #370
371 Element #371
372 Element #372
373 Element #373
374 Element #374
NUM_COL CHAR_COL
------------------------------------------------------------
375 Element #375
376 Element #376
377 Element #377
378 Element #378
379 Element #379
380 Element #380
381 Element #381
382 Element #382
383 Element #383
384 Element #384
385 Element #385
NUM_COL CHAR_COL
------------------------------------------------------------
386 Element #386
387 Element #387
388 Element #388
389 Element #389
390 Element #390
391 Element #391
392 Element #392
393 Element #393
394 Element #394
395 Element #395
396 Element #396
NUM_COL CHAR_COL
------------------------------------------------------------
397 Element #397
398 Element #398
399 Element #399
400 Element #400
401 Element #401
402 Element #402
403 Element #403
404 Element #404
405 Element #405
406 Element #406
407 Element #407
NUM_COL CHAR_COL
------------------------------------------------------------
408 Element #408
409 Element #409
410 Element #410
411 Element #411
412 Element #412
413 Element #413
414 Element #414
415 Element #415
416 Element #416
417 Element #417
418 Element #418
NUM_COL CHAR_COL
------------------------------------------------------------
419 Element #419
420 Element #420
421 Element #421
422 Element #422
423 Element #423
424 Element #424
425 Element #425
426 Element #426
427 Element #427
428 Element #428
429 Element #429
NUM_COL CHAR_COL
------------------------------------------------------------
430 Element #430
431 Element #431
432 Element #432
433 Element #433
434 Element #434
435 Element #435
436 Element #436
437 Element #437
438 Element #438
439 Element #439
440 Element #440
NUM_COL CHAR_COL
------------------------------------------------------------
441 Element #441
442 Element #442
443 Element #443
444 Element #444
445 Element #445
446 Element #446
447 Element #447
448 Element #448
449 Element #449
450 Element #450
451 Element #451
NUM_COL CHAR_COL
------------------------------------------------------------
452 Element #452
453 Element #453
454 Element #454
455 Element #455
456 Element #456
457 Element #457
458 Element #458
459 Element #459
460 Element #460
461 Element #461
462 Element #462
NUM_COL CHAR_COL
------------------------------------------------------------
463 Element #463
464 Element #464
465 Element #465
466 Element #466
467 Element #467
468 Element #468
469 Element #469
470 Element #470
471 Element #471
472 Element #472
473 Element #473
NUM_COL CHAR_COL
------------------------------------------------------------
474 Element #474
475 Element #475
476 Element #476
477 Element #477
478 Element #478
479 Element #479
480 Element #480
481 Element #481
482 Element #482
483 Element #483
484 Element #484
NUM_COL CHAR_COL
------------------------------------------------------------
485 Element #485
486 Element #486
487 Element #487
488 Element #488
489 Element #489
490 Element #490
491 Element #491
492 Element #492
493 Element #493
494 Element #494
495 Element #495
NUM_COL CHAR_COL
------------------------------------------------------------
496 Element #496
497 Element #497
498 Element #498
499 Element #499
500 Element #500
501 Element #501
502 Element #502
503 Element #503
504 Element #504
505 Element #505
506 Element #506
NUM_COL CHAR_COL
------------------------------------------------------------
507 Element #507
508 Element #508
509 Element #509
510 Element #510
511 Element #511
512 Element #512
513 Element #513
514 Element #514
515 Element #515
516 Element #516
517 Element #517
NUM_COL CHAR_COL
------------------------------------------------------------
518 Element #518
519 Element #519
520 Element #520
521 Element #521
522 Element #522
523 Element #523
524 Element #524
525 Element #525
526 Element #526
527 Element #527
528 Element #528
NUM_COL CHAR_COL
------------------------------------------------------------
529 Element #529
530 Element #530
531 Element #531
532 Element #532
533 Element #533
534 Element #534
535 Element #535
536 Element #536
537 Element #537
538 Element #538
539 Element #539
NUM_COL CHAR_COL
------------------------------------------------------------
540 Element #540
541 Element #541
542 Element #542
543 Element #543
544 Element #544
545 Element #545
546 Element #546
547 Element #547
548 Element #548
549 Element #549
550 Element #550
NUM_COL CHAR_COL
------------------------------------------------------------
551 Element #551
552 Element #552
553 Element #553
554 Element #554
555 Element #555
556 Element #556
557 Element #557
558 Element #558
559 Element #559
560 Element #560
561 Element #561
NUM_COL CHAR_COL
------------------------------------------------------------
562 Element #562
563 Element #563
564 Element #564
565 Element #565
566 Element #566
567 Element #567
568 Element #568
569 Element #569
570 Element #570
571 Element #571
572 Element #572
NUM_COL CHAR_COL
------------------------------------------------------------
573 Element #573
574 Element #574
575 Element #575
576 Element #576
577 Element #577
578 Element #578
579 Element #579
580 Element #580
581 Element #581
582 Element #582
583 Element #583
NUM_COL CHAR_COL
------------------------------------------------------------
584 Element #584
585 Element #585
586 Element #586
587 Element #587
588 Element #588
589 Element #589
590 Element #590
591 Element #591
592 Element #592
593 Element #593
594 Element #594
NUM_COL CHAR_COL
------------------------------------------------------------
595 Element #595
596 Element #596
597 Element #597
598 Element #598
599 Element #599
600 Element #600
601 Element #601
602 Element #602
603 Element #603
604 Element #604
605 Element #605
NUM_COL CHAR_COL
------------------------------------------------------------
606 Element #606
607 Element #607
608 Element #608
609 Element #609
610 Element #610
611 Element #611
612 Element #612
613 Element #613
614 Element #614
615 Element #615
616 Element #616
NUM_COL CHAR_COL
------------------------------------------------------------
617 Element #617
618 Element #618
619 Element #619
620 Element #620
621 Element #621
622 Element #622
623 Element #623
624 Element #624
625 Element #625
626 Element #626
627 Element #627
NUM_COL CHAR_COL
------------------------------------------------------------
628 Element #628
629 Element #629
630 Element #630
631 Element #631
632 Element #632
633 Element #633
634 Element #634
635 Element #635
636 Element #636
637 Element #637
638 Element #638
NUM_COL CHAR_COL
------------------------------------------------------------
639 Element #639
640 Element #640
641 Element #641
642 Element #642
643 Element #643
644 Element #644
645 Element #645
646 Element #646
647 Element #647
648 Element #648
649 Element #649
NUM_COL CHAR_COL
------------------------------------------------------------
650 Element #650
651 Element #651
652 Element #652
653 Element #653
654 Element #654
655 Element #655
656 Element #656
657 Element #657
658 Element #658
659 Element #659
660 Element #660
NUM_COL CHAR_COL
------------------------------------------------------------
661 Element #661
662 Element #662
663 Element #663
664 Element #664
665 Element #665
666 Element #666
667 Element #667
668 Element #668
669 Element #669
670 Element #670
671 Element #671
NUM_COL CHAR_COL
------------------------------------------------------------
672 Element #672
673 Element #673
674 Element #674
675 Element #675
676 Element #676
677 Element #677
678 Element #678
679 Element #679
680 Element #680
681 Element #681
682 Element #682
NUM_COL CHAR_COL
------------------------------------------------------------
683 Element #683
684 Element #684
685 Element #685
686 Element #686
687 Element #687
688 Element #688
689 Element #689
690 Element #690
691 Element #691
692 Element #692
693 Element #693
NUM_COL CHAR_COL
------------------------------------------------------------
694 Element #694
695 Element #695
696 Element #696
697 Element #697
698 Element #698
699 Element #699
700 Element #700
701 Element #701
702 Element #702
703 Element #703
704 Element #704
NUM_COL CHAR_COL
------------------------------------------------------------
705 Element #705
706 Element #706
707 Element #707
708 Element #708
709 Element #709
710 Element #710
711 Element #711
712 Element #712
713 Element #713
714 Element #714
715 Element #715
NUM_COL CHAR_COL
------------------------------------------------------------
716 Element #716
717 Element #717
718 Element #718
719 Element #719
720 Element #720
721 Element #721
722 Element #722
723 Element #723
724 Element #724
725 Element #725
726 Element #726
NUM_COL CHAR_COL
------------------------------------------------------------
727 Element #727
728 Element #728
729 Element #729
730 Element #730
731 Element #731
732 Element #732
733 Element #733
734 Element #734
735 Element #735
736 Element #736
737 Element #737
NUM_COL CHAR_COL
------------------------------------------------------------
738 Element #738
739 Element #739
740 Element #740
741 Element #741
742 Element #742
743 Element #743
744 Element #744
745 Element #745
746 Element #746
747 Element #747
748 Element #748
NUM_COL CHAR_COL
------------------------------------------------------------
749 Element #749
750 Element #750
751 Element #751
752 Element #752
753 Element #753
754 Element #754
755 Element #755
756 Element #756
757 Element #757
758 Element #758
759 Element #759
NUM_COL CHAR_COL
------------------------------------------------------------
760 Element #760
761 Element #761
762 Element #762
763 Element #763
764 Element #764
765 Element #765
766 Element #766
767 Element #767
768 Element #768
769 Element #769
770 Element #770
NUM_COL CHAR_COL
------------------------------------------------------------
771 Element #771
772 Element #772
773 Element #773
774 Element #774
775 Element #775
776 Element #776
777 Element #777
778 Element #778
779 Element #779
780 Element #780
781 Element #781
NUM_COL CHAR_COL
------------------------------------------------------------
782 Element #782
783 Element #783
784 Element #784
785 Element #785
786 Element #786
787 Element #787
788 Element #788
789 Element #789
790 Element #790
791 Element #791
792 Element #792
NUM_COL CHAR_COL
------------------------------------------------------------
793 Element #793
794 Element #794
795 Element #795
796 Element #796
797 Element #797
798 Element #798
799 Element #799
800 Element #800
801 Element #801
802 Element #802
803 Element #803
NUM_COL CHAR_COL
------------------------------------------------------------
804 Element #804
805 Element #805
806 Element #806
807 Element #807
808 Element #808
809 Element #809
810 Element #810
811 Element #811
812 Element #812
813 Element #813
814 Element #814
NUM_COL CHAR_COL
------------------------------------------------------------
815 Element #815
816 Element #816
817 Element #817
818 Element #818
819 Element #819
820 Element #820
821 Element #821
822 Element #822
823 Element #823
824 Element #824
825 Element #825
NUM_COL CHAR_COL
------------------------------------------------------------
826 Element #826
827 Element #827
828 Element #828
829 Element #829
830 Element #830
831 Element #831
832 Element #832
833 Element #833
834 Element #834
835 Element #835
836 Element #836
NUM_COL CHAR_COL
------------------------------------------------------------
837 Element #837
838 Element #838
839 Element #839
840 Element #840
841 Element #841
842 Element #842
843 Element #843
844 Element #844
845 Element #845
846 Element #846
847 Element #847
NUM_COL CHAR_COL
------------------------------------------------------------
848 Element #848
849 Element #849
850 Element #850
851 Element #851
852 Element #852
853 Element #853
854 Element #854
855 Element #855
856 Element #856
857 Element #857
858 Element #858
NUM_COL CHAR_COL
------------------------------------------------------------
859 Element #859
860 Element #860
861 Element #861
862 Element #862
863 Element #863
864 Element #864
865 Element #865
866 Element #866
867 Element #867
868 Element #868
869 Element #869
NUM_COL CHAR_COL
------------------------------------------------------------
870 Element #870
871 Element #871
872 Element #872
873 Element #873
874 Element #874
875 Element #875
876 Element #876
877 Element #877
878 Element #878
879 Element #879
880 Element #880
NUM_COL CHAR_COL
------------------------------------------------------------
881 Element #881
882 Element #882
883 Element #883
884 Element #884
885 Element #885
886 Element #886
887 Element #887
888 Element #888
889 Element #889
890 Element #890
891 Element #891
NUM_COL CHAR_COL
------------------------------------------------------------
892 Element #892
893 Element #893
894 Element #894
895 Element #895
896 Element #896
897 Element #897
898 Element #898
899 Element #899
900 Element #900
901 Element #901
902 Element #902
NUM_COL CHAR_COL
------------------------------------------------------------
903 Element #903
904 Element #904
905 Element #905
906 Element #906
907 Element #907
908 Element #908
909 Element #909
910 Element #910
911 Element #911
912 Element #912
913 Element #913
NUM_COL CHAR_COL
------------------------------------------------------------
914 Element #914
915 Element #915
916 Element #916
917 Element #917
918 Element #918
919 Element #919
920 Element #920
921 Element #921
922 Element #922
923 Element #923
924 Element #924
NUM_COL CHAR_COL
------------------------------------------------------------
925 Element #925
926 Element #926
927 Element #927
928 Element #928
929 Element #929
930 Element #930
931 Element #931
932 Element #932
933 Element #933
934 Element #934
935 Element #935
NUM_COL CHAR_COL
------------------------------------------------------------
936 Element #936
937 Element #937
938 Element #938
939 Element #939
940 Element #940
941 Element #941
942 Element #942
943 Element #943
944 Element #944
945 Element #945
946 Element #946
NUM_COL CHAR_COL
------------------------------------------------------------
947 Element #947
948 Element #948
949 Element #949
950 Element #950
951 Element #951
952 Element #952
953 Element #953
954 Element #954
955 Element #955
956 Element #956
957 Element #957
NUM_COL CHAR_COL
------------------------------------------------------------
958 Element #958
959 Element #959
960 Element #960
961 Element #961
962 Element #962
963 Element #963
964 Element #964
965 Element #965
966 Element #966
967 Element #967
968 Element #968
NUM_COL CHAR_COL
------------------------------------------------------------
969 Element #969
970 Element #970
971 Element #971
972 Element #972
973 Element #973
974 Element #974
975 Element #975
976 Element #976
977 Element #977
978 Element #978
979 Element #979
NUM_COL CHAR_COL
------------------------------------------------------------
980 Element #980
981 Element #981
982 Element #982
983 Element #983
984 Element #984
985 Element #985
986 Element #986
987 Element #987
988 Element #988
989 Element #989
990 Element #990
NUM_COL CHAR_COL
------------------------------------------------------------
991 Element #991
992 Element #992
993 Element #993
994 Element #994
995 Element #995
996 Element #996
997 Element #997
998 Element #998
999 Element #999
1000 Element #1000
1000 rows selected. SQL> SQL> drop table MyTable; Table dropped. SQL> SQL> SQL> --
</source>
The SAVE EXCEPTIONS clause will record any exception during the bulk operation, and continue processing.
<source lang="sql">
SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> SQL> DECLARE
2 TYPE t_Strings IS TABLE OF MyTable.char_col%TYPE
3 INDEX BY BINARY_INTEGER;
4 TYPE t_Numbers IS TABLE OF MyTable.num_col%TYPE
5 INDEX BY BINARY_INTEGER;
6 v_Strings t_Strings;
7 v_Numbers t_Numbers;
8 v_NumErrors NUMBER;
9 BEGIN
10 DELETE FROM MyTable;
11 FOR v_Count IN 1..10 LOOP
12 v_Strings(v_Count) := "123456789012345678901234567890";
13 v_Numbers(v_Count) := v_Count;
14 END LOOP;
15
16 FORALL v_Count IN 1..10
17 INSERT INTO MyTable (num_col, char_col)
18 VALUES (v_Numbers(v_Count), v_Strings(v_Count));
19
20 v_Strings(6) := v_Strings(6) || "a";
21
22 FORALL v_Count IN 1..10 SAVE EXCEPTIONS
23 UPDATE MyTable
24 SET char_col = char_col || v_Strings(v_Count)
25 WHERE num_col = v_Numbers(v_Count);
26 EXCEPTION
27 WHEN OTHERS THEN
28 DBMS_OUTPUT.PUT_LINE("Got exception: " || SQLERRM);
29 v_NumErrors := SQL%BULK_EXCEPTIONS.COUNT;
30 DBMS_OUTPUT.PUT_LINE(
31 "Number of errors during processing: " || v_NumErrors);
32 FOR v_Count IN 1..v_NumErrors LOOP
33 DBMS_OUTPUT.PUT_LINE("Error " || v_Count || ", iteration " ||
34 SQL%BULK_EXCEPTIONS(v_Count).error_index || " is: " ||
35 SQLERRM(0 - SQL%BULK_EXCEPTIONS(v_Count).error_code));
36 END LOOP;
37
38 COMMIT;
39 END;
40 /
Got exception: ORA-24381: error(s) in array DML Number of errors during processing: 1 Error 1, iteration 6 is: ORA-12899: value too large for column (actual: , maximum: ) PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table MyTable; Table dropped. SQL>
</source>
Use a FORALL to move an associative array into a table
<source lang="sql">
SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> SQL> CREATE TABLE myTable
2 (id NUMBER NOT NULL 3 ,CONSTRAINT id_pk PRIMARY KEY (id));
Table created. SQL> SQL> SQL> DECLARE
2 3 TYPE number_table IS TABLE OF myTable.id%TYPE INDEX BY BINARY_INTEGER; 4 5 6 number_list NUMBER_TABLE; 7 8 BEGIN 9 10 11 FOR i IN 1..10000 LOOP 12 13 number_list(i) := i; 14 15 END LOOP; 16 17 18 FORALL i IN 1..number_list.COUNT 19 INSERT INTO myTable VALUES (number_list(i)); 20 21 COMMIT; 22 23 END; 24 /
PL/SQL procedure successfully completed. SQL> SQL> drop table myTable; Table dropped. SQL>
</source>
