1040-1043

Previous Table of Contents Next

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); } 

Host Program Requirements

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.

Data Declaration Area

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.

Declare Section

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

  • Explicitly declared in the DECLARE section
  • Referenced with a colon (:) in all SQL statements and PL/SQL blocks
  • A datatype supported by the host language
  • A datatype compatible with that of its source or target database column

A host variable must not be

  • Subscripted
  • Prefixed with a colon in the host language statement
  • Used to identify a column, table, or other Oracle object
  • Used in data definition statements such as ALTER, CREATE, and DROP

A host variable can be

  • Used anywhere an expression can be used in a SQL statement
  • Associated with an indicator variable

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.

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.

SQL Connect Area

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... } 
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