Table 3 - 1. Pro*FORTRAN Sample Programs Sample Program 1: Simple Query. This program connects to Oracle. Sample Program 7: Dynamic SQL Method 2.
A Sample Programs. Oracle inter Media ('. A.1 Sample OCI C Program for Modifying Images or Testing Image. Two additional PL/SQL sample application packages are.
Sample Programs. Once you have installed inter. Media, you may choose to run the inter.
Program: Description addempPROC.pc: This Pro*C sample program prompts the user for information and inserts the corresponding data as a new employee into the EMP table. A Sample PL/SQL Programs. This appendix provides several PL/SQL programs to guide you in writing your own. The sample programs illustrate several important PL/SQL.
- Gradiance SQL Tutorial. Introduction to Pro*C Embedded SQL. so that you do not have to type in your username and password every time when running a sample program.
- The ODBC code sample prompts you for an ODBC data source name. You will then be prompted to enter a query and the sample will display the results of the query.
Media image OCI C program. This program can also be used as a test to confirm successful installation. This section describes how to run the inter.
Media image sample program. The inter. Media image sample files are located in < ORACLE_HOME> /ord/img/demo on UNIX and < ORACLE_HOME> \ord\img\demo on Windows where < ORACLE_HOME> is the Oracle home directory. A. 1. 1 Sample Program Installation Steps. For inter. Media image features, see the README. ORACLE_HOME> /ord/img/demo (on UNIX), and < ORACLE_HOME> \ord\img\demo (on Windows), where < ORACLE_HOME> is the Oracle home directory. A. 1. 2 Running the Program.
The file imgdemo. Media image features can be used from within a program. The program is written in C and uses OCI, Oracle Call Interface, to access the database and use inter. Media image features. The program operates on imgdemo. BMP) image in the demo directory.
Optionally, you can supply an image file name on the command line, provided the file resides in the same directory as the program. In either case, once the image has been manipulated by inter. Media, the resulting image is written to the file imgdemo. When the program is run, it deletes and re- creates a table named IMGDEMOTAB in the SCOTT schema of the default database. This table is used to hold the program data. Once the table is created, a reference to the image file is inserted into the table.
The data is then loaded into the table and converted to JFIF using the process. Copy( ) method of ORDImage. The image properties are extracted within the database using the set. Properties( ) method. An UPDATE statement is issued after the set. Properties( ) invocation. This is required to make the object attributes permanent because the set.
Properties( ) invocation has updated only a local copy of the type attributes. Next, the process( ) method is used to cut and scale the image within the database. This is followed by an update that commits the change. The program cuts a portion of the image 1.
This subimage is scaled to twice its original size and the resulting image is written out to the file system in a file named imgdemo. Upon completion, the program leaves the imgdemo. It also leaves the table IMGDEMOTAB in the SCOTT schema of the database. Execute the program by typing imgdemo on the command line. Use the command shown in Example A- 1.
Example A- 1 Execute the Sample Program from the Command Line. The program displays a number of messages describing its progress, along with any errors encountered in the event that something was not set up correctly. Expect to see the following messages. Dropping table IMGDEMOTAB.. Creating and populating table IMGDEMOTAB.. Loading data into cartridge..
Modifying image characteristics.. Writing image to file imgdemo. Disconnecting from database.. Logged off and detached from server. Demo completed successfully. If the program encounters any errors, it is likely that either inter.
Media software has not been installed correctly, or the database has not been started. If the program completes successfully, the original image and the resulting image, which has undergone the cutting and scaling described earlier, can be viewed with common image rendering tools. See Section A. 2 for a description of the Java sample application that is provided to help you learn to use the multimedia client- side Java classes so you can build your own applications.
Sample Programs. This chapter provides several embedded SQL programs to guide you in writing your own. These programs illustrate the key concepts and features of Pro*FORTRAN programming and demonstrate techniques that let you take full advantage of SQL's power and flexibility. Each sample program in this chapter is available online. Table 3 - 1 shows the usual filenames of the sample programs. However, the exact filenames are system- dependent. For specific filenames, see your Oracle system- specific documentation. Filename. Demonstrates..
SAMPLE1. PFOa simple query. SAMPLE2. PFOcursor operations. SAMPLE3. PFOarray fetches. SAMPLE4. PFOdatatype equivalencing. SAMPLE5. PFOan Oracle Forms user exit.
SAMPLE6. PFOdynamic SQL Method 1. SAMPLE7. PFOdynamic SQL Method 2. SAMPLE8. PFOdynamic SQL Method 3. SAMPLE9. PFOcalling a stored procedure. Table 3 - 1. Pro*FORTRAN Sample Programs. Sample Program 1: Simple Query. This program connects to Oracle, prompts the user for an employee number, queries the database for the employee's name, salary, and commission, then displays the result.
The program ends when the user enters a zero employee number. PROGRAM QUERY. EXEC SQL BEGIN DECLARE SECTION. CHARACTER*1. 0 UID. CHARACTER*1. 0 PWD. INTEGER EMPNO. CHARACTER*1. 0 ENAME. REAL SAL. REAL COMM.
INTEGER*2 ICOMM. EXEC SQL END DECLARE SECTION.
INTEGER TOTAL. EXEC SQL INCLUDE SQLCA. EXEC SQL WHENEVER SQLERROR DO CALL SQLERR. LOG ON TO ORACLE.
UID = 'SCOTT'. PWD = 'TIGER'. EXEC SQL CONNECT : UID IDENTIFIED BY : PWD.
PRINT *, 'CONNECTED TO ORACLE AS USER: ', UID. QUERY LOOP REPEATS UNTIL THE USER ENTERS A 0. TOTAL = 0. PRINT *, '\NENTER EMPLOYEE NUMBER (0 TO QUIT): '. READ '(I1. 0)', EMPNO. IF (EMPNO . EQ. 0) CALL SIGNOFF (TOTAL). EXEC SQL WHENEVER NOT FOUND GOTO 7. EXEC SQL SELECT ENAME, SAL, COMM.
INTO : ENAME, : SAL, : COMM: ICOMM. FROM EMP. 3 WHERE EMPNO = : EMPNO. PRINT *, 'EMPLOYEE SALARY COMMISSION\N'.
IF (ICOMM . EQ. - 1) THEN. PRINT '(A1. 0, 2. X, F7. 2, A1. 2)', ENAME, SAL, ' NULL'. PRINT '(A1. 0, 2.
X, F7. 2, 5. X, F7. ENAME, SAL, COMM. TOTAL = TOTAL + 1.
GOTO 2. 00. 0. PRINT *, 'NOT A VALID EMPLOYEE NUMBER - TRY AGAIN.'. GOTO 2. 00. 0. SUBROUTINE SIGNOFF (NUMQ). INTEGER NUMQ. EXEC SQL INCLUDE SQLCA. PRINT *, 'TOTAL NUMBER QUERIED WAS: ', NUMQ. PRINT *, 'HAVE A GOOD DAY.'. EXEC SQL COMMIT WORK RELEASE. SUBROUTINE SQLERR.
EXEC SQL INCLUDE SQLCA. EXEC SQL WHENEVER SQLERROR CONTINUE. PRINT *, 'ORACLE ERROR DETECTED: '. PRINT '(7. 0A1)', SQLEMC. EXEC SQL ROLLBACK WORK RELEASE. Sample Program 2: Cursor Operations.
This program connects to Oracle, declares and opens a cursor, fetches the names, salaries, and commissions of all salespeople, displays the results, then closes the cursor. PROGRAM CURSOR. EXEC SQL BEGIN DECLARE SECTION. CHARACTER*1. 0 UID.
CHARACTER*1. 0 PWD. CHARACTER*1. 0 ENAME. REAL SAL. REAL COMM. EXEC SQL END DECLARE SECTION. EXEC SQL INCLUDE SQLCA. EXEC SQL WHENEVER SQLERROR DO CALL SQLERR.
LOG ON TO ORACLE. UID = 'SCOTT'. PWD = 'TIGER'. EXEC SQL CONNECT : UID IDENTIFIED BY : PWD. PRINT *, 'CONNECTED TO ORACLE AS USER: ', UID. DECLARE THE CURSOR.
EXEC SQL DECLARE SALESPEOPLE CURSOR FOR. SELECT ENAME, SAL, COMM. FROM EMP. 3 WHERE JOB LIKE 'SALES%'. EXEC SQL OPEN SALESPEOPLE. PRINT *, 'SALESPERSON SALARY COMMISSION\N'.
LOOP, FETCHING ALL SALESPERSON'S STATISTICS. EXEC SQL WHENEVER NOT FOUND DO CALL SIGNOFF. EXEC SQL FETCH SALESPEOPLE INTO : ENAME, : SAL, : COMM. PRINT '(1. X, A1. X, F7. 2, 5. X, F7. ENAME, SAL, COMM. GOTO 3. 00. 0. SUBROUTINE SIGNOFF.
EXEC SQL INCLUDE SQLCA. EXEC SQL CLOSE SALESPEOPLE. PRINT *, 'HAVE A GOOD DAY.'. EXEC SQL COMMIT WORK RELEASE. SUBROUTINE SQLERR.
EXEC SQL INCLUDE SQLCA. EXEC SQL WHENEVER SQLERROR CONTINUE. PRINT *, 'ORACLE ERROR DETECTED: '.
PRINT '(7. 0A1)', SQLEMC. EXEC SQL ROLLBACK WORK RELEASE. Sample Program 3: Fetching in Batches. This program logs on to Oracle, declares and opens a cursor, fetches in batches using arrays, and prints the results using the subroutine PRTRES. PROGRAM ARRAYS. EXEC SQL BEGIN DECLARE SECTION.
CHARACTER*1. 0 UID. CHARACTER*1. 0 PWD. CHARACTER*1. 0 ENAME(5).
INTEGER EMPNO(5). REAL SAL(5).
EXEC SQL END DECLARE SECTION. NUMBER OF ROWS RETURNED, AND NUMBER TO PRINT. INTEGER NUMRET. INTEGER NUMP. EXEC SQL INCLUDE SQLCA.
EXEC SQL WHENEVER SQLERROR DO CALL SQLERR. LOG ON TO ORACLE. UID = 'SCOTT'. PWD = 'TIGER'. EXEC SQL CONNECT : UID IDENTIFIED BY : PWD. PRINT *, 'CONNECTED TO ORACLE AS USER: ', UID. DECLARE THE CURSOR, THEN OPEN IT. EXEC SQL DECLARE C1 CURSOR FOR.
SELECT EMPNO, ENAME, SAL. FROM EMP. EXEC SQL OPEN C1.
NUMRET = 0. * LOOP, FETCHING AND PRINTING BATCHES. UNTIL NOT FOUND BECOMES TRUE. EXEC SQL WHENEVER NOT FOUND GOTO 3. EXEC SQL FETCH C1 INTO : EMPNO, : ENAME, : SAL. NUMP = SQLERD(3) - NUMRET.
CALL PRTRES (NUMP, EMPNO, ENAME, SAL). NUMRET = SQLERD(3). GOTO 2. 00. 0. * PRINT FINAL SET OF ROWS, IF ANY. NUMP = SQLERD(3) - NUMRET. IF (NUMP . GT. 0) CALL PRTRES (NUMP, EMPNO, ENAME, SAL). CALL SIGNOFF. SUBROUTINE PRTRES (NUMP, EMPNO, ENAME, SAL). INTEGER NUMP. INTEGER EMPNO(NUMP).
CHARACTER*1. 0 ENAME(NUMP). REAL SAL(NUMP). PRINT HEADER. PRINT *, 'EMPLOYEE NUMBER EMPLOYEE NAME SALARY\N'. PRINT BATCH OF ROWS. DO 7. 00. 0 I = 1, NUMP. PRINT '(1. X, I4, 1.
X, A1. 0, 5. X, F7. EMPNO(I), ENAME(I), SAL(I). SUBROUTINE SIGNOFF. EXEC SQL INCLUDE SQLCA. EXEC SQL CLOSE C1.
PRINT *, 'HAVE A GOOD DAY.'. EXEC SQL COMMIT WORK RELEASE. SUBROUTINE SQLERR.
EXEC SQL INCLUDE SQLCA. EXEC SQL WHENEVER SQLERROR CONTINUE.
PRINT *, 'ORACLE ERROR DETECTED: '. PRINT '(7. 0A1)', SQLEMC. EXEC SQL ROLLBACK WORK RELEASE. Sample Program 4: Datatype Equivalencing. After connecting to Oracle, this program creates a database table named IMAGE in the SCOTT account, then simulates the insertion of bitmap images of employee numbers into the table.
Datatype equivalencing lets the program use the Oracle external datatype LONG RAW to represent the images. Later, when the user enters an employee number, the number's "bitmap" is selected from the IMAGE table and pseudo- displayed on the terminal screen. PROGRAM DTYEQV. EXEC SQL BEGIN DECLARE SECTION. CHARACTER*1. 0 UID. CHARACTER*1. 0 PWD. INTEGER EMPNO.
CHARACTER*1. 0 ENAME. REAL SAL. REAL COMM. CHARACTER*8. 19. 2 BUFFER.
EXEC SQL VAR BUFFER IS LONG RAW. INTEGER SELECTION.
EXEC SQL END DECLARE SECTION. CHARACTER*1. 0 REPLY. EXEC SQL INCLUDE SQLCA. EXEC SQL WHENEVER SQLERROR DO CALL SQLERR.
LOG ON TO ORACLE. UID = 'SCOTT'. PWD = 'TIGER'. EXEC SQL CONNECT : UID IDENTIFIED BY : PWD. PRINT *, 'CONNECTED TO ORACLE AS USER: ', UID. PRINT *, 'PROGRAM IS ABOUT TO DROP THE IMAGE '. TABLE - OK [Y/N]? READ '(A1. 0)', REPLY.
IF ((REPLY(1: 1) . NE. 'Y') . AND. (REPLY(1: 1) .
NE. 'Y')). 1 CALL SIGNOFF. EXEC SQL WHENEVER SQLERROR CONTINUE.
EXEC SQL DROP TABLE IMAGE. IF (SQLCDE . EQ. 0) THEN. PRINT *, 'TABLE IMAGE HAS BEEN DROPPED - '. CREATING NEW TABLE.'. ELSE IF (SQLCDE .
EQ. - 9. 42) THEN. PRINT *, 'TABLE IMAGE DOES NOT EXIST - '. CREATING NEW TABLE.'. CALL SQLERR. EXEC SQL WHENEVER SQLERROR DO CALL SQLERR. EXEC SQL CREATE TABLE IMAGE.
EMPNO NUMBER(4) NOT NULL, BITMAP LONG RAW). EXEC SQL DECLARE EMPCUR CURSOR FOR. SELECT EMPNO, ENAME FROM EMP.
EXEC SQL OPEN EMPCUR. PRINT *, 'INSERTING BITMAPS INTO IMAGE FOR ALL EMPLOYEES..'. EXEC SQL WHENEVER NOT FOUND GOTO 1. EXEC SQL FETCH EMPCUR INTO : EMPNO, : ENAME.
CALL GETIMG (EMPNO, BUFFER). EXEC SQL INSERT INTO IMAGE VALUES (: EMPNO, : BUFFER). PRINT *, 'EMPLOYEE ', ENAME, '..... IS DONE!'. GOTO 7. EXEC SQL CLOSE EMPCUR.
EXEC SQL COMMIT WORK. PRINT *, 'DONE INSERTING BITMAPS. NEXT, LETS DISPLAY SOME.'. BEGINNING OF DISPLAY LOOP. SELECTION = 0. PRINT *, '\NENTER EMPLOYEE NUMBER (0 TO QUIT): '. READ '(I1. 0)', SELECTION.
IF (SELECTION . EQ. CALL SIGNOFF. EXEC SQL WHENEVER NOT FOUND GOTO 1. EXEC SQL SELECT EMP. EMPNO, ENAME, SAL, NVL(COMM,0), BITMAP.
INTO : EMPNO, : ENAME, : SAL, : COMM, : BUFFER. FROM EMP, IMAGE. 3 WHERE EMP. EMPNO = : SELECTION. AND EMP. EMPNO = IMAGE. EMPNO. CALL SHWIMG (BUFFER). PRINT *, '\NEMPLOYEE ', ENAME, ' HAS SALARY ', SAL. AND COMMISSION ', COMM.
GOTO 1. 20. 00. 1. PRINT *, 'NOT A VALID EMPLOYEE NUMBER - TRY AGAIN.'. GOTO 1. 20. 00. SUBROUTINE GETIMG (ENUM, BUF). INTEGER ENUM. CHARACTER*8. 19. 2 BUF. INTEGER I. DO 1. I = 1, 8. 19. 2. BUF(I: I) = '*'.
SUBROUTINE SHWIMG (BUF). CHARACTER*8. 19. 2 BUF. INTEGER I. PRINT *, ' ***************************'. DO 2. 20. 00 I = 1, 9. PRINT *, ' ***************************'. SUBROUTINE SIGNOFF.
EXEC SQL INCLUDE SQLCA. PRINT *, 'HAVE A GOOD DAY.'.
EXEC SQL COMMIT WORK RELEASE. SUBROUTINE SQLERR. EXEC SQL INCLUDE SQLCA. EXEC SQL WHENEVER SQLERROR CONTINUE. PRINT *, 'ORACLE ERROR DETECTED: '. PRINT '(7. 0A1)', SQLEMC. EXEC SQL ROLLBACK WORK RELEASE.
Sample Program 5: Oracle Forms User Exit. This user exit concatenates form fields. To call the user exit from a Oracle Forms trigger, use the syntax< user_exit> ('CONCAT < field.
Oracle Forms and CONCAT is the name of the user exit. A sample CONCAT form invokes the user exit. For more information about Oracle Forms user exits, see Chapter 1. Programmer's Guide to the Oracle Precompilers. Note: The sample code listed is for a Oracle*Forms user exit and is not intended to be compiled in the same manner as the other sample programs listed in this chapter. INTEGER FUNCTION CONCAT (CMD,CMDL,ERR,ERRL,INQRY).
EXEC SQL BEGIN DECLARE SECTION. LOGICAL*1 VALUE(8.
LOGICAL*1 FINAL(2. LOGICAL*1 FIELD(8. EXEC SQL END DECLARE SECTION. EXEC SQL INCLUDE SQLCA. EXEC SQL WHENEVER SQLERROR GO TO 9. LOGICAL*1 CMD(8.
LOGICAL*1 ERR(8. INTEGER*2 CMDL, ERRL, INQRY. CERR IS A DYNAMICALLY BUILT ERROR MESSAGE RETURNED. TO SQL*FORMS. LOGICAL*1 CERR(8. TEMPORARY VARIABLES TO DO STRING MANIPULATIONS.
INTEGER*2 CMDCNT. INTEGER*2 FLDCNT. INTEGER*2 FNLCNT. INITIALIZE VARIABLES.
DO 1 I = 1, 8. 1. FIELD(I) = ' '. 1 VALUE(I) = ' '. DO 2 I = 1, 2. 41. FINAL(I) = ' '. FNLCNT = 0. STRIP CONCAT FROM COMMAND LINE. CMDCNT = 7. * LOOP UNTIL END OF COMMAND LINE. DO WHILE (CMDCNT .
LE. CMDL). * PARSE EACH FIELD DELIMITED BY A COMMA. FLDCNT = 0. DO WHILE ((CMD(CMDCNT) . NE. ','). AND.(CMDCNT . LE. CMDL)). FLDCNT = FLDCNT + 1. FIELD(FLDCNT) = CMD(CMDCNT).