Oracle Unleashed- P18

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

0
39
lượt xem
2
download

Oracle Unleashed- P18

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Oracle Unleashed- P18: When I first started using Oracle many years ago, it was possible to know the database and the tools available. With the rash of recent releases of different options for the database and the spate of new tools, only people who wear their underpants over their trousers will be able to know everything there is to know about the Oracle products.

Chủ đề:
Lưu

Nội dung Text: Oracle Unleashed- P18

  1. s Reducing Oracle Communication Overhead s Reducing Processing Overhead s New Features in Version 1.4 s Summary 50 Oracle Precompilers Extracting and manipulating data from a relational database would be very difficult if not for tools such as precompilers. The precompiler allows SQL statements to be embedded within procedural languages. Applications developed with this tool can use the best features of SQL, combined with the best features of the procedural language, creating a powerful module. This chapter discusses Oracle precompilers, creating a source program and some of the new features that Version 1.4 precompilers have. Oracle Precompilers The precompiler is a mechanism that allows SQL statements to be embedded within procedural languages. This tool provides the programmer with unlimited capabilities. To fully understand all the benefits of this tool, this chapter will focus on why we precompile, general features of the precompiler, what languages source code can be written in, how standardized are the precompilers, what options do they have, how to precompile your source code, and what is conditional precompiling. Why Precompile Source Code? Embedded SQL statements are not something a high-level language understands. The precompiler must therefore take the SQL statements and translate them into something the high-level language will understand. Libraries contain data structures that help guide the translation process for consistency. This general concept will help in understanding the precompiler features. General Precompiler Features One of the primary reasons to use a precompiler is to have the capability of utilizing SQL statements in a high-level language. Oracle precompilers offer many benefits and features that can help in this development. These features include the following: q Source code can be written in six different high-level languages, which all have ANSI/ISO precompilers. q Using a precomplier enables a user to take advantage of dynamic SQL, which allows for a more highly customized application. q [lb] Precompilers enable a user to process PL/SQL transaction blocks and use host arrays to process large amounts of data quickly; both dramatically improve performance. q Precompilers make conversions between Oracle internal datatypes and language datatypes, and check syntax and semantics. q Precompilers may also include conditional parameters to enable the program to be executed in different environments. q Direct interfaces with SQL*Forms can be done using user exits. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  2. q Precompilers provide variables to handle warning and error conditions, which are included in the ORACLE Communication Area (ORACA). Each of these features will be discussed in further detail later in the chapter. But you should know how many precompilers Oracle has before too much more detail is given. Precompiler Languages There are six procedural languages for which Oracle has precompilers available. During installation of Oracle, the host language is determined and the appropriate compiler is installed. The six languages include the following: q Ada q C q COBOL q FORTRAN q Pascal q PL/I With Oracle precompilers, you have the capabilities of precompiling and compiling several host programs, then linking them together into one module. Each program does not need to be written in the same language to be linked together, so standardization is a must. Oracle precompilers do meet national standards and will be discussed in the next section. ANSI/IO Standards Because SQL has become the standard language for relational databases, standardization has become an important issue. Oracle precompilers have taken exceptional efforts to meet standards set by the following organizations: q American National Standards Institute (ANSI) q International Standards Organization (ISO) q U.S. National Institute of Standards and Technology (NIST) Compliance for these requirements is measured by NIST, which uses over 300 test programs. Oracle precompilers have conformed to these standards by checking the SQL statements that are being embedded for standard syntax and semantics. But there is much more to precompiler function than syntax checking. Precompiler Options To increase flexibility among applications, Oracle precompilers have options that can be manipulated by the programmer to allow for certain conditions. For example, if a line of the source program exceeds 80 characters, an error occurs while precompiling. There is a precompiler option that allows the programmer to extend that line to 132 characters. These controls, along with others, are outlined in Table 50.1. The * character next to an item indicates that it can be entered inline. Table 50.1. Precompiler options. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  3. Syntax Default Specifies COMMON_NAME=block_name Name of FORTRAN common blocks DEFINE=symbol Symbol used in conditional precompilation ERRORS=YES|No* YES Whether errors are sent to the terminal FORMAT=ANSI|TERMINAL ANSI Format of COBOL or FORTRAN input line HOLD_CURSOR=YES|NO* NO How a cursor cache handles SQL statements HOST=C COB74 COBOL FORTRAN PASCAL PLI Host language INAME=path and filename Name of input file INCLUDE=path * Directory path for the INCLUDEd files IRECLEN=integer 80 Record length of input file LINES=YES|NO NO Whether C #line directives are generated LITDELIM=APOST QUOTE* QUOTE Delimiter for COBOL strings LNAME=path and filename Name of listing file Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  4. LRECLEN=integer 132 Record length of listing file LTYPE=LONG SHORT NONE LONG Type of listing MAXLITERAL=integer* Maximum length of string MAXOPENCURSORS=integer* 10 Maximum number of cursors cached MODE=ORACLE ASNI ANSI13 ANSI14 ISO ISO13 ISO14 ORACLE Compliance with ANSI/ISO standard ONAME=path and filename Name of output file ORACA=YES|NO NO Whether the ORACA is used ORECLEN=integer 80 Record length of output file PAGELEN=integer 66 Lines per page in listing RELEASE_CURSOR=YES|NO* NO How cursor cache handles SQL statements SELECT_ERROR=YES|NO* YES How SELECT errors are handled SQLCHECK=SEMANTICS Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  5. FULL SYNTAX LIMITED NONE * SYNTAX Extent of syntax and/or semantic checking USERID=username/password Valid Oracle username and password XREF=YES|NO* YES Cross-reference section in listing A precompiler command line might look something like the following: proc iname=example.pc include=ora_pcc: include=clib: ireclen=132 There is only one required argument, INAME. This argument tells the precompiler what the input filename is. So the minimum requirements for any precompiler command line could look like the following: proc iname=example.pc All of these precompiler options can be accessed online. Just enter the precompiler option without any argument at your operating system prompt. This help feature will display the name, syntax, default value and purpose of each option. There are some general guidelines to follow when setting options for precompiling: q When you precompile the program module that CONNECTs to Oracle, specify a value for MAXOPENCURSORS that is high enough for any of the program modules that will be linked together. q If you want to use just one SQLCA, you must declare it as a global in one of the program modules and as an external in the other modules. In C, for example, this is done by using the external storage class, which tells the precompiler to look for the SQLCA in another program module. Unless you declare the SQLCA as external, each program module will use its own local SQLCA. q You cannot DECLARE a cursor in one file and reference it in another. All these options can be utilized in several ways when precompiling your program, but what is the best way to precompile, compile, and link your source program? How to Precompile a Program In Version 1.4 of the precompilers, executables exists for each of the six languages previously mentioned. So each host language has a different command to run the precompiler. Table 50.2 shows which command to use for a specific language. Table 50.2. Precompiler commands. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  6. Host Language Precompiler Command C PROC COBOL PROCOB FORTRAN PROFOR Pascal PROPAS PL/I PROPLI There are several ways an embedded SQL program can be precompiled, compiled, and linked. Compiling multiple embedded SQL programs can become very time consuming if each step is done per program. This approach of issuing individual commands can lead to inconsistency between program modules. Executing each step of the compilation process should probably be used when it doesn't matter if standardization is met. For example, it doesn't matter if a common library is linked into your program but all other programs for this system depend on it. Therefore, utilizing a control file simplifies compiling your source program. This control file will evaluate what tasks need to be accomplished while adhering to company standards. Depending upon the platform that you are working on an executable file such as a . BAT or .COM can be created to accept parameters. The following example shows what a command line using a control file might look like. @PCL TEST TEST100 PCL. Control Filename: @PCL System Name: TEST Host Program: TEST100 Options: P(recompile) C(compile) L(ink) After receiving the 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. The following PCL.COM file is an example taken from a VAX platform to show how executable files can be created to control precompiling, compiling, and linking host programs. $ write sys$output "PCL.COM Version 2.3" $ $ write sys$output " " $ set noverify Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  7. $ assign $disk7:[vaxprod.com.ccom],$disk7:[vaxprod.'P1'.c'P1'] clib $ if "''P1'" .eqs "" then goto USAGE $ if "''P2'" .eqs "" then goto USAGE $! $ if "''P3'" .eqs "" then goto precompile $ if "''P3'" .eqs "P" then goto precompile $ if "''P3'" .eqs "p" then goto precompile $ if "''P3'" .eqs "PC" then goto precompile $ if "''P3'" .eqs "pc" then goto precompile $ if "''P3'" .eqs "PCL" then goto precompile $ if "''P3'" .eqs "pcl" then goto precompile $ if "''P3'" .eqs "PCLR" then goto precompile $ if "''P3'" .eqs "pclr" then goto precompile $! $ if "''P3'" .eqs "c" then goto compile $ if "''P3'" .eqs "C" then goto compile $ if "''P3'" .eqs "cl" then goto compile $ if "''P3'" .eqs "CL" then goto compile $ if "''P3'" .eqs "clr" then goto compile $ if "''P3'" .eqs "CLR" then goto compile $! $ if "''P3'" .eqs "l" then goto link $ if "''P3'" .eqs "L" then goto link $ if "''P3'" .eqs "lr" then goto link $ if "''P3'" .eqs "LR" then goto link $! $ if "''P3'" .eqs "r" then goto run $ if "''P3'" .eqs "R" then goto run $ goto USAGE $! Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  8. $! $! ************************************************************************* $! *************************** Precompile ****************************** $! ************************************************************************* $ precompile: $ proc iname='P2'.pc include=ora_pcc: include=clib: ireclen=132 $ $ if "''P3'" .eqs "p" then goto continue $ if "''P3'" .eqs "P" then goto continue $ $ $! ************************************************************************* $! ***************************** Compile ******************************* $! ************************************************************************* $ compile: $ write sys$output "Compiling ''P2'.C" $ cc/noopt/include_directory=clib:/define=VMS 'P2' $! $ if "''P3'" .eqs "pc" then goto continue $ if "''P3'" .eqs "PC" then goto continue $ if "''P3'" .eqs "c" then goto continue $ if "''P3'" .eqs "C" then goto continue $! $! $! ************************************************************************* $! ******************************* Link ********************************* $! ************************************************************************* $ link: $! Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  9. $ @ora_rdbms:loutl 'P2' 'P2''P4',clib:c'P1'.olb/lib,clib:ccom.olb/lib- ,ora_util:sqllib.olb/lib,ora_rdbms:oci/lib/include=(ocicee) 'P2' s $! $ endlink: $ if "''P3'" .eqs "PCL" then goto continue $ if "''P3'" .eqs "pcl" then goto continue $ if "''P3'" .eqs "cl" then goto continue $ if "''P3'" .eqs "CL" then goto continue $ if "''P3'" .eqs "l" then goto continue $ if "''P3'" .eqs "L" then goto continue $! $! $! ************************************************************************* $! ******************************** Run ********************************* $! ************************************************************************* $ run: $ P2 P1/P1 $! $! $! ************************************************************************* $! ************************* continue **************************** $! ************************************************************************* $ continue: $ goto exit $! $! ************************************************************************* $! *************************** USAGE ***************************** $! ************************************************************************* $ USAGE: Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  10. $ write sys$output " " $ write sys$output " " $ write sys$output "Usage: @PCL [SYSTEM] [PROGRAM NAME] [options] [d] [libinfo] $ write sys$output " " $ write sys$output "Where: APPLICATION - Application system: (TEST, etc) $ write sys$output " Used to link the system library (ie. LTEST.OLB/LIB) $ write sys$output " PROGRAM NAME - (TEST100, TEST200, etc) $ write sys$output " options - Options (PCLR): P = precompile $ write sys$output " C = compile $ write sys$output " L = link $ write sys$output " R = run $ write sys$output " PCLR = all the above (default) $ write sys$output " Options entered must appear in above order. $ write sys$output " valid: P PC C CL PCL not valid: CP LC PL $ write sys$output " d - Compile with #define DEBUG (default is nodebug) $ write sys$output " libinfo - Additional User link libraries: ,mydir: mylib.olb/lib $ write sys$output " " $ write sys$output "*Note: Upper case are REQUIRED, Lower case are optional $ write sys$output " " $ write sys$output "Example: @PCL TEST TEST100 PCL $ write sys$output " (compile TEST100.C, nodebug, links TEST200.OBJ including MYLIB.OLB) $ write sys$output " " $ goto exit $ $ $ exit: Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  11. $ deassign clib Compiling using a control file such as PCL.COM can be used even if you don't have embedded SQL in your program. This keeps all applications created by your shop consistent with each other. But a programmer can do still more to control how and what is precompiled in his or her source. The next section of the chapter 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, procedural and embedded SQL statements can be used to perform platform specific operations. Remember that conditional statements must be included in your source code, not the control file that will compile your code. The following Oracle statements are utilized when creating a conditional section. Statement Meaning EXEC ORACLE DEFINE symbol Define 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 End this control block Some symbols are port-specific and predefined for you when the Oracle precompilers are installed. Predefined operating- system symbols include CMS, MVS, DOS, UNIX and VMS. In the following example, conditional precompiling is shown using a predefined symbol. #include #include #include Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  12. #ifdef DOS #include #endif /* DECLARE AREA */ EXEC SQL BEGIN DECLARE SECTION; VARCHAR user_id[20]; VARCHAR passwd[20] EXEC SQL END DECLARE SECTION; /* INCLUDE AREA */ EXEC SQL INCLUDE SQLCA; /* FILE DECLARATIONS */ FILE *t_file; 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 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  13. { printf("\nSuccessfully connected to Oracle."); #ifdef DOS t_file = fopen("\\login.lst","w"); fprintf(t_file,"log in by %s",:userid); fclose(t_file); #end if; EXEC SQL COMMIT WORK RELEASE; exit(0); } } Now that you have an understanding of what the precompiler does and what you can control, creating a embedded SQL program will take front stage. The next section of this chapter focuses just on an embedded SQL program. This section will look at the basics of a source program, program requirements and how to handle errors. Embedded SQL Host Program Creating a host program that utilizes embedded SQL statements can be very beneficial. There are some specific guidelines and requirements that must be included in an embedded SQL program. This section of the chapter focuses on the basics of a precompiled program, program requirements, handling errors, host arrays, dynamic SQL, user exits, and performance tuning. As each topic is introduced, code examples will be provided in C for that section which will build into a completed program by chapters end. You then can precompile, compile, link, and execute it. This section begins with some basic concepts. Basics of a Host Program Three basic concepts must be discussed to ensure an understanding of the material that will be presented later in this chapter. These concepts are naming conventions, embedded SQL, and using PL/SQL blocks. Naming Conventions All embedded SQL programs must have the appropriate extension for the host language. This notifies the precompiler that embedded SQL is included and the program must be translated (precompiled) into the host language format. Table 50.3 indicates what extension should be used for the host language. Table 50.3. Host language file extensions. Host Language Standard File Extension Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  14. C PC COBOL PCO FORTRAN PFO Pascal PPA PL/I PPL After your program has been precompiled, a new file is created with a different extension; normal compiling and linking can then continue. When you modify your source code, remember to change the original file. In C, for example, the .PC file should be modified and not the .C file; otherwise your changes will not go into effect. The second basic concept, which is the focus of these programs, is embedded SQL statements. 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. All standard SQL commands can be used in the embedded statement. There are two types of embedded SQL statements: executable and declarative. Any SQL command can be embedded within a high-level language, but not the extended SQL commands that are included in SQL*PLUS. These include report formatting, editing SQL statements, and environment-setting commands. Executable Statements Executable statements are used to connect to Oracle; query, manipulate and control access to Oracle data; and to process data. These statements result in calls to and return codes from Oracle. There are four types of executable embedded SQL statements: data definition, data control, data manipulation and data retrieval. Later in the chapter each of these statements will be 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. Now that you know there are two sections involved in writing an embedded SQL host program, we should look at exactly what is required. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  15. The last concept is utilizing PL/SQL blocks. The next section will cover how PL/SQL blocks are included into your source program. PL/SQL Blocks Oracle precompilers treat PL/SQL blocks as a single SQL statement. This can greatly enhance the performance of your program. 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. Depending upon what type of environment you are working in, this could create an enormous amount of traffic—simply slowing everything down to a crawl. If you use a PL/SQL block, then you can pack multiple SQL statements into a section of code and execute the entire block. Because Oracle treats the entire block as a single statement, you can reduce overhead multiple times. 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 will be 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 /* 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() Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  16. { 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."); EXEC SQL COMMIT WORK RELEASE; get_employee_data(); } exit(0); } void get_employee_data() { printf("\nEmployee Number? "); scanf("%d\n",empl_number); /* BEGIN OF PL/SQL BLOCK */ EXEC SQL EXECUTE BEGIN Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  17. 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); } If you are working with a system that is database intensive, utilizing PL/SQL blocks will improve performance. Host Program Requirements Being able to pass data between Oracle and your application requires a variety of tasks to be completed successfully. There are two primary parts of an embedded SQL program that must be included: 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 This 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. There are three required sections within the data declaration area: the DECLARE section, 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 will be issued at precompile time. These variables are declared in the DECLARE section. Most host languages will 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 will store the data, and external specifies how the data is stored in the host variable. Table 50.4 shows the internal datatypes. Table 50.4. Internal datatypes. Name Code Description CHAR 1 < 255-byte, fixed-length character string Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  18. NUMBER 2 fixed or floating point number LONG 8 < 65535-byte, fixed-length character string ROWID 11 fixed-length binary number DATE 12 7-byte, fixed-length date/time value RAW 23 < 255-byte, fixed-length binary data LONGRAW 24 < 65535-byte, fixed-length binary data Table 50.5 shows the external datatypes. Table 50.5. External datatypes. Name Code Description VARCHAR2 1 < 255-byte, fixed-length character string CHAR 1 < 255-byte, fixed-length character string 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 VARNUM 6 variable-length binary number DECIMAL 7 COBOL or PL/I packed decimal LONG 8 < 65535-byte, fixed-length character string VARCHAR 9 < 65535-byte, fixed-length character string Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  19. ROWID 11 fixed-length binary number DATE 12 7-byte, fixed-length date/time value VARRAW 15 < 65533-byte, fixed-length binary data RAW 23 < 255-byte, fixed-length binary data LONGRAW 24 < 65535-byte, fixed-length binary data UNSIGNED 68 2-byte or 4-byte unsigned integer DISPLAY 91 COBOL numeric-character data It is important that the host variables (external) within the DECLARE section match the database datatype (internal). For example, if you declare a host variable such as receipt_date a character string and the database has it declared as a DATE type, you will receive an error. The following guidelines are recommended when declaring and referencing host variables. A host variable must be q explicitly declared in the DECLARE section q referenced with a colon(:) in all SQL statements and PL/SQL blocks q of a datatype supported by the host language q of a datatype compatible with that of its source or target database column A host variable must not be q subscripted q prefixed with a colon in the host language statement q used to identify a column, table, or other Oracle object q used in data definition statements such as ALTER, CREATE, and DROP A host variable can be q used anywhere an expression can be used in a SQL statement At precompile time, an association is made between variables declared and the database column type. If there is a discrepancy, a runtime error will occur; otherwise, the datatype is converted. VARCHAR variables are converted into the following structure: struct { unsigned short len; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  20. unsigned char arr[20]; } username; This structure helps eliminate character-counting algorithms. You can reference each element of the structure and manipulate it. The following example shows the syntax for the DECLARE section and how the elements of a VARCHAR can be used. #include /* DECLARE SECTION */ EXEC SQL BEGIN DECLARE SECTION; VARCHAR user_id[15]; EXEC SQL END DECLARE SECTION; main() { printf("/n What is your User ID: "); scanf("%s",user_id.arr); /* referencing the character string element */ user_id.len = strlen(user_id.arr); /* referencing the length element */ } After declaring a variety of host variables communication between the host program and Oracle needs to be established 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). When MODE=ORACLE (the default for the precompiler) or MODE=ANSI13, you must declare the SQLCA by hardcoding it, or by copying it into your program with the INCLUDE statement. If MODE=ANSI14 declaring the SQLCA is optional, however, you must declare the status variable SQLCODE. The SQLCA is a data structure that handles certain events and provides diagnostic checking between the Oracle RDMBS and the host program. SQLCA variables maintain valuable runtime statistics such as: warning codes with text, Oracle error codes and number of rows processed are convenient for handling special conditions within the host program. The following examples shows the syntax for including this file in the host program. -- declare section -- Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Đồng bộ tài khoản