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>