Tutorial Downloads .com

Home

Oracle Rdb 7 Guide to SQL Programming - pdf tutorial

This manual describes how to design and develop host language application programs that use SQL (structured query language) to store, modify, and retrieve data from Oracle Rdb databases.

Contents of this Oracle RDB tutorial

1 Introduction to SQL Programming
1.1 What Are the Two SQL Programming Interfaces?.. . . . . 1–1
1.1.1 SQL Module Processor..... . 1–2
1.1.2 SQL Precompiler..... . . . . . 1–3
1.2 Choosing a Programming Interface.... . 1–3
1.3 Finding Online Program Examples.... . 1–5
Part II Developing Application Programs That Use SQL
2 SQL Program Development Cycle
2.1 Overview of the Application Program Development Cycle. . . . . . 2–1
2.2 Understanding End-User Requirements... . . . . 2–2
2.3 Investigating Metadata and Data..... . . 2–3
2.4 Developing a Prototype..... . . . 2–4
2.5 Converting the Prototype to an Application Program.. . . 2–5
2.6 Developing an Application Program.... . 2–6
3 Introduction to SQL Module Language
3.1 Developing SQL Module Language Application Programs: Basic
Steps....... . . . 3–1
3.2 Creating an SQL Module Source File.... 3–3
3.2.1 Including Blank Lines and Comments in an SQL Module. . . 3–6
3.2.2 Naming a Module..... . . . . . 3–6
3.2.3 Specifying the Dialect..... . 3–7
3.2.4 Specifying Character Sets for a Session... . . 3–8
3.2.5 Identifying the Host Language That Calls Module Procedures . . . . . 3–8
3.2.6 Specifying the Catalog..... . 3–9
3.2.7 Specifying the Schema..... . 3–10
3.2.8 Specifying an Authorization Identifier.... . . . 3–10
3.2.9 Specifying the Alias..... . . . 3–11
3.2.10 Specifying That Parameters Must Include Colons.. . 3–11
3.2.11 Specifying DECLARE Statements in Modules.. . . . . 3–12
3.3 Calling SQL Module Procedures from a Host Language Program3–13
3.4 Writing Portable Applications Using the SQL Module Processor. 3–15
3.5 Finding More Information About the SQL Module Processor. . . . 3–15
4 Writing Module SQL Procedures
4.1 Introducing SQL Module Procedures.... 4–1
4.2 Specifying the Common Elements of SQL Module Procedures. . . 4–3
4.2.1 Naming a Procedure..... . . 4–3
4.2.2 Declaring Procedure Parameters.... 4–4
4.2.3 Parameters Required for Different Kinds of Procedures. . . . 4–5
4.2.4 Associating Procedure Parameters and Actual Parameters. . 4–9
4.2.5 Specifying Parameter Data Types...4–11
4.2.6 Effect of the LANGUAGE Clause on the Parameter Data Type . . . . . 4–12
4.2.7 Effect of the LANGUAGE Clause on the Parameter-Passing
Mechanism...... . . . 4–13
4.2.8 Overriding the Default Passing Mechanism for a Procedure
Parameter...... . . . 4–15
4.2.9 Requesting a Run-Time Check of Parameters Used by the Calling
Module......4–15
4.3 Using Single SQL Statements in Procedures... . 4–16
4.4 Using Compound Statements in Multistatement Procedures. . . . 4–16
4.5 Understanding the Restrictions of the SQL Module Language. . 4–18
5 Processing SQL Modules and Host Language Files
5.1 Invoking the SQL Module Processor.... 5–1
5.2 Processing SQL and Host Language Modules... 5–2
5.3 Bypassing Parameter Checking for Faster Compilation.. 5–4
5.4 Improving SQL Module Processor Performance for Remote
Databases......5–4
5.5 Using Context Files with SQL Module Language.. . . . . 5–5
5.6 Deciding on the Scope of an SQL Module... . . . . 5–6
6 Using Precompiled SQL
6.1 Understanding the Precompiler Process... . . . . . 6–1
6.2 Embedding SQL Statements in Host Programs..6–4
6.3 Invoking the Precompiler..... . . 6–7
6.4 Finding Precompile-Time and Compile-Time Errors.. .. . 6–11
6.5 Improving Precompiler Performance for Remote Databases. . . . . 6–12
6.6 Specifying Compile-Time and Run-Time Options.. . . . . . 6–12
6.6.1 Using the DECLARE MODULE Statement..6–13
6.6.2 Including Declarations in an SQL Context File.. . . . 6–14
6.7 Language-Specific Guidelines for Using the SQL Precompiler. . . 6–15
6.7.1 Embedding SQL Statements in Ada Source Files.. . 6–15
6.7.2 Precompiling Ada Programs.... . . . . 6–16
6.7.3 Embedding SQL Statements in C Source Files.. . . . 6–18
6.7.4 Embedding SQL Statements in COBOL Source Files. . . . . . 6–19
6.7.5 Embedding SQL Statements in FORTRAN Source Files. . . . 6–22
6.7.6 Embedding SQL Statements in Pascal Source Files.. 6–25
6.7.7 Embedding SQL Statements in PL/I Source Files.. . 6–26
7 Creating Images for Program Execution
7.1 Understanding Executable and Shareable Images.. . . . 7–2
7.2 Using the OpenVMS Linker..... 7–2
7.2.1 Linking Programs Compiled with the Digital C Compiler on
OpenVMS VAX Systems..... 7–3
7.2.2 Creating an Executable Image That Links with a Shareable
Image....... 7–4
7.2.3 Linking Ada Objects..... . . . 7–4
7.3 Creating Shareable Images..... 7–6
7.3.1 Creating Executable and Shareable Images Not Sharing Database
Attaches...... . . . . . 7–7
7.3.2 Creating Executable and Shareable Images Sharing Database
Attaches...... . . . . . 7–8
7.4 Installing Shareable Images....7–11
7.5 Linking Modules on Digital UNIX.... . . 7–12
7.5.1 Building Applications with Multiple Modules.. . . . . 7–13
7.6 Inserting Precompiled SQL Modules in Object Libraries and
Archives....... . 7–13
7.7 Running a Program.....7–14
7.8 Debugging SQL Statements and Program Code..7–14
8 Declaring and Using Parameters
8.1 Overview of Declaring and Using Parameters.... 8–1
8.2 Understanding Terminology..... 8–3
8.3 Understanding Parameter Function and Declaration Options. . . 8–3
8.4 Declaring the Data Types of Parameters... . . . . 8–6
8.5 Copying Parameter Declarations from a Source Outside Your
Program....... . 8–9
8.6 Using the SQL INCLUDE Statement...8–9
8.6.1 Using the SQL INCLUDE FROM DICTIONARY Statement. 8–10
8.6.2 Using the INCLUDE SQLCA Statement... . 8–11
8.6.3 Using the INCLUDE SQLDA or SQLDA2 Statement. . . . . . 8–12
8.6.4 Using the INCLUDE File Statement... . . . . 8–12
8.7 Using the SQL Module Language FROM path-name Clause. . . . 8–13
8.8 Using Host Language COPY or INCLUDE Statements.. 8–13
8.9 Declaring and Using Main Parameters... . . . . . 8–14
8.9.1 Declaring Main Parameters.... . . . . 8–15
8.9.2 Using Main Parameters..... 8–16
8.10 Declaring and Using Indicator Parameters... . . 8–20
8.10.1 Declaring Indicator Parameters.... . 8–20
8.10.2 Using Indicator Parameters.... . . . . 8–22
8.10.3 Using Indicator Parameters When Retrieving Values. . . . . . 8–23
8.10.4 Using Indicator Parameters When Storing Values.. . 8–25
8.11 Avoiding Mistakes When Declaring and Using Parameters. . . . . 8–26
8.11.1 Avoiding Mistakes When Using Embedded SQL.. . . 8–29
8.11.2 Avoiding Mistakes When Using SQL Modules.. . . . . 8–29
8.12 Declaring and Using Parameters in Specific Languages.8–30
8.12.1 Declaring and Using Parameters in Ada Source Files. . . . . . 8–30
8.12.2 Declaring and Using Parameters in C Source Files.. 8–31
8.12.3 Declaring and Using Parameters in COBOL Source Files. . . 8–34
8.12.4 Declaring and Using Parameters in FORTRAN Source Files. 8–34
8.12.5 Declaring and Using Parameters in Pascal Source Files. . . . 8–34
8.12.6 Declaring and Using Parameters in PL/I Source Files. . . . . . 8–34
8.12.7 Declaring and Using Parameters in SQL Modules and Calling
Programs...... . . . . 8–34
9 Using Date-Time Data Types
9.1 Storing Data in Date-Time Data Types... . . . . . 9–2
9.2 Using Date-Time Data Types in Programs... . . . 9–3
9.2.1 Converting Date-Time Data Types for Program Development9–4
9.2.2 Using Date-Time Data Types with the SQL Precompiler. . . . 9–6
9.2.3 Using Date-Time Data Types with the SQL Module Language . . . . . 9–9
9.3 Improving Portability When Using Date-Time Data Types. . . . . 9–14
9.4 Converting Applications and Databases... . . . . . 9–15
9.5 Handling DATE VMS Data Types in Applications.. . . . . 9–16
9.5.1 Using DATE VMS with Applications Specific to OpenVMS. . 9–17
9.5.2 Porting Applications That Contain DATE VMS Data Types. . 9–17
9.6 Using Date-Time Data Types with Dynamic SQL.. . . . . 9–20
9.6.1 Using CAST with Parameter Markers... . . . 9–20
9.6.2 Passing Dates as Text Strings to Dynamic SQL Statements. 9–21
Part III Run-Time Processing
10 Handling Run-Time Errors
10.1 Overview of SQL Error Handling.... . . . 10–1
10.2 Monitoring Execution of SQL Statements for Errors.. . . 10–4
10.2.1 Using SQLSTATE..... . . . . 10–5
10.2.2 Using SQLCODE..... . . . . . 10–6
10.2.3 Using the WHENEVER Statement... . . . . . 10–11
10.2.4 Using the sql_get_message_vector Routine and
RDB$LU_STATUS..... . . . . 10–14
10.2.5 Using the SQL Error-Handling Routines... . 10–20
10.3 Displaying Error Messages..... 10–30
10.3.1 Calling sql_signal..... . . . . . 10–31
10.3.2 Calling sql_get_error_text.... . . . . . 10–32
10.3.3 Displaying User-Supplied Error Messages... 10–33
10.3.4 Declaring SQL Routines Using an Include File.. . . . 10–34
10.4 Handling Duplicate Value Errors and Constraint Violations. . . . 10–34
10.4.1 Status Values for Constraint Violations and Duplicate Value
Errors......10–35
10.4.2 Controlling Constraint Evaluation...10–36
10.5 Handling Lock Conflicts and Deadlocks... . . . . 10–36
10.5.1 Handling Lock-Conflict Errors.... . . 10–37
10.5.2 Handling Deadlock Errors.... . . . . . 10–38
10.6 Handling Errors Caused by Failure to Attach to a Database or Start a
Transaction...... . . . . . 10–39
10.7 Improving Program Portability When Handling Errors and
Constraints...... . . . . . 10–40
11 Using Dynamic SQL
11.1 Introducing Dynamic SQL..... . 11–1
11.1.1 Categories of Statements That Can Be Dynamically Executed . . . . . 11–2
11.1.2 Using Dynamic SQL Statements to Process Other SQL
Statements...... . . . 11–4
11.1.3 Processing SQL Statements in Dynamic SQL.. . . . . 11–5
11.2 Executing Non-SELECT Statements Without Parameter Markers . . . . . 11–6
11.3 Handling Parameter Markers and Select List Items.. . . 11–9
11.3.1 Using the SQLDA and SQLDA2 Structures..11–10
11.3.2 Declaring SQLDA and SQLDA2 Structures..11–12
11.4 Executing Non-SELECT Statements with Parameter Markers. . 11–15
11.5 Testing Whether or Not a Statement Is a SELECT Statement. . 11–20
11.6 Processing SELECT Statements.... . . . 11–21
11.6.1 Executing SELECT Statements Without Parameter Markers:
Declaring Dynamic and Extended Dynamic Cursors.11–22
11.6.2 Executing SELECT Statements That Contain Parameter
Markers...... . . . . . 11–26
11.6.3 Using SQLDA2 and SQLERRD Structures to Test for Parameter
Markers and SELECT Statements...11–33
11.7 Processing Sets of Dynamically Generated Statements.. 11–37
11.7.1 Storing Statement Identifiers and Cursor Names.. . 11–38
11.7.2 Executing Multiple Non-SELECT Statements.. . . . . 11–39
11.7.3 Executing Multiple SELECT Statements... . 11–41
11.8 Finding the Sample Programs Used in This Chapter.. . . 11–45
Part IV Programmatic Structures
12 Using Compound Statements in SQL
12.1 Introducing Compound Statements.... . 12–1
12.2 Using Compound Statements to Increase Performance.. 12–2
12.3 Writing a Compound Statement.... . . . . 12–3
12.3.1 Declaring and Assigning Variables...12–4
12.3.2 Using the IF Statement..... 12–7
12.3.3 Using the CASE Statement.... . . . . 12–9
12.3.4 Using the LOOP Statement.... . . . . 12–10
12.3.5 Using the FOR Statement.... . . . . . 12–11
12.3.6 Using Labels in Compound Statements... . . 12–13
12.3.7 Using the LEAVE Statement.... . . . 12–14
12.3.8 Invoking Stored or External Procedures... . 12–15
12.4 Controlling the Atomicity of Compound Statements.. . . 12–16
12.5 Controlling Transactions in Compound Statements.. . . . 12–19
12.6 Processing Compound Statements Dynamically..12–20
12.7 Debugging Compound Statements.... . . 12–21
12.8 Retrieving Information About Compound Statements.. . 12–23
12.9 Handling Exception and Completion Conditions.. . . . . . 12–25
12.9.1 Retrieving Exception Conditions.... 12–25
12.9.2 Retrieving Completion Conditions...12–26
13 Using Stored Routines
13.1 What Are Stored Routines?..... 13–1
13.2 Understanding the Benefits of Storing Routines in a Database. . 13–2
13.3 Creating Stored Modules..... . . 13–3
13.3.1 Creating Stored Procedures.... . . . . 13–5
13.3.2 Creating Stored Functions.... . . . . . 13–8
13.3.3 Creating a Stored Function to Generate New Sequence Numbers . . . 13–10
13.4 Invoking Stored Procedures....13–11
13.5 Invoking Stored Functions..... . 13–13
13.6 Deleting Stored Routines..... . . 13–13
13.7 Tracking Stored Routine Dependencies... . . . . . 13–14
13.7.1 Procedure Dependency Type.... . . . 13–16
13.7.2 Language Semantic Dependency Type... . . . 13–17
13.7.3 Transaction Dependency Types.... . 13–18
13.8 Invalidating Stored Routines....13–20
13.9 Revalidating Stored Routines....13–22
13.9.1 Revalidating Invalidated Stored Routines... 13–22
13.9.2 Re-Creating Invalidated Stored Routines with Language Semantic
Dependencies...... . 13–24
14 Using External Routines
14.1 Introducing External Routines.... . . . . . 14–2
14.2 Developing External Routines.... . . . . . 14–3
14.3 Creating External Routine Definitions...14–4
14.3.1 Creating External Function Definitions... . . 14–5
14.3.2 Creating External Procedure Definitions... . 14–7
14.4 Modifying and Deleting External Routine Definitions.. . 14–10
14.5 Creating External Routines..... 14–10
14.5.1 Creating External Routines Based on Existing Routines. . . . 14–11
14.5.2 Writing User-Defined External Routines... . 14–14
14.5.3 Writing External Routines That Call into the Database. . . . 14–19
14.5.4 Writing Jacket Routines to Invoke External Routines. . . . . . 14–27
14.6 Creating Shareable Images for External Routines.. . . . . 14–30
14.6.1 Creating Shareable Images for External Routines on OpenVMS
VAX....... . 14–31
14.6.2 Creating Shareable Images for External Routines on OpenVMS
Alpha....... 14–32
14.7 Creating Shared Objects on Digital UNIX... . . . 14–33
14.8 Invoking External Routines..... 14–34
14.8.1 Invoking External Functions.... . . . 14–34
14.8.2 Invoking an External Function Within a Trigger.. . . 14–35
14.8.3 Invoking External Procedures.... . . 14–37
14.9 Specifying Execution Characteristics of Routines.. . . . . . 14–37
14.10 Understanding Routine Activation and Deactivation.. . . 14–39
14.11 Declaring and Passing Parameters and Return Values.. 14–41
14.12 Language-Specific Guidelines for Coding External Routines. . . . 14–43
14.12.1 Using External Routines with Ada... . . . . . 14–44
14.12.2 Using External Routines with C.... 14–45
14.12.3 Using External Routines with COBOL... . . 14–46
14.12.4 Using External Routines with FORTRAN... 14–46
14.12.5 Using External Routines with Pascal... . . . . 14–47
14.13 Using Notify Routines..... . . . . 14–47
14.14 Handling Exceptions in External Routines... . . 14–49
14.15 Understanding the Limitations of External Routines.. . . 14–49
14.16 Troubleshooting External Routines.... . 14–50
14.17 Improving Portability and Efficiency of External Routines. . . . . 14–52
Part V Your Program’s Context
15 Attaching to Databases
15.1 Specifying and Attaching to a Database... . . . . . 15–1
15.1.1 Specifying File or Repository Access for Database Attachment . . . . . 15–1
15.1.2 Specifying the Database Name.... . 15–2
15.1.3 Specifying Different Databases for Compile Time and Run Time . . . . 15–3
15.2 Specifying a Database on a Remote Node... . . . 15–5
15.2.1 Using the USER and USING Clauses for Remote User
Authentication...... 15–7
15.2.2 Using Command Line Qualifiers for Remote User Authentication . . . 15–8
15.2.3 Using Configuration Files for Remote User Authentication. . 15–8
15.2.4 Using a Proxy Account As the Remote Server Account. . . . . 15–9
15.2.5 Using the RDB$REMOTE Account As the Remote Server
Account...... . . . . . 15–10
15.2.6 Using an Alternate UCX or Internet Service.. . . . . . 15–11
15.3 Avoiding Asynchronous System Traps...15–11
15.4 Attaching to Databases in a Distributed Transaction.. . . 15–12
15.4.1 Avoiding Undetected Deadlock with Distributed Transactions15–12
15.4.2 Avoiding Privilege Errors on Distributed Transactions. . . . . 15–12
15.5 Using Aliases for Multiple Attaches.... . 15–13
15.6 Detaching from a Database..... 15–16
16 Managing Transaction Context
16.1 Understanding Transactions....16–1
16.1.1 Understanding Transaction Characteristics..16–2
16.1.2 Deciding When to Modify Transaction Characteristics. . . . . . 16–4
16.2 Specifying Transaction Characteristics in SQL Programs. . . . . . 16–5
16.2.1 Using Read-Only Transactions.... . . 16–7
16.2.2 Using Read/Write Transactions.... . 16–8
16.2.3 Using Batch-Update Transactions...16–9
16.2.4 Using the RESERVING Clause.... . 16–10
16.2.5 Choosing Whether to Wait for Locks to Be Obtained.16–13
16.2.6 Choosing an Isolation Level.... . . . . 16–15
16.2.6.1 Using a Serializable Transaction... . . . . 16–17
16.2.6.2 Using a Repeatable Read Transaction... 16–17
16.2.6.3 Using a Read Committed Transaction... 16–18
16.2.7 Benefits of Using Various Isolation Levels... 16–20
16.2.8 Using Aliases to Access More Than One Database in a Single
Transaction...... . . 16–22
16.3 Understanding the Scope of a Transaction... . . . 16–23
16.4 Using Distributed Transactions.... . . . . 16–26
16.5 Locking Database Resources....16–27
16.5.1 Locking Strategies..... . . . . 16–28
16.5.2 Intent Locks...... . . 16–28
16.5.3 Lock Conflicts...... . 16–30
16.5.4 Read-Only Transactions and the Snapshot File.. . . . 16–33
16.5.5 Encountering Lock-Conflict Errors with Read-Only Transactions . . . 16–34
16.5.6 Improving Concurrent Access.... . . . 16–35
16.6 Designing Transactions so They Do Not Span Terminal I/O
Operations......16–37
16.7 Deciding When to Evaluate Constraints... . . . . 16–44
16.7.1 Specifying Constraint Evaluation Time... . . 16–44
16.7.2 Recommendations for When to Evaluate Constraints. . . . . . 16–46
16.8 Committing or Rolling Back a Transaction... . . 16–47
17 Managing Multiple Connections in Programs
17.1 Introducing Connections..... . . 17–1
17.1.1 Defining a Session..... . . . . 17–2
17.1.2 Defining a Database Environment...17–3
17.1.3 Defining a Connection..... . 17–4
17.2 Creating, Switching Between, and Ending Connections.. 17–5
17.2.1 Creating Connections..... . . 17–6
17.2.2 Duplicating the Default Database Environment.. . . 17–6
17.2.3 Specifying Different Databases for the Same Aliases.17–8
17.2.4 Specifying an Additional Run-Time Attach..17–9
17.2.5 Switching Between Connections.... . 17–10
17.2.6 Ending Connections..... . . . 17–11
17.3 Using Transactions with Connections...17–12
17.4 Enabling and Disabling Connections in Programs.. . . . . 17–12
17.4.1 Enabling and Disabling Connections for Module Programming . . . . . 17–12
17.4.2 Enabling and Disabling Connections for Precompiled Programs . . . . 17–12
17.5 Using Connections in an Application.... 17–13
Part VI Data Manipulation in Programs
18 Using Cursors
18.1 Introduction to Cursors..... . . . 18–1
18.1.1 How Cursors Work..... . . . . 18–2
18.1.2 Comparing Cursors and Views.... . . 18–6
18.1.3 Deciding When a Cursor Is Needed... . . . . . 18–7
18.2 Understanding the Different Categories of Cursors.. . . . 18–8
18.3 Controlling the Opening and Closing of Cursors.. . . . . . 18–10
18.4 Using Table Cursors.....18–10
18.5 Using Holdable Cursors..... . . . 18–14
18.6 Using List Cursors...... 18–15
18.7 Using Scrollable List Cursors....18–17
18.8 Using Dynamic Cursors..... . . . 18–20
18.9 Using Extended Dynamic Cursors.... . . 18–21
19 Inserting, Updating, and Deleting Data
19.1 Loading a Database.....19–1
19.2 Inserting Rows...... . . . 19–2
19.2.1 Using the INSERT . . . VALUES Statement.. . . . . . 19–2
19.2.2 Using the INSERT . . . SELECT Statement.. . . . . . 19–3
19.3 Using List Cursors to Insert Large Data Structures.. . . 19–4
19.4 Updating Rows...... . . . 19–5
19.4.1 Selecting Data in the UPDATE Statement... 19–6
19.4.2 Using the UPDATE Statement with a Cursor.. . . . . 19–6
19.4.3 Using the UPDATE . . . RETURNING Statement.. . 19–8
19.5 Deleting Rows...... . . . 19–9
19.6 Deleting List Data...... 19–10
19.7 Using Triggers with Insert, Update, and Delete Operations. . . . 19–10
20 Using the Multiple Schema Option
20.1 Understanding Multischema Databases... . . . . . 20–2
20.2 Using Multischema Databases with the SQL Module Processor. 20–3
20.2.1 Setting Defaults for SQL Modules...20–3
20.2.2 Using Multischema Naming in an SQL Module File and C
Program...... . . . . . 20–5
20.3 Using Multischema Databases with the SQL Precompiler. . . . . . 20–8
20.3.1 Default Settings for the SQL Precompiler... 20–8
20.3.2 Using Multischema Naming in a Precompiled Program. . . . . 20–10
A Using SQL International Options
A.1 Controlling Input and Display Formats... . . . . . A–1
A.1.1 Using Locale Settings on Digital UNIX... . . A–2
A.2 Specifying Collating Sequences.... . . . . A–2
A.3 Using Collating Sequences..... . A–3
A.4 Collating Order for Oracle Rdb Character Sets..A–4
Examples
3–1 Parts of an SQL Module..... 3–4
3–2 Host Language That Calls an SQL Module..3–13
4–1 Writing SQL Modules to Accept the Database Name at Run Time . . 4–6
4–2 Passing the Database Name to an SQL Module.. . . 4–7
4–3 Using Parameters with Cursors.... . 4–8
6–1 Changing Compile-Time and Run-Time Settings with the DECLARE
MODULE Statement..... . . 6–13
6–2 Context File for Precompiled SQL Compilation.. . . . 6–14
6–3 Precompiling Ada Files..... 6–18
7–1 Using an Options File to Link with a Shareable Image. . . . . 7–4
7–2 Linking to Create a Shareable Image... . . . 7–7
7–3 Linking Shareable Images That Share Handles on OpenVMS
VAX....... . 7–9
7–4 Linking Shareable Images That Share Handles on OpenVMS
Alpha....... 7–9
7–5 Linking an Executable Image That Uses a Shareable Image. 7–11
9–1 Inserting Data-Time Data.... . . . . . 9–2
9–2 SQL Module Segment for Converting Date-Time Data Types9–4
9–3 C Program for Converting Date-Time Data Types.. . 9–5
9–4 SQL Precompiler Program Using Date-Time Data Types. . . . 9–6
9–5 C Program Using Date-Time Data Types with the SQL
Precompiler...... . . 9–8
9–6 SQL Module Using Date-Time Data Types... 9–10
9–7 C Program Using Date-Time Arithmetic... . 9–12
9–8 Casting Parameter Markers in Dynamic SQL Programs. . . . 9–20
10–1 Monitoring SQLCODE in SQL Module Language.. . 10–9
10–2 Monitoring SQLCODE and Stopping on Error.. . . . . 10–10
10–3 Using SQLCODE Values to Take Recovery Action.. . 10–10
10–4 Using the sql_get_message_vector Routine..10–16
10–5 Using RDB$LU_STATUS to Trap Constraint Violations. . . . 10–19
10–6 Using SQL Error-Handling Routines... . . . . 10–23
11–1 Executing Non-SELECT Statements Using the EXECUTE
IMMEDIATE Statement....11–6
11–2 Declaring SQLDA2 Structures.... . . 11–14
11–3 Executing Non-SELECT Statements with Parameter Markers . . . . . 11–15
11–4 Testing SQLERRD to Identify Non-SELECT Statements. . . . 11–21
11–5 Executing SELECT Statements Without Parameter Markers in an
SQL Module...... . . 11–23
11–6 Executing SELECT Statements Without Parameter Markers in a
Host Language Program....11–24
11–7 Executing SELECT Statements with Parameter Markers in an SQL
Precompiled Program..... . . 11–27
11–8 Testing Whether a Statement Is a SELECT Statement. . . . . 11–34
11–9 Storing Statement Identifiers and Cursor Names in Arrays. 11–38
11–10 Executing More Than One Non-SELECT Statement.11–39
11–11 Executing More Than One SELECT Statement.. . . . 11–42
13–1 Creating a Stored Module.... . . . . . 13–4
13–2 Creating a Stored Procedure.... . . . 13–5
13–3 Creating a Stored Function.... . . . . 13–8
13–4 Calling a Stored Procedure.... . . . . 13–12
13–5 Invoking a Stored Function.... . . . . 13–13
13–6 Deleting a Stored Module....13–14
13–7 Examining Procedure Dependency Type... . 13–16
13–8 Examining Transaction Dependency Type... 13–19
13–9 Stored Module Definition with Procedure Dependency Type. 13–22
14–1 Defining an External Function with the CREATE FUNCTION
Statement...... . . . 14–5
14–2 Defining an External Procedure with the CREATE PROCEDURE
Statement...... . . . 14–7
14–3 Defining an External Function for an Existing Function on
OpenVMS VAX...... 14–11
14–4 Defining an External Function for an Existing Function on
OpenVMS Alpha..... . . . . . 14–11
14–5 Defining an External Function for an Existing Function on
Digital UNIX...... . 14–13
14–6 Invoking a Predefined External Function Using an SQL Module . . . . 14–13
14–7 Invoking a Predefined Function with a C Program.. 14–13
14–8 Writing a User-Defined External Function in C.. . . . 14–15
14–9 Compiling and Linking a User-Defined External Function. . 14–15
14–10 Defining an External Function for a User-Defined Function. 14–16
14–11 Invoking a User-Defined External Function from an SQL Module . . . 14–16
14–12 Invoking a User-Defined External Function with a C Program . . . . . 14–17
14–13 Writing an External Routine to Call into a Database. . . . . . 14–20
14–14 Calling into a Database from an SQL Module.. . . . . 14–22
14–15 Defining an External Routine That Calls into the Database. 14–23
14–16 Writing a Jacket Routine in C.... . . 14–28
14–17 Compiling and Linking a Jacket Routine... . 14–28
14–18 Defining an External Function That Calls a Jacket Routine. 14–28
14–19 Invoking a Jacket Routine from an SQL Module.. . . 14–29
14–20 Invoking a Jacket Routine from a C Program.. . . . . 14–29
14–21 Invoking External Functions in SQL Statements.. . . 14–35
14–22 Invoking External Functions Within a Trigger Definition. . . 14–36
14–23 Tracking Database Activity with External Functions.14–36
14–24 Using Triggers and External Functions to Track Database
Activity...... . . . . . 14–37
14–25 Invoking External Procedures.... . . 14–37
14–26 Securing an External Function Definition... 14–40
15–1 Qualifying Table References with an Alias... 15–14
15–2 Using Aliases...... . 15–14
15–3 Working with More Than One Database... . 15–15
16–1 Updating a Row in a Multiuser Environment.. . . . . 16–38
16–2 Updating a Table Containing Constraints... 16–40
17–1 Declaring Databases for the Default Database Environment in
Embedded SQL.....17–4
17–2 SQL Module Using Connections.... . 17–13
17–3 C Program Using Connections.... . . 17–15
18–1 Using Table Cursors..... . . . 18–11
18–2 Using Holdable Table Cursors.... . . 18–14
18–3 Using List Cursors..... . . . . 18–16
18–4 Using Scrollable List Cursors.... . . . 18–18
18–5 Using Dynamic Cursors..... 18–20
18–6 Using Extended Dynamic Cursors...18–21
19–1 Loading a Table from a Data File in a Precompiled C Program . . . . . 19–2
19–2 Updating Rows in a Precompiled C Program.. . . . . . 19–7
19–3 Deleting List Data From a Row.... . 19–10
20–1 Using Multischema Names in an SQL Module File.. 20–5
20–2 Using Multischema Names in a Precompiled C Program. . . . 20–10
Figures
1–1 Using the SQL Module Processor in Program Development. . 1–2
1–2 Using the SQL Precompiler in Program Development. . . . . . 1–3
2–1 Stages of the Application Development Cycle.. . . . . 2–2
2–2 Using Interactive SQL as a Prototyping Tool.. . . . . . 2–5
3–1 Developing Applications with the SQL Module Processor. . . 3–3
4–1 Correspondence Between Actual Parameters, Procedure Parameters,
and Columns...... . 4–10
6–1 Application Program Development with the SQL Precompiler6–2
6–2 Scope of SQLCODE Declaration (COBOL)... 6–21
6–3 Scope in Which SQL Statements Are Allowed (COBOL). . . . 6–22
6–4 Scope in Which SQL Statements Are Allowed (FORTRAN). . 6–25
6–5 Scope of SQLCODE Declaration (PL/I)... . . . 6–27
6–6 Scope in Which SQL Statements Are Allowed (PL/I).6–27
16–1 Share Mode and Lock Type Options for Read/Write Transactions . . . 16–12
16–2 Transaction Scope with a SET TRANSACTION Statement. . 16–24
16–3 Transaction Scope with a DECLARE TRANSACTION Statement . . . 16–25
16–4 Transaction Scope with SET and DECLARE TRANSACTION
Statements...... . . . 16–26
16–5 Chart of Database Access Conflicts... . . . . . 16–32
16–6 Transaction Recovery-Unit Journal (.ruj) File During an Update
Transaction...... . . 16–48
17–1 Components of an SQL Connection... . . . . . 17–2
17–2 Default Connection..... . . . 17–5
17–3 Duplicating the Default Connection... . . . . . 17–8
17–4 Specifying Different Databases for the Same Aliases.17–9
17–5 Specifying an Additional Run-Time Attach..17–10
18–1 How Cursors and Related Statements Work Together. . . . . . 18–3
20–1 Structure of a Multischema SQL Database..20–3
Tables
6–1 Ending SQL Statements in Precompiled Host Language Source
Files....... . 6–6
6–2 Language Identifiers and Default File Extensions Used in
Precompiling Programs..... 6–8
6–3 Files Related to Precompiling Ada Source Modules.. 6–17
10–1 Types of Run-Time Errors.... . . . . . 10–2
10–2 SQL Techniques for Handling Errors... . . . . 10–3
10–3 Declaring Symbolic Error Codes in Embedded Host Languages . . . . . 10–18
13–1 Dependency Tracking Table.... . . . . 13–15
13–2 Statements Causing Stored Routine Invalidation.. . 13–20
14–1 Components for Building a User-Defined External Function. 14–14
14–2 Components for Building a Jacket Routine to Invoke an External
Function...... . . . . . 14–27
15–1 Options for Remote Access.... . . . . . 15–6
16–1 Phenomena Permitted at Each Isolation Level.. . . . 16–16
16–2 Intent Locks...... . . 16–29
17–1 SQL Statements Affecting Connections... . . 17–6
20–1 Module Defaults for Multischema SQL... . . 20–4
20–2 SQL Defaults for Compiler Attributes in Precompiled Programs . . . . 20–8
A–1 Collating Order for Oracle Rdb Character Sets.. . . . A–4

READ / DOWNLOAD this Oracle RDB tutorial


Resource: http://download.oracle.com/otn_hosted_doc/rdb/pdf/gsp.pdf
Posted By : Bob
On date : 09.25.09

Most used ORACLE Database Tutorials

Oracle SQL Developer 1.5.5 download and installation
To install Oracle SQL Developer 1.5.5 download the file and unzip into an empty folder. Select the "Use folder names" checkbox when unzipping the file. A Check for Updates facility is available to install third-party database drivers, if required.
Oracle Tutorials ( Ebooks , pdf's )
Oracle 8i the Complete ReferenceMcGrawHill-Oracle_Database_10g_New_Features.chm Database.10g..High.Availablity.with.RAC.Flashback.&.Data.Guard McGraw Hill - Oracle Database 10g, A Beginner's Guide.chm
Mastering Oracle PL/SQL: Practical Solutions - pdf tutorial
This book offers a wealth of tips, techniques, and complete strategies for maximizing the benefits of PL/SQL within your organization. By the end of this book, youíll be as convinced as we are that PL/SQL isnít just a useful toolóitís an integral part of any Oracle application youíll ever develop.
Oracle 10g Database PL/SQL User's Guide and Reference study material - pdf tutorial
Anyone developing PL/SQL-based applications for Oracle should read this book. This book is intended for programmers, systems analysts, project managers, database administrators, and others who need to automate database operations. People developing applications in other languages can also produce mixed-language applications with parts written in PL/SQL.
Oracle interview questions and answers
These are some common oracle interview questions with answers.
Oracle Database SQL Quick Reference tutorial
This quick reference contains a high-level description of the Structured Query Language (SQL) used to manage information in an Oracle database.
New Features in Oracle Database 10g
This tutorial provides the details of New Features in Oracle Database 10g
Oracle SQL Parallel Execution tutorial
This Oracle tutorial focuses on SQL parallel execution only, which consists of parallel query, parallel DML (Data Manipulation Language) and parallel DDL (Data Dictionary Language). While this pdf study material focuses on Oracle Database 11g, the information in this paper also applies to Oracle Database 10g and higher, unless explicitly stated.
Oracle SQL Developer Data Modeler tutorial and reference
Oracle SQL Developer Data Modeler is a new, graphical data modeling tool that facilitates and enhances communication between data architects, database administrators, application developers and users, and simplifies the data modeling development process itself.
Oracle Rdb 7 Guide to SQL Programming - pdf tutorial
This manual describes how to design and develop host language application programs that use SQL (structured query language) to store, modify, and retrieve data from Oracle Rdb databases.
Oracle TimesTen In-Memory database Extension for Oracle SQL Developers - pdf tutorial
Oracle TimesTen In-Memory database Extension for Oracle SQL Developers is a free extension to Oracle SQL Developer, a graphical tool that enhances productivity and simplifies database development tasks.
Oracle Database SQL Developer Installation Guide - pdf tutorial
This Oracle SQL installation guide is helpful for those who plan to download SAL Developer kit (.zip ) file and install it as a freestanding tool
SQL Apply Best Practices: Oracle Data Guard 10g study guide and reference
With Oracle Data Guard SQL Apply in Oracle Database 10g, Oracle is addressing the requirements of the business community for an online disaster-recovery solution that also provides a means to offload reporting and decision support operations from he primary database
Oracle SQL Model Clause version 10g - study guide and reference tutorial
Oracle Database 10g introduces an innovative approach to complex SQL calculations: the SQL Model clause.
Best Practices for Minimal Downtime Migration to ASM Oracle 10g tutorial
This oracle tutorial describes procedures to dramatically reduce downtime during the process of migrating Oracle databases residing on raw partitions, volumes or conventional file systems to Automatic Storage Management (ASM) by using Oracle Recovery Manager (RMAN) and Oracle Data Guard.

Latest added ORACLE Database Tutorials

New Features in Oracle Database 10g
This tutorial provides the details of New Features in Oracle Database 10g
Oracle Rdb 7 Guide to SQL Programming - pdf tutorial
This manual describes how to design and develop host language application programs that use SQL (structured query language) to store, modify, and retrieve data from Oracle Rdb databases.
Oracle interview questions and answers
These are some common oracle interview questions with answers.
Oracle SQL Developer Data Modeler Naming Standardization reference guide
SQL Developer Data Modeler provides and uses different elements when applying naming standards to models. You can define and then apply these standards to the different models available in SQL Developer Data Modeler.
Oracle SQL Developer 1.5.5 download and installation
To install Oracle SQL Developer 1.5.5 download the file and unzip into an empty folder. Select the "Use folder names" checkbox when unzipping the file. A Check for Updates facility is available to install third-party database drivers, if required.
Oracle SQL Model Clause version 10g - study guide and reference tutorial
Oracle Database 10g introduces an innovative approach to complex SQL calculations: the SQL Model clause.
SQL Apply Best Practices: Oracle Data Guard 10g study guide and reference
With Oracle Data Guard SQL Apply in Oracle Database 10g, Oracle is addressing the requirements of the business community for an online disaster-recovery solution that also provides a means to offload reporting and decision support operations from he primary database
Oracle Database SQL Developer Installation Guide - pdf tutorial
This Oracle SQL installation guide is helpful for those who plan to download SAL Developer kit (.zip ) file and install it as a freestanding tool
Best Practices for Minimal Downtime Migration to ASM Oracle 10g tutorial
This oracle tutorial describes procedures to dramatically reduce downtime during the process of migrating Oracle databases residing on raw partitions, volumes or conventional file systems to Automatic Storage Management (ASM) by using Oracle Recovery Manager (RMAN) and Oracle Data Guard.
Oracle SQL Developer Data Modeler tutorial and reference
Oracle SQL Developer Data Modeler is a new, graphical data modeling tool that facilitates and enhances communication between data architects, database administrators, application developers and users, and simplifies the data modeling development process itself.