1036-1039

Previous Table of Contents Next

Page 1036

There are several ways an embedded SQL program can be precompiled, compiled, and linked. Compiling multiple embedded SQL programs can be time-consuming so make files or command/script files are typically used. Depending upon the platform that you are using, you can create a command file such as a .BAT, .SH, or .COM to accept parameters. In addition, Oracle provides sample make files in the ORACLE_HOME/PROxxx sample subdirectories. By receiving parameters to evaluate, the control file can determine what needs to be done. This approach helps keep all the programs consistent with others in terms of linking current libraries, objects, or other files.

A programmer can do still more to control how and what is precompiled in his or her source. The next section discusses conditional precompiling.

Conditional Precompiling

The precompiler also allows for conditional precompiling. This gives you the ability to write a program for several different platforms. For example, you might want to include some section of your program for a UNIX platform but not for a VMS platform. Oracle precompilers recognize conditional sections of code. These sections are indicated by statements that define the environment and what actions to take. In this section, you can use procedural and embedded SQL statements to perform platform-specific operations.

You use the following Oracle statements when creating a conditional section:


Statement Meaning
EXEC ORACLE DEFINE symbol Defines a symbol
EXEC ORACLE IFDEF symbol If symbol is defined
EXEC ORACLE IFNDEF symbol If symbol is not defined
EXEC ORACLE ELSE Otherwise
EXEC ORACLE ENDIF Ends this control block

An example for Pro*C follows :

 EXEC ORACLE IFDEF VMS;    EXEC SQL SELECT DNAME       INTO :dept_name       FROM DEPT; EXEC ORACLE ENDIF; 

Now that you have an understanding of what the precompiler does and what you can control, creating an embedded SQL program will take front stage. The next section of this chapter focuses just on an embedded SQL program. This section looks at source program basics, program requirements, and handling errors.

Page 1037

Embedded SQL Host Programs

Creating a host program that uses embedded SQL statements can be very beneficial. There are some specific guidelines and requirements that you must include in an embedded SQL program. This section of the chapter focuses on precompiled program basics, program requirements, handling errors, host arrays, dynamic SQL, user exits, and performance tuning. As each topic is introduced, that section provides code examples in C that is built into a completed program. You can then precompile, compile, link, and execute it.

Basics of a Host Program

Three basic concepts you must understand to fully grasp the material in this chapter are naming conventions, embedded SQL, and PL/SQL blocks.

Naming Conventions

All embedded SQL programs must have the appropriate extension for the host language. This extension notifies the precompiler that embedded SQL is included and that the program must be translated (precompiled) into the host language format. Table 45.2 indicates what extension should be used for the host language.

Table 45.2. Host language file extensions.


Host Language Standard File Extension
C/C++ .PC
COBOL .PCO
FORTRAN .PFO
Pascal .PPA
PL/1 .PPL

After your program is precompiled, the output is placed in a file with the normal extension for the language; normal compiling and linking can then continue.

CAUTION
When you modify your source code, remember to change the original precompiled source file. In C, for example, you should modify the .PC file and not the .C file; otherwise, your changes will not go into effect when the precompiler is re-invoked.

The second basic concept, which is the focus of these programs, is embedded SQL statements.

Page 1038

Embedded SQL

Embedded SQL refers to SQL statements that are placed within an application program. The program itself is referred to as the host program. You can use all standard SQL commands in the embedded statement. The two types of embedded SQL statements are executable and declarative.

CAUTION
You can embed any SQL command within a high-level language, but you cannot embed the extended SQL commands included in SQL*Plus. These include report formatting commands, editing SQL statements, and environment-setting commands.
Executable Statements

Executable statements are used to connect to Oracle and query, manipulate, process, and control access to Oracle data. These statements result in calls to Oracle and return codes from Oracle. The four types of executable embedded SQL statements are data definition, data control, data manipulation, and data retrieval. Later in the chapter, each of these statements is discussed in further detail.

Declarative Statements

Declarative statements are used to declare Oracle objects, communication areas, and SQL variables . These statements do not result in Oracle calls or return codes and do not operate on Oracle data.

The last concept is utilizing PL/SQL blocks. The next section covers how PL/SQL blocks are included into your source program.

PL/SQL Blocks

It is possible to place PL/SQL blocks in Oracle precompiler programs. The benefits of using them are discussed next.

Benefits of PL/SQL Blocks

Using PL/SQL blocks can drastically reduce processing overhead, improve performance, and increase productivity. For example, each SQL statement that is executed generates a certain amount of communication and processing overhead. Client/server environments, in particular, can greatly benefit from decreased network traffic. Using PL/SQL blocks, multiple SQL statements are packed into a section of code and transferred to the server to execute. Because Oracle treats the entire block as a single statement, you can reduce overhead dramatically.

Page 1039

Including PL/SQL Blocks

To include PL/SQL in your host program, you need to first declare the host variables that you want to use in the PL/SQL block. Next, you need to bracket the SQL statement that is included in the PL/SQL blocks with the keywords EXEC SQL EXECUTE and END-EXEC. The following code example shows how a PL/SQL block is incorporated into your program:

 #include <stdio.h> /* DECLARE AREA */ EXEC SQL BEGIN DECLARE SECTION;   VARCHAR   user_id[20];   VARCHAR   passwd[20]    int                 emp_number;    VARCHAR  job_title[20];    VARCHAR  hire_date[10];    real               salary; EXEC SQL END DECLARE SECTION; /* INCLUDE AREA */ EXEC SQL INCLUDE SQLCA; /* FUNCTION DECLARATIONS */ void get_employee_data(); main() {     printf("/n What is your User ID: ");     scanf("%s",user_id.arr);     printf("\nEnter Password: ");     scanf("%s",passwd.arr);user_id.len = strlen(user_id.arr); passwd.len = strlen(passwrd.arr); /* CONNECTS TO DATABASE */     EXEC SQL CONNECT :userid IDENTIFIED BY :passwd;     if (sqlca.sqlcode < 0)     {        printf("\n%s",sqlca.sqlerrm.sqlerrmc);        EXEC SQL ROLLBACK WORK RELEASE;        exit(1);     }     else     {       printf("\nSuccessfully connected to Oracle."); get_employee_data();      EXEC SQL COMMIT WORK RELEASE;     } exit(0);   } void get_employee_data() {      printf("\nEmployee Number? ");      scanf("%d\n",empl_number); /* BEGINNING OF PL/SQL BLOCK */   EXEC SQL EXECUTE 
Previous Table of Contents Next


Oracle Unleashed
Oracle Development Unleashed (3rd Edition)
ISBN: 0672315750
EAN: 2147483647
Year: 1997
Pages: 391

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net