forked from synopse/mORMot
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathSynDBOracle.pas
3347 lines (3148 loc) · 142 KB
/
SynDBOracle.pas
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
/// Oracle DB direct access classes (via OCI)
// - this unit is a part of the freeware Synopse framework,
// licensed under a MPL/GPL/LGPL tri-license; version 1.18
unit SynDBOracle;
{
This file is part of Synopse framework.
Synopse framework. Copyright (C) 2015 Arnaud Bouchez
Synopse Informatique - http://synopse.info
*** BEGIN LICENSE BLOCK *****
Version: MPL 1.1/GPL 2.0/LGPL 2.1
The contents of this file are subject to the Mozilla Public License Version
1.1 (the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
http://www.mozilla.org/MPL
Software distributed under the License is distributed on an "AS IS" basis,
WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
for the specific language governing rights and limitations under the License.
The Original Code is Synopse mORMot framework.
The Initial Developer of the Original Code is Arnaud Bouchez.
Portions created by the Initial Developer are Copyright (C) 2015
the Initial Developer. All Rights Reserved.
Contributor(s):
- Adam Siwon (asiwon)
- richard6688
- mpv
Alternatively, the contents of this file may be used under the terms of
either the GNU General Public License Version 2 or later (the "GPL"), or
the GNU Lesser General Public License Version 2.1 or later (the "LGPL"),
in which case the provisions of the GPL or the LGPL are applicable instead
of those above. If you wish to allow use of your version of this file only
under the terms of either the GPL or the LGPL, and not to allow others to
use your version of this file under the terms of the MPL, indicate your
decision by deleting the provisions above and replace them with the notice
and other provisions required by the GPL or the LGPL. If you do not delete
the provisions above, a recipient may use your version of this file under
the terms of any one of the MPL, the GPL or the LGPL.
***** END LICENSE BLOCK *****
Version 1.15
- first public release, corresponding to mORMot framework 1.15
Version 1.16
- LONG columns will be handled as ftUTF8 fields, truncated to 32 KB of text
(fix error ORA-00932 at OCI client level)
- we found out that a computed column is returned as SQLT_NUM with Scale=0 even
if it is numeric with decimals (OCI 11.2 bug) -> so SQLT_INT trick is disabled
- fixed issue when using transactions methods with Oracle
Version 1.17
- now NLS_LANG environnement variable or current thread local will be used by
TSQLDBOracleConnectionProperties.Create if supplied code page parameter is 0
- now TSQLDBOracleConnectionProperties.Create constructor can be used directly
- previous TSQLDBOracleConnectionProperties reintroduced constructor renamed
CreateWithCodePage, to specify the code page to be used
- fixed issue with SQL requests containing '' and parameters
- fixed issue with SQLT_BIN/RAW kind of columns
- fixed issue with SQLT_IBFLOAT/SQLT_IBDOUBLE kind of columns
- fixed issue with CLOB/NCLOB/NVARCHAR kind of columns (including charset)
- added TSQLDBOracleStatement.ColumnToVarData() overridden method, for faster
process from mORMot external tables (i.e. SQLite3DB)
- fixed issue with TDateTime parameter (SQLT_DAT replaced by SQLT_TIMESTAMP)
- fixed issue with NULL parameter (avoid ORA-01024 error)
- fixed random issue when converting a SQLT_DATE result column value
- force ftCurrency content to be returned with '.' as decimal separator (as
expected by JSON), even with any other NLS_LANG encoding
- enhance OUT and IN/OUT bound parameters handling (for stored procedures),
with dedicated exceptions for any unhandled kind of parameter
- generated logging level reduced to the necessary (but with timing)
- added TSQLDBOracleStatement.ColumnBlobBytes method to retrieve TBytes BLOBs
- speed up of TSQLDBOracleStatement.ColumnInt() and Prepare() methods
- fixed issue in TSQLDBOracleStatement.ColumnToVarData() when retrieving date
- fixed issue in TSQLDBOracleStatement.Prepare() for some kinds of columns
- fixed issue in TSQLDBOracleStatement.ExecutePrepared at SQLT_LVC binding
- fixed BLOB memory leak in TSQLDBOracleStatement.FreeHandles
time fields from Oracle as external virtual tables
- fixed issue when NLS_DATE_FORMAT is not ISO-8601: since mORMot's ORM will
send date/time as ISO-8601 text, we now force proper NLS_DATE_FORMAT
- TOracleDate will now handle Cent=Year=Month=Day=Hour=Main=Sec=0 as TDateTime(0)
- new TOracleDate.From/ToIso8601 methods for direct SQLT_DAT handling
- code refactoring, especially about error handling and ODBC integration
Version 1.18
- implements Oracle server-side statement caching for a major performance
improvement e.g. for one row queries
- implements array binding of values as OCI_OBJECT, e.g. for an IN clause -
thanks mpv for the patch!
- avoid integer overflow in TOracleDate.ToIso8601() method in some cases
- now "" date/time values are not bound as null but as TOracleDate(0)
for array DML binding statements
- now you can use BLOB parameters for array DML binding statements (if all
the sent content is not > 32 MB after aligment to the biggest data block)
- force TOracleDate.ToDateTime to ignore any date before 30 Dec 1899 (i.e. any
TDateTime result < 0), to avoid e.g. wrong DecodeTime() computation from
retrieved value
- added SQLT_INTERVAL_YM/SQLT_INTERVAL_DS column support (as JSON text)
- added SynDBOracleOCIpath global variable to force oci.dll folder
- will search for OracleInstantClient\oci.dll in executable sub-folder (can be
used e.g. for Win64)
- enhance logging at connection to specify the client version, database name
and charset/codepage
- removed CreateWithCodePage() constructor: now the charset will be retrieved
at connection, and used for CHAR/NVARCHAR2 fields - see ticket [a6a639ec43]
- added some WIN1252 charset aliases for DB (ISO-8859-1/15)
- force ftCurrency to use '.' as decimal separator in returned SQLT_STR buffer
(as expected by JSON) - previous fix in 1.17 revision did not work
- added TSQLDBOracleConnectionProperties.ExtractTnsName() class method
- when a column charset does not match the connection value, log a warning and
force connection-level code page instead of raising an Exception
- fixed ticket [4c68975022] about broken SQL statement when logging active
- fixed ticket [545fbe7579] about TSQLDBConnection.LastErrorMessage not reset
- fixed ticket [5df9d39858] about NULL bindings in TSQLDBOracleStatement
- fixed ticket [aff1352239] to identify 9999-12-31 dates as valid
- fixed ticket [73aec05724] about potential BLOB/CURSOR resource leaks
- fixed ticket [e9cab7cd97] about OCI_SUCCESS_WITH_INFO not returning any data
- fixed truncated error message in TSQLDBOracleLib.HandleError() method
- exception during Commit should leave transaction state - see [ca035b8f0da]
- allow to return NULL columns (ex. "SELECT one, two, NULL AS three FROM table")
- fix potential ORA-24333 error when using stored procedures
- strip error text constants to decrease the generated .exe size by about 16KB
- create environment with OCI_EVENTS + OCI_THREADED for ticket [7f23c8d8d4],
and adding TSQLDBOracleConnectionProperties.EnvironmentInitializationMode
- added TSQLDBOracleConnectionProperties.RowsPrefetchSize property
- TSQLDBOracleStatement now logs execution time instead of instance life time
- remove unneeded TSQLDBOracleStatement.Prepare() Enter/Leave logs
- replaced confusing TVarData by a new dedicated TSQLVar memory structure,
shared with SynDB and mORMot units (includes methods refactoring)
}
{$I Synopse.inc} // define HASINLINE USETYPEINFO CPU32 CPU64 OWNNORMTOUPPER
interface
uses
{$ifdef MSWINDOWS}
Windows,
{$endif}
SysUtils,
{$ifndef DELPHI5OROLDER}
Variants,
{$endif}
Classes,
Contnrs,
SynCommons,
SynLog,
SynDB;
{ -------------- Oracle Client Interface native connection }
type
/// execption type associated to the native Oracle Client Interface (OCI)
ESQLDBOracle = class(ESQLDBException);
POracleDate = ^TOracleDate;
{$A-}
/// memory structure used to store a date and time in native Oracle format
// - follow the SQLT_DAT column type layout
TOracleDate = {$ifndef UNICODE}object{$else}record{$endif}
Cent, Year, Month, Day, Hour, Min, Sec: byte;
/// convert an Oracle date and time into Delphi TDateTime
// - this method will ignore any date before 30 Dec 1899 (i.e. any
// TDateTime result < 0), to avoid e.g. wrong DecodeTime() computation from
// retrieved value: if you need to retrieve dates before 1899, you should
// better retrieve the content using ISO-8601 text encoding
function ToDateTime: TDateTime;
/// convert an Oracle date and time into its textual expanded ISO-8601
// - will fill up to 21 characters, including double quotes
function ToIso8601(Dest: PUTF8Char): integer; overload;
/// convert an Oracle date and time into its textual expanded ISO-8601
// - return the ISO-8601 text, without double quotes
procedure ToIso8601(var aIso8601: RawByteString); overload;
/// convert Delphi TDateTime into native Oracle date and time format
procedure From(const aValue: TDateTime); overload;
/// convert textual ISO-8601 into native Oracle date and time format
procedure From(const aIso8601: RawUTF8); overload;
/// convert textual ISO-8601 into native Oracle date and time format
procedure From(aIso8601: PUTF8Char; Length: integer); overload;
end;
{$A+}
/// wrapper to an array of TOracleDate items
TOracleDateArray = array[0..(maxInt div sizeof(TOracleDate))-1] of TOracleDate;
/// event triggered when an expired password is detected
// - will allow to provide a new password
TOnPasswordExpired = function (Sender: TSQLDBConnection; var APassword: RawUTF8): Boolean of object;
/// will implement properties shared by native Oracle Client Interface connections
TSQLDBOracleConnectionProperties = class(TSQLDBConnectionPropertiesThreadSafe)
protected
fRowsPrefetchSize: Integer;
fBlobPrefetchSize: Integer;
fStatementCacheSize: integer;
fInternalBufferSize: integer;
fEnvironmentInitializationMode: integer;
fOnPasswordChanged: TNotifyEvent;
fOnPasswordExpired: TOnPasswordExpired;
function GetClientVersion: RawUTF8;
/// initialize fForeignKeys content with all foreign keys of this DB
// - used by GetForeignKey method
procedure GetForeignKeys; override;
public
/// initialize the connection properties
// - we don't need a database name parameter for Oracle connection: only
// aServerName is to be set
// - you may specify the TNSName in aServerName, or a connection string
// like '//host[:port]/[service_name]', e.g. '//sales-server:1523/sales'
// - connection is opened globaly as UTF-8, to match the internal encoding
// of our units; but CHAR / NVARCHAR2 fields will use the Oracle charset
// as retrieved from the opened connection (to avoid any conversion error)
constructor Create(const aServerName, aDatabaseName, aUserID, aPassWord: RawUTF8); override;
/// create a new connection
// - call this method if the shared MainConnection is not enough (e.g. for
// multi-thread access)
// - the caller is responsible of freeing this instance
// - this overridden method will create an TSQLDBOracleConnection instance
function NewConnection: TSQLDBConnection; override;
/// extract the TNS listener name from a Oracle full connection string
// - e.g. ExtractTnsName('1.2.3.4:1521/dbname') returns 'dbname'
class function ExtractTnsName(const aServerName: RawUTF8): RawUTF8;
procedure PasswordChanged(const ANewPassword: RawUTF8);
published
/// returns the Client version e.g. 'oci.dll rev. 11.2.0.1'
property ClientVersion: RawUTF8 read GetClientVersion;
/// the OCI initialization mode used for the connection
// - equals OCI_EVENTS or OCI_THREADED by default, since will likely be
// used in a multi-threaded context (even if this class is inheriting from
// TSQLDBConnectionPropertiesThreadSafe), and OCI_EVENTS is needed to support
// Oracle RAC Connection Load Balancing
// - can be tuned depending on the configuration or the Oracle version
property EnvironmentInitializationMode: integer
read fEnvironmentInitializationMode write fEnvironmentInitializationMode;
/// the size (in bytes) of the internal buffer used to retrieve rows in statements
// - default is 128 KB, which gives very good results
property InternalBufferSize: integer read fInternalBufferSize write fInternalBufferSize;
/// the size (in bytes) of rows data prefecth at OCI driver level
// - is set to 128 KB by default, but may be changed for tuned performance
property RowsPrefetchSize: integer read fRowsPrefetchSize write fRowsPrefetchSize;
/// the size (in bytes) of LOB prefecth
// - is set to 4096 (4 KB) by default, but may be changed for tuned performance
property BlobPrefetchSize: integer read fBlobPrefetchSize write fBlobPrefetchSize;
/// Password Expired event
property OnPasswordExpired: TOnPasswordExpired read FOnPasswordExpired write FOnPasswordExpired;
/// Password changed event
property OnPasswordChanged: TNotifyEvent read FOnPasswordChanged write FOnPasswordChanged;
/// the number of prepared statements cached by OCI on the Client side
// - is set to 30 by default
// - only used if UseCache=true
property StatementCacheSize: integer read fStatementCacheSize write fStatementCacheSize;
end;
/// implements a direct connection to the native Oracle Client Interface (OCI)
TSQLDBOracleConnection = class(TSQLDBConnectionThreadSafe)
protected
fEnv: pointer;
fError: pointer;
fServer: pointer;
fContext: pointer;
fSession: pointer;
fTrans: pointer;
fOCICharSet: cardinal;
fType_numList: pointer;
fType_strList: pointer;
// match DB charset for CHAR/NVARCHAR2, nil for OCI_UTF8/OCI_AL32UTF8
fAnsiConvert: TSynAnsiConvert;
procedure STRToUTF8(P: PAnsiChar; var result: RawUTF8;
ColumnDBCharSet,ColumnDBForm: Cardinal);
{$ifndef UNICODE}
procedure STRToAnsiString(P: PAnsiChar; var result: AnsiString;
ColumnDBCharSet,ColumnDBForm: Cardinal);
{$endif}
public
/// prepare a connection to a specified Oracle database server
constructor Create(aProperties: TSQLDBConnectionProperties); override;
/// release memory and connection
destructor Destroy; override;
/// connect to the specified Oracle database server
// - should raise an Exception on error
// - the connection will be globaly opened with UTF-8 encoding; for CHAR /
// NVARCHAR2 fields, the DB charset encoding will be retrieved from the
// server, to avoid any truncation during data retrieval
// - BlobPrefetchSize, RowsPrefetchSize and StatementCacheSize field values
// of the associated properties will be used to tune the opened connection
procedure Connect; override;
/// stop connection to the specified Oracle database server
// - should raise an Exception on error
procedure Disconnect; override;
/// return TRUE if Connect has been already successfully called
function IsConnected: boolean; override;
/// initialize a new SQL query statement for the given connection
// - the caller should free the instance after use
function NewStatement: TSQLDBStatement; override;
/// initialize a new SQL query statement for the given connection
// - if UseCache=true, this overridden implementation will use server-side
// Oracle statement cache - in this case, StatementCacheSize will define
// how many statements are to be cached
// - this method should return nil in case of error, or a prepared statement
// instance in case of success (with default RaiseExceptionOnError=false)
function NewStatementPrepared(const aSQL: RawUTF8;
ExpectResults: Boolean; RaiseExceptionOnError: Boolean=false): ISQLDBStatement; override;
/// begin a Transaction for this connection
// - current implementation do not support nested transaction with those
// methods: exception will be raised in such case
// - by default, TSQLDBOracleStatement works in AutoCommit mode, unless
// StartTransaction is called
procedure StartTransaction; override;
/// commit changes of a Transaction for this connection
// - StartTransaction method must have been called before
procedure Commit; override;
/// discard changes of a Transaction for this connection
// - StartTransaction method must have been called before
procedure Rollback; override;
/// allows to change the password of the current connected user
// - will first launch the OnPasswordExpired event to retrieve the new
// password, then change it and call OnPasswordChanged event on success
function PasswordChange: Boolean;
end;
/// implements a statement via the native Oracle Client Interface (OCI)
// - those statements can be prepared on the Delphi side, but by default we
// enabled the OCI-side statement cache, not to reinvent the wheel this time
// - note that bound OUT ftUTF8 parameters will need to be pre-allocated
// before calling - e.g. via BindTextU(StringOfChar(3000),paramOut)
// - you can also bind an TInt64DynArray or TRawUTF8DynArray as parameter to
// be assigned later as an OCI_OBJECT so that you may write such statements:
// ! var arr: TInt64DynArray = [1, 2, 3];
// ! Query := TSQLDBOracleConnectionProperties.NewThreadSafeStatementPrepared(
// ! 'select * from table where table.id in '+
// ! '(select column_value from table(cast(? as SYS.ODCINUMBERLIST)))');
// ! Query.BindArray(1,arr);
// ! Query.ExecutePrepared;
// (use SYS.ODCIVARCHAR2LIST type cast for TRawUTF8DynArray values)
TSQLDBOracleStatement = class(TSQLDBStatementWithParamsAndColumns)
protected
fStatement: pointer;
fError: pointer;
fPreparedParamsCount: integer;
fRowCount: cardinal;
fRowBufferCount: cardinal;
fRowFetched: cardinal;
fRowFetchedCurrent: cardinal;
fRowFetchedEnded: boolean;
fRowBuffer: TByteDynArray;
fBoundCursor: array of pointer;
fInternalBufferSize: cardinal;
// warning: shall be 32 bits aligned!
fTimeElapsed: TPrecisionTimer;
fUseServerSideStatementCache: boolean;
function DateTimeToDescriptor(aDateTime: TDateTime): pointer;
procedure FreeHandles(AfterError: boolean);
procedure FetchTest(Status: integer);
/// Col=0...fColumnCount-1
function GetCol(Col: Integer; out Column: PSQLDBColumnProperty): pointer;
// called by Prepare and CreateFromExistingStatement
procedure SetColumnsForPreparedStatement;
// called by Step and CreateFromExistingStatement
procedure FetchRows;
public
/// create an OCI statement instance, from an existing OCI connection
// - the Execute method can be called once per TSQLDBOracleStatement instance,
// but you can use the Prepare once followed by several ExecutePrepared methods
// - if the supplied connection is not of TOleDBConnection type, will raise
// an exception
constructor Create(aConnection: TSQLDBConnection); override;
/// initialize the class from an existing OCI statement (and connection)
// - to be called e.g. by ColumnCursor() for SQLT_RSET kind of column
constructor CreateFromExistingStatement(aConnection: TSQLDBConnection; aStatement: pointer);
/// release all associated memory and OCI handles
destructor Destroy; override;
/// Prepare an UTF-8 encoded SQL statement
// - parameters marked as ? will be bound later, before ExecutePrepared call
// - if ExpectResults is TRUE, then Step() and Column*() methods are available
// to retrieve the data rows
// - raise an ESQLDBOracle on any error
procedure Prepare(const aSQL: RawUTF8; ExpectResults: Boolean=false); overload; override;
/// Execute a prepared SQL statement
// - parameters marked as ? should have been already bound with Bind*() functions
// - raise an ESQLDBOracle on any error
procedure ExecutePrepared; override;
/// After a statement has been prepared via Prepare() + ExecutePrepared() or
// Execute(), this method must be called one or more times to evaluate it
// - you shall call this method before calling any Column*() methods
// - return TRUE on success, with data ready to be retrieved by Column*()
// - return FALSE if no more row is available (e.g. if the SQL statement
// is not a SELECT but an UPDATE or INSERT command)
// - access the first or next row of data from the SQL Statement result:
// if SeekFirst is TRUE, will put the cursor on the first row of results,
// otherwise, it will fetch one row of data, to be called within a loop
// - raise an ESQLDBOracle on any error
function Step(SeekFirst: boolean=false): boolean; override;
/// returns TRUE if the column contains NULL
function ColumnNull(Col: integer): boolean; override;
/// return a Column integer value of the current Row, first Col is 0
function ColumnInt(Col: integer): Int64; override;
/// return a Column floating point value of the current Row, first Col is 0
function ColumnDouble(Col: integer): double; override;
/// return a Column date and time value of the current Row, first Col is 0
function ColumnDateTime(Col: integer): TDateTime; override;
/// return a Column currency value of the current Row, first Col is 0
// - should retrieve directly the 64 bit Currency content, to avoid
// any rounding/conversion error from floating-point types
function ColumnCurrency(Col: integer): currency; override;
/// return a Column UTF-8 encoded text value of the current Row, first Col is 0
function ColumnUTF8(Col: integer): RawUTF8; override;
/// return a Column as a blob value of the current Row, first Col is 0
// - ColumnBlob() will return the binary content of the field is was not ftBlob,
// e.g. a 8 bytes RawByteString for a vtInt64/vtDouble/vtDate/vtCurrency,
// or a direct mapping of the RawUnicode
function ColumnBlob(Col: integer): RawByteString; override;
/// return a Column as a blob value of the current Row, first Col is 0
// - this function will return the BLOB content as a TBytes
// - this default virtual method will call ColumnBlob()
function ColumnBlobBytes(Col: integer): TBytes; override;
/// return a Column as a variant
// - this implementation will retrieve the data with no temporary variable
// (since TQuery calls this method a lot, we tried to optimize it)
// - a ftUTF8 content will be mapped into a generic WideString variant
// for pre-Unicode version of Delphi, and a generic UnicodeString (=string)
// since Delphi 2009: you may not loose any data during charset conversion
// - a ftBlob content will be mapped into a TBlobData AnsiString variant
function ColumnToVariant(Col: integer; var Value: Variant): TSQLDBFieldType; override;
/// return a Column as a TSQLVar value, first Col is 0
// - the specified Temp variable will be used for temporary storage of
// svtUTF8/svtBlob values
// - this implementation will retrieve the data with no temporary variable,
// and handling ftCurrency/NUMBER(22,0) as fast as possible, directly from
// the memory buffers returned by OCI: it will ensure best performance
// possible when called from TSQLVirtualTableCursorExternal.Column method
// as defined in mORMotDB unit (i.e. mORMot external DB access)
procedure ColumnToSQLVar(Col: Integer; var Value: TSQLVar;
var Temp: RawByteString); override;
/// append all columns values of the current Row to a JSON stream
// - will use WR.Expand to guess the expected output format
// - fast overridden implementation with no temporary variable (about 20%
// faster when run over high number of data rows)
// - BLOB field value is saved as Base64, in the '"\uFFF0base64encodedbinary"
// format and contains true BLOB data
procedure ColumnsToJSON(WR: TJSONWriter); override;
/// return a special CURSOR Column content as a SynDB result set
// - Cursors are not handled internally by mORMot, but Oracle usually use
// such structures to get data from strored procedures
// - such columns are mapped as ftUTF8, with the rows converted to JSON
// - this overridden method will allow direct access to the data rows
function ColumnCursor(Col: integer): ISQLDBRows; override;
/// bind a special CURSOR parameter to be returned as a SynDB result set
// - Cursors are not handled internally by mORMot, but some databases (e.g.
// Oracle) usually use such structures to get data from strored procedures
// - such parameters are mapped as ftUnknown, and is always of paramOut type
// - use BoundCursor() method to retrieve the corresponding ISQLDBRows after
// execution of the statement
// - this overridden method will prepare direct access to the data rows
procedure BindCursor(Param: integer); override;
/// return a special CURSOR parameter content as a SynDB result set
// - this method is not about a column, but a parameter defined with
// BindCursor() before method execution
// - Cursors are not handled internally by mORMot, but some databases (e.g.
// Oracle) usually use such structures to get data from strored procedures
// - this method allow direct access to the data rows after execution
// - this overridden method will allow direct access to the data rows
function BoundCursor(Param: Integer): ISQLDBRows; override;
/// returns the number of rows updated by the execution of this statement
function UpdateCount: integer; override;
end;
var
/// optional folder where the Oracle Client Library is to be searched
// - by default, the oci.dll library is searched in the system PATH, then
// in %ORACLE_HOME%\bin
// - you can specify here a folder name in which the oci.dll is to be found
SynDBOracleOCIpath: TFileName;
implementation
{ TOracleDate }
// see http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28395/oci03typ.htm#sthref389
function TOracleDate.ToDateTime: TDateTime;
begin
if (PInteger(@self)^=0) and (PInteger(PtrInt(@self)+3)^=0) then
// Cent=Year=Month=Day=Hour=Main=Sec=0 -> returns 0
result := 0 else begin
if Cent<=100 then // avoid TDateTime values < 0 (generates wrong DecodeTime)
result := 0 else
result := EncodeDate((Cent-100)*100+Year-100,Month,Day);
if (Hour<>0) or (Min<>0) or (Sec<>0) then
result := result+EncodeTime(Hour-1,Min-1,Sec-1,0);
end;
end;
procedure TOracleDate.ToIso8601(var aIso8601: RawByteString);
var tmp: array[0..23] of AnsiChar;
begin
if (PInteger(@self)^=0) and (PInteger(PtrInt(@self)+3)^=0) then
// Cent=Year=Month=Day=Hour=Main=Sec=0 -> stored as ""
aIso8601 := '' else begin
DateToIso8601PChar(tmp,true,(Cent-100)*100+Year-100,Month,Day);
if (Hour<>0) or (Min<>0) or (Sec<>0) then begin
TimeToIso8601PChar(@tmp[10],true,Hour-1,Min-1,Sec-1,'T');
SetString(aIso8601,tmp,19); // we use 'T' as TTextWriter.AddDateTime
end else
SetString(aIso8601,tmp,10); // only date
end;
end;
function TOracleDate.ToIso8601(Dest: PUTF8Char): integer;
var Y: cardinal;
begin
Dest^ := '"';
if (PInteger(@self)^=0) and (PInteger(PtrInt(@self)+3)^=0) then
// Cent=Year=Month=Day=Hour=Main=Sec=0 -> stored as ""
result := 2 else begin
Y := (Cent-100)*100+Year-100;
if Y>9999 then // avoid integer overflow -> stored as ""
result := 2 else begin
DateToIso8601PChar(Dest+1,true,Y,Month,Day);
if (Hour<>0) or (Min<>0) or (Sec<>0) then begin
TimeToIso8601PChar(Dest+11,true,Hour-1,Min-1,Sec-1,'T');
result := 21; // we use 'T' as TTextWriter.AddDateTime
end else
result := 12; // only date
end;
end;
Dest[result-1] := '"';
end;
procedure TOracleDate.From(const aValue: TDateTime);
var Y,M,D, HH,MM,SS,MS: word;
begin
PInteger(PtrInt(@self)+3)^ := 0; // set Day=Hour=Min=Sec to 0
if aValue<=0 then begin
PInteger(@self)^ := 0;
exit; // supplied TDateTime value = 0 -> store as null date
end;
DecodeDate(aValue,Y,M,D);
DecodeTime(aValue,HH,MM,SS,MS);
Cent := (Y div 100)+100;
Year := (Y mod 100)+100;
Month := M;
Day := D;
if (HH<>0) or (MM<>0) or (SS<>0) then begin
Hour := HH+1;
Min := MM+1;
Sec := SS+1;
end ;
end;
procedure TOracleDate.From(const aIso8601: RawUTF8);
begin
From(pointer(aIso8601),length(aIso8601));
end;
procedure TOracleDate.From(aIso8601: PUTF8Char; Length: integer);
var Value: QWord;
Value32: cardinal absolute Value;
Y: cardinal;
NoTime: boolean;
begin
PInteger(PtrInt(@self)+3)^ := 0; // set Day=Hour=Min=Sec to 0
Value := Iso8601ToTimeLogPUTF8Char(aIso8601,Length,@NoTime);
if Value=0 then begin
PInteger(@self)^ := 0;
exit; // invalid ISO-8601 text -> store as null date
end;
Y := (Value shr (6+6+5+5+4)) and 4095;
Cent := (Y div 100)+100;
Year := (Y mod 100)+100;
Month := ((Value32 shr (6+6+5+5)) and 15)+1;
Day := ((Value32 shr (6+6+5)) and 31)+1;
if NoTime then
exit;
Hour := ((Value32 shr (6+6)) and 31)+1;
Min := ((Value32 shr 6) and 63)+1;
Sec := (Value32 and 63)+1;
end;
{ Native OCI access interface }
type
{ Generic Oracle Types }
sword = Integer;
eword = Integer;
uword = LongInt;
sb4 = Integer;
ub4 = LongInt;
sb2 = SmallInt;
ub2 = Word;
sb1 = ShortInt;
ub1 = Byte;
dvoid = Pointer;
text = PAnsiChar;
OraText = PAnsiChar;
size_T = Integer;
pub1 = ^ub1;
psb1 = ^sb1;
pub2 = ^ub2;
psb2 = ^sb2;
pub4 = ^ub4;
psb4 = ^sb4;
pdvoid = ^dvoid;
{ Handle Types }
POCIHandle = Pointer;
PPOCIHandle = ^Pointer;
POCIEnv = POCIHandle;
POCIServer = POCIHandle;
POCIError = POCIHandle;
POCISvcCtx = POCIHandle;
POCIStmt = POCIHandle;
POCIDefine = POCIHandle;
POCISession = POCIHandle;
POCIBind = POCIHandle;
POCIDescribe = POCIHandle;
POCITrans = POCIHandle;
{ Descriptor Types }
POCIDescriptor = Pointer;
PPOCIDescriptor = ^POCIDescriptor;
POCISnapshot = POCIDescriptor;
POCILobLocator = POCIDescriptor;
POCIParam = POCIDescriptor;
POCIRowid = POCIDescriptor;
POCIComplexObjectComp = POCIDescriptor;
POCIAQEnqOptions = POCIDescriptor;
POCIAQDeqOptions = POCIDescriptor;
POCIAQMsgProperties = POCIDescriptor;
POCIAQAgent = POCIDescriptor;
POCIDate = POCIDescriptor;
POCIDateTime = POCIDescriptor;
POCIString = POCIDescriptor;
POCIType = POCIDescriptor;
POCIArray = POCIDescriptor;
POCIColl = POCIDescriptor;
/// OCIDuration - OCI object duration
// - A client can specify the duration of which an object is pinned (pin
// duration) and the duration of which the object is in memory (allocation
// duration). If the objects are still pinned at the end of the pin duration,
// the object cache manager will automatically unpin the objects for the
// client. If the objects still exist at the end of the allocation duration,
// the object cache manager will automatically free the objects for the client.
// - Objects that are pinned with the option OCI_DURATION_TRANS will get unpinned
// automatically at the end of the current transaction.
// - Objects that are pinned with the option OCI_DURATION_SESSION will get
// unpinned automatically at the end of the current session (connection).
// - The option OCI_DURATION_NULL is used when the client does not want to set
// the pin duration. If the object is already loaded into the cache, then the
// pin duration will remain the same. If the object is not yet loaded, the
// pin duration of the object will be set to OCI_DURATION_DEFAULT.
OCIDuration = ub2;
/// The OCITypeCode type is interchangeable with the existing SQLT type which is a ub2
OCITypeCode = ub2;
OCITypeGetOpt = (OCI_TYPEGET_HEADER, OCI_TYPEGET_ALL);
const
{ OCI Handle Types }
OCI_HTYPE_FIRST = 1;
OCI_HTYPE_ENV = 1;
OCI_HTYPE_ERROR = 2;
OCI_HTYPE_SVCCTX = 3;
OCI_HTYPE_STMT = 4;
OCI_HTYPE_BIND = 5;
OCI_HTYPE_DEFINE = 6;
OCI_HTYPE_DESCRIBE = 7;
OCI_HTYPE_SERVER = 8;
OCI_HTYPE_SESSION = 9;
OCI_HTYPE_TRANS = 10;
OCI_HTYPE_COMPLEXOBJECT = 11;
OCI_HTYPE_SECURITY = 12;
OCI_HTYPE_SUBSCRIPTION = 13;
OCI_HTYPE_DIRPATH_CTX = 14;
OCI_HTYPE_DIRPATH_COLUMN_ARRAY = 15;
OCI_HTYPE_DIRPATH_STREAM = 16;
OCI_HTYPE_PROC = 17;
OCI_HTYPE_LAST = 17;
{ OCI Descriptor Types }
OCI_DTYPE_FIRST = 50;
OCI_DTYPE_LOB = 50;
OCI_DTYPE_SNAP = 51;
OCI_DTYPE_RSET = 52;
OCI_DTYPE_PARAM = 53;
OCI_DTYPE_ROWID = 54;
OCI_DTYPE_COMPLEXOBJECTCOMP = 55;
OCI_DTYPE_FILE = 56;
OCI_DTYPE_AQENQ_OPTIONS = 57;
OCI_DTYPE_AQDEQ_OPTIONS = 58;
OCI_DTYPE_AQMSG_PROPERTIES = 59;
OCI_DTYPE_AQAGENT = 60;
OCI_DTYPE_LOCATOR = 61;
OCI_DTYPE_DATETIME = 62;
OCI_DTYPE_INTERVAL = 63;
OCI_DTYPE_AQNFY_DESCRIPTOR = 64;
OCI_DTYPE_LAST = 64;
OCI_DTYPE_DATE = 65; { Date }
OCI_DTYPE_TIME = 66; { Time }
OCI_DTYPE_TIME_TZ = 67; { Time with timezone }
OCI_DTYPE_TIMESTAMP = 68; { Timestamp }
OCI_DTYPE_TIMESTAMP_TZ = 69; { Timestamp with timezone }
OCI_DTYPE_TIMESTAMP_LTZ = 70; { Timestamp with local tz }
{ OCI Attributes Types }
OCI_ATTR_FNCODE = 1; // the OCI function code
OCI_ATTR_OBJECT = 2; // is the environment initialized in object mode
OCI_ATTR_NONBLOCKING_MODE = 3; // non blocking mode
OCI_ATTR_SQLCODE = 4; // the SQL verb
OCI_ATTR_ENV = 5; // the environment handle
OCI_ATTR_SERVER = 6; // the server handle
OCI_ATTR_SESSION = 7; // the user session handle
OCI_ATTR_TRANS = 8; // the transaction handle
OCI_ATTR_ROW_COUNT = 9; // the rows processed so far
OCI_ATTR_SQLFNCODE = 10; // the SQL verb of the statement
OCI_ATTR_PREFETCH_ROWS = 11; // sets the number of rows to prefetch
OCI_ATTR_NESTED_PREFETCH_ROWS = 12; // the prefetch rows of nested table
OCI_ATTR_PREFETCH_MEMORY = 13; // memory limit for rows fetched
OCI_ATTR_NESTED_PREFETCH_MEMORY = 14;// memory limit for nested rows
OCI_ATTR_CHAR_COUNT = 15; // this specifies the bind and define size in characters
OCI_ATTR_PDSCL = 16; // packed decimal scale
OCI_ATTR_FSPRECISION = OCI_ATTR_PDSCL; // fs prec for datetime data types
OCI_ATTR_PDPRC = 17; // packed decimal format
OCI_ATTR_LFPRECISION = OCI_ATTR_PDPRC; // fs prec for datetime data types
OCI_ATTR_PARAM_COUNT = 18; // number of column in the select list
OCI_ATTR_ROWID = 19; // the rowid
OCI_ATTR_CHARSET = 20; // the character set value
OCI_ATTR_NCHAR = 21; // NCHAR type
OCI_ATTR_USERNAME = 22; // username attribute
OCI_ATTR_PASSWORD = 23; // password attribute
OCI_ATTR_STMT_TYPE = 24; // statement type
OCI_ATTR_INTERNAL_NAME = 25; // user friendly global name
OCI_ATTR_EXTERNAL_NAME = 26; // the internal name for global txn
OCI_ATTR_XID = 27; // XOPEN defined global transaction id
OCI_ATTR_TRANS_LOCK = 28; //
OCI_ATTR_TRANS_NAME = 29; // string to identify a global transaction
OCI_ATTR_HEAPALLOC = 30; // memory allocated on the heap
OCI_ATTR_CHARSET_ID = 31; // Character Set ID
OCI_ATTR_CHARSET_FORM = 32; // Character Set Form
OCI_ATTR_MAXDATA_SIZE = 33; // Maximumsize of data on the server
OCI_ATTR_CACHE_OPT_SIZE = 34; // object cache optimal size
OCI_ATTR_CACHE_MAX_SIZE = 35; // object cache maximum size percentage
OCI_ATTR_PINOPTION = 36; // object cache default pin option
OCI_ATTR_ALLOC_DURATION = 37; // object cache default allocation duration
OCI_ATTR_PIN_DURATION = 38; // object cache default pin duration
OCI_ATTR_FDO = 39; // Format Descriptor object attribute
OCI_ATTR_POSTPROCESSING_CALLBACK = 40; // Callback to process outbind data
OCI_ATTR_POSTPROCESSING_CONTEXT = 41; // Callback context to process outbind data
OCI_ATTR_ROWS_RETURNED = 42; // Number of rows returned in current iter - for Bind handles
OCI_ATTR_FOCBK = 43; // Failover Callback attribute
OCI_ATTR_IN_V8_MODE = 44; // is the server/service context in V8 mode
OCI_ATTR_LOBEMPTY = 45; // empty lob ?
OCI_ATTR_SESSLANG = 46; // session language handle
OCI_ATTR_VISIBILITY = 47; // visibility
OCI_ATTR_RELATIVE_MSGID = 48; // relative message id
OCI_ATTR_SEQUENCE_DEVIATION = 49; // sequence deviation
OCI_ATTR_CONSUMER_NAME = 50; // consumer name
OCI_ATTR_DEQ_MODE = 51; // dequeue mode
OCI_ATTR_NAVIGATION = 52; // navigation
OCI_ATTR_WAIT = 53; // wait
OCI_ATTR_DEQ_MSGID = 54; // dequeue message id
OCI_ATTR_PRIORITY = 55; // priority
OCI_ATTR_DELAY = 56; // delay
OCI_ATTR_EXPIRATION = 57; // expiration
OCI_ATTR_CORRELATION = 58; // correlation id
OCI_ATTR_ATTEMPTS = 59; // # of attempts
OCI_ATTR_RECIPIENT_LIST = 60; // recipient list
OCI_ATTR_EXCEPTION_QUEUE = 61; // exception queue name
OCI_ATTR_ENQ_TIME = 62; // enqueue time (only OCIAttrGet)
OCI_ATTR_MSG_STATE = 63; // message state (only OCIAttrGet)
// NOTE: 64-66 used below
OCI_ATTR_AGENT_NAME = 64; // agent name
OCI_ATTR_AGENT_ADDRESS = 65; // agent address
OCI_ATTR_AGENT_PROTOCOL = 66; // agent protocol
OCI_ATTR_SENDER_ID = 68; // sender id
OCI_ATTR_ORIGINAL_MSGID = 69; // original message id
OCI_ATTR_QUEUE_NAME = 70; // queue name
OCI_ATTR_NFY_MSGID = 71; // message id
OCI_ATTR_MSG_PROP = 72; // message properties
OCI_ATTR_NUM_DML_ERRORS = 73; // num of errs in array DML
OCI_ATTR_DML_ROW_OFFSET = 74; // row offset in the array
OCI_ATTR_DATEFORMAT = 75; // default date format string
OCI_ATTR_BUF_ADDR = 76; // buffer address
OCI_ATTR_BUF_SIZE = 77; // buffer size
OCI_ATTR_DIRPATH_MODE = 78; // mode of direct path operation
OCI_ATTR_DIRPATH_NOLOG = 79; // nologging option
OCI_ATTR_DIRPATH_PARALLEL = 80; // parallel (temp seg) option
OCI_ATTR_NUM_ROWS = 81; // number of rows in column array
// NOTE that OCI_ATTR_NUM_COLS is a column
// array attribute too.
OCI_ATTR_COL_COUNT = 82; // columns of column array processed so far.
OCI_ATTR_STREAM_OFFSET = 83; // str off of last row processed
OCI_ATTR_SHARED_HEAPALLOC = 84; // Shared Heap Allocation Size
OCI_ATTR_SERVER_GROUP = 85; // server group name
OCI_ATTR_MIGSESSION = 86; // migratable session attribute
OCI_ATTR_NOCACHE = 87; // Temporary LOBs
OCI_ATTR_MEMPOOL_SIZE = 88; // Pool Size
OCI_ATTR_MEMPOOL_INSTNAME = 89; // Instance name
OCI_ATTR_MEMPOOL_APPNAME = 90; // Application name
OCI_ATTR_MEMPOOL_HOMENAME = 91; // Home Directory name
OCI_ATTR_MEMPOOL_MODEL = 92; // Pool Model (proc,thrd,both)
OCI_ATTR_MODES = 93; // Modes
OCI_ATTR_SUBSCR_NAME = 94; // name of subscription
OCI_ATTR_SUBSCR_CALLBACK = 95; // associated callback
OCI_ATTR_SUBSCR_CTX = 96; // associated callback context
OCI_ATTR_SUBSCR_PAYLOAD = 97; // associated payload
OCI_ATTR_SUBSCR_NAMESPACE = 98; // associated namespace
OCI_ATTR_PROXY_CREDENTIALS = 99; // Proxy user credentials
OCI_ATTR_INITIAL_CLIENT_ROLES = 100; // Initial client role list
OCI_ATTR_UNK = 101; // unknown attribute
OCI_ATTR_NUM_COLS = 102; // number of columns
OCI_ATTR_LIST_COLUMNS = 103; // parameter of the column list
OCI_ATTR_RDBA = 104; // DBA of the segment header
OCI_ATTR_CLUSTERED = 105; // whether the table is clustered
OCI_ATTR_PARTITIONED = 106; // whether the table is partitioned
OCI_ATTR_INDEX_ONLY = 107; // whether the table is index only
OCI_ATTR_LIST_ARGUMENTS = 108; // parameter of the argument list
OCI_ATTR_LIST_SUBPROGRAMS = 109; // parameter of the subprogram list
OCI_ATTR_REF_TDO = 110; // REF to the type descriptor
OCI_ATTR_LINK = 111; // the database link name
OCI_ATTR_MIN = 112; // minimum value
OCI_ATTR_MAX = 113; // maximum value
OCI_ATTR_INCR = 114; // increment value
OCI_ATTR_CACHE = 115; // number of sequence numbers cached
OCI_ATTR_ORDER = 116; // whether the sequence is ordered
OCI_ATTR_HW_MARK = 117; // high-water mark
OCI_ATTR_TYPE_SCHEMA = 118; // type's schema name
OCI_ATTR_TIMESTAMP = 119; // timestamp of the object
OCI_ATTR_NUM_ATTRS = 120; // number of sttributes
OCI_ATTR_NUM_PARAMS = 121; // number of parameters
OCI_ATTR_OBJID = 122; // object id for a table or view
OCI_ATTR_PTYPE = 123; // type of info described by
OCI_ATTR_PARAM = 124; // parameter descriptor
OCI_ATTR_OVERLOAD_ID = 125; // overload ID for funcs and procs
OCI_ATTR_TABLESPACE = 126; // table name space
OCI_ATTR_TDO = 127; // TDO of a type
OCI_ATTR_LTYPE = 128; // list type
OCI_ATTR_PARSE_ERROR_OFFSET = 129; // Parse Error offset
OCI_ATTR_IS_TEMPORARY = 130; // whether table is temporary
OCI_ATTR_IS_TYPED = 131; // whether table is typed
OCI_ATTR_DURATION = 132; // duration of temporary table
OCI_ATTR_IS_INVOKER_RIGHTS = 133; // is invoker rights
OCI_ATTR_OBJ_NAME = 134; // top level schema obj name
OCI_ATTR_OBJ_SCHEMA = 135; // schema name
OCI_ATTR_OBJ_ID = 136; // top level schema object id
OCI_ATTR_STMTCACHESIZE = 176; // size of the stm cache
OCI_ATTR_ROWS_FETCHED = 197; // rows fetched in last call
OCI_ATTR_DEFAULT_LOBPREFETCH_SIZE = 438; // default prefetch size
{ OCI Error Return Values }
OCI_SUCCESS = 0;
OCI_SUCCESS_WITH_INFO = 1;
OCI_NO_DATA = 100;
OCI_ERROR = -1;
OCI_INVALID_HANDLE = -2;
OCI_NEED_DATA = 99;
OCI_STILL_EXECUTING = -3123;
OCI_CONTINUE = -24200;
OCI_PASSWORD_INFO = 28002; // the password will expire within ... days
{ Generic Default Value for Modes, .... }
OCI_DEFAULT = $0;
{ OCI Init Mode }
OCI_THREADED = $1;
OCI_OBJECT = $2;
OCI_EVENTS = $4;
OCI_SHARED = $10;
OCI_NO_UCB = $40;
OCI_NO_MUTEX = $80;
{ fixed Client Character Set }
OCI_UTF8 = $367;
OCI_AL32UTF8 = $369;
OCI_UTF16ID = 1000;
OCI_WE8MSWIN1252 = 178;
{ OCI Credentials }
OCI_CRED_RDBMS = 1;
OCI_CRED_EXT = 2;
OCI_CRED_PROXY = 3;
{ OCI Authentication Mode }
OCI_MIGRATE = $0001; // migratable auth context
OCI_SYSDBA = $0002; // for SYSDBA authorization
OCI_SYSOPER = $0004; // for SYSOPER authorization
OCI_PRELIM_AUTH = $0008; // for preliminary authorization
{ OCIPasswordChange }
OCI_AUTH = $08; // Change the password but do not login
{ OCI Data Types }
SQLT_CHR = 1;
SQLT_NUM = 2;
SQLT_INT = 3;
SQLT_FLT = 4;
SQLT_STR = 5;
SQLT_VNU = 6;
SQLT_PDN = 7;
SQLT_LNG = 8;
SQLT_VCS = 9;
SQLT_NON = 10;
SQLT_RID = 11;
SQLT_DAT = 12;
SQLT_VBI = 15;
SQLT_BFLOAT = 21;
SQLT_BDOUBLE = 22;
SQLT_BIN = 23;
SQLT_LBI = 24;
_SQLT_PLI = 29;
SQLT_UIN = 68;
SQLT_SLS = 91;
SQLT_LVC = 94;
SQLT_LVB = 95;
SQLT_AFC = 96;
SQLT_AVC = 97;
SQLT_IBFLOAT = 100;
SQLT_IBDOUBLE = 101;
SQLT_CUR = 102;
SQLT_RDD = 104;
SQLT_LAB = 105;
SQLT_OSL = 106;
SQLT_NTY = 108;
SQLT_REF = 110;
SQLT_CLOB = 112;
SQLT_BLOB = 113;
SQLT_BFILEE = 114;
SQLT_CFILEE = 115;
SQLT_RSET = 116;
SQLT_NCO = 122;
SQLT_VST = 155;
SQLT_ODT = 156;
SQLT_DATE = 184;
SQLT_TIME = 185;
SQLT_TIME_TZ = 186;
SQLT_TIMESTAMP = 187;
SQLT_TIMESTAMP_TZ = 188;
SQLT_INTERVAL_YM = 189;
SQLT_INTERVAL_DS = 190;
SQLT_TIMESTAMP_LTZ = 232;
_SQLT_REC = 250;
_SQLT_TAB = 251;
_SQLT_BOL = 252;
{ OCI Statement Types }
OCI_STMT_SELECT = 1; // select statement
OCI_STMT_UPDATE = 2; // update statement
OCI_STMT_DELETE = 3; // delete statement
OCI_STMT_INSERT = 4; // Insert Statement
OCI_STMT_CREATE = 5; // create statement
OCI_STMT_DROP = 6; // drop statement
OCI_STMT_ALTER = 7; // alter statement
OCI_STMT_BEGIN = 8; // begin ... (pl/sql statement)
OCI_STMT_DECLARE = 9; // declare .. (pl/sql statement)
{ OCI Statement language }
OCI_NTV_SYNTAX = 1; // Use what so ever is the native lang of server
OCI_V7_SYNTAX = 2; // V7 language
OCI_V8_SYNTAX = 3; // V8 language
{ OCI Statement Execute mode }
OCI_BATCH_MODE = $01; // batch the oci statement for execution
OCI_EXACT_FETCH = $02; // fetch the exact rows specified
OCI_SCROLLABLE_CURSOR = $08; // cursor scrollable
OCI_DESCRIBE_ONLY = $10; // only describe the statement
OCI_COMMIT_ON_SUCCESS = $20; // commit, if successful execution
OCI_NON_BLOCKING = $40; // non-blocking
OCI_BATCH_ERRORS = $80; // batch errors in array dmls