Insert all 1000 elements using a single FORALL statement
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>
--