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.
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
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.
Three basic concepts you must understand to fully grasp the material in this chapter are naming conventions, embedded SQL, and PL/SQL blocks.
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 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 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 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.
It is possible to place PL/SQL blocks in Oracle precompiler programs. The benefits of using them are discussed next.
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
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