Banner Oracle Pro C Template Programming Training Workbook

To view this page ensure that Adobe Flash Player version 9.0.124 or greater is installed.

Please login or register to make a comment!

...Description...... more. less.

Inc. or its subsidiaries in the U.S. and other countries. Third-par ty names and marks referenced herein are trademarks or registered trademarks of their respective owners. Table of Contents Section A: Introduction....................................................................................................... .......5 Overview....................................................................................................................... ...........5 Introduction................................................................................................................... ...........6 Section B: Introduction to Pro*C.............................................................................................. 7 Overview....................................................................................................................... ...........7 Introduction to Pro*C and Objectives......................................................................................8 Banner Rules .................................................................................................................. ........10 Banner-Specific Functions.....................................................................................................1 7 Embed SQL Into C............................................................................................................... ..19 Write a Simple Report.......................................................................................................... ..20 Section C: Datatypes and Variables........................................................................................21 Overview....................................................................................................................... .........21 Include Files.................................................................................................................. .........22 Preprocessor Directives........................................................................................................ ..23 Datatypes and Variable Declaration.......................................................................................27 C Datatypes ................................................................................................................... .........34 Banner Datatypes .............................................................................................................. .....37 Shared Indicator Variables..................................................................................................... 38 Embedding SQL Using Cursors.............................................................................................39 Prompting for Parameters....................................................................................................... 41 Compiling...................................................................................................................... .........42 Self Check .................................................................................................................... ..........53 Section D: Formatting and Displaying Output......................................................................54 Overview....................................................................................................................... .........54 Formatting Output.............................................................................................................. ....55 Displaying Output ............................................................................................................. .....56 Header and Footer Functions .................................................................................................57 Querying the Database and Displaying a Report ...................................................................58 Debugging...................................................................................................................... ........59 GURADDR.pc .................................................................................................................... ...60 Self Check .................................................................................................................... ..........71 Section E: Banner Job Submission..........................................................................................72 Overview....................................................................................................................... .........72 Banner Job Submission Forms...............................................................................................73 Banner Job Submission Tables...............................................................................................82 Self Check .................................................................................................................... ..........88 Table of Contents (Continued) Section F: Sample Data and Reports ......................................................................................89 Overview....................................................................................................................... .........89 SWRIDEN........................................................................................................................ ......90 SWRADDR........................................................................................................................ ....91 SWBPERS........................................................................................................................ ......92 SWBSTDN........................................................................................................................ .....93 SWVTERM........................................................................................................................ ....94 SWRREGS........................................................................................................................ .....95 SWVCRSE........................................................................................................................ .....99 GURADDR.pc .................................................................................................................... .100 GURADDR.lis ................................................................................................................... ..115 Section G: Example program listings ...................................................................................116 example1.c..................................................................................................................... .......116 example2.c..................................................................................................................... .......117 example3.c..................................................................................................................... .......118 example4.c and example5.c..................................................................................................119 example6.pc.................................................................................................................... ......120 Example7.pc.................................................................................................................... .....122 example8.pc.................................................................................................................... ......125 example9.pc.................................................................................................................... ......128 example10.pc................................................................................................................... .....131 example11.pc................................................................................................................... .....135 example12.pc................................................................................................................... .....140 © SunGard 2004-2005 Pro*C Template Programming Page 5 Section A: Introduction Lesson: Overview Workbook goal The objective of this course is to develop Pro*C applications which access the Banner database using Oracle Pro*C Precompiler technology and Banner Job Submission routines. Intended audience Experience in writing C is recommended, though not an absolute requirement. Programming experience in another language will suffice. Section contents Introduction................................................................................................................... ...........6 © SunGard 2004-2005 Pro*C Template Programming Page 6 Section A: Introduction Lesson: Introduction Topics Covered " Running the Oracle Precompiler and Make Files " Identify Oracle and C (host) Datatypes " Embedded SQL Syntax " Query and Manipulate Data with Embedded SQL Statements. " Preprocessor directives " Debugging " Requirements for Processing Applications from Within the Banner Job Submission Environment. " Identify Banner Job Submission Routines and Requirements " Identify Banner Security Requirements © SunGard 2004-2005 Pro*C Template Programming Page 7 Section B: Introduction to Pro*C Lesson: Overview Introduction This section provides a basic introduction to Pro*C and how it is implemented in Banner. Intended audience Technical personnel, such as programmers, programmer analysts, and application managers Objectives At the end of this section, participants will have a better understanding of: " Banner rules " Banner functions " Embedding SQL into C Prerequisites " SQLPlus " PL/SQL " C - Experience in writing C is recommended, though not an absolute requirement. Programming experience in another language will suffice. Section contents Introduction to Pro*C and Objectives......................................................................................8 Banner Rules .................................................................................................................. ........10 Banner-Specific Functions.....................................................................................................1 7 Embed SQL Into C............................................................................................................... ..19 Write a Simple Report.......................................................................................................... ..20 © SunGard 2004-2005 Pro*C Template Programming Page 8 Section B: Introduction to Pro*C Lesson: Introduction to Pro*C and Objectives What is Pro*C? Pro*C is a method of combining the computing power of a high-level language like C and the DML capabilities of SQL, allowing you to execute any SQL statement from an application program. Main function Every program has one and only one main function. It is the function the system calls first when the program is executed. Main can be defined before, after or between definitions of other functions. Example main function int main(int argc, char *argv[]) " argc is the argurment count, including the command " argv is the argument vector, an array of strings in the static data area, terminated with a null pointer Function prototypes Following ANSI standards, all functions must be fully prototyped (declared) before use. As with variable declarations, each prototype has a storage class modifier of static to avoid potential linkage problems. Print statement printf("\n string text, %type d,variable); %type: " d decimal " s string " f float " i integer " \n newline character " \t horizontal tab " \v vertical tab © SunGard 2004-2005 Pro*C Template Programming Page 9 Section B: Introduction to Pro*C Lesson: Introduction to Pro*C and Objectives (Continued) IF-THEN-ELSE statements C SQL if condition do compound statements n {} else if condition do something compound in {} else do something compound in {} if condition then do something else condition do something end if ; © SunGard 2004-2005 Pro*C Template Programming Page 10 Section B: Introduction to Pro*C Lesson: Banner Rules ANSI C standard Adherence to the ANSI C standard is paramount; any exceptions are noted below. A copy of The C Programming Language, 2nd Edition, by Brian Kernighan and Dennis Ritchie, should be standard documentation for any programmer writing or modifying Banner C code. Global variable declarations All variable declarations global to the current compilation unit, function declarations, and function prototypes must include the storage class modifier static unless they need to be available for external linkage. Global variables and function declarations are, by default, external. To support proper modularity, each program unit should only make external those functions and variables which have been determined to be necessary for other code units to access. Function prototypes All functions must be fully prototyped, following ANSI standards, either in a header file (if accessed by more than one source file) or at the top of the source file where it is declared. A complete prototype consists of the return type of the function, the function name, and the types of each parameter, along with the formal parameter names. As a matter of style, the prototype should exactly match the actual function declaration. © SunGard 2004-2005 Pro*C Template Programming Page 11 Section B: Introduction to Pro*C Lesson: Banner Rules (Continued) Goto statement The goto statement should never be used in new C code and should be removed from all existing code when possible; this also eliminates any need for labels. Use structured programming techniques instead. Example with goto statements /* parameter validation code with gotos and labels */ askparms: input(ask_p_owner,"TABLE CREATOR: ",30,ALPHA); if ( !*ask_p_owner ) goto rdowner; strcpy(p_owner,ask_p_owner); goto nexta; rdowner: strcpy(parm_no,"01"); sel_optional_ind(FIRST_ROW); if ( compare(rpt_optional_ind,"O",EQS) ) goto nexta; goto missing_parms; nexta: Example without gotos /* parameter validation code without gotos and labels */ input(ask_p_owner,"TABLE CREATOR: ",30,ALPHA); if ( !*ask_p_owner ) { strcpy(parm_no,"01"); sel_optional_ind(FIRST_ROW); if ( compare(rpt_optional_ind,"O",NES) missing_required_parm("Table Creator"); } guastdf.h and standard headers All programs should include guastdf.h, and only this file should include standard headers. This will limit the number of changes necessary for new compilers or hardware platforms and will insure that all necessary headers are included. Note that guarpfe.h includes guastdf.h, so an explicit inclusion is not necessary. © SunGard 2004-2005 Pro*C Template Programming Page 12 Section B: Introduction to Pro*C Lesson: Banner Rules (Continued) Compiler-specific or platform-specific functions No compiler or platform specific functions may be used. Only those functions found in the ANSI standard libraries are available on all supported platforms. Exceptions to this rule, such as the use of the UNIX and OpenVMS provided function sleep, may be made with management approval. Refer to The C Programming Language, 2nd Edition , by Brian Kernighan and Dennis Ritchie for a definitive listing of the functions available. Restricted ANSI features The following ANSI features are not available under otherwise compliant compilers, such as older versions of DEC C, and are not to be used unless all supported compilers implement them in the future: " the atexit and memmove functions " concatenation of adjacent string literals " ## macro expansion Also, an assignment followed by the "address of" or "dereference" operator with no intervening space will be misinterpreted by some releases of DEC C. Accordingly, use a= *b; instead of a=*b; . Finally, DEC C requires that main be of type int and return a value to the operating system. int main(int argc,char *argv[]) /* suggested portable declaration of main */ Handling of filenames All handling of filenames from the operating system is done with the makefn and parsfn functions defined in guastdf.h to provide maximum code portability. © SunGard 2004-2005 Pro*C Template Programming Page 13 Section B: Introduction to Pro*C Lesson: Banner Rules (Continued) exit2os function All programs should use the function exit2os, defined in guastdf.h, to return to the operating system. This will ensure that all necessary database and memory cleanup is performed. Application code should never use the standard function exit. Also, application code should never reach a return from within the main function; however, to prevent warnings from some compilers, the exit2os call at the bottom of main should be immediately followed by a return. Example of exit2os int main(int argc,char *argv[]) { ... /* all done */ exit2os(EXIT_SUCCESS); return 0; } guaorac.c All Pro*C programs must include the file guaorac.c and use the provided database utility functions for database connection and disconnection, and use the macro POSTORA to check for database errors. This will insulate code from future changes to Pro*C internals and provide a common interface to the database for all programs. Most importantly, the login function must be used to connect to the database with Banner security enabled. Typecasting All application and support code should Pro*C pre-compile and C compile with no warnings or errors on all supported platforms. Following ANSI standards eliminates the majority of problems, but certain compilers may be more restrictive than others. For example, when using the Pro*C pseudodatatype VARCHAR, it is necessary to explicitly typecast the arr member to a character pointer in standard function calls under some compilers. In short, when in doubt, cast. © SunGard 2004-2005 Pro*C Template Programming Page 14 Section B: Introduction to Pro*C Lesson: Banner Rules (Continued) SQL trace facility All Pro*C programs should recognize the -t command-line switch to turn on the SQL trace facility. Programs generated by SCTCCONV use rptopen to handle this option. int main(int argc,char *argv[]) { extern short sqltrace_flag; rptopen(user_pass,argc,argv); login(); if ( sqltrace_flag ) EXEC SQL ALTER SESSION SET SQL_TRACE TRUE; Many C compilers allow modification of literals by means of pointers; this is not allowed in Sungard Higher Education C code. Consider the following code: ... char *ptr="SCT"; *ptr = '\0'; ... Here ptr points to an area of storage containing the string literal "SCT", which may not be unique storage if the same literal appears elsewhere in the program. Modifying the storage pointed to by ptr may work as expected, but if the new value assigned to ptr is longer than the original literal area, then memory errors will occur. Also, some compilers will signal an error or warning message if such an operation is attempted. Check error status Always check error status after any I/O or database operation. The guastdf.h include file defines the macro POSTORA to make Oracle error checking simpler, and all file I/O operations should be followed by a check using the ferror standard function. © SunGard 2004-2005 Pro*C Template Programming Page 15 Section B: Introduction to Pro*C Lesson: Banner Rules (Continued) Static storage class Functions which return a pointer to a local variable must give the static storage class to the return variable. If the keyword static is not supplied, then the storage for the local variable may be reused by the program before the calling function is able to access the address. This is a common error which is rarely caught by the compiler or tools such as lint and may even work correctly on some machines, depending on the way that the memory heap is managed. For example, consider a function that generates a new password string and returns a pointer to the new value. The calling function will then copy this value elsewhere for storage, as the value will be lost when the password function is next called. Indicator variables Indicator variables must be used on all SQL output variables, and on all nonstring input variables (unless a non-NULL value is guaranteed.) This is to prevent truncation warnings when the target is too small for the source, and to properly handle NULL values. Refer to "Check error status" above for an example. Typedefing complex structures Complex structures which will be reused should be typedefed in order to simplify and clarify the code. For example, consider the structure and declarations for implementing a linked list of file information. Oracle datatype equivalencing Use Oracle datatype equivalencing to handle C-style null-terminated strings in preference to the VARCHAR pseudo-datatype. All SCTCCONV converted code and most subsequent Banner code uses this method. The include files guastdf.h and guaorac.c provide predefined typedefs for string sizes from 2 to 256 characters in length (1 to 255 usable characters plus the terminating null). If a particular application requires longer strings, or strings embedded within arrays, then use explicit Pro*C TYPE IS and VAR IS logic (see the Programmer's Guide to the Oracle Pro*C Precompiler for details). © SunGard 2004-2005 Pro*C Template Programming Page 16 Section B: Introduction to Pro*C Lesson: Banner Rules (Continued) Numeric datatypes Use the appropriate numeric datatype for the application, keeping in mind the limitations of each. The basic choices are a C integer type, a C floating-point type, or the Sunagrd Higher Education- provided pseudo-datatype of NUMSTR. Integers Integers are limited to whole numbers only, and in comparison to the Oracle internal NUMBER datatype have a small number of significant digits. A C integer datatype (e.g., long, unsigned int) should only be used as a SQL input/output variable if the Oracle column is a whole number that will never be larger/smaller than the ANSI- defined range for the C datatype; for example, the ANSI-defined minimal magnitudes for a long datatype are -2 31 to 2 31 -1 (approximately +/- two billion). Integer data types may be appropriate for database columns such as counters and sequences. Floating-point numbers Floating-point numbers in C have a minimum of 10 significant digits in the ANSI standard. This limitation makes them inappropriate for most currency calculations. However, all Banner- supported platforms currently have at least 15 digits of precision for the double datatype, so using double as an SQL input/output variable is acceptable provided that the database column in question is known to never exceed 15 digits. This precision should be adequate for nearly all calculations involving U.S. currency, but may be inadequate for non-U.S. currency transactions. NUMSTR pseudo-datatype All SCTCCONV converted code, and much code written subsequent to the conversion, uses the NUMSTR pseudo-datatype in order to provide a guaranteed 24 digits of precision. This datatype is implemented by representing numbers as fixed character strings, and only the four basic arithmetic operators are provided; more elaborate calculations must be performed in the database. The advantages of this datatype are the increased precision, and the elimination of the need for indicator-variable processing (since empty strings are interpreted by Oracle as NULLs.) © SunGard 2004-2005 Pro*C Template Programming Page 17 Section B: Introduction to Pro*C Lesson: Banner-Specific Functions compare A three-argument comparison between two strings or two NUMSTRs and returns true or false based on the type of comparison being done. (Anything involving a NULL is false.) NUMSTR Operation STRING EQ equivalence EQS NE non-equivalence NES LT less than LTS LE less than or equal LES GT greater than GTS GE greater than or equal GES compare(charstr1,charstr2,EQ) inlist Accepts a string variable, data type, and a set of strings, terminated by NULL. inlist(variable, DATATYPE, dVALUE d, dvalue d,NULL) " *datatype is either ALPHA for strings or NUM for NUMSTR variables Input Takes a target variable, prompt string, size of the target variable, and datatype of the target variable. input(variable, dprompt text d,size,DATATYPE) " *datatype is either ALPHA for strings or NUM for NUMSTR variables Multiply Takes and multiplies two NUMSTR arguments and returns the result into the NUMSTR target. multiply(result, arg1, arg2) Divide Takes and divides two NUMSTR arguments and returns the result into the NUMSTR target. divide(result, arg1, arg2) Note : division by zero causes program termination. © SunGard 2004-2005 Pro*C Template Programming Page 18 Section B: Introduction to Pro*C Lesson: Banner-Specific Functions (Continued) Add Takes and adds two NUMSTR arguments and returns the result into the NUMSTR target. add(result, arg1, arg2) Subtract Takes and subtracts the second NUMSTR argument from the first NUMSTR argument and returns the result into the NUMSTR target. divide(result, arg1, arg2) © SunGard 2004-2005 Pro*C Template Programming Page 19 Section B: Introduction to Pro*C Lesson: Embed SQL Into C Syntax and placement All SQL statements need to start with EXEC SQL and end with a semicolon. SQL statements can be placed anywhere within a C block, with the restriction that the declarative statements may not come after the executable statements. © SunGard 2004-2005 Pro*C Template Programming Page 20 Section B: Introduction to Pro*C Lesson: Write a Simple Report GURREGS A simple registration report based on term code and all select CRNs. Run from host and job submission All Banner Pro*C reports/processes can be run from either the host or job submission. We need to be concerned with the one up number and pulling the parameters out of the job submission run table. © SunGard 2004-2005 Pro*C Template Programming Page 21 Section C: Datatypes and Variables Lesson: Overview Introduction This section discusses datatypes, variables and cursors and their usage in Pro*C. Intended audience Technical personnel, such as programmers, programmer analysts, and application managers Objectives At the end of this section, participants will have a better understanding of: " Datatypes " Variables " Cursors Prerequisites " SQLPlus " PL/SQL " C - Experience in writing C is recommended, though not an absolute requirement. Programming experience in another language will suffice. Section contents Include Files.................................................................................................................. .........22 Preprocessor Directives........................................................................................................ ..23 Datatypes and Variable Declaration.......................................................................................27 C Datatypes ................................................................................................................... .........34 Banner Datatypes .............................................................................................................. .....37 Shared Indicator Variables..................................................................................................... 38 Embedding SQL Using Cursors.............................................................................................39 Prompting for Parameters....................................................................................................... 41 Compiling...................................................................................................................... .........42 Self Check .................................................................................................................... ..........53 © SunGard 2004-2005 Pro*C Template Programming Page 22 Section C: Datatypes and Variables Lesson: Include Files Standard C header files " Stdlib.h standard library header " Stdio.h standard input/output header communications between the program and your terminal " String.h string operations " Ctype.h character types " Math.h mathematical declarations " Stdarg.h variable argument lists " Time.h time types Standard Banner files " Guastdf.h Declares functions and macros for extended precision math, OS aware filename handling, common string operations and other miscellaneous tasks. It also includes the above standard C header files. " Guarpfe.h Declares functions that control placement of output printed on the report " Guaorac.c Declares the CHAR string, the NUMSTR string and login information to connect to the database © SunGard 2004-2005 Pro*C Template Programming Page 23 Section C: Datatypes and Variables Lesson: Preprocessor Directives Preprocessor The Pro*C preprocessor recognizes most C preprocessor commands and effectively performs the required macro substitutions, file inclusions, and conditional source text inclusions or exclusions. The Pro*C preprocessor uses the values obtained from preprocessing and alters the source output text (the generated .c output file). Example An example should clarify this point. Consider the following program fragment: #include "my_header.h" ... VARCHAR name[VC_LEN]; /* a Pro*C-supplied datatype */ char another_name[VC_LEN]; /* a pure C datatype */ ... Suppose the file my_header.h in the current directory contains, among other things, the line #define VC_LEN 20. The precompiler reads the file my_header.h, and uses the defined value of VC_LEN (i.e., 20), declares the structure of name as VARCHAR[20). char is a native type. The precompiler does not substitute 20 in the declaration of another_name[VC_LEN]. This does not matter, since the precompiler does not need to process declarations of C datatypes, even when they are used as host variables. It is left up to the C compiler's preprocessor to physically include the file my_header.h, and perform the substitution of 20 for VC_LEN in the declaration of another_name. © SunGard 2004-2005 Pro*C Template Programming Page 24 Section C: Datatypes and Variables Lesson: Preprocessor Directives (Continued) Directives The preprocessor directives that Pro*C/C++ supports are: " #define to write macros for use by the precompiler and the C compiler " #include to read other source files for use by the precompiler " #if to precompile and compile source text based on evaluation of a constant expression to 0 " #ifdef to precompile and compile source text conditionally, depending on the existence of a defined constant " #ifndef to exclude source text conditionally " #endif to end an #if or #ifdef or #ifndef command " #else to select an alternative body of source text to be precompiled and compiled, in case an #if or #ifdef or #ifndef condition is not satisfied " #elif to select an alternative body of source text to be precompiled and compiled, depending on the value of a constant or a macro argument. Directives Ignored Some C preprocessor directives are not used by the Pro*C preprocessor. Most of these directives are not relevant for the precompiler. For example, #pragma is a directive for the C compiler; the precompiler does not process it. The C preprocessor directives not processed by the precompiler are: " # to convert a preprocessor macro parameter to a string constant " ## to merge two preprocessor tokens in a macro definition " #error to produce a compile-time error message " #pragma to pass implementation-dependent information to the C compiler " #line to supply a line number for C compiler messages While the C compiler preprocessor may support these directives, Pro*C does not use them. Most of these directives are not used by the precompiler. Use these directives in your Pro*C program if your compiler supports them, but only in C, not in embedded SQL statements or declarations of variables using datatypes supplied by the precompiler, such as VARCHAR. © SunGard 2004-2005 Pro*C Template Programming Page 25 Section C: Datatypes and Variables Lesson: Preprocessor Directives (Continued) Conditional inclusion The #include, #ifdef and #endif preprocessor directives can be used to conditionally include a file that the precompiler requires. For example: #ifdef ORACLE_MODE # include <sqlca.h> #else long SQLCODE; #endif Using #define There are restrictions on the use of the #define preprocessor directive in Pro*C. The #define directive can be used to create symbolic constants for use in executable SQL statements. The following invalid example demonstrates this: #define RESEARCH_DEPT 40 ... EXEC SQL SELECT empno, sal INTO :emp_number, :salary /* host arrays */ FROM emp WHERE deptno = RESEARCH_DEPT; /* INVALID! */ The only declarative SQL statements where a #define macro is legally used are TYPE and VAR statements. So, for example, the following use of a macro is legal in Pro*C: #define STR_LEN 40 SQL Statements Not Allowed in #include Because of the way the Pro*C preprocessor handles the #include directive, as described in the previous section, the #include directive cannot be used to include files that contain embedded SQL statements. Use #include to include files that contain purely declarative statements and directives; for example, #defines, and declarations of variables and structures required by the precompiler, such as in sqlca.h. © SunGard 2004-2005 Pro*C Template Programming Page 26 Section C: Datatypes and Variables Lesson: Preprocessor Directives (Continued) EXEC SQL INCLUDE and #include Summary When an EXEC SQL INCLUDE is used as a statement in your program, the precompiler includes the source text in the output (.c) file. Therefore, there are declarative and executable embedded SQL statements in a file that is included using EXEC SQL INCLUDE. When a file using #include is included, the precompiler merely reads the file and keeps track of #defined macros. WARNING: VARCHAR declarations and SQL statements are NOT allowed in #included files. For this reason, SQL statements cannot be in files that are included using the Pro*C preprocessor #include directive. © SunGard 2004-2005 Pro*C Template Programming Page 27 Section C: Datatypes and Variables Lesson: Datatypes and Variable Declaration Storage Typically, a host program inputs data to Oracle, and Oracle outputs data to the program. Oracle stores input data in database tables and stores output data in program host variables. To store a data item, Oracle must know its datatype, which specifies a storage format and valid range of values. Datatypes Oracle recognizes two kinds of datatypes: internal and external. Internal datatypes specify how Oracle stores column values in database tables, as well as the formats used to represent pseudocolumn (values such as NULL, SYSDATE, USER, etc.) values. External datatypes specify the formats used to store values in input and output host variables. © SunGard 2004-2005 Pro*C Template Programming Page 28 Section C: Datatypes and Variables Lesson: Datatypes and Variable Declaration (Continued) Internal These internal datatypes can be quite different from C datatypes; for example, C has no datatype that is equivalent to the Oracle NUMBER datatype. However, NUMBERs can be converted between C datatypes such as float and double, with some restrictions. For example, the Oracle NUMBER datatype allows up to 38 decimal digits of precision, while no current C implementations can represent doubles with that degree of precision. The Oracle NUMBER datatype represents values exactly (within the precision limits), while floating-point formats cannot represent values such as 10.0 exactly. Name Description VARCHAR2 variable-length character string, <= 4000 bytes NVARCHAR2 or NCHAR VARYING variable-length single-byte or fixed-width multi-byte string,<= 4000 bytes NUMBER numeric value, represented in a binary coded decimal format LONG variable-length character string <=2**31-1 bytes ROWID binary value DATE fixed-length date + time value, 7 bytes RAW variable-length binary data, <=255 bytes LONG RAW variable-length binary data, <= 2**31-1 bytes CHAR fixed-length character string, <= 2000 bytes NCHAR fixed-length single-byte or fixed-width multi- byte string, <= 2000 bytes MLSLABEL tag for operating system label, 2-5 bytes BFILE external file binary data, <= 4 Gbytes BLOB binary data, <= 4 Gbytes CLOB character data, <= 4 Gbytes NCLOB multi-byte character data, <= 4 Gbytes © SunGard 2004-2005 Pro*C Template Programming Page 29 Section C: Datatypes and Variables Lesson: Datatypes and Variable Declaration (Continued) External The external datatypes include all the internal datatypes plus several datatypes that closely match C constructs. For example, the STRING external datatype refers to a C null-terminated string. Name Description VARCHAR2 variable-length character string, <=64Kbytes You specify the maximum length of a VARCHAR2(n) value in bytes, not characters. If a VARCHAR2(n) variable stores multi-byte characters, its maximum length can be less than n characters. When you precompile using the options DBMS=V6 or CHAR_MAP=VARCHAR2, Oracle assigns the VARCHAR2 datatype to all host variables that you declare as char[n] or char. NUMBER Decimal number, represented using a binary-coded floating-point format INTEGER signed integer The INTEGER datatype to store numbers that have no fractional part. An integer is a signed, 2-byte or 4- byte binary number. The order of the bytes in a word is system-dependent. You must specify a length for input and output host variables. On output, if the column value is a real number, Oracle truncates any fractional part. © SunGard 2004-2005 Pro*C Template Programming Page 30 Section C: Datatypes and Variables Lesson: Datatypes and Variable Declaration (Continued) Table, continued Name Description FLOAT real number The FLOAT datatype to store numbers that have a fractional part or that exceed the capacity of the INTEGER datatype. The number is represented using the floating-point format of your computer and typically requires 4 or 8 bytes of storage. You must specify a length for input and output host variables. Oracle can represent numbers with greater precision than most floating-point implementations because the internal format of Oracle numbers is decimal. This can cause a loss of precision when fetching into a FLOAT variable. STRING null-terminated variable length character string The STRING datatype is like the VARCHAR2 datatype, except that a STRING value is always null-terminated. When you precompile using the option CHAR_MAP=STRING, Oracle assigns the STRING datatype to all host variables that you declare as char[n] or char. © SunGard 2004-2005 Pro*C Template Programming Page 31 Section C: Datatypes and Variables Lesson: Datatypes and Variable Declaration (Continued) Table, continued Name Description VARNUM Decimal number, like NUMBER, but includes representation length component The VARNUM datatype is like the NUMBER datatype, except that the first byte of a VARNUM variable stores the length of the representation. On input, you must set the first byte of the host variable to the length of the value. On output, the host variable contains the length followed by the number as represented internally by Oracle. To accommodate the largest possible number, the host variable must be 22 bytes long. After SELECTing a column value into a VARNUM host variable, you can check the first byte to get the length of the value. Normally, there is little reason to use this datatype. LONG fixed-length character string, up to 2**31-1 bytes the LONG datatype to store fixed-length character strings. The LONG datatype is like the VARCHAR2 datatype, except that the maximum length of a LONG value is 2147483647 bytes or two gigabytes. © SunGard 2004-2005 Pro*C Template Programming Page 32 Section C: Datatypes and Variables Lesson: Datatypes and Variable Declaration (Continued) Table, continued Name Description VARCHAR Variable-length character string, <= 65533 bytes the VARCHAR datatype to store variable-length character strings. VARCHAR variables have a 2-byte length field followed by a <=65533-byte string field. However, for VARCHAR array elements, the maximum length of the string field is 65530 bytes. When you specify the length of a VARCHAR variable, be sure to include two bytes for the length field. For longer strings, use the LONG VARCHAR datatype. ROWID binary value, external length is system dependent the ROWID datatype to store binary rowids in (typically 13-byte) fixed-length fields. The field size is port-specific. Check your system-specific Oracle documentation. You can use character host variables to store rowids in a readable format. When you SELECT or FETCH a rowid into a character host variable, Oracle converts the binary value to an 18-byte character string and returns it in the format: BBBBBBBB.RRRR.FFFF where BBBBBBBB is the block in the database file, RRRR is the row in the block (the first row is 0), and FFFF is the database file. These numbers are hexadecimal. For example, the rowid 0000000E.000A.0007 points to the 11th row in the 15th block in the 7th database file. © SunGard 2004-2005 Pro*C Template Programming Page 33 Section C: Datatypes and Variables Lesson: Datatypes and Variable Declaration (Continued) Table, continued Name Description DATE fixed-length date/time value, 7 bytes VARRAW Variable-length binary data, <= 65533 bytes RAW fixed-length binary data, <= 65533 bytes LONG RAW fixed-length binary data, <= 2**31-1 bytes UNSIGNED Unsigned integer LONG VARCHAR Variable-length character string, <= 2**31-5 bytes LONG VARRAW Variable-length binary data, <= 2**31-5 bytes CHAR fixed-length character string, <= 255 bytes CHARZ fixed-length, null-terminated character string, <= 65534 bytes CHARF used in TYPE or VAR statements to force CHAR to default to CHAR, instead of VARCHAR2 or CHARZ MLSLABEL tag for operating system label, 2-5 bytes (Trusted Oracle only) © SunGard 2004-2005 Pro*C Template Programming Page 34 Section C: Datatypes and Variables Lesson: C Datatypes Host variables Host variables are the key to communication between your host program and Oracle. Typically, a precompiler program inputs data from a host variable to Oracle, and Oracle outputs data to a host variable in the program. Oracle stores input data in database columns, and stores output data in program host variables. Scalar expressions A host variable can be any arbitrary C expression that resolves to a scalar type. However, a host variable must also be an lvalue. Host arrays of most host variables are also supported. C datatypes The C datatype must be compatible with that of the source or target database column. C datatype or pseudotype Description char single character char[n] n-character array (string) int Integer short small integer long large integer float Floating-point number (usually single precision) double Floating-point number (always double precision) VARCHAR[n] Variable-length string © SunGard 2004-2005 Pro*C Template Programming Page 35 Section C: Datatypes and Variables Lesson: C Datatypes (Continued) Datatype compatibility Oracle C Description VARCHAR2(Y) (Note 1) Char single character CHAR(X) (Note 1) Char[n] VARCHAR[n] Int Short Long Float Double n-byte character array n-byte variable-length character array integer small integer large integer floating-point number double-precision floating-point number NUMBER Int integer NUMBER(P,S) (Note 2) Short Long Float double char char[n] VARCHAR[n] small integer large integer floating-point number double-precision floating-point number single character n-byte character array n-byte variable-length character array DATE Char[n] VARCHAR[n] n-byte character array n-byte variable-length character array LONG Char[n] VARCHAR[n] n-byte character array n-byte variable-length character array RAW(X) (Note 1) Unsigned char[n] VARCHAR[n] n-byte character array n-byte variable-length character array LONG RAW Unsigned char[n] VARCHAR[n] n-byte character array n-byte variable-length character array © SunGard 2004-2005 Pro*C Template Programming Page 36 Section C: Datatypes and Variables Lesson: C Datatypes (Continued) Datatype Compatibility, cont. Oracle C Description ROWID Unsigned char[n] VARCHAR[n] n-byte character array n-byte variable-length character array MLSLABEL Unsigned char[n] VARCHAR[n] n-byte character array n-byte variable-length character array Ranges " X ranges from 1 to 255. 1 is the default value. " Y ranges from 1 to 4000. " P ranges from 2 to 38. " S ranges from -84 to 127. © SunGard 2004-2005 Pro*C Template Programming Page 37 Section C: Datatypes and Variables Lesson: Banner Datatypes NUMSTR Number character string conversion. A char[27] with 16 digits to the left and 8 digits to the right of the decimal point. CHARx Null terminated character string for character arrays from 2 to 256 characters in length. CHARx, allows strings to be stored where x represents length from 1 to 255 characters. © SunGard 2004-2005 Pro*C Template Programming Page 38 Section C: Datatypes and Variables Lesson: Shared Indicator Variables Indicator variables A "null"flag attached to host variables. Oracle assigns a value to an indicator variable based on the following: -1 a NULL was returned 0 Oracle assigned an intact column value to the host variable -2 Oracle assigned truncated column variable and the original column length could not be determined >0 Oracle assigned a truncated value into the host variable The indicator length is the original column length in bytes. Declaration All indicator variables must be declared explicitly as a 2-byte integer, prefixed with a colon in SQL statements, and immediately follow its host variable in SQL statements and PL/SQL blocks. An indicator variable must not be prefixed with a colon in host language statements, follow its host variable in host language statements, or be an Oracle reserved word. Variable scope A variable defined outside of all functions is a global variable. It can be accessed in any function within the program. Variables defined in a function are local to that function and cannot be accessed by any other function. © SunGard 2004-2005 Pro*C Template Programming Page 39 Section C: Datatypes and Variables Lesson: Embedding SQL Using Cursors Function prototypes and definitions " Prototype A prototype tells the compiler what type of parameters to expect and what to return depending on the function type. " Definition The specification tells how to use the function. Enclosed in curly braces is the body of the function, beginning with optional declared local variables and followed by statements to be executed. MODE Integer parameter for functions containing SQL cursors. Mode has three macros defined with values that determine what the cursor should do. " FIRST_ROW Value of 0, opens the cursor and gets the first row. " NEXT_ROW Value of 1, to get the next row. " CLOSE_CURSOR Value of 2, to close the cursor. © SunGard 2004-2005 Pro*C Template Programming Page 40 Section C: Datatypes and Variables Lesson: Embedding SQL Using Cursors (Continued) Cursors Identifies the current row in the set of rows returned by the query. To manipulate a cursor, it must be declared, opened, fetched and closed. " Declare The declare cursor statement defines a cursor by giving it a unique name and associating it with a query. o EXEC SQL DECLARE cursor_xxx CURSOR FOR " Open The open statement is used to execute the query and identify the active set. The cursor is positioned just before the first row of the active set. o EXEC SQL OPEN cursor_xxx; " Fetch Retrieves rows from the active set and specifies the output host variables that will contain the results. The first time a fetch is executed the cursor moves from before the first row in the active set to the first row. o EXEC SQL FETCH cursor_xxx INTO " Close When done fetching rows from the active set, close the cursor to free the resources. o EXEC SQL CLOSE cursor_xxx; POSTORA The Oracle dberror function is called which checks Oracle for error conditions. If an error condition exists dberror prints the error number, related text and exits the program. © SunGard 2004-2005 Pro*C Template Programming Page 41 Section C: Datatypes and Variables Lesson: Prompting for Parameters Comments To comment out lines or just to have informative text a single line can be commented out using /* text */ or /* comment text that goes for multiple lines has a start in one place and the */ at the end. Labels Used in conjuction with a goto statement, it causes program control to jump to a statement bearing the label name. A colon is used to separate a labeled statement from its label. Once the focus has moved down, the program continues downward through the function. hello: printf("hello label d); if (a > b) goto hello; Input Prompts text, then accepts a string of up to size bites placing the result in a target variable. For character string input, type should be ALPHA, for NUMSTR input, type should be NUM. In the event the user enters an invalid number or the string is too long, a warning message is printed and the prompt will prompt again. input(target_variable, "Prompt Text d, size, type); © SunGard 2004-2005 Pro*C Template Programming Page 42 Section C: Datatypes and Variables Lesson: Compiling Make file Compiling C code under the UNIX operating system is accomplished through the use of the make command, a special-purpose scripting language usually provided as part of the UNIX language development environment. A makefile is needed for all but the most basic make operations; it specifies the actions to be taken to perform particular tasks, such as making an executable from a C source file, or building an object file from a Pro*C file. sctproc.mk To use the sctproc.mk makefile, go to the directory containing the source code to be compiled, and enter a make command specifying both the makefile and the file to be generated. Example*: make -f sctproc.mk guraddr (*this assumes you are working in your home directory) Refer to your operating system documentation for further details on makefile construction and usage. © SunGard 2004-2005 Pro*C Template Programming Page 43 Section C: Datatypes and Variables Lesson: Compiling (Continued) Example Example: GURADDR.pc /*****************************************************************************/ /* GURADDR.pc */ /* AUDIT TRAIL: 6.0 */ /* 1. New address reporting program. GT 04/13/04 */ /* AUDIT TRAIL END */ /*****************************************************************************/ #include "guarpfe.h" EXEC SQL INCLUDE guaorac.c; #define SCT_DEBUG /***********************/ /*Variable Declarations*/ /***********************/ EXEC SQL BEGIN DECLARE SECTION; static CHAR200 command_type=""; static NUMSTR pidm=""; static CHAR10 id=""; static CHAR40 name=""; static CHAR7 parm_atyp_code=""; static CHAR2 parm_reenter=""; static CHAR2 parm_gender=""; static CHAR7 parm_term_code=""; static CHAR31 institution=""; static CHAR8 rptname=""; static NUMSTR pageno=""; static NUMSTR one=""; static CHAR2 valid_ind=""; static CHAR31 street_line1=""; static CHAR34 addr_locn=""; static CHAR31 title_var=""; static CHAR12 run_date=""; static CHAR9 run_time=""; /*Shared Indicator variables*/ short Ind_01; short Ind_02; short Ind_03; short Ind_04; short Ind_05; short Ind_06; EXEC SQL END DECLARE SECTION; © SunGard 2004-2005 Pro*C Template Programming Page 44 Section C: Datatypes and Variables Lesson: Compiling (Continued) Example (cont.) /*function prototypes*/ static int get_students(int); static void get_parameters(void); static void print_address(); static void header(void); static void footer(void); static int get_institution(int mode); static int validate_one_up_no(int mode); static int validate_atyp_code(int mode); static int validate_term_code(int mode); static void convert_parms(void); static int sel_title(int mode); int main(int argc,char *argv[]) { extern short sqltrace_flag; rptopen(user_pass,argc,argv); login(); /*TABLE DEFINITIONS*/ /*Table 1 Page Number, Institution, Run date and time */ /*Table 2 Report name, Report Description */ /*Table 3 Column Headings */ /*Table 4 ID, Name, Address */ /*Table 5 Report Control Information */ table(T_DEFINE,1,1,15,30,70,105,120,122,132,NULL); table(T_DEFINE,2,1,25,30,70,110,132,NULL); table(T_DEFINE,3,1,10,13,43,45,80,NULL); table(T_DEFINE,4,1,10,13,43,45,80,NULL); table(T_DEFINE,5,1,132,NULL); strcpy(rptname,"GURADDR"); strcpy(pageno,"0"); strcpy(one,"1"); get_institution(FIRST_ROW); sel_title(FIRST_ROW); get_parameters(); header(); report(get_students,print_address,NULL,footer); if ( sqltrace_flag ) EXEC SQL ALTER SESSION SET SQL_TRACE TRUE; exit2os(EXIT_SUCCESS); } © SunGard 2004-2005 Pro*C Template Programming Page 45 Section C: Datatypes and Variables Lesson: Compiling (Continued) Example (cont.) static int get_students(int mode) { #ifdef SCT_DEBUG printf("\n in get students"); #endif EXEC SQL DECLARE cursor_001 CURSOR FOR select swriden_pidm, swriden_id, rtrim(swriden_last_name,' ')||', '||rtrim(swriden_first_name,' '), swraddr_street_line1, rtrim(swraddr_city,' ')||', '||swraddr_stat_code||' '|| swraddr_zip from swriden, swraddr, swbpers, swbstdn where swriden_pidm = swraddr_pidm and swriden_pidm = swbstdn_pidm and swraddr_pidm = swbstdn_pidm and swbpers_pidm = swbstdn_pidm and swbstdn_term_code_eff = :parm_term_code and swraddr_atyp_code = :parm_atyp_code and swriden_pidm = swbpers_pidm and swbpers_sex = :parm_gender order by rtrim(swriden_last_name,' ')||', '||rtrim(swriden_first_name,' '); if ( mode==CLOSE_CURSOR ) { EXEC SQL CLOSE cursor_001; POSTORA; } if ( mode==FIRST_ROW ) { EXEC SQL OPEN cursor_001; POSTORA; } EXEC SQL FETCH cursor_001 INTO :pidm:Ind_01, :id:Ind_02, :name:Ind_03, :street_line1:Ind_04, :addr_locn:Ind_05; POSTORA; #ifdef SCT_DEBUG printf("\n pidm ->%s<-", pidm); printf("\n id ->%s<-", id); printf("\n name ->%s<-", name); printf("\n sl1 ->%s<-", street_line1); printf("\n addr locn ->%s<-", addr_locn); #endif © SunGard 2004-2005 Pro*C Template Programming Page 46 Section C: Datatypes and Variables Lesson: Compiling (Continued) Example (cont.) if ( NO_ROWS_FOUND ) { *pidm='\0'; *id='\0'; *name='\0'; *street_line1='\0'; *addr_locn='\0'; return FALSE; } return TRUE; } /*end get_students*/ static void get_parameters(void) { printf("****************************\n"); printf("* Starting GURADDR 6.0 *\n"); printf("****************************\n"); starting_parms: input(parm_term_code,"\nEnter Admit Term Code: ",6,ALPHA); if (!*parm_term_code) goto missing_parms; else { strcpy(valid_ind,"N"); validate_term_code(FIRST_ROW); if (compare(valid_ind,"Y",EQS) ) goto atyp_selection; else goto invalid_term_code; } atyp_selection: input(parm_atyp_code,"\nEnter Address Type Code: ",2,ALPHA); if (!*parm_atyp_code) goto missing_parms; else { strcpy(valid_ind,"N"); validate_atyp_code(FIRST_ROW); if (compare(valid_ind,"Y",EQS) ) goto gender_selection; else goto invalid_atyp_code; } © SunGard 2004-2005 Pro*C Template Programming Page 47 Section C: Datatypes and Variables Lesson: Compiling (Continued) Example (cont.) gender_selection: input(parm_gender,"\nEnter Gender Code [M]ale, [F]emale: ",1,ALPHA); if (!*parm_gender) goto missing_parms; if (inlist(parm_gender,ALPHA,"M","m","F","f",NULL)) goto re_enter_parms; else goto invalid_gender; invalid_parms: printf("\nInvalid one up number......ABORTING!!!"); goto error_finished; invalid_atyp_code: printf("\nInvalid Address Type code......ABORTING!!!"); goto error_finished; invalid_term_code: printf("\nInvalid Term code......ABORTING!!!"); goto error_finished; invalid_gender: printf("\nInvalid Gender......ABORTING!!!"); goto error_finished; missing_parms: printf("\nRequired parameter is missing, aborting job!"); goto error_finished; re_enter_parms: input(parm_reenter,"\nWould you like to re-enter the parameters? Y/[N]",1,ALPHA); if (inlist(parm_reenter,ALPHA,"Y","y",NULL)) goto starting_parms; else goto finished; error_finished: exit2os(EXIT_FAILURE); finished: convert_parms(); } /*end get parameters */ © SunGard 2004-2005 Pro*C Template Programming Page 48 Section C: Datatypes and Variables Lesson: Compiling (Continued) Example (cont.) static void print_address() { table(T_BEGIN,4); prtstr(id); newcol(); prtstr(name); newcol(); prtstr(street_line1); newline(); prtstr(addr_locn); skipline(1); table(T_END); } static void header(void) { table(T_BEGIN,1); prtstr("PAGE: "); add(pageno,pageno,one); prtnum(pageno,"999999"); newcol(); setmode(M_CENTER); prtstr(institution); newcol(); prtstr(run_date); newcol(); prtstr(run_time); table(T_END); table(T_BEGIN,2); prtstr("REPORT: "); prtstr(rptname); newcol(); newline(); setmode(M_CENTER); prtstr(title_var); table(T_END); skipline(2); table(T_BEGIN,3); prtstr("ID"); newcol(); prtstr("Name"); newcol(); prtstr("Address"); newcol(); table(T_END); } © SunGard 2004-2005 Pro*C Template Programming Page 49 Section C: Datatypes and Variables Lesson: Compiling (Continued) Example (cont.) static int get_institution(int mode) { EXEC SQL DECLARE cursor_005 CURSOR FOR select gubinst_name, TO_CHAR(sysdate,'DD-MON-YYYY'), TO_CHAR(sysdate,'HH:MI PM') from gubinst; if ( mode==CLOSE_CURSOR ) { EXEC SQL CLOSE cursor_005; POSTORA; return TRUE; } if ( mode==FIRST_ROW ) { EXEC SQL OPEN cursor_005; POSTORA; } EXEC SQL FETCH cursor_005 INTO :institution:Ind_01, :run_date:Ind_02, :run_time:Ind_03; POSTORA; if ( NO_ROWS_FOUND ) { *institution='\0'; *run_date='\0'; *run_time='\0'; return FALSE; } return TRUE; } © SunGard 2004-2005 Pro*C Template Programming Page 50 Section C: Datatypes and Variables Lesson: Compiling (Continued) Example (cont.) static void footer(void) { newpage(); header(); table(T_BEGIN,5); skipline(2); prtstr("*********REPORT CONTROL INFORMATION*********"); newcol(); prtstr("Report Name: "); prtstr(rptname); newcol(); prtstr("Term Code: "); prtstr(parm_term_code); newcol(); prtstr("Address Type Code: "); prtstr(parm_atyp_code); newcol(); prtstr("Gender: "); prtstr(parm_gender); table(T_END); } static int validate_atyp_code(int mode) { #ifdef SCT_DEBUG printf("\n validating atyp_code ......."); #endif EXEC SQL DECLARE cursor_002 CURSOR FOR SELECT 'Y' FROM stvatyp WHERE stvatyp_code = upper(:parm_atyp_code); if

less