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

a simple query

SAMPLE2.PFO

cursor operations

SAMPLE3.PFO

array fetches

SAMPLE4.PFO

datatype equivalencing

SAMPLE5.PFO

an Oracle Forms user exit

SAMPLE6.PFO

dynamic SQL Method 1

SAMPLE7.PFO

dynamic SQL Method 2

SAMPLE8.PFO

dynamic SQL Method 3

SAMPLE9.PFO

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

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.

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 <field1>, <field2>, ..., <result_field>');

where user_exit is a packaged procedure supplied with 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 11 of the 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.

Sample Program 9: Calling a Stored Procedure

Before trying the sample program, you must create a PL/SQL package named calldemo, by running a script named CALLDEMO.SQL, which is supplied with Pro*FORTRAN and shown below. The script can be found in the Pro*FORTRAN demo library. Check your Oracle system-specific documentation for exact spelling of the script.

CREATE OR REPLACE PACKAGE calldemo AS
TYPE name_array IS TABLE OF emp.ename%type
INDEX BY BINARY_INTEGER;
TYPE job_array IS TABLE OF emp.job%type
INDEX BY BINARY_INTEGER;
TYPE sal_array IS TABLE OF emp.sal%type
INDEX BY BINARY_INTEGER;
PROCEDURE get_employees(
dept_number IN number, -- department to query
batch_size IN INTEGER, -- rows at a time
found IN OUT INTEGER, -- rows actually returned
done_fetch OUT INTEGER, -- all done flag
emp_name OUT name_array,
job OUT job_array,
sal OUT sal_array);
END calldemo;
/
CREATE OR REPLACE PACKAGE BODY calldemo AS
CURSOR get_emp (dept_number IN number) IS
SELECT ename, job, sal FROM emp
WHERE deptno = dept_number;
-- Procedure "get_employees" fetches a batch of employee
-- rows (batch size is determined by the client/caller
-- of the procedure). It can be called from other
-- stored procedures or client application programs.
-- The procedure opens the cursor if it is not
-- already open, fetches a batch of rows, and
-- returns the number of rows actually retrieved. At
-- end of fetch, the procedure closes the cursor.
PROCEDURE get_employees(
dept_number IN number,
batch_size IN INTEGER,
found IN OUT INTEGER,
done_fetch OUT INTEGER,
emp_name OUT name_array,
job OUT job_array,
sal OUT sal_array) IS
BEGIN
IF NOT get_emp%ISOPEN THEN -- open the cursor if
OPEN get_emp(dept_number); -- not already open
END IF;
-- Fetch up to "batch_size" rows into PL/SQL table,
-- tallying rows found as they are retrieved. When all
-- rows have been fetched, close the cursor and exit
-- the loop, returning only the last set of rows found.
done_fetch := 0; -- set the done flag FALSE
found := 0;
FOR i IN 1..batch_size LOOP
FETCH get_emp INTO emp_name(i), job(i), sal(i);
IF get_emp%NOTFOUND THEN -- if no row was found
CLOSE get_emp;
done_fetch := 1; -- indicate all done
EXIT;
ELSE
found := found + 1; -- count row
END IF;
END LOOP;
END;
END;
/

The following sample program connects to Oracle, prompts the user for a department number, then calls a PL/SQL procedure named get_employees, which is stored in package calldemo. The procedure declares three PL/SQL tables as OUT formal parameters, then fetches a batch of employee data into the PL/SQL tables. The matching actual parameters are host tables. When the procedure finishes, row values in the PL/SQL tables are automatically assigned to the corresponding elements in the host tables. The program calls the procedure repeatedly, displaying each batch of employee data, until no more data is found.