File: Oracle.pm

package info (click to toggle)
libdbd-oracle-perl 1.21-2
  • links: PTS
  • area: contrib
  • in suites: lenny
  • size: 1,808 kB
  • ctags: 576
  • sloc: ansic: 6,172; perl: 4,753; makefile: 14
file content (3841 lines) | stat: -rwxr-xr-x 133,760 bytes parent folder | download
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
1602
1603
1604
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702
1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
1734
1735
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
1781
1782
1783
1784
1785
1786
1787
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838
1839
1840
1841
1842
1843
1844
1845
1846
1847
1848
1849
1850
1851
1852
1853
1854
1855
1856
1857
1858
1859
1860
1861
1862
1863
1864
1865
1866
1867
1868
1869
1870
1871
1872
1873
1874
1875
1876
1877
1878
1879
1880
1881
1882
1883
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
1910
1911
1912
1913
1914
1915
1916
1917
1918
1919
1920
1921
1922
1923
1924
1925
1926
1927
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
1964
1965
1966
1967
1968
1969
1970
1971
1972
1973
1974
1975
1976
1977
1978
1979
1980
1981
1982
1983
1984
1985
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078
2079
2080
2081
2082
2083
2084
2085
2086
2087
2088
2089
2090
2091
2092
2093
2094
2095
2096
2097
2098
2099
2100
2101
2102
2103
2104
2105
2106
2107
2108
2109
2110
2111
2112
2113
2114
2115
2116
2117
2118
2119
2120
2121
2122
2123
2124
2125
2126
2127
2128
2129
2130
2131
2132
2133
2134
2135
2136
2137
2138
2139
2140
2141
2142
2143
2144
2145
2146
2147
2148
2149
2150
2151
2152
2153
2154
2155
2156
2157
2158
2159
2160
2161
2162
2163
2164
2165
2166
2167
2168
2169
2170
2171
2172
2173
2174
2175
2176
2177
2178
2179
2180
2181
2182
2183
2184
2185
2186
2187
2188
2189
2190
2191
2192
2193
2194
2195
2196
2197
2198
2199
2200
2201
2202
2203
2204
2205
2206
2207
2208
2209
2210
2211
2212
2213
2214
2215
2216
2217
2218
2219
2220
2221
2222
2223
2224
2225
2226
2227
2228
2229
2230
2231
2232
2233
2234
2235
2236
2237
2238
2239
2240
2241
2242
2243
2244
2245
2246
2247
2248
2249
2250
2251
2252
2253
2254
2255
2256
2257
2258
2259
2260
2261
2262
2263
2264
2265
2266
2267
2268
2269
2270
2271
2272
2273
2274
2275
2276
2277
2278
2279
2280
2281
2282
2283
2284
2285
2286
2287
2288
2289
2290
2291
2292
2293
2294
2295
2296
2297
2298
2299
2300
2301
2302
2303
2304
2305
2306
2307
2308
2309
2310
2311
2312
2313
2314
2315
2316
2317
2318
2319
2320
2321
2322
2323
2324
2325
2326
2327
2328
2329
2330
2331
2332
2333
2334
2335
2336
2337
2338
2339
2340
2341
2342
2343
2344
2345
2346
2347
2348
2349
2350
2351
2352
2353
2354
2355
2356
2357
2358
2359
2360
2361
2362
2363
2364
2365
2366
2367
2368
2369
2370
2371
2372
2373
2374
2375
2376
2377
2378
2379
2380
2381
2382
2383
2384
2385
2386
2387
2388
2389
2390
2391
2392
2393
2394
2395
2396
2397
2398
2399
2400
2401
2402
2403
2404
2405
2406
2407
2408
2409
2410
2411
2412
2413
2414
2415
2416
2417
2418
2419
2420
2421
2422
2423
2424
2425
2426
2427
2428
2429
2430
2431
2432
2433
2434
2435
2436
2437
2438
2439
2440
2441
2442
2443
2444
2445
2446
2447
2448
2449
2450
2451
2452
2453
2454
2455
2456
2457
2458
2459
2460
2461
2462
2463
2464
2465
2466
2467
2468
2469
2470
2471
2472
2473
2474
2475
2476
2477
2478
2479
2480
2481
2482
2483
2484
2485
2486
2487
2488
2489
2490
2491
2492
2493
2494
2495
2496
2497
2498
2499
2500
2501
2502
2503
2504
2505
2506
2507
2508
2509
2510
2511
2512
2513
2514
2515
2516
2517
2518
2519
2520
2521
2522
2523
2524
2525
2526
2527
2528
2529
2530
2531
2532
2533
2534
2535
2536
2537
2538
2539
2540
2541
2542
2543
2544
2545
2546
2547
2548
2549
2550
2551
2552
2553
2554
2555
2556
2557
2558
2559
2560
2561
2562
2563
2564
2565
2566
2567
2568
2569
2570
2571
2572
2573
2574
2575
2576
2577
2578
2579
2580
2581
2582
2583
2584
2585
2586
2587
2588
2589
2590
2591
2592
2593
2594
2595
2596
2597
2598
2599
2600
2601
2602
2603
2604
2605
2606
2607
2608
2609
2610
2611
2612
2613
2614
2615
2616
2617
2618
2619
2620
2621
2622
2623
2624
2625
2626
2627
2628
2629
2630
2631
2632
2633
2634
2635
2636
2637
2638
2639
2640
2641
2642
2643
2644
2645
2646
2647
2648
2649
2650
2651
2652
2653
2654
2655
2656
2657
2658
2659
2660
2661
2662
2663
2664
2665
2666
2667
2668
2669
2670
2671
2672
2673
2674
2675
2676
2677
2678
2679
2680
2681
2682
2683
2684
2685
2686
2687
2688
2689
2690
2691
2692
2693
2694
2695
2696
2697
2698
2699
2700
2701
2702
2703
2704
2705
2706
2707
2708
2709
2710
2711
2712
2713
2714
2715
2716
2717
2718
2719
2720
2721
2722
2723
2724
2725
2726
2727
2728
2729
2730
2731
2732
2733
2734
2735
2736
2737
2738
2739
2740
2741
2742
2743
2744
2745
2746
2747
2748
2749
2750
2751
2752
2753
2754
2755
2756
2757
2758
2759
2760
2761
2762
2763
2764
2765
2766
2767
2768
2769
2770
2771
2772
2773
2774
2775
2776
2777
2778
2779
2780
2781
2782
2783
2784
2785
2786
2787
2788
2789
2790
2791
2792
2793
2794
2795
2796
2797
2798
2799
2800
2801
2802
2803
2804
2805
2806
2807
2808
2809
2810
2811
2812
2813
2814
2815
2816
2817
2818
2819
2820
2821
2822
2823
2824
2825
2826
2827
2828
2829
2830
2831
2832
2833
2834
2835
2836
2837
2838
2839
2840
2841
2842
2843
2844
2845
2846
2847
2848
2849
2850
2851
2852
2853
2854
2855
2856
2857
2858
2859
2860
2861
2862
2863
2864
2865
2866
2867
2868
2869
2870
2871
2872
2873
2874
2875
2876
2877
2878
2879
2880
2881
2882
2883
2884
2885
2886
2887
2888
2889
2890
2891
2892
2893
2894
2895
2896
2897
2898
2899
2900
2901
2902
2903
2904
2905
2906
2907
2908
2909
2910
2911
2912
2913
2914
2915
2916
2917
2918
2919
2920
2921
2922
2923
2924
2925
2926
2927
2928
2929
2930
2931
2932
2933
2934
2935
2936
2937
2938
2939
2940
2941
2942
2943
2944
2945
2946
2947
2948
2949
2950
2951
2952
2953
2954
2955
2956
2957
2958
2959
2960
2961
2962
2963
2964
2965
2966
2967
2968
2969
2970
2971
2972
2973
2974
2975
2976
2977
2978
2979
2980
2981
2982
2983
2984
2985
2986
2987
2988
2989
2990
2991
2992
2993
2994
2995
2996
2997
2998
2999
3000
3001
3002
3003
3004
3005
3006
3007
3008
3009
3010
3011
3012
3013
3014
3015
3016
3017
3018
3019
3020
3021
3022
3023
3024
3025
3026
3027
3028
3029
3030
3031
3032
3033
3034
3035
3036
3037
3038
3039
3040
3041
3042
3043
3044
3045
3046
3047
3048
3049
3050
3051
3052
3053
3054
3055
3056
3057
3058
3059
3060
3061
3062
3063
3064
3065
3066
3067
3068
3069
3070
3071
3072
3073
3074
3075
3076
3077
3078
3079
3080
3081
3082
3083
3084
3085
3086
3087
3088
3089
3090
3091
3092
3093
3094
3095
3096
3097
3098
3099
3100
3101
3102
3103
3104
3105
3106
3107
3108
3109
3110
3111
3112
3113
3114
3115
3116
3117
3118
3119
3120
3121
3122
3123
3124
3125
3126
3127
3128
3129
3130
3131
3132
3133
3134
3135
3136
3137
3138
3139
3140
3141
3142
3143
3144
3145
3146
3147
3148
3149
3150
3151
3152
3153
3154
3155
3156
3157
3158
3159
3160
3161
3162
3163
3164
3165
3166
3167
3168
3169
3170
3171
3172
3173
3174
3175
3176
3177
3178
3179
3180
3181
3182
3183
3184
3185
3186
3187
3188
3189
3190
3191
3192
3193
3194
3195
3196
3197
3198
3199
3200
3201
3202
3203
3204
3205
3206
3207
3208
3209
3210
3211
3212
3213
3214
3215
3216
3217
3218
3219
3220
3221
3222
3223
3224
3225
3226
3227
3228
3229
3230
3231
3232
3233
3234
3235
3236
3237
3238
3239
3240
3241
3242
3243
3244
3245
3246
3247
3248
3249
3250
3251
3252
3253
3254
3255
3256
3257
3258
3259
3260
3261
3262
3263
3264
3265
3266
3267
3268
3269
3270
3271
3272
3273
3274
3275
3276
3277
3278
3279
3280
3281
3282
3283
3284
3285
3286
3287
3288
3289
3290
3291
3292
3293
3294
3295
3296
3297
3298
3299
3300
3301
3302
3303
3304
3305
3306
3307
3308
3309
3310
3311
3312
3313
3314
3315
3316
3317
3318
3319
3320
3321
3322
3323
3324
3325
3326
3327
3328
3329
3330
3331
3332
3333
3334
3335
3336
3337
3338
3339
3340
3341
3342
3343
3344
3345
3346
3347
3348
3349
3350
3351
3352
3353
3354
3355
3356
3357
3358
3359
3360
3361
3362
3363
3364
3365
3366
3367
3368
3369
3370
3371
3372
3373
3374
3375
3376
3377
3378
3379
3380
3381
3382
3383
3384
3385
3386
3387
3388
3389
3390
3391
3392
3393
3394
3395
3396
3397
3398
3399
3400
3401
3402
3403
3404
3405
3406
3407
3408
3409
3410
3411
3412
3413
3414
3415
3416
3417
3418
3419
3420
3421
3422
3423
3424
3425
3426
3427
3428
3429
3430
3431
3432
3433
3434
3435
3436
3437
3438
3439
3440
3441
3442
3443
3444
3445
3446
3447
3448
3449
3450
3451
3452
3453
3454
3455
3456
3457
3458
3459
3460
3461
3462
3463
3464
3465
3466
3467
3468
3469
3470
3471
3472
3473
3474
3475
3476
3477
3478
3479
3480
3481
3482
3483
3484
3485
3486
3487
3488
3489
3490
3491
3492
3493
3494
3495
3496
3497
3498
3499
3500
3501
3502
3503
3504
3505
3506
3507
3508
3509
3510
3511
3512
3513
3514
3515
3516
3517
3518
3519
3520
3521
3522
3523
3524
3525
3526
3527
3528
3529
3530
3531
3532
3533
3534
3535
3536
3537
3538
3539
3540
3541
3542
3543
3544
3545
3546
3547
3548
3549
3550
3551
3552
3553
3554
3555
3556
3557
3558
3559
3560
3561
3562
3563
3564
3565
3566
3567
3568
3569
3570
3571
3572
3573
3574
3575
3576
3577
3578
3579
3580
3581
3582
3583
3584
3585
3586
3587
3588
3589
3590
3591
3592
3593
3594
3595
3596
3597
3598
3599
3600
3601
3602
3603
3604
3605
3606
3607
3608
3609
3610
3611
3612
3613
3614
3615
3616
3617
3618
3619
3620
3621
3622
3623
3624
3625
3626
3627
3628
3629
3630
3631
3632
3633
3634
3635
3636
3637
3638
3639
3640
3641
3642
3643
3644
3645
3646
3647
3648
3649
3650
3651
3652
3653
3654
3655
3656
3657
3658
3659
3660
3661
3662
3663
3664
3665
3666
3667
3668
3669
3670
3671
3672
3673
3674
3675
3676
3677
3678
3679
3680
3681
3682
3683
3684
3685
3686
3687
3688
3689
3690
3691
3692
3693
3694
3695
3696
3697
3698
3699
3700
3701
3702
3703
3704
3705
3706
3707
3708
3709
3710
3711
3712
3713
3714
3715
3716
3717
3718
3719
3720
3721
3722
3723
3724
3725
3726
3727
3728
3729
3730
3731
3732
3733
3734
3735
3736
3737
3738
3739
3740
3741
3742
3743
3744
3745
3746
3747
3748
3749
3750
3751
3752
3753
3754
3755
3756
3757
3758
3759
3760
3761
3762
3763
3764
3765
3766
3767
3768
3769
3770
3771
3772
3773
3774
3775
3776
3777
3778
3779
3780
3781
3782
3783
3784
3785
3786
3787
3788
3789
3790
3791
3792
3793
3794
3795
3796
3797
3798
3799
3800
3801
3802
3803
3804
3805
3806
3807
3808
3809
3810
3811
3812
3813
3814
3815
3816
3817
3818
3819
3820
3821
3822
3823
3824
3825
3826
3827
3828
3829
3830
3831
3832
3833
3834
3835
3836
3837
3838
3839
3840
3841
#   Oracle.pm
#
#   Copyright (c) 1994-2005 Tim Bunce, Ireland
#
#   See COPYRIGHT section in the documentation below

require 5.003;

$DBD::Oracle::VERSION = '1.21';

my $ORACLE_ENV  = ($^O eq 'VMS') ? 'ORA_ROOT' : 'ORACLE_HOME';

{
    package DBD::Oracle;

    use DBI ();
    use DynaLoader ();
    use Exporter ();
    @ISA = qw(DynaLoader Exporter);
    %EXPORT_TAGS = (
	ora_types => [ qw(
	    ORA_VARCHAR2 ORA_STRING ORA_NUMBER ORA_LONG ORA_ROWID ORA_DATE
	    ORA_RAW ORA_LONGRAW ORA_CHAR ORA_CHARZ ORA_MLSLABEL ORA_XMLTYPE
	    ORA_CLOB ORA_BLOB ORA_RSET ORA_VARCHAR2_TABLE ORA_NUMBER_TABLE
	    SQLT_INT SQLT_FLT ORA_OCI SQLT_CHR SQLT_BIN     
	) ],
        ora_session_modes => [ qw( ORA_SYSDBA ORA_SYSOPER ) ],
        ora_fetch_orient  => [ qw( OCI_FETCH_NEXT OCI_FETCH_CURRENT OCI_FETCH_FIRST 
        			   OCI_FETCH_LAST OCI_FETCH_PRIOR OCI_FETCH_ABSOLUTE 
        			   OCI_FETCH_RELATIVE)],
    	ora_exe_modes     => [ qw(OCI_STMT_SCROLLABLE_READONLY)],
    );
    @EXPORT_OK = qw(OCI_FETCH_NEXT OCI_FETCH_CURRENT OCI_FETCH_FIRST OCI_FETCH_LAST OCI_FETCH_PRIOR
    		    OCI_FETCH_ABSOLUTE 	OCI_FETCH_RELATIVE ORA_OCI SQLCS_IMPLICIT SQLCS_NCHAR ora_env_var ora_cygwin_set_env);
    #unshift @EXPORT_OK, 'ora_cygwin_set_env' if $^O eq 'cygwin';
    Exporter::export_ok_tags(qw(ora_types ora_session_modes ora_fetch_orient ora_exe_modes));

    my $Revision = substr(q$Revision: 1.103 $, 10);

    require_version DBI 1.51;

    bootstrap DBD::Oracle $VERSION;

    $drh = undef;	# holds driver handle once initialised

    sub CLONE {
        $drh = undef ;
    }
              
    sub driver{
	return $drh if $drh;
	my($class, $attr) = @_;
	my $oci = DBD::Oracle::ORA_OCI();

	$class .= "::dr";

	# not a 'my' since we use it above to prevent multiple drivers

	$drh = DBI::_new_drh($class, {
	    'Name' => 'Oracle',
	    'Version' => $VERSION,
	    'Err'    => \my $err,
	    'Errstr' => \my $errstr,
	    'Attribution' => "DBD::Oracle $VERSION using OCI$oci by Tim Bunce",
	    });
	DBD::Oracle::dr::init_oci($drh) ;
	$drh->STORE('ShowErrorStatement', 1);

        DBD::Oracle::db->install_method("ora_lob_read");
        DBD::Oracle::db->install_method("ora_lob_write");
        DBD::Oracle::db->install_method("ora_lob_append");
        DBD::Oracle::db->install_method("ora_lob_trim");
        DBD::Oracle::db->install_method("ora_lob_length");
        DBD::Oracle::db->install_method("ora_nls_parameters");
        DBD::Oracle::db->install_method("ora_can_unicode");
 	DBD::Oracle::st->install_method("ora_fetch_scroll");
 	DBD::Oracle::st->install_method("ora_scroll_position");
 	
	$drh;
    }


    END {
	# Used to silence 'Bad free() ...' warnings caused by bugs in Oracle's code
	# being detected by Perl's malloc.
	$ENV{PERL_BADFREE} = 0;
	#undef $Win32::TieRegistry::Registry if $Win32::TieRegistry::Registry;
    }

    sub AUTOLOAD {
    	(my $constname = $AUTOLOAD) =~ s/.*:://;
    	my $val = constant($constname);
    	*$AUTOLOAD = sub { $val };
    	goto &$AUTOLOAD;
    }

}


{   package DBD::Oracle::dr; # ====== DRIVER ======
    use strict;

    my %dbnames = ();	# holds list of known databases (oratab + tnsnames)

    sub load_dbnames {
	my ($drh) = @_;
	my $debug = $drh->debug;
	my $oracle_home = DBD::Oracle::ora_env_var($ORACLE_ENV);
	local *FH;
	my $d;

	if (($^O eq 'MSWin32') or ($^O =~ /cygwin/i)) {
	  # XXX experimental, will probably change
	  $drh->trace_msg("Trying to fetch ORACLE_HOME and ORACLE_SID from the registry.\n")
		if $debug;
	  my $sid = DBD::Oracle::ora_env_var("ORACLE_SID");
	  $dbnames{$sid} = $oracle_home if $sid and $oracle_home;
	  $drh->trace_msg("Found $sid \@ $oracle_home.\n") if $debug && $sid;
	}

	# get list of 'local' database SIDs from oratab
	foreach $d (qw(/etc /var/opt/oracle), DBD::Oracle::ora_env_var("TNS_ADMIN")) {
	    next unless defined $d;
	    next unless open(FH, "<$d/oratab");
	    $drh->trace_msg("Loading $d/oratab\n") if $debug;
	    my $ot;
	    while (defined($ot = <FH>)) {
		next unless $ot =~ m/^\s*(\w+)\s*:\s*(.*?)\s*:/;
		$dbnames{$1} = $2;	# store ORACLE_HOME value
		$drh->trace_msg("Found $1 \@ $2.\n") if $debug;
	    }
	    close FH;
	    last;
	}

	# get list of 'remote' database connection identifiers
	my @tns_admin;
	push @tns_admin, (
	  "$oracle_home/network/admin",	# OCI 7 and 8.1
	  "$oracle_home/net80/admin",	# OCI 8.0
	) if $oracle_home;
	push @tns_admin, "/var/opt/oracle";
	foreach $d ( DBD::Oracle::ora_env_var("TNS_ADMIN"), ".", @tns_admin  ) {
	    next unless $d && open(FH, "<$d/tnsnames.ora");
	    $drh->trace_msg("Loading $d/tnsnames.ora\n") if $debug;
	    local *_;
	    while (<FH>) {
		next unless m/^\s*([-\w\.]+)\s*=/;
		my $name = $1;
		$drh->trace_msg("Found $name. ".($dbnames{$name} ? "(oratab entry overridden)" : "")."\n")
		    if $debug;
		$dbnames{$name} = 0; # exists but false (to distinguish from oratab)
	    }
	    close FH;
	    last;
	}

	$dbnames{0} = 1;	# mark as loaded (even if empty)
    }

    sub data_sources {
	my $drh = shift;
	load_dbnames($drh) unless %dbnames;
	my @names = sort  keys %dbnames;
	my @sources = map { $_ ? ("dbi:Oracle:$_") : () } @names;
	return @sources;
    }


    sub connect {
	my ($drh, $dbname, $user, $auth, $attr)= @_;

	if ($dbname =~ /;/) {
	    my ($n,$v);
	    $dbname =~ s/^\s+//;
	    $dbname =~ s/\s+$//;
	    my @dbname = map {
		($n,$v) = split /\s*=\s*/, $_, -1;
		Carp::carp("DSN component '$_' is not in 'name=value' format")
		    unless defined $v && defined $n;
                (uc($n), $v)
	    } split /\s*;\s*/, $dbname;
	    my %dbname = ( PROTOCOL => 'tcp', @dbname );

	    # extract main attributes for connect_data portion
	    my @connect_data_attr = qw(SID INSTANCE_NAME SERVER SERVICE_NAME);
	    my %connect_data = map { ($_ => delete $dbname{$_}) }
		grep { exists $dbname{$_} } @connect_data_attr;
	    my $connect_data = join "", map { "($_=$connect_data{$_})" } keys %connect_data;

	    return $drh->DBI::set_err(-1,
		"Can't connect using this syntax without specifying a HOST and one of @connect_data_attr")
		unless $dbname{HOST} and %connect_data;

	    my @addrs = map { "($_=$dbname{$_})" } keys %dbname;
	    my $addrs = join "", @addrs;
	    if ($dbname{PORT}) {
		$addrs = "(ADDRESS=$addrs)";
	    }
	    else {
		$addrs = "(ADDRESS_LIST=(ADDRESS=$addrs(PORT=1526))"
				     . "(ADDRESS=$addrs(PORT=1521)))";
	    }
	    $dbname = "(DESCRIPTION=$addrs(CONNECT_DATA=$connect_data))";
	    $drh->trace_msg("connect using '$dbname'");
	}

	# If the application is asking for specific database
	# then we may have to mung the dbname

	$dbname = $1 if !$dbname && $user && $user =~ s/\@(.*)//s;

	$drh->trace_msg("$ORACLE_ENV environment variable not set\n")
		if !$ENV{$ORACLE_ENV} and $^O ne "MSWin32";

	# create a 'blank' dbh

	$user = '' if not defined $user;
        (my $user_only = $user) =~ s:/.*::;
	my ($dbh, $dbh_inner) = DBI::_new_dbh($drh, {
	    'Name' => $dbname,
	    'dbi_imp_data' => $attr->{dbi_imp_data},
	    # these two are just for backwards compatibility
	    'USER' => uc $user_only, 'CURRENT_USER' => uc $user_only,
	    });

	# Call Oracle OCI logon func in Oracle.xs file
	# and populate internal handle data.
	DBD::Oracle::db::_login($dbh, $dbname, $user, $auth, $attr)
	    or return undef;

	if ($attr && $attr->{ora_module_name}) {
	    eval {
		$dbh->do(q{BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;},
		       undef, $attr->{ora_module_name});
	    };
	}
	unless (length $user_only) {
	    $user_only = $dbh->selectrow_array(q{
		SELECT SYS_CONTEXT('userenv','session_user') FROM DUAL
	    });
	    $dbh_inner->{Username} = $user_only;
	    # these two are just for backwards compatibility
	    $dbh_inner->{USER} = $dbh_inner->{CURRENT_USER} = uc $user_only;
	}

	$dbh;
    }
    
     sub private_attribute_info {
            return { ora_home_key=>undef};
    }

}


{   package DBD::Oracle::db; # ====== DATABASE ======
    use strict;
    use DBI qw(:sql_types);

    sub prepare {
	my($dbh, $statement, @attribs)= @_;

	# create a 'blank' sth

	my $sth = DBI::_new_sth($dbh, {
	    'Statement' => $statement,
	    });

	# Call Oracle OCI parse func in Oracle.xs file.
	# and populate internal handle data.

	DBD::Oracle::st::_prepare($sth, $statement, @attribs)
	    or return undef;

	$sth;
    }


    sub ping {
	my($dbh) = @_;
	my $ok = 0;
	eval {
	    local $SIG{__DIE__};
	    local $SIG{__WARN__};
	    # we know that Oracle 7 prepare does a describe so this will
	    # actually talk to the server and is this a valid and cheap test.
	    my $sth =  $dbh->prepare("select SYSDATE from DUAL /* ping */");
	    # But Oracle 8+ doesn't talk to server unless we describe the query
	    $ok = $sth && $sth->FETCH('NUM_OF_FIELDS');
	};
	return ($@) ? 0 : $ok;
    }


    sub get_info {
	my($dbh, $info_type) = @_;
	require DBD::Oracle::GetInfo;
	my $v = $DBD::Oracle::GetInfo::info{int($info_type)};
	$v = $v->($dbh) if ref $v eq 'CODE';
	return $v;
    }

    sub private_attribute_info {
        return { ora_max_nested_cursors => undef,
                 ora_array_chunk_size   => undef,
                 ora_ph_type		=> undef,
                 ora_ph_csform		=> undef,
                 ora_parse_error_offset => undef,
                 ora_dbh_share		=> undef,
                 ora_use_proc_connection=> undef,
                 ora_envhp		=> undef,
                 ora_context		=> undef,
                 ora_svchp		=> undef,
                 ora_errhp		=> undef,
                 ora_init_mode		=> undef,
                 ora_charset		=> undef,	
                 ora_ncharset		=> undef,
                 ora_session_mode	=> undef,
                 };
    }
   

    sub table_info {
	my($dbh, $CatVal, $SchVal, $TblVal, $TypVal) = @_;
	# XXX add knowledge of temp tables, etc
	# SQL/CLI (ISO/IEC JTC 1/SC 32 N 0595), 6.63 Tables
	if (ref $CatVal eq 'HASH') {
	    ($CatVal, $SchVal, $TblVal, $TypVal) =
		@$CatVal{'TABLE_CAT','TABLE_SCHEM','TABLE_NAME','TABLE_TYPE'};
	}
	my @Where = ();
	my $SQL;
	if ( defined $CatVal && $CatVal eq '%' && (!defined $SchVal || $SchVal eq '') && (!defined $TblVal || $TblVal eq '')) { # Rule 19a
		$SQL = <<'SQL';
SELECT NULL TABLE_CAT
     , NULL TABLE_SCHEM
     , NULL TABLE_NAME
     , NULL TABLE_TYPE
     , NULL REMARKS
  FROM DUAL
SQL
	}
	elsif ( defined $SchVal && $SchVal eq '%' && (!defined $CatVal || $CatVal eq '') && (!defined $TblVal || $TblVal eq '')) { # Rule 19b
		$SQL = <<'SQL';
SELECT NULL TABLE_CAT
     , s    TABLE_SCHEM
     , NULL TABLE_NAME
     , NULL TABLE_TYPE
     , NULL REMARKS
  FROM
(
  SELECT USERNAME s FROM ALL_USERS
  UNION
  SELECT 'PUBLIC' s FROM DUAL
)
 ORDER BY TABLE_SCHEM
SQL
	}
	elsif ( defined $TypVal && $TypVal eq '%' && (!defined $CatVal || $CatVal eq '') && (!defined $SchVal || $SchVal eq '') && (!defined $TblVal || $TblVal eq '')) { # Rule 19c
		$SQL = <<'SQL';
SELECT NULL TABLE_CAT
     , NULL TABLE_SCHEM
     , NULL TABLE_NAME
     , t.tt TABLE_TYPE
     , NULL REMARKS
  FROM
(
  SELECT 'TABLE'    tt FROM DUAL
    UNION
  SELECT 'VIEW'     tt FROM DUAL
    UNION
  SELECT 'SYNONYM'  tt FROM DUAL
    UNION
  SELECT 'SEQUENCE' tt FROM DUAL
) t
 ORDER BY TABLE_TYPE
SQL
	}
	else {
		$SQL = <<'SQL';
SELECT *
  FROM
(
  SELECT /*+ RULE*/
       NULL         TABLE_CAT
     , t.OWNER      TABLE_SCHEM
     , t.TABLE_NAME TABLE_NAME
     , decode(t.OWNER
	  , 'SYS'    , 'SYSTEM '
	  , 'SYSTEM' , 'SYSTEM '
          , '' ) || t.TABLE_TYPE TABLE_TYPE
     , c.COMMENTS   REMARKS
  FROM ALL_TAB_COMMENTS c
     , ALL_CATALOG      t
 WHERE c.OWNER      (+) = t.OWNER
   AND c.TABLE_NAME (+) = t.TABLE_NAME
   AND c.TABLE_TYPE (+) = t.TABLE_TYPE
)
SQL
		if ( defined $SchVal ) {
			push @Where, "TABLE_SCHEM LIKE '$SchVal' ESCAPE '\\'";
		}
		if ( defined $TblVal ) {
			push @Where, "TABLE_NAME  LIKE '$TblVal' ESCAPE '\\'";
		}
		if ( defined $TypVal ) {
			my $table_type_list;
			$TypVal =~ s/^\s+//;
			$TypVal =~ s/\s+$//;
			my @ttype_list = split (/\s*,\s*/, $TypVal);
			foreach my $table_type (@ttype_list) {
				if ($table_type !~ /^'.*'$/) {
					$table_type = "'" . $table_type . "'";
				}
				$table_type_list = join(", ", @ttype_list);
			}
			push @Where, "TABLE_TYPE IN ($table_type_list)";
		}
		$SQL .= ' WHERE ' . join("\n   AND ", @Where ) . "\n" if @Where;
		$SQL .= " ORDER BY TABLE_TYPE, TABLE_SCHEM, TABLE_NAME\n";
	}
	my $sth = $dbh->prepare($SQL) or return undef;
	$sth->execute or return undef;
	$sth;
}


    sub primary_key_info {
        my($dbh, $catalog, $schema, $table) = @_;
        if (ref $catalog eq 'HASH') {
            ($schema, $table) = @$catalog{'TABLE_SCHEM','TABLE_NAME'};
            $catalog = undef;
        }                  
	my $SQL = <<'SQL';
SELECT *
  FROM
(
  SELECT /*+ RULE*/
         NULL              TABLE_CAT
       , c.OWNER           TABLE_SCHEM
       , c.TABLE_NAME      TABLE_NAME
       , c.COLUMN_NAME     COLUMN_NAME
       , c.POSITION        KEY_SEQ
       , c.CONSTRAINT_NAME PK_NAME
    FROM ALL_CONSTRAINTS   p
       , ALL_CONS_COLUMNS  c
   WHERE p.OWNER           = c.OWNER
     AND p.TABLE_NAME      = c.TABLE_NAME
     AND p.CONSTRAINT_NAME = c.CONSTRAINT_NAME
     AND p.CONSTRAINT_TYPE = 'P'
)
 WHERE TABLE_SCHEM = ?
   AND TABLE_NAME  = ?
 ORDER BY TABLE_SCHEM, TABLE_NAME, KEY_SEQ
SQL
#warn "@_\n$Sql ($schema, $table)";
	my $sth = $dbh->prepare($SQL) or return undef;
	$sth->execute($schema, $table) or return undef;
	$sth;
}

    sub foreign_key_info {
	my $dbh  = shift;
	my $attr = ( ref $_[0] eq 'HASH') ? $_[0] : {
	    'UK_TABLE_SCHEM' => $_[1],'UK_TABLE_NAME ' => $_[2]
	   ,'FK_TABLE_SCHEM' => $_[4],'FK_TABLE_NAME ' => $_[5] };
	my $SQL = <<'SQL';  # XXX: DEFERABILITY
SELECT *
  FROM
(
  SELECT /*+ RULE*/
         to_char( NULL )    UK_TABLE_CAT
       , uk.OWNER           UK_TABLE_SCHEM
       , uk.TABLE_NAME      UK_TABLE_NAME
       , uc.COLUMN_NAME     UK_COLUMN_NAME
       , to_char( NULL )    FK_TABLE_CAT
       , fk.OWNER           FK_TABLE_SCHEM
       , fk.TABLE_NAME      FK_TABLE_NAME
       , fc.COLUMN_NAME     FK_COLUMN_NAME
       , uc.POSITION        ORDINAL_POSITION
       , 3                  UPDATE_RULE
       , decode( fk.DELETE_RULE, 'CASCADE', 0, 'RESTRICT', 1, 'SET NULL', 2, 'NO ACTION', 3, 'SET DEFAULT', 4 )
                            DELETE_RULE
       , fk.CONSTRAINT_NAME FK_NAME
       , uk.CONSTRAINT_NAME UK_NAME
       , to_char( NULL )    DEFERABILITY
       , decode( uk.CONSTRAINT_TYPE, 'P', 'PRIMARY', 'U', 'UNIQUE')
                            UNIQUE_OR_PRIMARY
    FROM ALL_CONSTRAINTS    uk
       , ALL_CONS_COLUMNS   uc
       , ALL_CONSTRAINTS    fk
       , ALL_CONS_COLUMNS   fc
   WHERE uk.OWNER            = uc.OWNER
     AND uk.CONSTRAINT_NAME  = uc.CONSTRAINT_NAME
     AND fk.OWNER            = fc.OWNER
     AND fk.CONSTRAINT_NAME  = fc.CONSTRAINT_NAME
     AND uk.CONSTRAINT_TYPE IN ('P','U')
     AND fk.CONSTRAINT_TYPE  = 'R'
     AND uk.CONSTRAINT_NAME  = fk.R_CONSTRAINT_NAME
     AND uk.OWNER            = fk.R_OWNER
     AND uc.POSITION         = fc.POSITION
)
 WHERE 1              = 1
SQL
	my @BindVals = ();
	while ( my ( $k, $v ) = each %$attr ) {
	    if ( $v ) {
		$SQL .= "   AND $k = ?\n";
		push @BindVals, $v;
	    }
	}
	$SQL .= " ORDER BY UK_TABLE_SCHEM, UK_TABLE_NAME, FK_TABLE_SCHEM, FK_TABLE_NAME, ORDINAL_POSITION\n";
	my $sth = $dbh->prepare( $SQL ) or return undef;
	$sth->execute( @BindVals ) or return undef;
	$sth;
    }


    sub column_info {
	my $dbh  = shift;
	my $attr = ( ref $_[0] eq 'HASH') ? $_[0] : {
	    'TABLE_SCHEM' => $_[1],'TABLE_NAME' => $_[2],'COLUMN_NAME' => $_[3] };
	my($typecase,$typecaseend) = ('','');
	if (ora_server_version($dbh)->[0] >= 8) {
	    $typecase = <<'SQL';
CASE WHEN tc.DATA_TYPE LIKE 'TIMESTAMP% WITH% TIME ZONE' THEN 95
     WHEN tc.DATA_TYPE LIKE 'TIMESTAMP%'                 THEN 93
     WHEN tc.DATA_TYPE LIKE 'INTERVAL DAY% TO SECOND%'   THEN 110
     WHEN tc.DATA_TYPE LIKE 'INTERVAL YEAR% TO MONTH'    THEN 107
ELSE
SQL
	    $typecaseend = 'END';
	}
	my $SQL = <<"SQL";
SELECT *
  FROM
(
  SELECT /*+ RULE*/
         to_char( NULL )     TABLE_CAT
       , tc.OWNER            TABLE_SCHEM
       , tc.TABLE_NAME       TABLE_NAME
       , tc.COLUMN_NAME      COLUMN_NAME
       , $typecase decode( tc.DATA_TYPE
         , 'MLSLABEL' , -9106
         , 'ROWID'    , -9104
         , 'UROWID'   , -9104
         , 'BFILE'    ,    -4 -- 31?
         , 'LONG RAW' ,    -4
         , 'RAW'      ,    -3
         , 'LONG'     ,    -1
         , 'UNDEFINED',     0
         , 'CHAR'     ,     1
         , 'NCHAR'    ,     1
         , 'NUMBER'   ,     decode( tc.DATA_SCALE, NULL, 8, 3 )
         , 'FLOAT'    ,     8
         , 'VARCHAR2' ,    12
         , 'NVARCHAR2',    12
         , 'BLOB'     ,    30
         , 'CLOB'     ,    40
         , 'NCLOB'    ,    40
         , 'DATE'     ,    93
         , NULL
         ) $typecaseend      DATA_TYPE          -- ...
       , tc.DATA_TYPE        TYPE_NAME          -- std.?
       , decode( tc.DATA_TYPE
         , 'LONG RAW' , 2147483647
         , 'LONG'     , 2147483647
         , 'CLOB'     , 2147483647
         , 'NCLOB'    , 2147483647
         , 'BLOB'     , 2147483647
         , 'BFILE'    , 2147483647
         , 'NUMBER'   , decode( tc.DATA_SCALE
                        , NULL, 126
                        , nvl( tc.DATA_PRECISION, 38 )
                        )
         , 'FLOAT'    , tc.DATA_PRECISION
         , 'DATE'     , 19
         , tc.DATA_LENGTH
         )                   COLUMN_SIZE
       , decode( tc.DATA_TYPE
         , 'LONG RAW' , 2147483647
         , 'LONG'     , 2147483647
         , 'CLOB'     , 2147483647
         , 'NCLOB'    , 2147483647
         , 'BLOB'     , 2147483647
         , 'BFILE'    , 2147483647
         , 'NUMBER'   , nvl( tc.DATA_PRECISION, 38 ) + 2
         , 'FLOAT'    ,  8 -- ?
         , 'DATE'     , 16
         , tc.DATA_LENGTH
         )                   BUFFER_LENGTH
       , decode( tc.DATA_TYPE
         , 'DATE'     ,  0
         , tc.DATA_SCALE
         )                   DECIMAL_DIGITS     -- ...
       , decode( tc.DATA_TYPE
         , 'FLOAT'    ,  2
         , 'NUMBER'   ,  decode( tc.DATA_SCALE, NULL, 2, 10 )
         , NULL
         )                   NUM_PREC_RADIX
       , decode( tc.NULLABLE
         , 'Y'        ,  1
         , 'N'        ,  0
         , NULL
         )                   NULLABLE
       , cc.COMMENTS         REMARKS
       , tc.DATA_DEFAULT     COLUMN_DEF         -- Column is LONG!
       , decode( tc.DATA_TYPE
         , 'MLSLABEL' , -9106
         , 'ROWID'    , -9104
         , 'UROWID'   , -9104
         , 'BFILE'    ,    -4 -- 31?
         , 'LONG RAW' ,    -4
         , 'RAW'      ,    -3
         , 'LONG'     ,    -1
         , 'UNDEFINED',     0
         , 'CHAR'     ,     1
         , 'NCHAR'    ,     1
         , 'NUMBER'   ,     decode( tc.DATA_SCALE, NULL, 8, 3 )
         , 'FLOAT'    ,     8
         , 'VARCHAR2' ,    12
         , 'NVARCHAR2',    12
         , 'BLOB'     ,    30
         , 'CLOB'     ,    40
         , 'NCLOB'    ,    40
         , 'DATE'     ,     9 -- not 93!
         , NULL
         )                   SQL_DATA_TYPE      -- ...
       , decode( tc.DATA_TYPE
         , 'DATE'     ,     3
         , NULL
         )                   SQL_DATETIME_SUB   -- ...
       , to_number( NULL )   CHAR_OCTET_LENGTH  -- TODO
       , tc.COLUMN_ID        ORDINAL_POSITION
       , decode( tc.NULLABLE
         , 'Y'        , 'YES'
         , 'N'        , 'NO'
         , NULL
         )                   IS_NULLABLE
    FROM ALL_TAB_COLUMNS  tc
       , ALL_COL_COMMENTS cc
   WHERE tc.OWNER         = cc.OWNER
     AND tc.TABLE_NAME    = cc.TABLE_NAME
     AND tc.COLUMN_NAME   = cc.COLUMN_NAME
)
 WHERE 1              = 1
SQL
	my @BindVals = ();
	while ( my ( $k, $v ) = each %$attr ) {
	    if ( $v ) {
		$SQL .= "   AND $k LIKE ? ESCAPE '\\'\n";
		push @BindVals, $v;
	    }
	}
	$SQL .= " ORDER BY TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION\n";
	my $sth = $dbh->prepare( $SQL ) or return undef;
	$sth->execute( @BindVals ) or return undef;
	$sth;
    }

    sub type_info_all {
	my ($dbh) = @_;
        my $version = ( ora_server_version($dbh)->[0] < DBD::Oracle::ORA_OCI() )
                    ?   ora_server_version($dbh)->[0] : DBD::Oracle::ORA_OCI();
        my $vc2len = ( $version < 8 ) ? "2000" : "4000";

	my $type_info_all = [
	    {
		TYPE_NAME          =>  0,
		DATA_TYPE          =>  1,
		COLUMN_SIZE        =>  2,
		LITERAL_PREFIX     =>  3,
		LITERAL_SUFFIX     =>  4,
		CREATE_PARAMS      =>  5,
		NULLABLE           =>  6,
		CASE_SENSITIVE     =>  7,
		SEARCHABLE         =>  8,
		UNSIGNED_ATTRIBUTE =>  9,
		FIXED_PREC_SCALE   => 10,
		AUTO_UNIQUE_VALUE  => 11,
		LOCAL_TYPE_NAME    => 12,
		MINIMUM_SCALE      => 13,
		MAXIMUM_SCALE      => 14,
		SQL_DATA_TYPE      => 15,
		SQL_DATETIME_SUB   => 16,
		NUM_PREC_RADIX     => 17,
		INTERVAL_PRECISION => 18,
	    },
	    [ "LONG RAW",        SQL_LONGVARBINARY, 2147483647,"'",  "'",
		undef,            1,0,0,undef,0,undef,
		"LONG RAW",        undef,undef,SQL_LONGVARBINARY,undef,undef,undef, ],
	    [ "RAW",             SQL_VARBINARY,     2000,      "'",  "'",
		"max length",     1,0,3,undef,0,undef,
		"RAW",             undef,undef,SQL_VARBINARY,    undef,undef,undef, ],
	    [ "LONG",            SQL_LONGVARCHAR,   2147483647,"'",  "'",
		undef,            1,1,0,undef,0,undef,
		"LONG",            undef,undef,SQL_LONGVARCHAR,  undef,undef,undef, ],
	    [ "CHAR",            SQL_CHAR,          2000,      "'",  "'",
		"max length",     1,1,3,undef,0,0,
		"CHAR",            undef,undef,SQL_CHAR,         undef,undef,undef, ],
	    [ "DECIMAL",         SQL_DECIMAL,       38,        undef,undef,
		"precision,scale",1,0,3,0,    0,0,
		"DECIMAL",         0,    38,   SQL_DECIMAL,      undef,10,   undef, ],
	    [ "DOUBLE PRECISION",SQL_DOUBLE,        15,        undef,undef,
		undef, 1,0,3,0,    0,0,
		"DOUBLE PRECISION",undef,undef,SQL_DOUBLE,       undef,10,   undef, ],
	    [ "DATE",            SQL_TYPE_TIMESTAMP,19,        "'",  "'",
		undef,            1,0,3,undef,0,0,
		"DATE",            0,    0,    SQL_DATE,         3,    undef,undef, ],
	    [ "VARCHAR2",        SQL_VARCHAR,       $vc2len,   "'",  "'",
		"max length",     1,1,3,undef,0,0,
		"VARCHAR2",        undef,undef,SQL_VARCHAR,      undef,undef,undef, ],
	];
	push @$type_info_all,
	    [ "BLOB",            SQL_LONGVARBINARY, 2147483647,"'",  "'",
		 undef,            1,1,0,undef,0,undef,
		"BLOB",            undef,undef,SQL_LONGVARBINARY,undef,undef,undef, ],
	    [ "BFILE",           SQL_LONGVARBINARY, 2147483647,"'",  "'",
		undef,            1,1,0,undef,0,undef,
		"BFILE",           undef,undef,SQL_LONGVARBINARY,undef,undef,undef, ],
	    [ "CLOB",            SQL_LONGVARCHAR,   2147483647,"'",  "'",
		undef,            1,1,0,undef,0,undef,
		"CLOB",            undef,undef,SQL_LONGVARCHAR,  undef,undef,undef, ],
	    if $version >= 8;
	return $type_info_all;
    }

    sub plsql_errstr {
	# original version thanks to Bob Menteer
	my $sth = shift->prepare_cached(q{
	    SELECT name, type, line, position, text
	    FROM user_errors ORDER BY name, type, sequence
	}) or return undef;
	$sth->execute or return undef;
	my ( @msg, $oname, $otype, $name, $type, $line, $pos, $text );
	$oname = $otype = 0;
	while ( ( $name, $type, $line, $pos, $text ) = $sth->fetchrow_array ) {
	    if ( $oname ne $name || $otype ne $type ) {
		push @msg, "Errors for $type $name:";
		$oname = $name;
		$otype = $type;
	    }
	    push @msg, "$line.$pos: $text";
	}
	return join( "\n", @msg );
    }

    #
    # note, dbms_output must be enabled prior to usage
    #
    sub dbms_output_enable {
	my ($dbh, $buffersize) = @_;
	$buffersize ||= 20000;	# use oracle 7.x default
	$dbh->do("begin dbms_output.enable(:1); end;", undef, $buffersize);
    }

    sub dbms_output_get {
	my $dbh = shift;
	my $sth = $dbh->prepare_cached("begin dbms_output.get_line(:l, :s); end;")
		or return;
	my ($line, $status, @lines);
	# line can be greater that 255 (e.g. 7 byte date is expanded on output)
	$sth->bind_param_inout(':l', \$line,  400, { ora_type => 1 });
	$sth->bind_param_inout(':s', \$status, 20, { ora_type => 1 });
	if (!wantarray) {
	    $sth->execute or return undef;
	    return $line if $status eq '0';
	    return undef;
	}
	push @lines, $line while($sth->execute && $status eq '0');
	return @lines;
    }

    sub dbms_output_put {
	my $dbh = shift;
	my $sth = $dbh->prepare_cached("begin dbms_output.put_line(:1); end;")
		or return;
	my $line;
	foreach $line (@_) {
	    $sth->execute($line) or return;
	}
	return 1;
    }

 
    sub dbms_msgpipe_get {
	my $dbh = shift;
	my $sth = $dbh->prepare_cached(q{
	    begin dbms_msgpipe.get_request(:returnpipe, :proc, :param); end;
	}) or return;
	my $msg = ['','',''];
	$sth->bind_param_inout(":returnpipe", \$msg->[0],   30);
	$sth->bind_param_inout(":proc",       \$msg->[1],   30);
	$sth->bind_param_inout(":param",      \$msg->[2], 4000);
	$sth->execute or return undef;
	return $msg;
    }

    sub dbms_msgpipe_ack {
	my $dbh = shift;
	my $msg = shift;
	my $sth = $dbh->prepare_cached(q{
	    begin dbms_msgpipe.acknowledge(:returnpipe, :errormsg, :param); end;
	}) or return;
	$sth->bind_param_inout(":returnpipe", \$msg->[0],   30);
	$sth->bind_param_inout(":proc",       \$msg->[1],   30);
	$sth->bind_param_inout(":param",      \$msg->[2], 4000);
	$sth->execute or return undef;
	return 1;
    }

    sub ora_server_version {
	my $dbh = shift;
	return $dbh->{ora_server_version} if defined $dbh->{ora_server_version};
	$dbh->{ora_server_version} =
	   [ split /\./, $dbh->selectrow_array(<<'SQL', undef, 'Oracle%', 'Personal Oracle%') .''];
SELECT version
  FROM product_component_version
 WHERE product LIKE ? or product LIKE ?
SQL
    }

    sub ora_nls_parameters {
	my $dbh = shift;
	my $refresh = shift;

	if ($refresh || !$dbh->{ora_nls_parameters}) {
            my $nls_parameters = $dbh->selectall_arrayref(q{
		SELECT parameter, value FROM v$nls_parameters
	    }) or return;
	    $dbh->{ora_nls_parameters} = { map { $_->[0] => $_->[1] } @$nls_parameters };
	}

	# return copy of params to protect against accidental editing
	my %nls = %{$dbh->{ora_nls_parameters}};
	return \%nls;
    }

    sub ora_can_unicode {
	my $dbh = shift;
	my $refresh = shift;
	# 0 = No Unicode support.
	# 1 = National character set is Unicode-based.
	# 2 = Database character set is Unicode-based.
	# 3 = Both character sets are Unicode-based.

	return $dbh->{ora_can_unicode}
	    if defined $dbh->{ora_can_unicode} && !$refresh;

	my $nls = $dbh->ora_nls_parameters($refresh);

	$dbh->{ora_can_unicode}  = 0;
	$dbh->{ora_can_unicode} += 1 if $nls->{NLS_NCHAR_CHARACTERSET} =~ /UTF/;
	$dbh->{ora_can_unicode} += 2 if $nls->{NLS_CHARACTERSET}       =~ /UTF/;

	return $dbh->{ora_can_unicode};
    }

}   # end of package DBD::Oracle::db


{   package DBD::Oracle::st; # ====== STATEMENT ======


   sub bind_param_inout_array {
	my $sth = shift;
	my ($p_id, $value_array,$maxlen, $attr) = @_;
	return $sth->set_err($DBI::stderr, "Value for parameter $p_id must be an arrayref, not a ".ref($value_array))
	   if defined $value_array and ref $value_array and ref $value_array ne 'ARRAY';
	   
	return $sth->set_err($DBI::stderr, "Can't use named placeholder '$p_id' for non-driver supported bind_param_inout_array")
	   unless DBI::looks_like_number($p_id); # because we rely on execute(@ary) here
	   
	return $sth->set_err($DBI::stderr, "Placeholder '$p_id' is out of range")
	   if $p_id <= 0; # can't easily/reliably test for too big
	   
	# get/create arrayref to hold params
	my $hash_of_arrays = $sth->{ParamArrays} ||= { };
	   
        $$hash_of_arrays{$p_id} = $value_array;
	return ora_bind_param_inout_array($sth, $p_id, $value_array,$maxlen, $attr);
	1;

    }
    
    
    sub execute_for_fetch {
       my ($sth, $fetch_tuple_sub, $tuple_status) = @_;
       my $row_count = 0;
       my $tuple_count="0E0";
       my $tuple_batch_status;
       my $dbh = $sth->{Database};
       my $batch_size =($dbh->{'ora_array_chunk_size'}||= 1000);
       if(defined($tuple_status)) {
           @$tuple_status = ();
           $tuple_batch_status = [ ];
       }
       
       while (1) {
           my @tuple_batch;
           for (my $i = 0; $i < $batch_size; $i++) {
                push @tuple_batch, [ @{$fetch_tuple_sub->() || last} ];
           }
           last unless @tuple_batch;
           my $res = ora_execute_array($sth,
                                           \@tuple_batch,
                                           scalar(@tuple_batch),
                                           $tuple_batch_status);
           if(defined($res) && defined($row_count)) {
                $row_count += $res;
           } else {
                $row_count = undef;
           }
           $tuple_count+=@$tuple_batch_status;
           push @$tuple_status, @$tuple_batch_status
           if defined($tuple_status);
       }
       if (!wantarray) {
	   return undef if !defined $row_count;
   	   return $tuple_count;
       }
       return (defined $row_count ? $tuple_count : undef, $row_count);
    }

    sub private_attribute_info {
        return { ora_lengths 		=> undef,
                 ora_types   		=> undef,
                 ora_rowid		=> undef,
                 ora_est_row_width	=> undef,
                 ora_type               => undef,
                 ora_field              => undef,
                 ora_csform		=> undef,
                 ora_maxdata_size	=> undef,
                 ora_parse_lang		=> undef,
                 ora_placeholders	=> undef,
                 ora_auto_lob		=> undef,
                 ora_check_sql		=> undef
                };
    }
   
}

1;

__END__

=head1 NAME

DBD::Oracle - Oracle database driver for the DBI module

=head1 SYNOPSIS

  use DBI;

  $dbh = DBI->connect("dbi:Oracle:$dbname", $user, $passwd);

  $dbh = DBI->connect("dbi:Oracle:host=$host;sid=$sid", $user, $passwd);

  # See the DBI module documentation for full details

  # for some advanced uses you may need Oracle type values:
  use DBD::Oracle qw(:ora_types);


=head1 DESCRIPTION

DBD::Oracle is a Perl module which works with the DBI module to provide
access to Oracle databases.

=head1 CONNECTING TO ORACLE

This is a topic which often causes problems. Mainly due to Oracle's many
and sometimes complex ways of specifying and connecting to databases.
(James Taylor and Lane Sharman have contributed much of the text in
this section.)

=head2 Connecting without environment variables or tnsnames.ora file

If you use the C<host=$host;sid=$sid> style syntax, for example:

  $dbh = DBI->connect("dbi:Oracle:host=myhost.com;sid=ORCL", $user, $passwd);

then DBD::Oracle will construct a full connection descriptor string
for you and Oracle will not need to consult the tnsnames.ora file.

If a C<port> number is not specified then the descriptor will try both
1526 and 1521 in that order (e.g., new then old).  You can check which
port(s) are in use by typing "$ORACLE_HOME/bin/lsnrctl stat" on the server.


=head2 Oracle Environment Variables

Oracle typically uses two environment variables to specify default
connections: ORACLE_SID and TWO_TASK.

ORACLE_SID is really unnecessary to set since TWO_TASK provides the
same functionality in addition to allowing remote connections.

  % setenv TWO_TASK T:hostname:ORACLE_SID            # for csh shell
  $ TWO_TASK=T:hostname:ORACLE_SID export TWO_TASK   # for sh shell

  % sqlplus username/password

Note that if you have *both* local and remote databases, and you
have ORACLE_SID *and* TWO_TASK set, and you don't specify a fully
qualified connect string on the command line, TWO_TASK takes precedence
over ORACLE_SID (i.e. you get connected to remote system).

  TWO_TASK=P:sid

will use the pipe driver for local connections using SQL*Net v1.

  TWO_TASK=T:machine:sid

will use TCP/IP (or D for DECNET, etc.) for remote SQL*Net v1 connection.

  TWO_TASK=dbname

will use the info stored in the SQL*Net v2 F<tnsnames.ora>
configuration file for local or remote connections.

The ORACLE_HOME environment variable should be set correctly.
In general, the value used should match the version of Oracle that
was used to build DBD::Oracle.  If using dynamic linking then
ORACLE_HOME should match the version of Oracle that will be used
to load in the Oracle client libraries (via LD_LIBRARY_PATH, ldconfig,
or similar on Unix).

ORACLE_HOME can be left unset if you aren't using any of Oracle's
executables, but it is I<not> recommended and error messages may not display.
It should be set to the ORACLE_HOME directory of the version of Oracle
that DBD::Oracle was compiled with.

Discouraging the use of ORACLE_SID makes it easier on the users to see
what is going on. (It's unfortunate that TWO_TASK couldn't be renamed,
since it makes no sense to the end user, and doesn't have the ORACLE prefix).

=head2 Connection Examples Using DBD::Oracle

First, how to connect to a local database I<without> using a Listener:

  $dbh = DBI->connect('dbi:Oracle:SID','scott', 'tiger');

you can also leave the SID empty:

  $dbh = DBI->connect('dbi:Oracle:','scott', 'tiger');

in which case Oracle client code will use the ORACLE_SID environment
variable (if TWO_TASK env var isn't defined).

Below are various ways of connecting to an oracle database using
SQL*Net 1.x and SQL*Net 2.x.  "Machine" is the computer the database is
running on, "SID" is the SID of the database, "DB" is the SQL*Net 2.x
connection descriptor for the database.

B<Note:> Some of these formats may not work with Oracle 8+.

  BEGIN {
     $ENV{ORACLE_HOME} = '/home/oracle/product/7.x.x';
     $ENV{TWO_TASK}    = 'DB';
  }
  $dbh = DBI->connect('dbi:Oracle:','scott', 'tiger');
  #  - or -
  $dbh = DBI->connect('dbi:Oracle:','scott/tiger');

Refer to your Oracle documentation for valid values of TWO_TASK.

Here are some variations (not setting TWO_TASK) in order of preference:

  $dbh = DBI->connect('dbi:Oracle:DB','username','password')

  $dbh = DBI->connect('dbi:Oracle:DB','username/password','')

  $dbh = DBI->connect('dbi:Oracle:','username@DB','password')

  $dbh = DBI->connect('dbi:Oracle:host=foobar;sid=ORCL;port=1521', 'scott/tiger', '')

  $dbh = DBI->connect('dbi:Oracle:', q{scott/tiger@(DESCRIPTION=
  (ADDRESS=(PROTOCOL=TCP)(HOST= foobar)(PORT=1521))
  (CONNECT_DATA=(SID=ORCL)))}, "")

If you are having problems with login taking a long time (>10 secs say)
then you might have tripped up on an Oracle bug. You can try using one
of the ...@DB variants as a workaround. E.g.,

  $dbh = DBI->connect('','username/password@DB','');

On the other hand, that may cause you to trip up on another Oracle bug
that causes alternating connection attempts to fail! (In reality only
a small proportion of people experience these problems.)


To connect to a local database with a user which has been set-up to
authenticate via the OS ("ALTER USER username IDENTIFIED EXTERNALLY"):

  $dbh = DBI->connect('dbi:Oracle:','/','');

Note the lack of a connection name (use the ORACLE_SID environment
variable). If an explicit SID is used you'll probably get an ORA-01004 error.

That only works for local databases. (Authentication to remote Oracle
databases using your unix login name without a password and is possible
but it's not secure and not recommended so not documented here. If you
can't find the information elsewhere then you probably shouldn't be
trying to do it.)


=head2 Optimizing Oracle's listener

[By Lane Sharman <lane@bienlogic.com>] I spent a LOT of time optimizing
listener.ora and I am including it here for anyone to benefit from. My
connections over tnslistener on the same humble Netra 1 take an average
of 10-20 milli seconds according to tnsping. If anyone knows how to
make it better, please let me know!

  LISTENER =
   (ADDRESS_LIST =
    (ADDRESS =
      (PROTOCOL = TCP)
      (Host = aa.bbb.cc.d)
      (Port = 1521)
      (QUEUESIZE=10)
    )
   )

  STARTUP_WAIT_TIME_LISTENER = 0
  CONNECT_TIMEOUT_LISTENER = 10
  TRACE_LEVEL_LISTENER = OFF
  SID_LIST_LISTENER =
   (SID_LIST =
    (SID_DESC =
      (SID_NAME = xxxx)
      (ORACLE_HOME = /xxx/local/oracle7-3)
        (PRESPAWN_MAX = 40)
        (PRESPAWN_LIST=
        (PRESPAWN_DESC=(PROTOCOL=tcp) (POOL_SIZE=40) (TIMEOUT=120))
      )
     )
   )

1) When the application is co-located on the host AND there is no need for
outside SQLNet connectivity, stop the listener. You do not need it. Get
your application/cgi/whatever working using pipes and shared memory. I am
convinced that this is one of the connection bugs (sockets over the same
machine). Note the $ENV{ORAPIPES} env var.  The essential code to do
this at the end of this section.

2) Be careful in how you implement the multi-threaded server. Currently I
am not using it in the initxxxx.ora file but will be doing some more testing.

3) Be sure to create user rollback segments and use them; do not use the
system rollback segments; however, you must also create a small rollback
space for the system as well.

5) Use large tuning settings and get lots of RAM. Check out all the
parameters you can set in v$parameters because there are quite a few not
documented you may to set in your initxxx.ora file.

6) Use svrmgrl to control oracle from the command line. Write lots of small
SQL scripts to get at V$ info.

  use DBI;
  # Environmental variables used by Oracle
  $ENV{ORACLE_SID}   = "xxx";
  $ENV{ORACLE_HOME}  = "/opt/oracle7";
  $ENV{EPC_DISABLED} = "TRUE";
  $ENV{ORAPIPES} = "V2";
  my $dbname = "xxx";
  my $dbuser = "xxx";
  my $dbpass = "xxx";
  my $dbh = DBI->connect("dbi:Oracle:$dbname", $dbuser, $dbpass)
             || die "Unable to connect to $dbname: $DBI::errstr\n";

=head2 Oracle utilities

If you are still having problems connecting then the Oracle adapters
utility may offer some help. Run these two commands:

  $ORACLE_HOME/bin/adapters
  $ORACLE_HOME/bin/adapters $ORACLE_HOME/bin/sqlplus

and check the output. The "Protocol Adapters" section should be the
same.  It should include at least "IPC Protocol Adapter" and "TCP/IP
Protocol Adapter".

If it generates any errors which look relevant then please talk to your
Oracle technical support (and not the dbi-users mailing list). Thanks.
Thanks to Mark Dedlow for this information.

=head2 Constants

=over 4

=item :ora_session_modes

ORA_SYSDBA ORA_SYSOPER

=item :ora_types

  ORA_VARCHAR2 ORA_STRING ORA_NUMBER ORA_LONG ORA_ROWID ORA_DATE ORA_RAW
  ORA_LONGRAW ORA_CHAR ORA_CHARZ ORA_MLSLABEL ORA_XMLTYPE ORA_CLOB ORA_BLOB 
  ORA_RSET ORA_VARCHAR2_TABLE ORA_NUMBER_TABLE SQLT_INT SQLT_FLT ORA_OCI 
  SQLT_CHR SQLT_BIN  

=over 4

=item SQLCS_IMPLICIT

=item SQLCS_NCHAR

SQLCS_IMPLICIT and SQLCS_NCHAR are I<character set form> values.
See notes about Unicode elsewhere in this document.

=item SQLT_INT

=item SQLT_FLT

These types are used only internally, and may be specified as internal
bind type for ORA_NUMBER_TABLE. See notes about ORA_NUMBER_TABLE elsewhere
in this document

=item ORA_OCI

Oracle doesn't provide a formal API for determining the exact version
number of the OCI client library used, so DBD::Oracle has to go digging
(and sometimes has to more or less guess).  The ORA_OCI constant
holds the result of that process.

In string context ORA_OCI returns the full "A.B.C.D" version string.

In numeric context ORA_OCI returns the major.minor version number
(8.1, 9.2, 10.0 etc).  But note that version numbers are not actually
floating point and so if Oracle ever makes a release that has a two
digit minor version, such as C<9.10> it will have a lower numeric
value than the preceding C<9.9> release. So use with care.

The contents and format of ORA_OCI are subject to change (it may,
for example, become a I<version object> in later releases).
I recommend that you avoid checking for exact values.

=back

=item :ora_fetch_orient

  OCI_FETCH_CURRENT OCI_FETCH_NEXT OCI_FETCH_FIRST OCI_FETCH_LAST
  OCI_FETCH_PRIOR OCI_FETCH_ABSOLUTE OCI_FETCH_RELATIVE 

These constants are used to set the orientaion of a fetch on a scrollable cursor.

=item :ora_exe_modes

  OCI_STMT_SCROLLABLE_READONLY 

=head2 Connect Attributes

=over 4

=item ora_session_mode

The ora_session_mode attribute can be used to connect with SYSDBA
authorization and SYSOPER authorization.
The ORA_SYSDBA and ORA_SYSOPER constants can be imported using

  use DBD::Oracle qw(:ora_session_modes);

This is one case where setting ORACLE_SID may be useful since
connecting as SYSDBA or SYSOPER via SQL*Net is frequently disabled
for security reasons.

Example:

  $dsn = "dbi:Oracle:";       # no dbname here
  $ENV{ORACLE_SID} = "orcl";  # set ORACLE_SID as needed
  delete $ENV{TWO_TASK};      # make sure TWO_TASK isn't set

  $dbh = DBI->connect($dsn, "", "", { ora_session_mode => ORA_SYSDBA });

It has been reported that this only works if $dsn does not contain a SID
so that Oracle then uses the value of the ORACLE_SID (not TWO_TASK)
environment variable to connect to a local instance. Also the username
and password should be empty, and the user executing the script needs
to be part of the dba group or osdba group.

=item ora_oratab_orahome

Passing a true value for the ora_oratab_orahome attribute will make
DBD::Oracle change $ENV{ORACLE_HOME} to make the Oracle home directory
specified in the C</etc/oratab> file I<if> the database to connect to
is specified as a SID that exists in the oratab file, and DBD::Oracle was
built to use the Oracle 7 OCI API (not Oracle 8+).

=item ora_module_name

After connecting to the database the value of this attribute is passed
to the SET_MODULE() function in the C<DBMS_APPLICATION_INFO> PL/SQL
package. This can be used to identify the application to the DBA for
monitoring and performance tuning purposes. For example:

  DBI->connect($dsn, $user, $passwd, { ora_module_name => $0 });

=item ora_dbh_share

Needs at least Perl 5.8.0 compiled with ithreads. Allows to share database
connections between threads. The first connect will make the connection, 
all following calls to connect with the same ora_dbh_share attribute
will use the same database connection. The value must be a reference
to a already shared scalar which is initialized to an empty string.

  our $orashr : shared = '' ;

  $dbh = DBI->connect ($dsn, $user, $passwd, {ora_dbh_share => \$orashr}) ;

=item ora_use_proc_connection

This attribute allows to create a DBI handle for an existing SQLLIB
database connection. This can be used to share database connections
between Oracle ProC code and DBI running in an embedded Perl interpreter.
The SQLLIB connection id is appended after the "dbi:Oracle:" initial
argument to DBI::connect.

For example, if in ProC a connection is made like

  EXEC SQL CONNECT 'user/pass@db' AT 'CONID';

the connection may be used from DBI after running something like

  my $dbh = DBI->connect("dbi:Oracle:CONID", "", "",
                        { ora_use_proc_connection => 1 });

To disconnect, first call $dbh->disconnect(), then disconnect in ProC.

This attribute requires DBD::Oracle to be built with the -ProC
option to Makefile.PL.  It is not available with OCI_V7. Not tested
with Perl ithreads or with the ora_dbh_share connect attribute.

=item ora_envhp

The first time a connection is made a new OCI 'environment' is
created by DBD::Oracle and stored in the driver handle.
Subsequent connects reuse (share) that same OCI environment
by default.

The ora_envhp attribute can be used to disable the reuse of the OCI
environment from a previous connect. If the value is C<0> then
a new OCI environment is allocated and used for this connection.

The OCI environment is what holds information about the client side
context, such as the local NLS environment. So by altering %ENV and
setting ora_envhp to 0 you can create connections with different
NLS settings. This is most useful for testing.

=item ora_charset, ora_ncharset

For oracle versions >= 9.2 you can specify the client charset and
ncharset with the ora_charset and ora_ncharset attributes.  You
still need to pass C<ora_envhp = 0> for all but the first connect.

These attributes override the settings from environment variables.

  $dbh = DBI->connect ($dsn, $user, $passwd,
                       {ora_charset => 'AL32UTF8'});

=head2 Database Handle Attributes

=item ora_ph_type

The default placeholder data type for the database session.
The C<TYPE> or L</ora_type> attributes to L<DBI/bind_param> and
L<DBI/bind_param_inout> override the data type for individual placeholders.
The most frequent reason for using this attribute is to permit trailing spaces
in values passed by placeholders.

Constants for the values allowed for this attribute can be imported using

  use DBD::Oracle qw(:ora_types);

Only the following values are permitted for this attribute.

=over 4

=item ORA_VARCHAR2

Oracle clients using OCI 8 will strip trailing spaces and allow embedded \0 bytes.
Oracle clients using OCI 9.2 do not strip trailing spaces and allow embedded \0 bytes.
This is the normal default placeholder type.

=item ORA_STRING

Don't strip trailing spaces and end the string at the first \0.

=item ORA_CHAR

Don't strip trailing spaces and allow embedded \0.
Force 'blank-padded comparison semantics'.

For example:

  use DBD::Oracle qw(:ora_types);
  
  $SQL="select username from all_users where username = ?";
  #username is a char(8)
  $sth=$dbh->prepare($SQL)";
  $sth->bind_param(1,'bloggs',{ ora_type => ORA_CHAR});

Will pad bloggs out to 8 characters and return the username.  

=back

=item ora_parse_error_offset

If the previous error was from a failed C<prepare> due to a syntax error,
this attribute gives the offset into the C<Statement> attribute where the
error was found.

=back

=over 4

=item ora_array_chunk_size

Because of OCI limitations, DBD::Oracle needs to buffer up rows of
bind values in its C<execute_for_fetch> implementation. This attribute
sets the number of rows to buffer at a time (default value is 1000).

The C<execute_for_fetch> function will collect (at most) this many
rows in an array, send them of to the DB for execution, then go back
to collect the next chunk of rows and so on. This attribute can be
used to limit or extend the number of rows processed at a time.

Note that this attribute also applies to C<execute_array>, since that
method is implemented using C<execute_for_fetch>.

=back

=head2 Prepare Attributes

These attributes may be used in the C<\%attr> parameter of the
L<DBI/prepare> database handle method.

=over 4

=item ora_placeholders

Set to false to disable processing of placeholders. Used mainly for loading a
PL/SQL package that has been I<wrapped> with Oracle's C<wrap> utility.

=item ora_parse_lang

Tells the connected database how to interpret the SQL statement.
If 1 (default), the native SQL version for the database is used.
Other recognized values are 0 (old V6, treated as V7 in OCI8),
2 (old V7), 7 (V7), and 8 (V8).
All other values have the same effect as 1.

=item ora_auto_lob

If true (the default), fetching retrieves the contents of the CLOB or
BLOB column in most circumstances.  If false, fetching retrieves the
Oracle "LOB Locator" of the CLOB or BLOB value.

See L</Handling LOBs> for more details.
See also the LOB tests in 05dbi.t of Oracle::OCI for examples
of how to use LOB Locators.

=item ora_pers_lob

If 1 and your DBD::Oracle was built using OCI 10.2 or later the L<Data Interface for Persistent LOBs> will be
used for LOBs.

=item ora_check_sql

If 1 (default), force SELECT statements to be described in prepare().
If 0, allow SELECT statements to defer describe until execute().

See L</Prepare postponed till execute> for more information.

=item ora_exe_mode

This will set the execute mode of the current statement. Presently only one mode is supported;

  OCI_STMT_SCROLLABLE_READONLY - make result set scrollable

See L</Scrollable Cursors> for more details.

=item ora_prefetch_memory

Sets the memory level for top level rows to be prefetched. Rows up to the specified top level row 
count C<RowCacheSize> are fetched if it occupies no more than the specified memory usage limit. The default value is 0, 
which means that memory size is not included in computing the number of rows to prefetch. If
the C<RowCacheSize> value is set to 0 or a negative number when using this value then only 
the memory value entered is used to compute the number of rows to prefetch.

See L</Prefetching Rows> for more details.

=back

=head2 Placeholder Binding Attributes

These attributes may be used in the C<\%attr> parameter of the
L<DBI/bind_param> or L<DBI/bind_param_inout> statement handle methods.

=over 4

=item ora_type

Specify the placeholder's data type using an Oracle data type.
A fatal error is raised if C<ora_type> and the DBI C<TYPE> attribute
are used for the same placeholder.
Some of these types are not supported by the current version of
DBD::Oracle and will cause a fatal error if used.
Constants for the Oracle datatypes may be imported using

  use DBD::Oracle qw(:ora_types);

Potentially useful values when DBD::Oracle was built using OCI 7 and later:

  ORA_VARCHAR2, ORA_STRING, ORA_LONG, ORA_RAW, ORA_LONGRAW,
  ORA_CHAR, ORA_MLSLABEL, ORA_RSET   

Additional values when DBD::Oracle was built using OCI 8 and later:

  ORA_CLOB, ORA_BLOB, ORA_XMLTYPE, ORA_VARCHAR2_TABLE, ORA_NUMBER_TABLE

Additional values when DBD::Oracle was built using OCI 10.2 and later:

  SQLT_CHR, SQLT_BIN 

See L</Data Interface for Persistent LOBs> for how to use these two values.

See L</Binding Cursors> for the correct way to use ORA_RSET.

See L</Handling LOBs> for how to use ORA_CLOB and ORA_BLOB.

See L</SYS.DBMS_SQL datatypes> for ORA_VARCHAR2_TABLE, ORA_NUMBER_TABLE.

See L</Data Interface for Persistent LOBs> for the correct way to use SQLT_CHR and SQLT_BIN.

See L</Other Data Types> for more information.

See also L<DBI/Placeholders and Bind Values>.

=item ora_csform

Specify the OCI_ATTR_CHARSET_FORM for the bind value. Valid values
are SQLCS_IMPLICIT (1) and SQLCS_NCHAR (2). Both those constants can
be imported from the DBD::Oracle module. Rarely needed.

=item ora_csid

Specify the I<integer> OCI_ATTR_CHARSET_ID for the bind value. 
Character set names can't be used currently.

=item ora_maxdata_size

Specify the integer OCI_ATTR_MAXDATA_SIZE for the bind value. 
May be needed if a character set conversion from client to server
causes the data to use more space and so fail with a truncation error.

=item ora_maxarray_numentries

Specify the maximum number of array entries to allocate. Used with
ORA_VARCHAR2_TABLE, ORA_NUMBER_TABLE. Define the maximum number of
array entries Oracle can pass back to you in OUT variable of type
TABLE OF ... .

=item ora_internal_type

Specify internal data representation. Currently is supported only for
ORA_NUMBER_TABLE.

=back

=head2 Trailing Spaces

Please note that only the Oracle OCI 8 strips trailing spaces from VARCHAR placeholder
values and uses Nonpadded Comparison Semantics with the result. 
This causes trouble if the spaces are needed for
comparison with a CHAR value or to prevent the value from
becoming '' which Oracle treats as NULL.
Look for Blank-padded Comparison Semantics and Nonpadded
Comparison Semantics in Oracle's SQL Reference or Server
SQL Reference for more details.

To preserve trailing spaces in placeholder values for Oracle clients that use OCI 8, 
either change the default placeholder type with L</ora_ph_type> or the placeholder
type for a particular call to L<DBI/bind> or L<DBI/bind_param_inout>
with L</ora_type> or C<TYPE>.
Using L<ORA_CHAR> with L<ora_type> or C<SQL_CHAR> with C<TYPE>
allows the placeholder to be used with Padded Comparison Semantics
if the value it is being compared to is a CHAR, NCHAR, or literal.

Please remember that using spaces as a value or at the end of
a value makes visually distinguishing values with different
numbers of spaces difficult and should be avoided.

Oracle Clients that use OCI 9.2 do not strip trailing spaces.

=head2 Padded Char Fields

Oracle Clients after OCI 9.2 will automatically pad CHAR placeholder values to the size of the CHAR.
As the default placeholder type value in DBD::Oracle is ORA_VARCHAR2 to access this behaviour you will 
have to change the default placeholder type with L</ora_ph_type> or placeholder 
type for a particular call with L<DBI/bind> or L<DBI/bind_param_inout>
with L</ORA_CHAR> or C<ORA_CHARZ>.

=head1 Metadata

=head2 C<get_info()>

DBD::Oracle supports C<get_info()>, but (currently) only a few info types.

=head2 C<table_info()>

DBD::Oracle supports attributes for C<table_info()>.

In Oracle, the concept of I<user> and I<schema> is (currently) the
same. Because database objects are owned by an user, the owner names
in the data dictionary views correspond to schema names.
Oracle does not support catalogs so TABLE_CAT is ignored as
selection criterion.

Search patterns are supported for TABLE_SCHEM and TABLE_NAME.

TABLE_TYPE may contain a comma-separated list of table types.
The following table types are supported:

  TABLE
  VIEW
  SYNONYM
  SEQUENCE

The result set is ordered by TABLE_TYPE, TABLE_SCHEM, TABLE_NAME.

The special enumerations of catalogs, schemas and table types are
supported. However, TABLE_CAT is always NULL.

An identifier is passed I<as is>, i.e. as the user provides or
Oracle returns it.
C<table_info()> performs a case-sensitive search. So, a selection
criterion should respect upper and lower case.
Normally, an identifier is case-insensitive. Oracle stores and
returns it in upper case. Sometimes, database objects are created
with quoted identifiers (for reserved words, mixed case, special
characters, ...). Such an identifier is case-sensitive (if not all
upper case). Oracle stores and returns it as given.
C<table_info()> has no special quote handling, neither adds nor
removes quotes.

=head2 C<primary_key_info()>

Oracle does not support catalogs so TABLE_CAT is ignored as
selection criterion.
The TABLE_CAT field of a fetched row is always NULL (undef).
See L</table_info()> for more detailed information.

If the primary key constraint was created without an identifier,
PK_NAME contains a system generated name with the form SYS_Cn.

The result set is ordered by TABLE_SCHEM, TABLE_NAME, KEY_SEQ.

An identifier is passed I<as is>, i.e. as the user provides or
Oracle returns it.
See L</table_info()> for more detailed information.

=head2 C<foreign_key_info()>

This method (currently) supports the extended behaviour of SQL/CLI, i.e. the
result set contains foreign keys that refer to primary B<and> alternate keys.
The field UNIQUE_OR_PRIMARY distinguishes these keys.

Oracle does not support catalogs, so C<$pk_catalog> and C<$fk_catalog> are
ignored as selection criteria (in the new style interface).
The UK_TABLE_CAT and FK_TABLE_CAT fields of a fetched row are always
NULL (undef).
See L</table_info()> for more detailed information.

If the primary or foreign key constraints were created without an identifier,
UK_NAME or FK_NAME contains a system generated name with the form SYS_Cn.

The UPDATE_RULE field is always 3 ('NO ACTION'), because Oracle (currently)
does not support other actions.

The DELETE_RULE field may contain wrong values. This is a known Bug (#1271663)
in Oracle's data dictionary views. Currently (as of 8.1.7), 'RESTRICT' and
'SET DEFAULT' are not supported, 'CASCADE' is mapped correctly and all other
actions (incl. 'SET NULL') appear as 'NO ACTION'.

The DEFERABILITY field is always NULL, because this columns is
not present in the ALL_CONSTRAINTS view of older Oracle releases.

The result set is ordered by UK_TABLE_SCHEM, UK_TABLE_NAME, FK_TABLE_SCHEM,
FK_TABLE_NAME, ORDINAL_POSITION.

An identifier is passed I<as is>, i.e. as the user provides or
Oracle returns it.
See L</table_info()> for more detailed information.

=head2 C<column_info()>

Oracle does not support catalogs so TABLE_CAT is ignored as
selection criterion.
The TABLE_CAT field of a fetched row is always NULL (undef).
See L</table_info()> for more detailed information.

The CHAR_OCTET_LENGTH field is (currently) always NULL (undef).

Don't rely on the values of the BUFFER_LENGTH field!
Especially the length of FLOATs may be wrong.

Datatype codes for non-standard types are subject to change.

Attention! The DATA_DEFAULT (COLUMN_DEF) column is of type LONG.

The result set is ordered by TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION.

An identifier is passed I<as is>, i.e. as the user provides or
Oracle returns it.
See L</table_info()> for more detailed information.


=head1 Unicode

DBD::Oracle now supports Unicode UTF-8. There are, however, a number
of issues you should be aware of, so please read all this section
carefully.

In this section we'll discuss "Perl and Unicode", then "Oracle and
Unicode", and finally "DBD::Oracle and Unicode".

Information about Unicode in general can be found at:
L<http://www.unicode.org/>. It is well worth reading because there are
many misconceptions about Unicode and you may be holding some of them.

=head2 Perl and Unicode

Perl began implementing Unicode with version 5.6, but the implementation
did not mature until version 5.8 and later. If you plan to use Unicode
you are I<strongly> urged to use Perl 5.8.2 or later and to I<carefully> read
the Perl documentation on Unicode:

   perldoc perluniintro    # in Perl 5.8 or later
   perldoc perlunicode

And then read it again.

Perl's internal Unicode format is UTF-8
which corresponds to the Oracle character set called AL32UTF8.

=head2 Oracle and Unicode

Oracle supports many characters sets, including several different forms
of Unicode.  These include:

  AL16UTF16  =>  valid for NCHAR columns (CSID=2000)
  UTF8       =>  valid for NCHAR columns (CSID=871), deprecated
  AL32UTF8   =>  valid for NCHAR and CHAR columns (CSID=873)

When you create an Oracle database, you must specify the DATABASE 
character set (used for DDL, DML and CHAR datatypes) and the NATIONAL 
character set (used for NCHAR and NCLOB types).
The character sets used in your database can be found using:

  $hash_ref = $dbh->ora_nls_parameters()
  $database_charset = $hash_ref->{NLS_CHARACTERSET};
  $national_charset = $hash_ref->{NLS_NCHAR_CHARACTERSET};

The Oracle 9.2 and later default for the national character set is AL16UTF16.
The default for the database character set is often US7ASCII.
Although many experienced DBAs will consider an 8bit character set like
WE8ISO8859P1 or WE8MSWIN1252.  To use any character set with Oracle
other than US7ASCII, requires that the NLS_LANG environment variable be set.
See the L<"Oracle UTF8 is not UTF-8"> section below.


You are strongly urged to read the Oracle Internationalization documentation
specifically with respect the choices and trade offs for creating
a databases for use with international character sets.

Oracle uses the NLS_LANG environment variable to indicate what
character set is being used on the client.  When fetching data Oracle
will convert from whatever the database character set is to the client
character set specified by NLS_LANG. Similarly, when sending data to
the database Oracle will convert from the character set specified by
NLS_LANG to the database character set.

The NLS_NCHAR environment variable can be used to define a different
character set for 'national' (NCHAR) character types.

Both UTF8 and AL32UTF32 can be used in NLS_LANG and NLS_NCHAR.
For example:

   NLS_LANG=AMERICAN_AMERICA.UTF8
   NLS_LANG=AMERICAN_AMERICA.AL32UTF8
   NLS_NCHAR=UTF8
   NLS_NCHAR=AL32UTF8

Oracle 8 client libraries have a number of bugs related to character
set handling, especially when connected to an Oracle 9+ server.
For this reason a number of DBD::Oracle tests are disabled when
using an Oracle 8 client. If you wish to use Unicode, I recommend
upgrading client and server to Oracle 9 or later.

=head2 Oracle UTF8 is not UTF-8

AL32UTF8 should be used in preference to UTF8 if it works for you,
which it should for Oracle 9.2 or later. If you're using an old
version of Oracle that doesn't support AL32UTF8 then you should
avoid using any Unicode characters that require surrogates, in other
words characters beyond the Unicode BMP (Basic Multilingual Plane).

That's because the character set that Oracle calls "UTF8" doesn't
conform to the UTF-8 standard in its handling of surrogate characters.
Technically the encoding that Oracle calls "UTF8" is known as "CESU-8".
Here are a couple of extracts from L<http://www.unicode.org/reports/tr26/>:

  CESU-8 is useful in 8-bit processing environments where binary
  collation with UTF-16 is required. It is designed and recommended
  for use only within products requiring this UTF-16 binary collation
  equivalence. It is not intended nor recommended for open interchange.

  As a very small percentage of characters in a typical data stream
  are expected to be supplementary characters, there is a strong
  possibility that CESU-8 data may be misinterpreted as UTF-8.
  Therefore, all use of CESU-8 outside closed implementations is
  strongly discouraged, such as the emittance of CESU-8 in output
  files, markup language or other open transmission forms.

Oracle uses this internally because it collates (sorts) in the same order
as UTF16, which is the basis of Oracle's internal collation definitions.

Rather than change UTF8 for clients Oracle chose to define a new character
set called "AL32UTF8" which does conform to the UTF-8 standard.
(The AL32UTF8 character set can't be used on the server because it
would break collation.)

Because of that, for the rest of this document we'll use "AL32UTF8".
If you're using an Oracle version below 9.2 you'll need to use "UTF8"
until you upgrade.

=head2 DBD::Oracle and Unicode

DBD::Oracle Unicode support has been implemented for Oracle versions 9
or greater, and Perl version 5.6 or greater (though we I<strongly>
suggest that you use Perl 5.8.2 or later).

You can check which Oracle version your DBD::Oracle was built with by
importing the C<ORA_OCI> constant from DBD::Oracle.

B<Fetching Data>

Any data returned from Oracle to DBD::Oracle in the AL32UTF8
character set will be marked as UTF-8 to ensure correct handling by Perl.

For Oracle to return data in the AL32UTF8 character set the
NLS_LANG or NLS_NCHAR environment variable I<must> be set as described
in the previous section.

When fetching NCHAR, NVARCHAR, or NCLOB data from Oracle, DBD::Oracle
will set the Perl UTF-8 flag on the returned data if either NLS_NCHAR
is AL32UTF8, or NLS_NCHAR is not set and NLS_LANG is AL32UTF8.

When fetching other character data from Oracle, DBD::Oracle
will set the Perl UTF-8 flag on the returned data if NLS_LANG is AL32UTF8.

B<Sending Data using Placeholders>

Data bound to a placeholder is assumed to be in the default client
character set (specified by NLS_LANG) except for a few special
cases. These are listed here with the highest precedence first:

If the C<ora_csid> attribute is given to bind_param() then that
is passed to Oracle and takes precedence.

If the value is a Perl Unicode string (UTF-8) then DBD::Oracle
ensures that Oracle uses the Unicode character set, regardless of
the NLS_LANG and NLS_NCHAR settings.

If the placeholder is for inserting an NCLOB then the client NLS_NCHAR
character set is used. (That's useful but inconsistent with the other behaviour
so may change. Best to be explicit by using the C<ora_csform>
attribute.)

If the C<ora_csform> attribute is given to bind_param() then that
determines if the value should be assumed to be in the default
(NLS_LANG) or NCHAR (NLS_NCHAR) client character set. 


   use DBD::Oracle qw( SQLCS_IMPLICIT SQLCS_NCHAR );
   ...
   $sth->bind_param(1, $value, { ora_csform => SQLCS_NCHAR }); 

or

   $dbh->{ora_ph_csform} = SQLCS_NCHAR; # default for all future placeholders

B<Sending Data using SQL>

Oracle assumes the SQL statement is in the default client character
set (as specified by NLS_LANG). So Unicode strings containing
non-ASCII characters should not be used unless the default client
character set is AL32UTF8.

=head2 DBD::Oracle and Other Character Sets and Encodings

The only multi-byte Oracle character set supported by DBD::Oracle is
"AL32UTF8" (and "UTF8"). Single-byte character sets should work well.

=head1 SYS.DBMS_SQL datatypes

DBD::Oracle has built-in support for B<SYS.DBMS_SQL.VARCHAR2_TABLE>
and B<SYS.DBMS_SQL.NUMBER_TABLE> data types. The simple example is here:

    my $statement='
    DECLARE
    	tbl	SYS.DBMS_SQL.VARCHAR2_TABLE;
    BEGIN
    	tbl := :mytable;
    	:cc := tbl.count();
    	tbl(1) := \'def\';
    	tbl(2) := \'ijk\';
    	:mytable := tbl;
    END;
    ';

    my $sth=$dbh->prepare( $statement );

    my @arr=( "abc" );

    $sth->bind_param_inout(":mytable", \@arr, 10, {
            ora_type => ORA_VARCHAR2_TABLE,
            ora_maxarray_numentries => 100
    } ) );
    $sth->bind_param_inout(":cc", \$cc, 100 ) );
    $sth->execute();
    print	"Result: cc=",$cc,"\n",
    	"\tarr=",Data::Dumper::Dumper(\@arr),"\n";

=over

=item OCI_VARCHAR2_TABLE

SYS.DBMS_SQL.VARCHAR2_TABLE object is always bound to array reference.
( in bind_param() and bind_param_inout() ). When you bind array, you need
to specify full buffer size for OUT data. So, there are two parameters:
I<max_len> (specified as 3rd argument of bind_param_inout() ),
and I<ora_maxarray_numentries>. They define maximum array entry length and
maximum rows, that can be passed to Oracle and back to you. In this
example we send array with 1 element with length=3, but allocate space for 100
Oracle array entries with maximum length 10 of each. So, you can get no more
than 100 array entries with length <= 10. 

If you set I<max_len> to zero, maximum array entry length is calculated
as maximum length of entry of array bound. If 0 < I<max_len> < length( $some_element ),
truncation occur.

If you set I<ora_maxarray_numentries> to zero, current (at bind time) bound
array length is used as maximum. If 0 < I<ora_maxarray_numentries> < scalar(@array),
not all array entries are bound.

=item OCI_NUMBER_TABLE

SYS.DBMS_SQL.NUMBER_TABLE object handling is much alike ORA_VARCHAR2_TABLE.
The main difference is internal data representation. Currently 2 types of
bind is allowed : as C-integer, or as C-double type. To select one of them,
you may specify additional bind parameter I<ora_internal_type> as either
B<SQLT_INT> or B<SQLT_FLT> for C-integer and C-double types.
Integer size is architecture-specific and is usually 32 or 64 bit.
Double is standard IEEE 754 type.

I<ora_internal_type> defaults to double (SQLT_FLT).

I<max_len> is ignored for OCI_NUMBER_TABLE.

Currently, you cannot bind full native Oracle NUMBER(38). If you really need,
send request to dbi-dev list.

The usage example is here:

    $statement='
    DECLARE
            tbl     SYS.DBMS_SQL.NUMBER_TABLE;
    BEGIN
            tbl := :mytable;
            :cc := tbl(2);
            tbl(4) := -1;
            tbl(5) := -2;
            :mytable := tbl;
    END;
    ';
    
    $sth=$dbh->prepare( $statement );
    
    if( ! defined($sth) ){
            die "Prepare error: ",$dbh->errstr,"\n";
    }
    
    @arr=( 1,"2E0","3.5" );
    
    # note, that ora_internal_type defaults to SQLT_FLT for ORA_NUMBER_TABLE .
    if( not $sth->bind_param_inout(":mytable", \@arr, 10, {
                    ora_type => ORA_NUMBER_TABLE,
                    ora_maxarray_numentries => (scalar(@arr)+2),
                    ora_internal_type => SQLT_FLT
              } ) ){
            die "bind :mytable error: ",$dbh->errstr,"\n";
    }
    $cc=undef;
    if( not $sth->bind_param_inout(":cc", \$cc, 100 ) ){
            die "bind :cc error: ",$dbh->errstr,"\n";
    }
    
    if( not $sth->execute() ){
            die "Execute failed: ",$dbh->errstr,"\n";
    }
    print   "Result: cc=",$cc,"\n",
            "\tarr=",Data::Dumper::Dumper(\@arr),"\n";

The result is like:

    Result: cc=2
            arr=$VAR1 = [
              '1',
              '2',
              '3.5',
              '-1',
              '-2'
            ];

If you change bind type to B<SQLT_INT>, like:

    ora_internal_type => SQLT_INT

you get:

    Result: cc=2
            arr=$VAR1 = [
              1,
              2,
              3,
              -1,
              -2
            ];

=back

=head1 Other Data Types

DBD::Oracle does not I<explicitly> support most Oracle data types.
It simply asks Oracle to return them as strings and Oracle does so.
Mostly.  Similarly when binding placeholder values DBD::Oracle binds
them as strings and Oracle converts them to the appropriate type,
such as DATE, when used.

Some of these automatic conversions to and from strings use NLS
settings to control the formatting for output and the parsing for
input. The most common example is the DATE type. The default NLS
format for DATE might be DD-MON-YYYY and so when a DATE type is
fetched that's how Oracle will format the date. NLS settings also
control the default parsing of strings into DATE values. An error
will be generated if the contents of the string don't match the
NLS format. If you're dealing in dates which don't match the default
NLS format then you can either change the default NLS format or, more
commonly, use TO_CHAR(field, "format") and TO_DATE(?, "format")
to explicitly specify formats for converting to and from strings.

A slightly more subtle problem can occur with NUMBER types. The
default NLS settings might format numbers with a fullstop ("C<.>")
to separate thousands and a comma ("C<,>") as the decimal point.
Perl will generate warnings and use incorrect values when numbers,
returned and formatted as strings in this way by Oracle, are used
in a numeric context.  You could explicitly convert each numeric
value using the TO_CHAR(...) function but that gets tedious very
quickly. The best fix is to change the NLS settings. That can be
done for an individual connection by doing:

  $dbh->do("ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,'");

There are some types, like BOOLEAN, that Oracle does not automatically
convert to or from strings (pity).  These need to be converted
explicitly using SQL or PL/SQL functions.

Examples:

   # DATE values
   my $sth0 = $dbh->prepare( <<SQL_END );
   SELECT username, TO_CHAR( created, ? )
      FROM all_users
      WHERE created >= TO_DATE( ?, ? )
   SQL_END
   $sth0->execute( 'YYYY-MM-DD HH24:MI:SS', "2003", 'YYYY' );

   # BOOLEAN values
   my $sth2 = $dbh->prepare( <<PLSQL_END );
   DECLARE
      b0 BOOLEAN;
      b1 BOOLEAN;
      o0 VARCHAR2(32);
      o1 VARCHAR2(32);

      FUNCTION to_bool( i VARCHAR2 ) RETURN BOOLEAN IS
      BEGIN
         IF    i IS NULL          THEN RETURN NULL;
         ELSIF i = 'F' OR i = '0' THEN RETURN FALSE;
         ELSE                          RETURN TRUE;
         END IF;
      END;
      FUNCTION from_bool( i BOOLEAN ) RETURN NUMBER IS
      BEGIN
         IF    i IS NULL THEN RETURN NULL;
         ELSIF i         THEN RETURN 1;
         ELSE                 RETURN 0;
         END IF;
      END;
   BEGIN
      -- Converting values to BOOLEAN
      b0 := to_bool( :i0 );
      b1 := to_bool( :i1 );

      -- Converting values from BOOLEAN
      :o0 := from_bool( b0 );
      :o1 := from_bool( b1 );
   END;
   PLSQL_END
   my ( $i0, $i1, $o0, $o1 ) = ( "", "Something else" );
   $sth2->bind_param( ":i0", $i0 );
   $sth2->bind_param( ":i1", $i1 );
   $sth2->bind_param_inout( ":o0", \$o0, 32 );
   $sth2->bind_param_inout( ":o1", \$o1, 32 );
   $sth2->execute();
   foreach ( $i0, $b0, $o0, $i1, $b1, $o1 ) {
      $_ = "(undef)" if ! defined $_;
   }
   print "$i0 to $o0, $i1 to $o1\n";
   # Result is : "'' to '(undef)', 'Something else' to '1'"


=head1 PL/SQL Examples

Most of these PL/SQL examples come from: Eric Bartley <bartley@cc.purdue.edu>.

   /*
    * PL/SQL to create package with stored procedures invoked by
    * Perl examples.  Execute using sqlplus.
    *
    * Use of "... OR REPLACE" prevents failure in the event that the
    * package already exists.
    */

    CREATE OR REPLACE PACKAGE plsql_example
    IS
      PROCEDURE proc_np;

      PROCEDURE proc_in (
          err_code IN NUMBER
      );

      PROCEDURE proc_in_inout (
          test_num IN NUMBER,
          is_odd IN OUT NUMBER
      );

      FUNCTION func_np
        RETURN VARCHAR2;

    END plsql_example;
  /

    CREATE OR REPLACE PACKAGE BODY plsql_example
    IS
      PROCEDURE proc_np
      IS
        whoami VARCHAR2(20) := NULL;
      BEGIN
        SELECT USER INTO whoami FROM DUAL;
      END;

      PROCEDURE proc_in (
        err_code IN NUMBER
      )
      IS
      BEGIN
        RAISE_APPLICATION_ERROR(err_code, 'This is a test.');
      END;

      PROCEDURE proc_in_inout (
        test_num IN NUMBER,
        is_odd IN OUT NUMBER
      )
      IS
      BEGIN
        is_odd := MOD(test_num, 2);
      END;

      FUNCTION func_np
        RETURN VARCHAR2
      IS
        ret_val VARCHAR2(20);
      BEGIN
        SELECT USER INTO ret_val FROM DUAL;
        RETURN ret_val;
      END;

    END plsql_example;
  /
  /* End PL/SQL for example package creation. */

  use DBI;

  my($db, $csr, $ret_val);

  $db = DBI->connect('dbi:Oracle:database','user','password')
        or die "Unable to connect: $DBI::errstr";

  # So we don't have to check every DBI call we set RaiseError.
  # See the DBI docs now if you're not familiar with RaiseError.
  $db->{RaiseError} = 1;

  # Example 1	Eric Bartley <bartley@cc.purdue.edu>
  #
  # Calling a PLSQL procedure that takes no parameters. This shows you the
  # basic's of what you need to execute a PLSQL procedure. Just wrap your
  # procedure call in a BEGIN END; block just like you'd do in SQL*Plus.
  #
  # p.s. If you've used SQL*Plus's exec command all it does is wrap the
  #      command in a BEGIN END; block for you.

  $csr = $db->prepare(q{
    BEGIN
      PLSQL_EXAMPLE.PROC_NP;
    END;
  });
  $csr->execute;


  # Example 2	Eric Bartley <bartley@cc.purdue.edu>
  #
  # Now we call a procedure that has 1 IN parameter. Here we use bind_param
  # to bind out parameter to the prepared statement just like you might
  # do for an INSERT, UPDATE, DELETE, or SELECT statement.
  #
  # I could have used positional placeholders (e.g. :1, :2, etc.) or
  # ODBC style placeholders (e.g. ?), but I prefer Oracle's named
  # placeholders (but few DBI drivers support them so they're not portable).

  my $err_code = -20001;

  $csr = $db->prepare(q{
  	BEGIN
  	    PLSQL_EXAMPLE.PROC_IN(:err_code);
  	END;
  });

  $csr->bind_param(":err_code", $err_code);

  # PROC_IN will RAISE_APPLICATION_ERROR which will cause the execute to 'fail'.
  # Because we set RaiseError, the DBI will croak (die) so we catch that with eval.
  eval {
    $csr->execute;
  };
  print 'After proc_in: $@=',"'$@', errstr=$DBI::errstr, ret_val=$ret_val\n";


  # Example 3	Eric Bartley <bartley@cc.purdue.edu>
  #
  # Building on the last example, I've added 1 IN OUT parameter. We still
  # use a placeholders in the call to prepare, the difference is that
  # we now call bind_param_inout to bind the value to the place holder.
  #
  # Note that the third parameter to bind_param_inout is the maximum size
  # of the variable. You normally make this slightly larger than necessary.
  # But note that the Perl variable will have that much memory assigned to
  # it even if the actual value returned is shorter.

  my $test_num = 5;
  my $is_odd;

  $csr = $db->prepare(q{
  	BEGIN
  	    PLSQL_EXAMPLE.PROC_IN_INOUT(:test_num, :is_odd);
  	END;
  });

  # The value of $test_num is _copied_ here
  $csr->bind_param(":test_num", $test_num);

  $csr->bind_param_inout(":is_odd", \$is_odd, 1);

  # The execute will automagically update the value of $is_odd
  $csr->execute;

  print "$test_num is ", ($is_odd) ? "odd - ok" : "even - error!", "\n";


  # Example 4	Eric Bartley <bartley@cc.purdue.edu>
  #
  # What about the return value of a PLSQL function? Well treat it the same
  # as you would a call to a function from SQL*Plus. We add a placeholder
  # for the return value and bind it with a call to bind_param_inout so
  # we can access it's value after execute.

  my $whoami = "";

  $csr = $db->prepare(q{
  	BEGIN
  	    :whoami := PLSQL_EXAMPLE.FUNC_NP;
  	END;
  });

  $csr->bind_param_inout(":whoami", \$whoami, 20);
  $csr->execute;
  print "Your database user name is $whoami\n";

  $db->disconnect;

You can find more examples in the t/plsql.t file in the DBD::Oracle
source directory.

Oracle 9.2 appears to have a bug where a variable bound
with bind_param_inout() that isn't assigned to by the executed
PL/SQL block may contain garbage.
See L<http://www.mail-archive.com/dbi-users@perl.org/msg18835.html>

=head2 Avoid Using "SQL Call"

Avoid using the "SQL Call" statement with DBD:Oracle as you might find that
DBD::Oracle will not raise an exception in some case.  Specifically if you use
"SQL Call" to run a procedure all "No data found" exceptions will be quietly 
ignored and returned as null. According to Oracle support this is part of the same
mechanism where;

  select (select * from dual where 0=1) from dual
  
returns a null value rather than an exception.

=head1 Private database handle functions

These functions are called through the method func()
which is described in the DBI documentation.

=head2 plsql_errstr

This function returns a string which describes the errors
from the most recent PL/SQL function, procedure, package,
or package body compile in a format similar to the output
of the SQL*Plus command 'show errors'.

The function returns undef if the error string could not
be retrieved due to a database error.
Look in $dbh->errstr for the cause of the failure.

If there are no compile errors, an empty string is returned.

Example:

    # Show the errors if CREATE PROCEDURE fails
    $dbh->{RaiseError} = 0;
    if ( $dbh->do( q{
        CREATE OR REPLACE PROCEDURE perl_dbd_oracle_test as
        BEGIN
            PROCEDURE filltab( stuff OUT TAB ); asdf
        END; } ) ) {} # Statement succeeded
    }
    elsif ( 6550 != $dbh->err ) { die $dbh->errstr; } # Utter failure
    else {
        my $msg = $dbh->func( 'plsql_errstr' );
        die $dbh->errstr if ! defined $msg;
        die $msg if $msg;
    }

=head2 dbms_output_enable / dbms_output_put / dbms_output_get

These functions use the PL/SQL DBMS_OUTPUT package to store and
retrieve text using the DBMS_OUTPUT buffer.  Text stored in this buffer
by dbms_output_put or any PL/SQL block can be retrieved by
dbms_output_get or any PL/SQL block connected to the same database
session.

Stored text is not available until after dbms_output_put or the PL/SQL
block that saved it completes its execution.  This means you B<CAN NOT>
use these functions to monitor long running PL/SQL procedures.

Example 1:

  # Enable DBMS_OUTPUT and set the buffer size
  $dbh->{RaiseError} = 1;
  $dbh->func( 1000000, 'dbms_output_enable' );

  # Put text in the buffer . . .
  $dbh->func( @text, 'dbms_output_put' );

  # . . . and retrieve it later
  @text = $dbh->func( 'dbms_output_get' );

Example 2:

  $dbh->{RaiseError} = 1;
  $sth = $dbh->prepare(q{
    DECLARE tmp VARCHAR2(50);
    BEGIN
      SELECT SYSDATE INTO tmp FROM DUAL;
      dbms_output.put_line('The date is '||tmp);
    END;
  });
  $sth->execute;

  # retrieve the string
  $date_string = $dbh->func( 'dbms_output_get' );


=over 4

=item dbms_output_enable ( [ buffer_size ] )

This function calls DBMS_OUTPUT.ENABLE to enable calls to package
DBMS_OUTPUT procedures GET, GET_LINE, PUT, and PUT_LINE.  Calls to
these procedures are ignored unless DBMS_OUTPUT.ENABLE is called
first.

The buffer_size is the maximum amount of text that can be saved in the
buffer and must be between 2000 and 1,000,000.  If buffer_size is not
given, the default is 20,000 bytes.

=item dbms_output_put ( [ @lines ] )

This function calls DBMS_OUTPUT.PUT_LINE to add lines to the buffer.

If all lines were saved successfully the function returns 1.  Depending
on the context, an empty list or undef is returned for failure.

If any line causes buffer_size to be exceeded, a buffer overflow error
is raised and the function call fails.  Some of the text might be in
the buffer.

=item dbms_output_get

This function calls DBMS_OUTPUT.GET_LINE to retrieve lines of text from
the buffer.

In an array context, all complete lines are removed from the buffer and
returned as a list.  If there are no complete lines, an empty list is
returned.

In a scalar context, the first complete line is removed from the buffer
and returned.  If there are no complete lines, undef is returned.

Any text in the buffer after a call to DBMS_OUTPUT.GET_LINE or
DBMS_OUTPUT.GET is discarded by the next call to DBMS_OUTPUT.PUT_LINE,
DBMS_OUTPUT.PUT, or DBMS_OUTPUT.NEW_LINE.

=item reauthenticate ( $username, $password )

Starts a new session against the current database using the credentials
supplied.

=item ora_nls_parameters ( [ $refresh ] )

Returns a hash reference containing the current NLS parameters, as given
by the v$nls_parameters view. The values fetched are cached between calls.
To cause the latest values to be fetched, pass a true value to the function.

=item ora_can_unicode ( [ $refresh ] )

Returns a number indicating whether either of the database character sets
is a Unicode encoding. Calls ora_nls_parameters() and passes the optional
$refresh parameter to it.

0 = Neither character set is a Unicode encoding.

1 = National character set is a Unicode encoding.

2 = Database character set is a Unicode encoding.

3 = Both character sets are Unicode encodings.

=back

=head1 Prepare postponed till execute

The DBD::Oracle module can avoid an explicit 'describe' operation
prior to the execution of the statement unless the application requests
information about the results (such as $sth->{NAME}). This reduces
communication with the server and increases performance (reducing the
number of PARSE_CALLS inside the server).

However, it also means that SQL errors are not detected until
C<execute()> (or $sth->{NAME} etc) is called instead of when
C<prepare()> is called. Note that if the describe is triggered by the
use of $sth->{NAME} or a similar attribute and the describe fails then
I<an exception is thrown> even if C<RaiseError> is false!

Set L</ora_check_sql> to 0 in prepare() to enable this behaviour.


=head1 Scrollable Cursors

Oracle supports the concept of a 'Scrollable Cursor' which is defined as a 'Result Set' where
the rows can be fetched either sequentially or non-sequentially. One can fetch rows forward, 
backwards, from any given position or the n-th row from the current position in the result set.

Rows are numbered sequentially starting at one and client-side caching of the partial or entire result set
can improve performance by limiting round trips to the server.

Oracle does not support DML type operations with scrollable cursors so you are limited
to simple 'Select' operations only. As well you can not use this functionality with remote 
mapped queries or if the LONG datatype is part of the select list. 

However, LOBSs, CLOBSs, and BLOBs do work as do all the regular bind, and fetch methods.  

Only use scrollable cursors if you really have a good reason to. They do use up considerable 
more server and client resources and have poorer response times than non-scrolling cursors.


=head2 Enabling Scrollable Cursors

To enable this functionality you must first import the 'Fetch Orientation' and the 'Execution Mode' constants by using;

   use DBD::Oracle qw(:ora_fetch_orient :ora_exe_modes);
  
Next you will have to tell DBD::Oracle that you will be using scrolling by setting the ora_exe_mode attribute on the
statement handle to 'OCI_STMT_SCROLLABLE_READONLY' with the prepare method;

  $sth=$dbh->prepare($SQL,{ora_exe_mode=>OCI_STMT_SCROLLABLE_READONLY});

When the statement is executed you will then be able to use 'ora_fetch_scroll' method to get a row
or you can still use any of the other fetch methods but with a poorer response time than if you used a 
non-scrolling cursor. As well scrollable cursors are compatible with any applicable bind methods.


=head2 Scrollable Cursor Methods

The following driver-specific methods are used with scrollable cursors.

=item ora_scroll_position

  $position =  $sth->ora_scroll_position();
      
This method returns the current position (row number) attribute of the result set. Prior to the first fetch this value is 0. This is the only time
this value will be 0 after the first fetch the value will be set, so you can use this value to test if any rows have been fetched.
The minimum value will always be 1 after the first fetch. The maximum value will always be the total number of rows in the record set. 

=item ora_fetch_scroll

  @ary =  $sth->ora_fetch_scroll($fetch_orient,$fetch_offset);

Works the same as fetchrow_array method however, one passes in a 'Fetch Orientation' constant and a fetch_offset 
value which will then determine the row that will be fetched. It returns the row as a list containing the field values. 
Null fields are returned as undef values in the list.

The valid orientation constant and fetch offest values combination are detailed below 

  OCI_FETCH_CURRENT, fetches the current row, the fetch offset value is ignored.
  OCI_FETCH_NEXT, fetches the next row from the current position, the fetch offset value is ignored.
  OCI_FETCH_FIRST, fetches the first row, the fetch offset value is ignored.
  OCI_FETCH_LAST, fetches the last row, the fetch offset value is ignored.
  OCI_FETCH_PRIOR, fetches the previous row from the current position, the fetch offset value is ignored.
  OCI_FETCH_ABSOLUTE, fetches the row that is specified by the fetch offset value.
  OCI_FETCH_RELATIVE, fetches the row relative from the current position as specified by the fetch offset value.

  OCI_FETCH_ABSOLUTE, and a fetch offset value of 1 is equivalent to a OCI_FETCH_FIRST.
  OCI_FETCH_ABSOLUTE, and a fetch offset value of 0 is equivalent to a OCI_FETCH_CURRENT.

  OCI_FETCH_RELATIVE, and a fetch offset value of 0 is equivalent to a OCI_FETCH_CURRENT.
  OCI_FETCH_RELATIVE, and a fetch offset value of 1 is equivalent to a OCI_FETCH_NEXT.
  OCI_FETCH_RELATIVE, and a fetch offset value of -1 is equivalent to a OCI_FETCH_PRIOR.

The effect that a ora_fetch_scroll method call has on the current_positon attribute is detailed below.

  OCI_FETCH_CURRENT, has no effect on the current_positon attribute.
  OCI_FETCH_NEXT, increments current_positon attribute by 1
  OCI_FETCH_NEXT, when at the last row in the record set does not change current_positon attribute, it is equivalent to a OCI_FETCH_CURRENT 
  OCI_FETCH_FIRST, sets the current_positon attribute to 1.
  OCI_FETCH_LAST, sets the current_positon attribute to the total number of rows in the record set.
  OCI_FETCH_PRIOR, decrements current_positon attribute by 1.
  OCI_FETCH_PRIOR, when at the first row in the record set does not change current_positon attribute, it is equivalent to a OCI_FETCH_CURRENT.
  OCI_FETCH_ABSOLUTE, sets the current_positon attribute to the fetch offest value.
  OCI_FETCH_ABSOLUTE, and a fetch offset value that is less than 1 does not change current_positon attribute, it is equivalent to a OCI_FETCH_CURRENT.
  OCI_FETCH_ABSOLUTE, and a fetch offset value that is greater than the number of records in the record set, does not change current_positon attribute, it is equivalent to a OCI_FETCH_CURRENT.
  OCI_FETCH_RELATIVE, sets the current_positon attribute to (current_positon attribute + fetch offset value).
  OCI_FETCH_RELATIVE, and a fetch offset value that makes the current position less than 1, does not change fetch offest value so it is equivalent to a OCI_FETCH_CURRENT.
  OCI_FETCH_RELATIVE, and a fetch offset value that makes it greater than the number of records in the record set, does not change fetch offest value so it is equivalent to a OCI_FETCH_CURRENT.

The effects of the differing orientation constants on the first fetch (current_postion attribute at 0) are as follows.

  OCI_FETCH_CURRENT, dose not fetch a row or change the current_positon attribute.
  OCI_FETCH_FIRST, fetches row 1 and sets the current_positon attribute to 1.
  OCI_FETCH_LAST, fetches the last row in the record set and sets the current_positon attribute to the total number of rows in the record set.
  OCI_FETCH_NEXT, equivalent to a OCI_FETCH_FIRST.
  OCI_FETCH_PRIOR, equivalent to a OCI_FETCH_CURRENT.
  OCI_FETCH_ABSOLUTE, and a fetch offset value that is less than 1 is equivalent to a OCI_FETCH_CURRENT.
  OCI_FETCH_ABSOLUTE, and a fetch offset value that is greater than the number of records in the record set is equivalent to a OCI_FETCH_CURRENT.
  OCI_FETCH_RELATIVE, and a fetch offset value that is less than 1 is equivalent to a OCI_FETCH_CURRENT.
  OCI_FETCH_RELATIVE, and a fetch offset value that makes it greater than the number of records in the record set, is equivalent to a OCI_FETCH_CURRENT.

=head2 Scrollable Cursor Usage

Given a simple code like this:

  use DBI;
  use DBD::Oracle qw(:ora_types :ora_fetch_orient :ora_exe_modes);
  my $dbh = DBI->connect($dsn, $dbuser, '');
  my $SQL = "select id,
                     first_name,
                     last_name
                from employee";
  my $sth=$dbh->prepare($SQL,{ora_exe_mode=>OCI_STMT_SCROLLABLE_READONLY});
  $sth->execute();
  my $value;

and one assumes that the number of rows returned from the query is 20, the code snippets below will illustrate the use of ora_fetch_scroll
method;

=item Fetching the Last Row

  $value =  $sth->ora_fetch_scroll(OCI_FETCH_LAST,0);
  print "id=".$value->[0].", First Name=".$value->[1].", Last Name=".$value->[2]."\n";
  print "current scroll position=".$sth->ora_scroll_position()."\n";

The current_positon attribute to will be 20 after this snippet.  This is also a way to get the number of rows in the record set, however,
if the record set is large this could take some time. 

=item Fetching the Current Row

  $value =  $sth->ora_fetch_scroll(OCI_FETCH_CURRENT,0);
  print "id=".$value->[0].", First Name=".$value->[1].", Last Name=".$value->[2]."\n";
  print "current scroll position=".$sth->ora_scroll_position()."\n";

The current_positon attribute will still be 20 after this snippet.

=item Fetching the First Row

  $value =  $sth->ora_fetch_scroll(OCI_FETCH_FIRST,0);
  print "id=".$value->[0].", First Name=".$value->[1].", Last Name=".$value->[2]."\n";
  print "current scroll position=".$sth->ora_scroll_position()."\n";

The current_positon attribute will be 1 after this snippet.

=item Fetching the Next Row

  for(my $i=0;$i<=3;$i++){
     $value =  $sth->ora_fetch_scroll(OCI_FETCH_NEXT,0);
     print "id=".$value->[0].", First Name=".$value->[1].", Last Name=".$value->[2]."\n";
  }
  print "current scroll position=".$sth->ora_scroll_position()."\n";

The current_positon attribute will be 5 after this snippet.

=item Fetching the Prior Row

  for(my $i=0;$i<=3;$i++){
     $value =  $sth->ora_fetch_scroll(OCI_FETCH_PRIOR,0);
     print "id=".$value->[0].", First Name=".$value->[1].", Last Name=".$value->[2]."\n";
  }
  print "current scroll position=".$sth->ora_scroll_position()."\n";

The current_positon attribute will be 1 after this snippet.

=item Fetching the 10th Row

  $value =  $sth->ora_fetch_scroll(OCI_FETCH_ABSOLUTE,10);
  print "id=".$value->[0].", First Name=".$value->[1].", Last Name=".$value->[2]."\n";
  print "current scroll position=".$sth->ora_scroll_position()."\n";

The current_positon attribute will be 10 after this snippet.

=item Fetching the 10th to 14th Row

  for(my $i=10;$i<15;$i++){
      $value =  $sth->ora_fetch_scroll(OCI_FETCH_ABSOLUTE,$i);
      print "id=".$value->[0].", First Name=".$value->[1].", Last Name=".$value->[2]."\n";
  }
  print "current scroll position=".$sth->ora_scroll_position()."\n";

The current_positon attribute will be 14 after this snippet.

=item Fetching the 14th to 10th Row

  for(my $i=14;$i>9;$i--){
    $value =  $sth->ora_fetch_scroll(OCI_FETCH_ABSOLUTE,$i);
    print "id=".$value->[0].", First Name=".$value->[1].", Last Name=".$value->[2]."\n";
  }
  print "current scroll position=".$sth->ora_scroll_position()."\n";

The current_positon attribute will be 10 after this snippet.

=item Fetching the 5th Row From the Present Position.

  $value =  $sth->ora_fetch_scroll(OCI_FETCH_RELATIVE,5);
  print "id=".$value->[0].", First Name=".$value->[1].", Last Name=".$value->[2]."\n";
  print "current scroll position=".$sth->ora_scroll_position()."\n";

The current_positon attribute will be 15 after this snippet.

=item Fetching the 9th Row Prior From the Present Position

  $value =  $sth->ora_fetch_scroll(OCI_FETCH_RELATIVE,-9);
  print "id=".$value->[0].", First Name=".$value->[1].", Last Name=".$value->[2]."\n";
  print "current scroll position=".$sth->ora_scroll_position()."\n";

The current_positon attribute will be 6 after this snippet.   

=item Use Finish

  $sth->finish();

When using scrollable cursors it is required that you use the $sth->finish() method when you are done with the cursor as this type of
cursor has to be explicitly cancelled on the server. If you do not do this you may cause resource problems on your database.  

=head2 Prefetching Rows

One can override the DBD::Oracle's default pre-fetch values by using the DBI database handle attribute C<RowCacheSize> and or the 
Prepare Attribute 'ora_prefetch_memory'. Tweaking these values may yield improved performance. 

   $dbh->{RowCacheSize} = 10;
   $sth=$dbh->prepare($SQL,{ora_exe_mode=>OCI_STMT_SCROLLABLE_READONLY,ora_prefetch_memory=>10000});
   
In the above example 10 rows will be prefetched up to a maximum of 10000 bytes of data.  The Oracle´┐Ż Call Interface Programmer's Guide,
suggests a good row cache value for a scrollable cursor is about 20% of expected size of the record set. 

The prefetch settings tell the DBD::Oracle to grab x rows (or x-bytes) when it needs to get new rows. This happens on the first 
fetch that sets the current_positon to any value other than 0. In the above example if we do a OCI_FETCH_FIRST the first 10 rows are
loaded into the buffer and DBD::Oracle will not have to go back to the server for more rows. When record 11 is fetched DBD::Oracle
fetches and returns this row and the next 9 rows are loaded into the buffer. In this case if you fetch backwards from 10 to 1 
no server round trips are made.

With large record sets it is best not to attempt to go to the last record as this may take some time, A large buffer size might even slow down
the fetch. If you must get the number of rows in a large record set you might try using an few large OCI_FETCH_ABSOLUTEs and then an OCI_FETCH_LAST,
this might save some time. So if you had a record set of 10000 rows and you set the buffer to 5000 and did a OCI_FETCH_LAST one would fetch the first 5000 rows into the buffer then the next 5000 rows.  
If one requires only the first few rows there is no need to set a large prefetch value.  

If the ora_prefetch_memory less than 1 or not present then memory size is not included in computing the 
number of rows to prefetch otherwise the number of rows will be limited to memory size. Likewise if the RowCacheSize is less than 1 it
is not included in the computing of the prefetch rows.  

=head1 Data Interface for Persistent LOBs

Oracle 10.2 and later extended the OCI API to work directly with LOB datatypes. In other words you can treat all LOB type data as if it was
a LONG, LONG RAW, or VARCHAR2. So you can perform INSERT, UPDATE, fetch, bind, and define operations on LOBs using the same techniques 
you would use on other datatypes that store character or binary data. There are fewer round trips to the server as no 'LOB Locators' are
used, normally one can get an entire LOB is a single round trip. The data interface is suppose to supports LOBs of any size less than 2GB,
however it seems there is an practical limit of about 15mb.

=head2 Simple Usage

No special methods are needed to use this interface. To do a select statement all that is required import the 'ora_types' constants with;
  
  use DBD::Oracle qw(:ora_types);
  
and ensure the set statement handle's prepare method 'ora_pers_lob' attribute is set to '1' and the database 
handle's 'LongReadLen' attribute is set to a value that will exceed the expected size of the LOB. If the size of the lob exceeds this then DBD::Oracle
will return a 'ORA-24345: A Truncation' error.  To stop this set the handle's 'LongTruncOk' attribute to '1'.

For example give this table;

  CREATE TABLE test_lob (id NUMBER,
               clob1 CLOB, 
               clob2 CLOB, 
               blob1 BLOB, 
               blob2 BLOB)

this code;

  $dbh->{LongReadLen} = 2*1024*1024; #2 meg
  $SQL='select p_id,lob_1,lob_2,blob_2 from test_lobs';
  $sth=$dbh->prepare($SQL,{ora_pers_lob=>1});
  $sth->execute();
  while (my ( $p_id,$log,$log2,$log3,$log4 )=$sth->fetchrow()){
    print "p_id=".$p_id."\n";
    print "clob1=".$clob1."\n";
    print "clob2=".$clob2."\n";
    print "blob1=".$blob2."\n";
    print "blob2=".$blob2."\n";
  }

Will select out all of the LOBs in the table as long as they are all under 2MB in length.  Longer lobs will throw a error. Adding this line;

  $dbh->{LongTruncOk}=0;
  
before the execute will return all the lobs but they will only be a maximum of 2MB in size.

=head2 Binding for Updates and Inserts

To bind for updates and inserts all that is required to use this interface is to set the statement handle's prepare method 
'ora_type' attribute to 'SQLT_CHR' in the case of CLOBs and NCLOBs or 'SQLT_BIN' in the case of BLOBs as in this example for an insert;

  my $in_clob = "<document>\n";
  $in_clob .= "  <value>$_</value>\n" for 1 .. 10_000;
  $in_clob .= "</document>\n";
  my $in_blob ="0101" for 1 .. 10_000;

  $SQL='insert into test_lob3@tpgtest (id,clob1,clob2, blob1,blob2) values(?,?,?,?,?)';
  $sth=$dbh->prepare($SQL );
  $sth->bind_param(1,3);
  $sth->bind_param(2,$in_clob,{ora_type=>SQLT_CHR});
  $sth->bind_param(3,$in_clob,{ora_type=>SQLT_CHR});
  $sth->bind_param(4,$in_blob,{ora_type=>SQLT_BIN});
  $sth->bind_param(5,$in_blob,{ora_type=>SQLT_BIN});
  $sth->execute();
  
So far the only limit reached with this form of insert is the BLOBs must be under 2GB in size.

=head2 Support for Remote LOBs;

The data interface for Persistent LOBs also supports remote LOBs (access over a dblink). Given a database called 'lob_test' that has a 'LINK' defined like this;

  CREATE DATABASE LINK link_test CONNECT TO test_lobs IDENTIFIED BY tester USING 'lob_test';
  
to a remote database called 'test_lobs', the following code will work;

  $dbh = DBI->connect('dbi:Oracle:','test@lob_test','test');
  $dbh->{LongReadLen} = 2*1024*1024; #2 meg
  $sth=$dbh->prepare($SQL,{ora_pers_lob=>1});
  $SQL='select p_id,lob_1,lob_2,blob_2 from test_lobs@link_test';
  $sth=$dbh->prepare($SQL,{ora_pers_lob=>1,ora_check_sql=>0});
  $sth->execute();
  while (my ( $p_id,$log,$log2,$log3,$log4 )=$sth->fetchrow()){
     print "p_id=".$p_id."\n";
     print "clob1=".$clob1."\n";
     print "clob2=".$clob2."\n";
     print "blob1=".$blob2."\n";
     print "blob2=".$blob2."\n";
  }
  
Below are the limitations of Remote LOBs;

=item Queries involving more than one database are not supported;

so the following returns an error:

  SELECT t1.lobcol, 
  	 a2.lobcol 
    FROM t1, 
         t2.lobcol@dbs2 a2 W
   WHERE LENGTH(t1.lobcol) = LENGTH(a2.lobcol);
  
as does:

     SELECT t1.lobcol 
       FROM t1@dbs1
  UNION ALL
     SELECT t2.lobcol 
       FROM t2@dbs2;

=item DDL commands are not supported;

so the following returns an error:

  CREATE VIEW v AS SELECT lob_col FROM tab@dbs;  

=item Only binds and defines for data going into remote persistent LOBs are supported. 

so that parameter passing in PL/SQL where CHAR data is bound or defined for remote LOBs is not allowed . 

These statements all produce errors:

  SELECT foo() FROM table1@dbs2;
  
  SELECT foo()@dbs INTO char_val FROM DUAL;
  
  SELECT XMLType().getclobval FROM table1@dbs2;

=item If the remote object is a view such as

  CREATE VIEW v AS SELECT foo() FROM ...

the following would not work:

  SELECT * FROM v@dbs2;

=item Limited PL/SQL parameter passing

PL/SQL parameter passing is not allowed where the actual argument is a LOB type
and the remote argument is one of VARCHAR2, NVARCHAR2, CHAR, NCHAR, or RAW.

=item RETURNING INTO does not support implicit conversions between CHAR and CLOB.

so the following returns an error:

  SELECT t1.lobcol as test, a2.lobcol FROM t1, t2.lobcol@dbs2 a2 RETURNING test

=back

=head2 Caveats

So far this is relatively new technology and this is a first attempt to incorporate it into DBD::ORCALE so 
please report any problems you may have with it.

If you are doing some critical programming I would use the regular LOB functions as they are well tried.

Not all of the interface has been implemented yet, the following are not supported;

  1) Piecewise, and callback binds for INSERT and UPDATE operations.
  2) Array binds for INSERT and UPDATE operations.
  3) Piecewise and callback binds for SELECT operation.


=head1 Handling LOBs

=head2 Simple Usage

The value of an Oracle LOB column is not the content of the LOB. It's a
'LOB Locator' which, after being selected or inserted needs extra
processing to read or write the content of the LOB. This being said if your 
LOB column is a NULL LOB then the locator is null and there is no locator for
DBD::Oracle to work with and it will return a 

  DBD::Oracle::db::ora_lob_read: locator is not of type OCILobLocatorPtr
  
error. 

To correct for this you must use an SQL UPDATE statement to reset the 
LOB column to a non-NULL (or empty) value with an SQL like this;

  UPDATE lob_example 
     SET bindata=EMPTY_BLOB()
   WHERE bindata IS NULL.

When fetching LOBs they are, by default, made to look just like LONGs and
are subject to the LongReadLen and LongTruncOk attributes. Note that
with OCI 7 DBD::Oracle pre-allocates the whole buffer (LongReadLen) at
the time the statement is prepared.  With OCI 8+ it grows the buffer to
the amount needed for the largest LOB to be fetched so far.

When inserting or updating LOBs some I<major> magic has to be performed
behind the scenes to make it transparent.  Basically the driver has to
insert a 'LOB Locator' and then refetch the newly inserted LOB
Locator before being able to write the data into it.  However, it works
well most of the time, and I've made it as fast as possible, just one
extra server-round-trip per insert or update after the first.  For the
time being, only single-row LOB updates are supported.

To insert or update a large LOB using a placeholder, DBD::Oracle has to
know in advance that it is a LOB type. So you need to say:

  $sth->bind_param($field_num, $lob_value, { ora_type => ORA_CLOB });

The ORA_CLOB and ORA_BLOB constants can be imported using

  use DBD::Oracle qw(:ora_types);

or use the corresponding integer values (112 and 113).

One further wrinkle: for inserts and updates of LOBs, DBD::Oracle has
to be able to tell which parameters relate to which table fields.
In all cases where it can possibly work it out for itself, it does,
however, if there are multiple LOB fields of the same type in the table
then you need to tell it which field each LOB param relates to:

  $sth->bind_param($idx, $value, { ora_type=>ORA_CLOB, ora_field=>'foo' });

There are some limitations inherent in the way DBD::Oracle makes typical
LOB operations simple by hiding the LOB Locator processing:

 - Can't read/write LOBs in chunks (except via DBMS_LOB.WRITEAPPEND in PL/SQL)
 - To INSERT a LOB, you need UPDATE privilege.

The alternative is to disable the automatic LOB Locator processing.
If L</ora_auto_lob> is 0 in prepare(), you can fetch the LOB Locators and
do all the work yourself using the ora_lob_*() methods and/or Oracle::OCI.
See the L</LOB Methods> section below.

=head2 LOB support in PL/SQL

LOB Locators can be passed to PL/SQL calls by binding them to placeholders
with the proper C<ora_type>.  If L</ora_auto_lob> is true, output LOB
parameters will be automatically returned as strings. 

If the Oracle driver has support for temporary LOBs (Oracle 9i and higher),
strings can be bound to input LOB placeholders and will be automatically 
converted to LOBs.

Example:
     # Build a large XML document, bind it as a CLOB,
     # extract elements through PL/SQL and return as a CLOB

     # $dbh is a connected database handle 
     # output will be large

     local $dbh->{LongReadLen} = 1_000_000;

     my $in_clob = "<document>\n";
     $in_clob .= "  <value>$_</value>\n" for 1 .. 10_000;
     $in_clob .= "</document>\n";

     my $out_clob;
     
     
     my $sth = $dbh->prepare(<<PLSQL_END);
     -- extract 'value' nodes
     DECLARE
       x XMLTYPE := XMLTYPE(:in);
     BEGIN
       :out := x.extract('/document/value').getClobVal();
     END;

     PLSQL_END
     
     # :in param will be converted to a temp lob
     # :out parameter will be returned as a string.

     $sth->bind_param( ':in', $in_clob, { ora_type => ORA_CLOB } );
     $sth->bind_param_inout( ':out', \$out_clob, 0, { ora_type => ORA_CLOB } );
     $sth->execute;


=head2 LOB Locator Methods

The following driver-specific methods let you manipulate "LOB Locators".
LOB locators can be selected from tables directly, if the C<ora_auto_lob>
attribute is false, or returned via PL/SQL procedure calls.

(If using a DBI version earlier than 1.36 they must be called via the
func() method. Note that methods called via func() don't honour
RaiseError etc, and so it's important to check $dbh->err after each call.
It's recommended that you upgrade to DBI 1.38 or later.)

Note that LOB locators are only valid while the statement handle that
created them is valid.  When all references to the original statement
handle are lost, the handle is destroyed and the locators are freed.

B<Warning:> Currently multi-byte character set issues have not been
fully worked out.  So these methods may not do what you expect if
either the Perl data is utf8 or the CLOB is a multi-byte character set
(including uft8). The current behaviour in these situations may not be
correct and is B<subject to change>. I<Testing and patches are most welcome.>

=over 4

=item ora_lob_read

  $data = $dbh->ora_lob_read($lob_locator, $offset, $length);

Read a portion of the LOB. $offset starts at 1.
Uses the Oracle OCILobRead function.

=item ora_lob_write

  $rc = $dbh->ora_lob_write($lob_locator, $offset, $data);

Write/overwrite a portion of the LOB. $offset starts at 1.
Uses the Oracle OCILobWrite function.

=item ora_lob_append

  $rc = $dbh->ora_lob_append($lob_locator, $data);

Append $data to the LOB.  Uses the Oracle OCILobWriteAppend function.

NOTE: This method should I<not> be used if either the client or the
server are Oracle version 8 due to Oracle bug #886191.

=item ora_lob_trim

  $rc = $dbh->ora_lob_trim($lob_locator, $length);

Trims the length of the LOB to $length.
Uses the Oracle OCILobTrim function.

=item ora_lob_length

  $length = $dbh->ora_lob_length($lob_locator);

Returns the length of the LOB.
Uses the Oracle OCILobGetLength function.

=back

=head2 LOB Locator Method Examples

I<Note:> Make sure you first read the note in the section above about
multi-byte character set issues with these methods.

The following examples demonstrate the usage of LOB Locators
to read, write, and append data, and to query the size of
large data.

The following examples assume a table containing two large
object columns, one binary and one character, with a primary
key column, defined as follows:

   CREATE TABLE lob_example (
      lob_id      INTEGER PRIMARY KEY,
      bindata     BLOB,
      chardata    CLOB
   )

It also assumes a sequence for use in generating unique
lob_id field values, defined as follows:

   CREATE SEQUENCE lob_example_seq


=head2 Example: Inserting a new row with large data

Unless enough memory is available to store and bind the
entire lob data for insert all at once, the lob columns must
be written interactively, piece by piece.  In the case of a new row,
this is performed by first inserting a row, with empty values in
the lob columns, then modifying the row by writing the large data
interactively to the lob columns using their LOB locators as handles.

The insert statement must create token values in the lob
columns.  Here, we use the empty string for both the binary
and character large object columns 'bindata' and 'chardata'.

After the INSERT statement, a SELECT statement is used to
acquire lob locators to the 'bindata' and 'chardata' fields
of the newly inserted row.  Because these lob locators are
subsequently written, they must be acquired from a select
statement containing the clause 'FOR UPDATE' (lob locators
are only valid within the transaction that fetched them, so
can't be used effectively if AutoCommit is enabled).

   my $lob_id = $dbh->selectrow_array( <<"   SQL" );
      SELECT lob_example_seq.nextval FROM DUAL
   SQL

   my $sth = $dbh->prepare( <<"   SQL" );
      INSERT INTO lob_example
      ( lob_id, bindata, chardata )
      VALUES ( ?, EMPTY_BLOB(),EMPTY_CLOB() )
   SQL
   $sth->execute( $lob_id );

   $sth = $dbh->prepare( <<"   SQL", { ora_auto_lob => 0 } );
      SELECT bindata, chardata
      FROM lob_example
      WHERE lob_id = ?
      FOR UPDATE
   SQL
   $sth->execute( $lob_id );
   my ( $bin_locator, $char_locator ) = $sth->fetchrow_array();
   $sth->finish();

   open BIN_FH, "/binary/data/source" or die;
   open CHAR_FH, "/character/data/source" or die;
   my $chunk_size = 4096;   # Arbitrary chunk size

   # BEGIN WRITING BIN_DATA COLUMN
   my $offset = 1;   # Offsets start at 1, not 0
   my $length = 0;
   my $buffer = '';
   while( $length = read( BIN_FH, $buffer, $chunk_size ) ) {
      $dbh->ora_lob_write( $bin_locator, $offset, $buffer );
      $offset += $length;
   }

   # BEGIN WRITING CHAR_DATA COLUMN
   $offset = 1;   # Offsets start at 1, not 0
   $length = 0;
   $buffer = '';
   while( $length = read( CHAR_FH, $buffer, $chunk_size ) ) {
      $dbh->ora_lob_write( $char_locator, $offset, $buffer );
      $offset += $length;
   }


In this example we demonstrate the use of ora_lob_write()
interactively to append data to the columns 'bin_data' and
'char_data'.  Had we used ora_lob_append(), we could have
saved ourselves the trouble of keeping track of the offset
into the lobs.  The snippet of code beneath the comment
'BEGIN WRITING BIN_DATA COLUMN' could look as follows:

   my $buffer = '';
   while ( read( BIN_FH, $buffer, $chunk_size ) ) {
      $dbh->ora_lob_append( $bin_locator, $buffer );
   }

The scalar variables $offset and $length are no longer
needed, because ora_lob_append() keeps track of the offset
for us.


=head2 Example: Updating an existing row with large data

In this example, we demonstrate a technique for overwriting
a portion of a blob field with new binary data.  The blob
data before and after the section overwritten remains
unchanged.  Hence, this technique could be used for updating
fixed length subfields embedded in a binary field.

   my $lob_id = 5;   # Arbitrary row identifier, for example

   $sth = $dbh->prepare( <<"   SQL", { ora_auto_lob => 0 } );
      SELECT bindata
      FROM lob_example
      WHERE lob_id = ?
      FOR UPDATE
   SQL
   $sth->execute( $lob_id );
   my ( $bin_locator ) = $sth->fetchrow_array();

   my $offset = 100234;
   my $data = "This string will overwrite a portion of the blob";
   $dbh->ora_lob_write( $bin_locator, $offset, $data );

After running this code, the row where lob_id = 5 will
contain, starting at position 100234 in the bin_data column,
the string "This string will overwrite a portion of the blob".

=head2 Example: Streaming character data from the database

In this example, we demonstrate a technique for streaming
data from the database to a file handle, in this case
STDOUT.  This allows more data to be read in and written out
than could be stored in memory at a given time.

   my $lob_id = 17;   # Arbitrary row identifier, for example

   $sth = $dbh->prepare( <<"   SQL", { ora_auto_lob => 0 } );
      SELECT chardata
      FROM lob_example
      WHERE lob_id = ?
   SQL
   $sth->execute( $lob_id );
   my ( $char_locator ) = $sth->fetchrow_array();

   my $chunk_size = 1034;   # Arbitrary chunk size, for example
   my $offset = 1;   # Offsets start at 1, not 0
   while( my $data = $dbh->ora_lob_read( $char_locator, $offset, $chunk_size ) ) {
      print STDOUT $data;
      $offset += $chunk_size;
   }

Notice that the select statement does not contain the phrase
"FOR UPDATE".  Because we are only reading from the lob
locator returned, and not modifying the lob it refers to,
the select statement does not require the "FOR UPDATE"
clause.

=head2 Example: Truncating existing large data

In this example, we truncate the data already present in a
large object column in the database.  Specifically, for each
row in the table, we truncate the 'bindata' value to half
its previous length.

After acquiring a lob locator for the column, we query its
length, then we trim the length by half.  Because we modify
the large objects with the call to ora_lob_trim(), we must
select the lob locators 'FOR UPDATE'.

   my $sth = $dbh->prepare( <<"   SQL", { ora_auto_lob => 0 } );
      SELECT bindata
      FROM lob_example
      FOR UPATE
   SQL
   $sth->execute();
   while( my ( $bin_locator ) = $sth->fetchrow_array() ) {
      my $binlength = $dbh->ora_lob_length( $bin_locator );
      if( $binlength > 0 ) {
         $dbh->ora_lob_trim( $bin_locator, $binlength/2 );
      }
   }

=head1 Binding Cursors

Cursors can be returned from PL/SQL blocks, either from stored
functions (or procedures with OUT parameters) or
from direct C<OPEN> statements, as shown below:

  use DBI;
  use DBD::Oracle qw(:ora_types);
  my $dbh = DBI->connect(...);
  my $sth1 = $dbh->prepare(q{
      BEGIN OPEN :cursor FOR
          SELECT table_name, tablespace_name
          FROM user_tables WHERE tablespace_name = :space;
      END;
  });
  $sth1->bind_param(":space", "USERS");
  my $sth2;
  $sth1->bind_param_inout(":cursor", \$sth2, 0, { ora_type => ORA_RSET } );
  $sth1->execute;
  # $sth2 is now a valid DBI statement handle for the cursor
  while ( my @row = $sth2->fetchrow_array ) { ... }

The only special requirement is the use of C<bind_param_inout()> with an
attribute hash parameter that specifies C<ora_type> as C<ORA_RSET>.
If you don't do that you'll get an error from the C<execute()> like:
"ORA-06550: line X, column Y: PLS-00306: wrong number or types of
arguments in call to ...".

Here's an alternative form using a function that returns a cursor.
This example uses the pre-defined weak (or generic) REF CURSOR type
SYS_REFCURSOR. This is an Oracle 9 feature. For Oracle 8, you must
create your own REF CURSOR type in a package (see the C<curref.pl>
script mentioned at the end of this section).

  # Create the function that returns a cursor
  $dbh->do(q{
      CREATE OR REPLACE FUNCTION sp_ListEmp RETURN SYS_REFCURSOR
      AS l_cursor SYS_REFCURSOR;
      BEGIN
          OPEN l_cursor FOR select ename, empno from emp
              ORDER BY ename;
          RETURN l_cursor;
      END;
  });

  # Use the function that returns a cursor
  my $sth1 = $dbh->prepare(q{BEGIN :cursor := sp_ListEmp; END;});
  my $sth2;
  $sth1->bind_param_inout(":cursor", \$sth2, 0, { ora_type => ORA_RSET } );
  $sth1->execute;
  # $sth2 is now a valid DBI statement handle for the cursor
  while ( my @row = $sth2->fetchrow_array ) { ... }

A cursor obtained from PL/SQL as above may be passed back to PL/SQL
by binding for input, as shown in this example, which explicitly
closes a cursor:

  my $sth3 = $dbh->prepare("BEGIN CLOSE :cursor; END;");
  $sth3->bind_param(":cursor", $sth2, { ora_type => ORA_RSET } );
  $sth3->execute;

It is not normally necessary to close a cursor
explicitly in this way. Oracle will close the cursor automatically
at the first client-server interaction after the cursor statement handle is
destroyed. An explicit close may be desirable if the reference to
the cursor handle from the PL/SQL statement handle delays the destruction
of the cursor handle for too long. This reference remains until the
PL/SQL handle is re-bound, re-executed or destroyed.

See the C<curref.pl> script in the Oracle.ex directory in the DBD::Oracle
source distribution for a complete working example.

=head1 Fetching Nested Cursors

Oracle supports the use of select list expressions of type REF CURSOR.
These may be explicit cursor expressions - C<CURSOR(SELECT ...)>, or
calls to PL/SQL functions which return REF CURSOR values. The values
of these expressions are known as nested cursors.

The value returned to a Perl program when a nested cursor is fetched
is a statement handle. This statement handle is ready to be fetched from.
It should not (indeed, must not) be executed.

Oracle imposes a restriction on the order of fetching when nested
cursors are used. Suppose C<$sth1> is a handle for a select statement
involving nested cursors, and C<$sth2> is a nested cursor handle fetched
from C<$sth1>. C<$sth2> can only be fetched from while C<$sth1> is
still active, and the row containing C<$sth2> is still current in C<$sth1>.
Any attempt to fetch another row from C<$sth1> renders all nested cursor
handles previously fetched from C<$sth1> defunct.

Fetching from such a defunct handle results in an error with the message
C<ERROR nested cursor is defunct (parent row is no longer current)>.

This means that the C<fetchall...> or C<selectall...> methods are not useful
for queries returning nested cursors. By the time such a method returns,
all the nested cursor handles it has fetched will be defunct.

It is necessary to use an explicit fetch loop, and to do all the
fetching of nested cursors within the loop, as the following example
shows:

    use DBI;
    my $dbh = DBI->connect(...);
    my $sth = $dbh->prepare(q{
        SELECT dname, CURSOR(
            SELECT ename FROM emp
                WHERE emp.deptno = dept.deptno
                ORDER BY ename
        ) FROM dept ORDER BY dname
    });
    $sth->execute;
    while ( my ($dname, $nested) = $sth->fetchrow_array ) {
        print "$dname\n";
        while ( my ($ename) = $nested->fetchrow_array ) {
            print "        $ename\n";
        }
    }


The cursor returned by the function C<sp_ListEmp> defined in the
previous section can be fetched as a nested cursor as follows:

    my $sth = $dbh->prepare(q{SELECT sp_ListEmp FROM dual});
    $sth->execute;
    my ($nested) = $sth->fetchrow_array;
    while ( my @row = $nested->fetchrow_array ) { ... }

=head2 Pre-fetching Nested Cursors

By default, DBD::Oracle pre-fetches rows in order to reduce the number of
round trips to the server. For queries which do not involve nested cursors,
the number of pre-fetched rows is controlled by the DBI database handle
attribute C<RowCacheSize> (q.v.).

In Oracle, server side open cursors are a controlled resource, limited in
number, on a per session basis, to the value of the initialization
parameter C<OPEN_CURSORS>. Nested cursors count towards this limit.
Each nested cursor in the current row counts 1, as does
each nested cursor in a pre-fetched row. Defunct nested cursors do not count.

An Oracle specific database handle attribute, C<ora_max_nested_cursors>,
further controls pre-fetching for queries involving nested cursors. For
each statement handle, the total number of nested cursors in pre-fetched
rows is limited to the value of this parameter. The default value
is 0, which disables pre-fetching for queries involving nested cursors.

=head1 Returning A Value from an INSERT

Oracle supports an extended SQL insert syntax which will return one
or more of the values inserted. This can be particularly useful for
single-pass insertion of values with re-used sequence values
(avoiding a separate "select seq.nextval from dual" step).

  $sth = $dbh->prepare(qq{
      INSERT INTO foo (id, bar)
      VALUES (foo_id_seq.nextval, :bar)
      RETURNING id INTO :id
  });
  $sth->bind_param(":bar", 42);
  $sth->bind_param_inout(":id", \my $new_id, 99);
  $sth->execute;
  print "The id of the new record is $new_id\n";

If you have many columns to bind you can use code like this:

  @params = (... column values for record to be inserted ...);
  $sth->bind_param($_, $params[$_-1]) for (1..@params);
  $sth->bind_param_inout(@params+1, \my $new_id, 99);
  $sth->execute;

If you have many rows to insert you can take advantage of Oracle's built in execute array feature
with code like this:

  my @in_values=('1',2,'3','4',5,'6',7,'8',9,'10');
  my @out_values;
  my @status;
  my $sth = $dbh->prepare(qq{
        INSERT INTO foo (id, bar)
        VALUES (foo_id_seq.nextval, ?)
        RETURNING id INTO ?
  });
  $sth->bind_param_array(1,\@in_values);
  $sth->bind_param_inout_array(2,\@out_values,0,{ora_type => ORA_VARCHAR2});
  $sth->execute_array({ArrayTupleStatus=>\@status}) or die "error inserting";
  foreach my $id (@out_values){
	print 'returned id='.$id.'\n';
  }
	
Which will return all the ids into @out_values. 

Note: 

1) This will only work for numbered placeholders,

2) The third parameter of bind_param_inout_array "maxlen" is required by DBI but not used by DBD::Oracle

3) The "ora_type" attribute is not needed but only ORA_VARCHAR2 will work.

=head1 Returning A Recordset

DBD::Oracle does not currently support binding a PL/SQL table (aka array)
as an IN OUT parameter to any Perl data structure.  You cannot therefore call
a PL/SQL function or procedure from DBI that uses a non-atomic data type as
either a parameter, or a return value.  However, if you are using Oracle 9.0.1
or later, you can make use of table (or pipelined) functions.

For example, assume you have the existing PL/SQL Package :

CREATE OR REPLACE PACKAGE Array_Example AS
    --
    TYPE tRec IS RECORD (
        Col1    NUMBER,
        Col2    VARCHAR2 (10),
        Col3    DATE) ;
    --
    TYPE taRec IS TABLE OF tRec INDEX BY BINARY_INTEGER ;
    --
    FUNCTION Array_Func RETURN taRec ;
    --
END Array_Example ;

CREATE OR REPLACE PACKAGE BODY Array_Example AS
--
FUNCTION Array_Func RETURN taRec AS
--
    l_Ret       taRec ;
--
BEGIN
    FOR i IN 1 .. 5 LOOP
        l_Ret (i).Col1 := i ;
        l_Ret (i).Col2 := 'Row : ' || i ;
        l_Ret (i).Col3 := TRUNC (SYSDATE) + i ;
    END LOOP ;
    RETURN l_Ret ;
END ;
--
END Array_Example ;
/

Currently, there is no way to directly call the function
Array_Example.Array_Func from DBI.  However, by making the following relatively
painless additions, its not only possible, but extremely efficient.

First, you need to create database object types that correspond to the record
and table types in the package.  From the above example, these would be :

  CREATE OR REPLACE TYPE tArray_Example__taRec
  AS OBJECT (
      Col1    NUMBER,
      Col2    VARCHAR2 (10),
      Col3    DATE
  ) ;

  CREATE OR REPLACE TYPE taArray_Example__taRec
  AS TABLE OF tArray_Example__taRec ;

Now, assuming the existing function needs to remain unchanged (it is probably
being called from other PL/SQL code), we need to add a new function to the
package.  Here's the new package specification and body :

  CREATE OR REPLACE PACKAGE Array_Example AS
      --
      TYPE tRec IS RECORD (
	  Col1    NUMBER,
	  Col2    VARCHAR2 (10),
	  Col3    DATE) ;
      --
      TYPE taRec IS TABLE OF tRec INDEX BY BINARY_INTEGER ;
      --
      FUNCTION Array_Func RETURN taRec ;
      FUNCTION Array_Func_DBI RETURN taArray_Example__taRec PIPELINED ;
      --
  END Array_Example ;

  CREATE OR REPLACE PACKAGE BODY Array_Example AS
  --
  FUNCTION Array_Func RETURN taRec AS
      l_Ret  taRec ;
  BEGIN
      FOR i IN 1 .. 5 LOOP
	  l_Ret (i).Col1 := i ;
	  l_Ret (i).Col2 := 'Row : ' || i ;
	  l_Ret (i).Col3 := TRUNC (SYSDATE) + i ;
      END LOOP ;
      RETURN l_Ret ;
  END ;

  FUNCTION Array_Func_DBI RETURN taArray_Example__taRec PIPELINED AS
      l_Set  taRec ;
  BEGIN
      l_Set := Array_Func ;
      FOR i IN l_Set.FIRST .. l_Set.LAST LOOP
	  PIPE ROW (
	      tArray_Example__taRec (
		  l_Set (i).Col1,
		  l_Set (i).Col2,
		  l_Set (i).Col3
	      )
	  ) ;
      END LOOP ;
      RETURN ;
  END ;
  --
  END Array_Example ;

As you can see, the new function is very simple.  Now, it is a simple matter
of calling the function as a straight-forward SELECT from your DBI code.  From
the above example, the code would look something like this :

  my $sth = $dbh->prepare('SELECT * FROM TABLE(Array_Example.Array_Func_DBI)');
  $sth->execute;
  while ( my ($col1, $col2, $col3) = $sth->fetchrow_array {
    ...
  }

=head1 Timezones

If TWO_TASK isn't set, Oracle uses the TZ variable from the local environment.
 
If TWO_TASK IS set, Oracle uses the TZ variable of the listener process
running on the server.

You could have multiple listeners, each with their own TZ, and assign
users to the appropriate listener by setting TNS_ADMIN to a directory
that contains a tnsnames.ora file that points to the port that their
listener is on.

[Brad Howerter, who supplied this info said: "I've done this to simulate
running a Perl script at the end of the previous month even though it
was the 6th of the new month.  I had the dba start up a listener with
TZ=X+144.  (144 hours = 6 days)"]

=head1 Object & Collection Data Types 

Oracle databases allow for the creation of object oriented like user-defined types.  
There are two types of objects, Embedded--an object stored in a column of a regular table 
and REF--an object that uses the REF retrieval mechanism. 

DBD::Oracle supports only the 'selection' of embedded objects of the following types OBJECT, VARRAY
and TABLE in any combination. Support is seamless and recursive, meaning you 
need only supply a simple SQL statement to get all the values in an embedded object 
as an array of scalars. 

For example, given this type and table;

  CREATE OR REPLACE TYPE  "PHONE_NUMBERS" as varray(10) of varchar(30);
  
  CREATE TABLE  "CONTACT" 
     (	"COMPANYNAME" VARCHAR2(40), 
  	"ADDRESS" VARCHAR2(100), 
  	"PHONE_NUMBERS"  "PHONE_NUMBERS" 
   )

The code to access all the data in the table could be something like this;

   my $sth = $dbh->prepare('SELECT * FROM CONTACT');
   $sth->execute;
   while ( my ($company, $address, $phone) = $sth->fetchrow()) {
        print "Company: ".$company."\n";
        print "Address: ".$address."\n";
        print "Phone #: ";
        
        foreach my $items (@$phone){
           print $items.", ";
        }
        print "\n";
   }

Note that values in PHONE_NUMBERS are returned as an array reference '@$phone'.

As stated before DBD::Oracle will automatically drill into the embedded object and extract 
all of the data as reference arrays of scalars. The example below has OBJECT type embedded in a TABLE type embedded in an
SQL TABLE;

   CREATE OR REPLACE TYPE GRADELIST AS TABLE OF NUMBER;

   CREATE OR REPLACE TYPE STUDENT AS OBJECT(
       NAME          VARCHAR2(60),
       SOME_GRADES   GRADELIST);

   CREATE OR REPLACE TYPE STUDENTS_T AS TABLE OF STUDENT;

   CREATE TABLE GROUPS( 
       GRP_ID        NUMBER(4),
       GRP_NAME      VARCHAR2(10),
       STUDENTS      STUDENTS_T)
     NESTED TABLE STUDENTS STORE AS GROUP_STUDENTS_TAB
      (NESTED TABLE SOME_GRADES STORE AS GROUP_STUDENT_GRADES_TAB);

The following code will access all of the embedded data;

   $SQL='select grp_id,grp_name,students as my_students_test from groups';
   $sth=$dbh->prepare($SQL);
   $sth->execute();
   while (my ($grp_id,$grp_name,$students)=$sth->fetchrow()){
      print "Group ID#".$grp_id." Group Name =".$grp_name."\n";
      foreach my $student (@$students){
         print "Name:".$student->[0]."\n";
         print "Marks:";
         foreach my $grades (@$student->[1]){
            foreach my $marks (@$grades){
               print $marks.",";     
            }
         }
         print "\n";
      }
      print "\n";
   }

So far DBD::Oracle has been tested on a table with 20 embedded Objects, Varrays and Tables 
nested to 10 levels.

Any NULL values found in the embedded object will be returned as 'undef'.

=head1 Support for Insert of XMLType (ORA_XMLTYPE)

Inserting large XML data sets into tables with XMLType fields is now supported by DBD::Oracle. The only special 
requirement is the use of bind_param() with an attribute hash parameter that specifies ora_type as ORA_XMLTYPE. For
example with a table like this;

   create table books (book_id number, book_xml XMLType);

one can insert data using this code

   $SQL='insert into books values (1,:p_xml)';
   $xml= '<Books>
	  	<Book id=1>
	  		<Title>Programming the Perl DBI</Title>
	                <Subtitle>The Cheetah Book</Subtitle>
	                <Authors>
	                	<Author>T. Bunce</Author>
	                	<Author>Alligator Descartes</Author>
	                </Authors>
	                
	        </Book>
	        <Book id=10000>...
	    </Books>';
   my $sth =$dbh-> prepare($SQL);
   $sth-> bind_param("p_xml", $xml, { ora_type => ORA_XMLTYPE }); 
   $sth-> execute();
       
In the above case we will assume that $xml has 10000 Book nodes and is over 32k in size and is well formed XML. 
This will also work for XML that is smaller than 32k as well. Attempting to insert malformed XML will cause an error. 

=head1 Oracle Related Links

=head2 Oracle Instant Client

  http://www.oracle.com/technology/tech/oci/instantclient/index.html
  
=head2 Oracle on Linux

  http://www.eGroups.com/list/oracle-on-linux
  http://www.ixora.com.au/

=head2 Free Oracle Tools and Links

  ora_explain supplied and installed with DBD::Oracle.

  http://www.orafaq.com/

  http://vonnieda.org/oracletool/

=head2 Commercial Oracle Tools and Links

Assorted tools and references for general information.
No recommendation implied.

  http://www.platinum.com/products/oracle.htm
  http://www.SoftTreeTech.com
  http://www.databasegroup.com

Also PL/Vision from RevealNet and Steven Feuerstein, and
"Q" from Savant Corporation.


=head1 SEE ALSO

L<DBI>

http://search.cpan.org/~timb/DBD-Oracle/MANIFEST for all files in
the DBD::Oracle source distribution including the examples in the
Oracle.ex directory

  http://search.cpan.org/search?query=Oracle&mode=dist

=head1 AUTHOR

DBD::Oracle by Tim Bunce. DBI by Tim Bunce.

=head1 ACKNOWLEDGEMENTS

A great many people have helped me with DBD::Oracle over the 12 years
between 1994 and 2006.  Far too many to name, but I thank them all.
Many are named in the Changes file.

See also L<DBI/ACKNOWLEDGEMENTS>.

=head1 MAINTAINER

As of release 1.17 in February 2006 The Pythian Group, Inc. (L<http://www.pythian.com>)
are taking the lead in maintaining DBD::Oracle with my assistance and
gratitude. That frees more of my time to work on DBI for Perl 5 and Perl 6.

=head1 COPYRIGHT

The DBD::Oracle module is Copyright (c) 1994-2006 Tim Bunce. Ireland.

The DBD::Oracle module is free open source software; you can
redistribute it and/or modify it under the same terms as Perl 5.

=head1 CONTRIBUTING

If you'd like DBD::Oracle to do something new or different the best way
to make that happen is to do it yourself and email to dbi-dev@perl.org a
patch of the source code (using 'diff' - see below) that shows the changes.

=head2 How to create a patch using Subversion

The DBD::Oracle source code is maintained using Subversion (a replacement
for CVS, see L<http://subversion.tigris.org/>). To access the source
you'll need to install a Subversion client. Then, to get the source
code, do:

  svn checkout http://svn.perl.org/modules/dbd-oracle/trunk

If it prompts for a username and password use your perl.org account
if you have one, else just 'guest' and 'guest'. The source code will
be in a new subdirectory called C<trunk>.

To keep informed about changes to the source you can send an empty email
to dbd-oracle-changes-subscribe@perl.org after which you'll get an email with the
change log message and diff of each change checked-in to the source.

After making your changes you can generate a patch file, but before
you do, make sure your source is still upto date using:

  svn update 

If you get any conflicts reported you'll need to fix them first.
Then generate the patch file from within the C<trunk> directory using:

  svn diff > foo.patch

Read the patch file, as a sanity check, and then email it to dbi-dev@perl.org.

=head2 How to create a patch without Subversion

Unpack a fresh copy of the distribution:

  tar xfz DBD-Oracle-1.40.tar.gz

Rename the newly created top level directory:

  mv DBD-Oracle-1.40 DBD-Oracle-1.40.your_foo

Edit the contents of DBD-Oracle-1.40.your_foo/* till it does what you want.

Test your changes and then remove all temporary files:

  make test && make distclean

Go back to the directory you originally unpacked the distribution:

  cd ..

Unpack I<another> copy of the original distribution you started with:

  tar xfz DBD-Oracle-1.40.tar.gz

Then create a patch file by performing a recursive C<diff> on the two
top level directories:

  diff -r -u DBD-Oracle-1.40 DBD-Oracle-1.40.your_foo > DBD-Oracle-1.40.your_foo.patch

=head2 Speak before you patch

For anything non-trivial or possibly controversial it's a good idea
to discuss (on dbi-dev@perl.org) the changes you propose before
actually spending time working on them. Otherwise you run the risk
of them being rejected because they don't fit into some larger plans
you may not be aware of.

=cut