Melnick J., Smith T., Gregolre J.
Table of contents :
Send Us Your Comments……Page 29
Preface……Page 31
1 Introduction……Page 41
What is an Oracle Precompiler?……Page 42
Why Use SQL……Page 43
Pro*C/C++ Precompiler Benefits……Page 44
Why Not Code Using SQLLIB Calls and Not Use Pro*C/C++?……Page 47
I Am Confused By Character Handling in Pro*C/C++…….Page 48
How Can I Compile and Link My Application?……Page 49
When My Application Runs Under Oracle9i, I Keep Getting an Ora-1405 Error (Fetched Column Value I………Page 50
Are All SQLLIB Functions Private?……Page 51
How Does Oracle9i Support The New Object Types?……Page 52
2 Precompiler Concepts……Page 53
Embedded SQL Statements……Page 54
Embedded SQL Syntax……Page 56
Host and Indicator Variables……Page 57
Oracle Datatypes……Page 58
Transactions……Page 59
Steps in Developing an Embedded SQL Application……Page 60
Declare Section……Page 62
Delimiters……Page 63
Function Prototyping……Page 64
Line Length……Page 65
Operators……Page 66
Symbol Definition……Page 67
Guidelines……Page 68
Compile and Link……Page 69
Example Data……Page 70
Example Program: A Simple Query……Page 71
3 Database Concepts……Page 75
Connect to the Database……Page 76
Using the ALTER AUTHORIZATION Clause to Change Passwords……Page 77
Automatic Connects……Page 78
Concurrent Logons……Page 80
Default Databases and Connections……Page 81
Explicit Connections……Page 82
Implicit Connections……Page 88
Definitions of Transactions Terms……Page 89
How Transactions Guard Your Database……Page 90
How to Begin and End Transactions……Page 91
Using the COMMIT Statement……Page 92
Using the SAVEPOINT Statement……Page 93
The ROLLBACK Statement……Page 94
Statement-Level Rollbacks……Page 96
The SET TRANSACTION Statement……Page 97
Using FOR UPDATE OF……Page 98
Using LOCK TABLE……Page 99
Distributed Transactions Handling……Page 100
Guidelines……Page 101
Using PL/SQL……Page 102
4 Datatypes and Host Variables……Page 103
Internal Datatypes……Page 104
External Datatypes……Page 105
Additional External Datatypes……Page 114
Host Variables……Page 116
Host Variable Declaration……Page 117
Host Variable Referencing……Page 120
The INDICATOR Keyword……Page 121
Example of INDICATOR Variable Usage……Page 122
VARCHAR Variables……Page 123
VARCHAR Variable Declaration……Page 124
VARCHAR Variable Referencing……Page 125
Pass VARCHAR Variables to a Function……Page 126
Find the Length of the VARCHAR Array Component……Page 127
Example Program: Using sqlvcp()……Page 128
Cursor Variables……Page 131
Declare a Cursor Variable……Page 132
Open a Cursor Variable……Page 133
Cursor Variables with the OCI (Release 7 Only)……Page 136
Restrictions……Page 138
Example: cv_demo.sql and sample11.pc……Page 139
CONTEXT Variables……Page 142
Universal ROWIDs……Page 144
Host Structures……Page 146
Host Structures and Arrays……Page 147
Nested Structures and Unions……Page 148
Host Indicator Structures……Page 149
Example Program: Cursor and a Host Structure……Page 150
Pointer Variable Referencing……Page 153
Structure Pointers……Page 154
Globalization Support……Page 155
CHARACTER SET [IS] NCHAR_CS……Page 157
CONVBUFSZ Clause in VAR……Page 158
Indicator Variables……Page 159
5 Advanced Topics……Page 161
Precompiler Option CHAR_MAP……Page 162
Effect of the DBMS and CHAR_MAP Options……Page 163
VARCHAR Variables and Pointers……Page 168
Unicode Variables……Page 169
Datatype Equivalencing……Page 172
Host Variable Equivalencing……Page 173
User-Defined Type Equivalencing……Page 174
The EXEC SQL VAR and TYPE Directives……Page 175
Example: Datatype Equivalencing (sample4.pc):……Page 176
How the Pro*C/C++ Preprocessor Works……Page 189
Preprocessor Directives……Page 190
ORA_PROC Macro……Page 191
Some Preprocessor Examples……Page 192
Include the SQLCA, ORACA, and SQLDA……Page 194
Include Files……Page 196
Precompiled Header File Creation……Page 197
Use of the Precompiled Header Files……Page 198
Examples……Page 199
Effects of Options……Page 201
Usage Notes……Page 203
Symbol Definition……Page 204
Evaluation of Numeric Constants……Page 205
Numeric Constants in Pro*C/C++……Page 206
Runtime Context in the OCI Release 8 Environment……Page 207
Interface to OCI Release 8……Page 208
SQLEnvGet()……Page 209
Embedded OCI Release 8 Calls……Page 210
Embedded OCI Release 7 Calls……Page 212
Remote and Multiple Connections……Page 213
New Names for SQLLIB Public Functions……Page 214
X/Open Application Development……Page 216
Oracle-Specific Issues……Page 218
6 Embedded SQL……Page 221
Output versus Input Host Variables……Page 222
Indicator Variables……Page 223
Insert NULLs……Page 224
Fetch NULLs……Page 225
The Basic SQL Statements……Page 226
The SELECT Statement……Page 228
The INSERT Statement……Page 229
The UPDATE Statement……Page 230
The WHERE Clause……Page 231
Cursors……Page 232
The DECLARE CURSOR Statement……Page 233
The OPEN Statement……Page 234
The FETCH Statement……Page 235
The CLOSE_ON_COMMIT Precompiler Option……Page 236
The PREFETCH Precompiler Option……Page 237
The CURRENT OF Clause……Page 239
Restrictions……Page 240
A Complete Example……Page 241
Positioned Update……Page 243
7 Embedded PL/SQL……Page 245
Cursor FOR Loops……Page 246
Procedures and Functions……Page 247
PL/SQL Tables……Page 248
User-Defined Records……Page 249
Embedded PL/SQL Blocks……Page 250
Example: Using Host Variables with PL/SQL……Page 251
Complex Example……Page 253
VARCHAR Pseudotype……Page 255
Indicator Variables……Page 256
NULLs Handling……Page 257
Host Arrays……Page 258
ARRAYLEN Statement……Page 261
Optional Keyword EXECUTE……Page 263
Cursor Usage in Embedded PL/SQL……Page 264
Stored PL/SQL and Java Subprograms……Page 265
Creating Stored Subprograms……Page 266
Calling a Stored PL/SQL or Java Subprogram……Page 268
External Procedures……Page 274
Restrictions on External Procedures……Page 275
Creating the External Procedure……Page 276
SQLExtProcError()……Page 277
Using Dynamic SQL……Page 278
8 Host Arrays……Page 279
Maximum Size of Arrays……Page 280
Referencing Host Arrays……Page 281
ANSI Restriction and Requirements……Page 282
Cursor Fetches……Page 283
Using sqlca.sqlerrd[2]……Page 284
Number of Rows Fetched……Page 285
Sample Program 3: Host Arrays……Page 286
Host Array Restrictions……Page 288
Inserting with Arrays……Page 289
Updating with Arrays……Page 290
Deleting with Arrays……Page 291
Using the FOR Clause……Page 292
FOR Clause Restrictions……Page 293
Using the WHERE Clause……Page 294
Arrays of Structs……Page 295
Restrictions on Arrays of Structs……Page 296
Declaring an Array of Structs……Page 297
Variables Guidelines……Page 298
Declaring a Pointer to an Array of Structs……Page 299
Examples……Page 300
Mimicking CURRENT OF……Page 306
9 Handling Runtime Errors……Page 309
Status Variables……Page 310
The SQL Communications Area……Page 311
The SQLSTATE Status Variable……Page 312
SQLSTATE Values……Page 313
Declaring SQLCODE……Page 322
Warning Flags……Page 323
Parse Error Offsets……Page 324
Declaring the SQLCA……Page 325
SQLCA Contents……Page 326
SQLCA Structure……Page 328
PL/SQL Considerations……Page 331
Getting the Full Text of Error Messages……Page 332
Using the WHENEVER Directive……Page 333
WHENEVER Actions……Page 334
WHENEVER Examples……Page 335
Use of DO BREAK and DO CONTINUE……Page 337
Guidelines for WHENEVER……Page 338
Obtaining the Text of SQL Statements……Page 341
Using the Oracle Communications Area (ORACA)……Page 344
ORACA Contents……Page 345
Choosing Runtime Options……Page 347
Structure of the ORACA……Page 348
ORACA Example……Page 351
10 Precompiler Options……Page 355
The Precompiler Command……Page 356
Precompiler Options……Page 357
Precedence of Option Values……Page 358
Macro and Micro Options……Page 360
Quick Reference……Page 361
Inline……Page 364
AUTO_CONNECT……Page 366
CLOSE_ON_COMMIT……Page 367
CODE……Page 368
COMP_CHARSET……Page 369
CPP_SUFFIX……Page 370
DBMS……Page 371
DEF_SQLCODE……Page 372
DEFINE……Page 373
DYNAMIC……Page 375
ERRTYPE……Page 376
FIPS……Page 377
HEADER……Page 378
HOLD_CURSOR……Page 379
INAME……Page 380
INCLUDE……Page 381
LINES……Page 382
LNAME……Page 383
LTYPE……Page 384
MAXOPENCURSORS……Page 385
MODE……Page 386
NLS_CHAR……Page 387
NLS_LOCAL……Page 388
ONAME……Page 389
PAGELEN……Page 390
PARSE……Page 391
PREFETCH……Page 392
RELEASE_CURSOR……Page 393
SQLCHECK……Page 394
SYS_INCLUDE……Page 395
THREADS……Page 396
UNSAFE_NULL……Page 397
UTF16_CHARSET……Page 398
VARCHAR……Page 399
VERSION……Page 400
11 Shared Server Applications……Page 401
What are Threads?……Page 402
Runtime Contexts in Pro*C/C++……Page 403
Runtime Context Usage Models……Page 404
Multiple Threads Sharing a Single Runtime Context……Page 405
Multiple Threads Sharing Multiple Runtime Contexts……Page 406
Embedded SQL Statements and Directives……Page 407
CONTEXT USE Examples……Page 410
Shared Server Example……Page 412
12 C++ Applications……Page 421
No Special Macro Processing……Page 422
Code Generation……Page 423
Parsing Code……Page 424
System Header Files……Page 425
cppdemo1.pc……Page 426
cppdemo2.pc……Page 429
cppdemo3.pc……Page 433
13 Oracle Dynamic SQL……Page 437
When to Use Dynamic SQL……Page 438
Requirements for Dynamic SQL Statements……Page 439
Methods for Using Dynamic SQL……Page 440
Method 2……Page 441
Guidelines……Page 442
Using Method 1……Page 444
Example Program: Dynamic SQL Method 1……Page 445
Using Method 2……Page 449
The USING Clause……Page 450
Example Program: Dynamic SQL Method 2……Page 451
PREPARE……Page 455
OPEN……Page 456
Example Program: Dynamic SQL Method 3……Page 457
Using Method 4……Page 461
The DESCRIBE Statement……Page 462
What is a SQLDA?……Page 463
Implementing Oracle Method 4……Page 464
Using Host Arrays……Page 465
With Method 2……Page 466
With Oracle Method 4……Page 467
14 ANSI Dynamic SQL……Page 469
Precompiler Options……Page 470
Overview of ANSI SQL Statements……Page 471
Example Code……Page 474
Oracle Extensions……Page 475
Reference Semantics……Page 476
Using Arrays for Bulk Operations……Page 477
Support for Object Types……Page 479
ANSI Dynamic SQL Precompiler Options……Page 480
ALLOCATE DESCRIPTOR……Page 481
DEALLOCATE DESCRIPTOR……Page 482
GET DESCRIPTOR……Page 483
SET DESCRIPTOR……Page 487
DESCRIBE INPUT……Page 491
DESCRIBE OUTPUT……Page 492
EXECUTE……Page 493
Use of EXECUTE IMMEDIATE……Page 494
OPEN Cursor……Page 495
FETCH……Page 496
CLOSE a Dynamic Cursor……Page 497
Restrictions……Page 498
ansidyn1.pc……Page 499
ansidyn2.pc……Page 507
15 Oracle Dynamic SQL: Method 4……Page 519
What Information Does Oracle Need?……Page 520
How is the SQLDA Referenced?……Page 521
Purpose of the SQLDA……Page 522
Declaring a SQLDA……Page 523
Allocating a SQLDA……Page 524
The N Variable……Page 525
The L Variable……Page 526
The T Variable……Page 527
The I Variable……Page 528
The X Variable……Page 529
Converting Data……Page 530
Coercing Datatypes……Page 533
Handling NULL/Not NULL Datatypes……Page 536
The Basic Steps……Page 537
A Closer Look at Each Step……Page 538
Declare the SQLDAs……Page 539
Set the Maximum Number to DESCRIBE……Page 540
DESCRIBE the Bind Variables……Page 543
Get Values and Allocate Storage for Bind Variables……Page 546
DESCRIBE the Select List……Page 548
Reset Length/Datatype of Each Select-list Item……Page 550
Get and Process Select-List Values……Page 553
CLOSE the Cursor……Page 555
Using Host Arrays……Page 556
sample12.pc……Page 558
Example Program: Dynamic SQL Method 4……Page 559
16 Large Objects (LOBs)……Page 575
Security for BFILEs……Page 576
Temporary LOBs……Page 577
LOB Buffering Subsystem……Page 578
Three Ways to Access LOBs……Page 579
LOB Locators in Your Application……Page 581
Initializing a LOB……Page 582
For All LOB Statements……Page 584
For the LOB Buffering Subsystem……Page 585
For Host Variables……Page 586
APPEND……Page 587
ASSIGN……Page 588
COPY……Page 589
CREATE TEMPORARY……Page 590
DISABLE BUFFERING……Page 591
ERASE……Page 592
FILE CLOSE ALL……Page 593
FLUSH BUFFER……Page 594
FREE TEMPORARY……Page 595
LOAD FROM FILE……Page 596
OPEN……Page 597
READ……Page 598
WRITE……Page 600
DESCRIBE……Page 602
Navigational Interface Example……Page 605
READ a BLOB, Write a File Example……Page 607
Read a File, WRITE a BLOB Example……Page 609
lobdemo1.pc……Page 611
17 Objects……Page 627
REFs to Object Types……Page 628
Type Inheritance……Page 629
NULL Indicators……Page 630
Associative Interface……Page 631
ALLOCATE……Page 632
Accessing Objects Using the Associative Interface……Page 633
Navigational Interface……Page 635
When to Use the Navigational Interface……Page 636
OBJECT CREATE……Page 637
OBJECT DEREF……Page 638
OBJECT DELETE……Page 639
Navigational Access to Objects……Page 640
OBJECT SET……Page 643
OBJECT GET……Page 644
CONTEXT OBJECT OPTION SET……Page 646
New Precompiler Options for Objects……Page 647
DURATION……Page 648
INTYPE……Page 649
Type Checking at Runtime……Page 650
Associative Access……Page 651
Navigational Access……Page 652
Example Code for Type Inheritance……Page 653
Example Code for Navigational Access……Page 663
Using C Structures……Page 671
Generating a C Structure for a REF……Page 672
Using OCIDate, OCIString, OCINumber, and OCIRaw……Page 673
Summarizing the New Database Types in Pro*C/C++……Page 674
Restrictions on Using Oracle Datatypes in Dynamic SQL……Page 677
18 Collections……Page 679
Nested Tables……Page 680
C and Collections……Page 681
Manipulating Collections……Page 682
Rules for Access……Page 683
OBJECT GET and SET……Page 684
COLLECTION GET……Page 686
COLLECTION SET……Page 688
COLLECTION APPEND……Page 690
COLLECTION TRIM……Page 691
COLLECTION DESCRIBE……Page 692
Type and Table Creation……Page 695
GET and SET Example……Page 697
DESCRIBE Example……Page 699
RESET Example……Page 701
Example Program:coldemo1.pc……Page 702
19 The Object Type Translator……Page 715
What is the Object Type Translator……Page 716
Invoking OTT……Page 719
The OTT Command Line……Page 720
The INTYPE File……Page 722
OTT Datatype Mappings……Page 724
NULL Indicator Structs……Page 730
OTT Support for Type Inheritance……Page 731
The OUTTYPE File……Page 734
Using OTT with OCI Applications……Page 735
Accessing and Manipulating Objects with OCI……Page 737
Calling the Initialization Function……Page 738
Using OTT with Pro*C/C++ Applications……Page 740
OTT Reference……Page 743
OTT Command Line Syntax……Page 744
OTT Parameters……Page 745
Where OTT Parameters Can Appear……Page 749
Structure of the INTYPE File……Page 750
Nested #include File Generation……Page 752
SCHEMA_NAMES Usage……Page 754
Default Name Mapping……Page 756
Restriction……Page 758
20 User Exits……Page 759
What Is a User Exit?……Page 760
Developing a User Exit……Page 761
The IAF GET Statement……Page 762
The IAF PUT Statement……Page 764
Passing Parameters to a User Exit……Page 765
Using the SQLIEM Function……Page 766
An Example……Page 767
Example Program: A User Exit……Page 768
Using the GENXTB Utility……Page 770
Guidelines……Page 771
Issuing Commands……Page 772
EXEC TOOLS SET……Page 773
EXEC TOOLS SET CONTEXT……Page 774
EXEC TOOLS MESSAGE……Page 775
A New Features……Page 777
Precompiled Header Files……Page 778
New Names for SQLLIB Functions……Page 779
ANSI Dynamic SQL……Page 780
Calling Java from PL/SQL……Page 781
Error Message Codes……Page 782
Migration From Earlier Releases……Page 783
B Reserved Words, Keywords, and Namespaces……Page 785
Reserved Words and Keywords……Page 786
Oracle Reserved Namespaces……Page 788
C Performance Tuning……Page 791
How Can Performance Be Improved?……Page 792
Using Embedded PL/SQL……Page 793
Optimizer Hints……Page 795
Taking Advantage of Row-Level Locking……Page 796
Handling Explicit Cursors……Page 797
Using the Cursor Management Options……Page 799
Avoiding Unnecessary Reparsing……Page 803
D Syntactic and Semantic Checking……Page 806
Controlling the Type and Extent of Checking……Page 807
Specifying SQLCHECK=SEMANTICS……Page 808
Enabling a Semantic Check……Page 809
Specifying SQLCHECK=SYNTAX……Page 810
Entering the SQLCHECK Option……Page 811
E System-Specific References……Page 813
Byte Ordering……Page 814
User Exits……Page 815
F Embedded SQL Statements and Directives……Page 817
Summary of Precompiler Directives and Embedded SQL Statements……Page 821
About The Statement Descriptions……Page 824
How to Read Syntax Diagrams……Page 825
Optional Keywords and Parameters……Page 826
Oracle Names……Page 827
ALLOCATE (Executable Embedded SQL Extension)……Page 828
ALLOCATE DESCRIPTOR (Executable Embedded SQL)……Page 830
CACHE FREE ALL (Executable Embedded SQL Extension)……Page 831
CALL (Executable Embedded SQL)……Page 832
CLOSE (Executable Embedded SQL)……Page 834
COLLECTION APPEND (Executable Embedded SQL Extension)……Page 835
COLLECTION DESCRIBE (Executable Embedded SQL Extension)……Page 836
COLLECTION GET (Executable Embedded SQL Extension)……Page 837
COLLECTION RESET (Executable Embedded SQL Extension)……Page 838
COLLECTION TRIM (Executable Embedded SQL Extension)……Page 839
COMMIT (Executable Embedded SQL)……Page 840
CONNECT (Executable Embedded SQL Extension)……Page 842
CONTEXT ALLOCATE (Executable Embedded SQL Extension)……Page 844
CONTEXT FREE (Executable Embedded SQL Extension)……Page 845
CONTEXT OBJECT OPTION GET (Executable Embedded SQL Extension)……Page 846
CONTEXT OBJECT OPTION SET (Executable Embedded SQL Ext)……Page 848
CONTEXT USE (Oracle Embedded SQL Directive)……Page 849
DEALLOCATE DESCRIPTOR (Embedded SQL Statement)……Page 850
DECLARE CURSOR (Embedded SQL Directive)……Page 851
DECLARE DATABASE (Oracle Embedded SQL Directive)……Page 853
DECLARE STATEMENT (Embedded SQL Directive)……Page 855
DECLARE TABLE (Oracle Embedded SQL Directive)……Page 856
DECLARE TYPE (Oracle Embedded SQL Directive)……Page 858
DELETE (Executable Embedded SQL)……Page 859
DESCRIBE (Executable Embedded SQL Extension)……Page 863
DESCRIBE DESCRIPTOR (Executable Embedded SQL)……Page 865
ENABLE THREADS (Executable Embedded SQL Extension)……Page 866
EXECUTE … END-EXEC (Executable Embedded SQL Extension)……Page 867
EXECUTE (Executable Embedded SQL)……Page 869
EXECUTE DESCRIPTOR (Executable Embedded SQL)……Page 871
EXECUTE IMMEDIATE (Executable Embedded SQL)……Page 873
FETCH (Executable Embedded SQL)……Page 874
FETCH DESCRIPTOR (Executable Embedded SQL)……Page 877
FREE (Executable Embedded SQL Extension)……Page 879
GET DESCRIPTOR (Executable Embedded SQL)……Page 880
INSERT (Executable Embedded SQL)……Page 884
LOB APPEND (Executable Embedded SQL Extension)……Page 887
LOB CLOSE (Executable Embedded SQL Extension)……Page 888
LOB CREATE TEMPORARY (Executable Embedded SQL Extension)……Page 889
LOB DESCRIBE (Executable Embedded SQL Extension)……Page 890
LOB DISABLE BUFFERING (Executable Embedded SQL Extension)……Page 891
LOB ERASE (Executable Embedded SQL Extension)……Page 892
LOB FILE CLOSE ALL (Executable Embedded SQL Extension)……Page 893
LOB FLUSH BUFFER (Executable Embedded SQL Extension)……Page 894
LOB LOAD (Executable Embedded SQL Extension)……Page 895
LOB OPEN (Executable Embedded SQL Extension)……Page 896
LOB TRIM (Executable Embedded SQL Extension)……Page 897
LOB WRITE (Executable Embedded SQL Extension)……Page 898
OBJECT CREATE (Executable Embedded SQL Extension)……Page 899
OBJECT DELETE (Executable Embedded SQL Extension)……Page 900
OBJECT DEREF (Executable Embedded SQL Extension)……Page 901
OBJECT FLUSH (Executable Embedded SQL Extension)……Page 902
OBJECT GET (Executable Embedded SQL Extension)……Page 903
OBJECT RELEASE (Executable Embedded SQL Extension)……Page 905
OBJECT SET (Executable Embedded SQL Extension)……Page 906
OBJECT UPDATE (Executable Embedded SQL Extension)……Page 907
OPEN (Executable Embedded SQL)……Page 908
OPEN DESCRIPTOR (Executable Embedded SQL)……Page 910
PREPARE (Executable Embedded SQL)……Page 912
REGISTER CONNECT (Executable Embedded SQL Extension)……Page 914
ROLLBACK (Executable Embedded SQL)……Page 915
SAVEPOINT (Executable Embedded SQL)……Page 919
SELECT (Executable Embedded SQL)……Page 920
SET DESCRIPTOR (Executable Embedded SQL)……Page 923
TYPE (Oracle Embedded SQL Directive)……Page 927
UPDATE (Executable Embedded SQL)……Page 928
VAR (Oracle Embedded SQL Directive)……Page 932
WHENEVER (Embedded SQL Directive)……Page 935
Index……Page 939
Reviews
There are no reviews yet.