Page 1040
BEGIN SELECT JOB, HIREDATE, SAL INTO :job_title, :hire_date, :salary FROM EMP WHERE EMPNO = :emp_number; END; END-EXEC; /* END OF PL/SQL BLOCK */ printf("%s %s %d\n",:job_title.arr,:hire_date.arr,:salary); }
Being able to pass data between Oracle and your application requires a variety of tasks to be completed successfully. The two primary parts of an embedded SQL program that must be included are the data declaration area (which involves the declarative statements) and the data manipulation area (which involves the executable statements). Before you can execute anything in your program, you must make your program aware of the players.
The data declaration area is used to define all host variables , include extra files, and establish a connection to the database. Sometimes this area is referred to as the program prologue. The three required sections within the data declaration area are the declare section, the SQL include section, and the SQL connect area.
All host language variables referenced in a SQL statement must be declared to Oracle; otherwise , an error message is issued at precompile time. These variables are declared in the DECLARE section. Most host languages allow multiple DECLARE sections per precompiled unit, but you must have at least one. These sections can be defined locally or globally. Host variables within this section can be of any length, but only the first 31 characters are evaluated. These variables can consist of letters , digits, and underscores, but they must begin with an alpha character. To store a datatype, Oracle must know the format and valid range of values. Oracle recognizes only two kinds of datatypes: internal and external. Internal datatypes indicate how Oracle stores the data, and external datatypes specify how the data is stored in the host variable. Table 45.3 shows the internal datatypes.
Table 45.3. Internal datatypes.
Name | Code | Description |
CHAR | 96 | Less than or equal to 255-byte, fixed-length character string |
MLSLABEL | 105 | Less than or equal to 5-byte, variable-length binary label |
Page 1041
Name | Code | Description |
VARCHAR2 | 1 | Less than 2000-byte, variable-length string |
NUMBER | 2 | Fixed or floating-point number |
LONG | 8 | Less than or equal to 2,147,483,647-byte, variable-length character string |
ROWID | 11 | Fixed-length binary number |
DATE | 12 | 7-byte, fixed-length date/time value |
RAW | 23 | Less than or equal to 255-byte, variable-length binary data |
LONGRAW | 24 | Less than or equal to 2,147,483,647-byte, fixed-length binary data |
Table 45.4 shows most of the external datatypes.
Table 45.4. External datatypes.
Name | Code | Description |
VARCHAR2 | 1 | Less than or equal to 65,535-byte, variable-length character string |
CHAR | 1, 96 | Less than or equal to 65,535-byte, variable-length character string |
MLSLABEL | 105 | Less than or equal to 5-byte, variable-length binary label |
NUMBER | 2 | Fixed or floating-point number |
INTEGER | 3 | 2-byte or 4-byte signed integer |
FLOAT | 4 | 4-byte or 8-byte floating-point number |
STRING | 5 | Null- terminated character string |
DECIMAL | 7 | COBOL packed decimal |
LONG | 8 | Less than or equal to 2,147,483,647-byte, fixed-length character string |
VARCHAR | 9 | Less than or equal to 65,535-byte, fixed-length character string |
ROWID | 11 | Fixed-length binary number |
DATE | 12 | 7-byte, fixed-length date/time value |
VARRAW | 15 | Less than or equal to 65,533-byte, fixed-length binary data |
RAW | 23 | Less than or equal to 255-byte, fixed-length binary data |
LONGRAW | 24 | Less than or equal to 2,147,483,647-byte, fixed-length binary data |
UNSIGNED | 68 | 2-byte or 4-byte unsigned integer |
DISPLAY | 91 | COBOL numeric-character data |
Page 1042
New external data types are LONG VARCHAR, LONG VARRAW, MLSLABEL, CHARF, and CHARZ. See Chapter 3 of the Programmer's Guide to Oracle Precompilers, version 1.8, for an exhaustive list. It is important that the host variables (external) within the DECLARE section match the database data- type (internal). At runtime, Oracle automatically converts between external and internal data- types. It is your responsibility to make sure the datatypes are convertible . For example, if you declare a host variable string type and place a value of "TODAY" in it and insert it into the database as a DATE type, you receive an error. The following guidelines are recommended when declaring and referencing host variables. A host variable must be
A host variable must not be
A host variable can be
After declaring a variety of host variables, you must establish communication between the host program and Oracle to monitor successes and failures. This communication is made by utilizing the SQL include area.
This section of the program enables the user to include copies of files into the host program. Any file can be included if it contains embedded SQL statements. The most common include file is the SQL Communication Area file (SQLCA).
The SQLCA is a data structure that handles certain events and provides diagnostic checking between the Oracle RDBMS and the host program. SQLCA variables maintain valuable runtime statistics such as warning codes with text, Oracle error codes, and number of rows processed , which are convenient for handling special conditions within the host program. The following example shows the syntax for including this file in the host program:
Page 1043
-- declare section -- #include <stdio.h> EXEC SQL BEGIN DECLARE SECTION; VARCHAR user_id[101]; EXEC SQL END DECLARE SECTION; /* SQL INCLUDE AREA */ EXEC SQL INCLUDE SQLCA; main() { printf("\n What is your User ID: "); scanf("%s",user_id.arr); user_id.len = strlen(user_id.arr); }
After establishing communication capabilities, the program must connect to the database to actually start communicating.
The host program must log on to Oracle before you can manipulate data. A CONNECT statement must be the first statement executed. SQL*Net allows you to concurrently access any combination of local and remote databases, which is somewhat limited by the operating system, or you can make multiple connections to the same database. You should contact your network manager for specific connect guidelines. The following example shows how you connect to Oracle:
#include <stdio.h> /* DECLARE AREA */ EXEC SQL BEGIN DECLARE SECTION; char user_id[100]; EXEC SQL END DECLARE SECTION; /* INCLUDE AREA */ EXEC SQL INCLUDE SQLCA; main() { char buffer[50]; printf("\n What is your User ID: "); scanf("%s",buffer); sprintf(user_id,"%s/",buffer); printf("\nEnter Password: "); scanf("%s",buffer); strcat(user_id,buffer); /* CONNECTS TO DATABASE */ EXEC SQL CONNECT :userid; continue with program... }