Release 8.0
A54654_01
 Library |
 Product |
 Contents |
 Index |
| PL/SQL User's Guide and Reference Release 8.0 A54654_01 |
|
This appendix provides several PL/SQL programs to guide you in writing your own. The sample programs illustrate several important PL/SQL concepts and features.
Running the Programs
Sample 1. FOR Loop
Sample 2. Cursors
Sample 3. Scoping
Sample 4. Batch Transaction Processing
Sample 5. Embedded PL/SQL
Sample 6. Calling a Stored Procedure
The sample programs in this appendix and many others throughout this Guide are available online. So, they are preceded by the following comment:
-- available online in file <filename>
The list below gives their locations in this Guide and the names of the corresponding online files. However, the exact name and storage location of an online file are system dependent.
| Online File | Location in Guide |
|---|---|
|
examp1 |
|
|
examp2 |
|
|
examp3 |
|
|
examp4 |
|
|
examp5 |
|
|
examp6 |
|
|
examp7 |
|
|
examp8 |
|
|
examp11 |
|
|
examp12 |
|
|
examp13 |
|
|
examp14 |
|
|
sample1 |
|
|
sample2 |
|
|
sample3 |
|
|
sample4 |
|
|
sample5 |
|
|
sample6 |
Some samples are run interactively from SQL*Plus; others are run from Pro*C programs. You can experiment with the samples from any Oracle account. But, the Pro*C examples expect you to use the SCOTT/TIGER account.
Before trying the samples, you must create some database tables, then load the tables with data. You do that by running two SQL*Plus scripts, EXAMPBLD and EXAMPLOD, supplied with PL/SQL. These scripts can be found in the PL/SQL installation library. Check the Oracle installation or user's guide for your system.
Below is a listing of the SQL*Plus script EXAMPBLD. The CREATE statements in this script build the database tables processed by the sample programs. To run the script, invoke SQL*Plus, then issue the following command:
SQL> START EXAMPBLD
set compatibility V6
/
drop table accounts
/
create table accounts(
account_id number(4) not null,
bal number(11,2))
/
create unique index accounts_index on accounts (account_id)
/
drop table action
/
create table action(
account_id number(4) not null,
oper_type char(1) not null,
new_value number(11,2),
status char(45),
time_tag date not null)
/
drop table bins
/
create table bins(
bin_num number(2) not null,
part_num number(4),
amt_in_bin number(4))
/
drop table data_table
/ create table data_table(
exper_num number(2),
n1 number(5),
n2 number(5),
n3 number(5))
/
drop table emp
/
create table emp(
empno number(4) not null,
ename char(10),
job char(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2))
/
drop table inventory
/
create table inventory(
prod_id number(5) not null,
product char(15),
quantity number(5))
/
drop table journal
/
create table journal(
account_id number(4) not null,
action char(45) not null,
amount number(11,2),
date_tag date not null)
/
drop table num1_tab
/
create table num1_tab(
sequence number(3) not null,
num number(4))
/
drop table num2_tab
/
create table num2_tab(
sequence number(3) not null,
num number(4))
/
drop table purchase_record
/
create table purchase_record(
mesg char(45),
purch_date date)
/
drop table ratio
/
create table ratio(
sample_id number(3) not null,
ratio number)
/
drop table result_table
/
create table result_table(
sample_id number(3) not null,
x number,
y number)
/
drop table sum_tab
/
create table sum_tab(
sequence number(3) not null,
sum number(5))
/
drop table temp
/
create table temp(
num_col1 number(9,4),
num_col2 number(9,4),
char_col char(55))
/
create or replace package personnel as
type charArrayTyp is table of varchar2(10)
index by binary_integer;
type numArrayTyp is table of float
index by binary_integer; procedure get_employees(
dept_number in integer,
batch_size in integer,
found in out integer,
done_fetch out integer,
emp_name out charArrayTyp,
job-title out charArrayTyp,
salary out numArrayTyp);
end personnel;
/
create or replace package body personnel as
cursor get_emp (dept_number integer) is
select ename, job, sal from emp
where deptno = dept_number;
procedure get_employees(
dept_number in integer,
batch_size in integer,
found in out integer,
done_fetch out integer,
emp_name out charArrayTyp,
job_title out charArrayTyp,
salary out numArrayTyp) is
begin
if not get_emp%isopen then
open get_emp(dept_number);
end if;
done_fetch := 0;
found := 0;
for i in 1..batch_size loop
fetch get_emp into emp_name(i),
job_title(i), salary(i);
if get_emp%notfound then
close get_emp;
done_fetch := 1;
exit;
else
found := found + 1;
end if;
end loop;
end get_employees;
end personnel;
/
Below is a listing of the SQL*Plus script EXAMPLOD. The INSERT statements in this script load (or reload) the database tables processed by the sample programs. To run the script, invoke SQL*Plus in the same Oracle account from which you ran EXAMPBLD, then issue the following command:
SQL> START EXAMPLOD
delete from accounts
/
insert into accounts values (1,1000.00)
/
insert into accounts values (2,2000.00)
/
insert into accounts values (3,1500.00)
/
insert into accounts values (4,6500.00)
/
insert into accounts values (5,500.00)
/
delete from action
/
insert into action values
(3,'u',599,null,sysdate)
/
insert into action values
(6,'i',20099,null,sysdate)
/
insert into action values
(5,'d',null,null,sysdate)
/
insert into action values
(7,'u',1599,null,sysdate)
/
insert into action values
(1,'i',399,null,sysdate)
/
insert into action values
(9,'d',null,null,sysdate)
/
insert into action values
(10,'x',null,null,sysdate)
/
delete from bins
/ insert into bins values (1, 5469, 650)
/
insert into bins values (2, 7243, 450)
/
insert into bins values (3, 5469, 120)
/
insert into bins values (4, 5469, 300)
/
insert into bins values (5, 6085, 415)
/
insert into bins values (6, 5469, 280)
/
insert into bins values (7, 8159, 619)
/
delete from data_table
/
insert into data_table values
(1, 10, 167, 17)
/
insert into data_table values
(1, 16, 223, 35)
/
insert into data_table values
(2, 34, 547, 2)
/
insert into data_table values
(3, 23, 318, 11)
/
insert into data_table values
(1, 17, 266, 15)
/
insert into data_table values
(1, 20, 117, 9)
/
delete from emp
/
insert into emp values
(7369,'SMITH','CLERK',7902,TO_DATE('12-17-80','MM-DD-YY'),
800,NULL,20)
/
insert into emp values
(7499,'ALLEN','SALESMAN',7698,TO_DATE('02-20-81','MM-DD-YY'),
1600,300,30)
/
insert into emp values
(7521,'WARD','SALESMAN',7698,TO_DATE('02-22-81','MM-DD-YY'),
1250,500,30)
/ insert into emp values
(7566,'JONES','MANAGER',7839,TO_DATE('04-02-81','MM-DD-YY'),
2975,NULL,20)
/
insert into emp values
(7654,'MARTIN','SALESMAN',7698,TO_DATE('09-28-81','MM-DD-YY'),
1250,1400,30)
/
insert into emp values
(7698,'BLAKE','MANAGER',7839,TO_DATE('05-1-81','MM-DD-YY'),
2850,NULL,30)
/
insert into emp values
(7782,'CLARK','MANAGER',7839,TO_DATE('06-9-81','MM-DD-YY'),
2450,NULL,10)
/
insert into emp values
(7788,'SCOTT','ANALYST',7566,SYSDATE-85,3000,NULL,20)
/
insert into emp values
(7839,'KING','PRESIDENT',NULL,TO_DATE('11-17-81','MM-DD-YY'),
5000,NULL,10)
/
insert into emp values
(7844,'TURNER','SALESMAN',7698,TO_DATE('09-8-81','MM-DD-YY'),
1500,0,30)
/
insert into emp values
(7876,'ADAMS','CLERK',7788,SYSDATE-51,1100,NULL,20)
/
insert into emp values
(7900,'JAMES','CLERK',7698,TO_DATE('12-3-81','MM-DD-YY'),
950,NULL,30)
/
insert into emp values
(7902,'FORD','ANALYST',7566,TO_DATE('12-3-81','MM-DD-YY'),
3000,NULL,20)
/
insert into emp values
(7934,'MILLER','CLERK',7782,TO_DATE('01-23-82','MM-DD-YY'),
1300,NULL,10)
/
delete from inventory
/
insert into inventory values
('TENNIS RACKET', 3)
/ insert into inventory values
('GOLF CLUB', 4)
/
insert into inventory values
('SOCCER BALL', 2)
/
delete from journal
/
delete from num1_tab
/
insert into num1_tab values (1, 5)
/
insert into num1_tab values (2, 7)
/
insert into num1_tab values (3, 4)
/
insert into num1_tab values (4, 9)
/
delete from num2_tab
/
insert into num2_tab values (1, 15)
/
insert into num2_tab values (2, 19)
/
insert into num2_tab values (3, 27)
/
delete from purchase_record
/
delete from ratio
/
delete from result_table
/
insert into result_table values (130, 70, 87)
/
insert into result_table values (131, 77, 194)
/
insert into result_table values (132, 73, 0)
/
insert into result_table values (133, 81, 98)
/
delete from sum_tab
/
delete from temp
/
commit
The following example uses a simple FOR loop to insert ten rows into a database table. The values of a loop index, counter variable, and either of two character strings are inserted. Which string is inserted depends on the value of the loop index.
Not applicable.
-- available online in file SAMPLE1
DECLARE
x NUMBER := 100;
BEGIN
FOR i IN 1..10 LOOP
IF MOD(i,2) = 0 THEN -- i is even
INSERT INTO temp VALUES (i, x, 'i is even');
ELSE
INSERT INTO temp VALUES (i, x, 'i is odd');
END IF;
x := x + 100;
END LOOP;
COMMIT;
END;
SQL> SELECT * FROM temp ORDER BY col1;
COL1 COL2 MESSAGE
----- ------- ---------
1 100 i is odd
2 200 i is even
3 300 i is odd
4 400 i is even
5 500 i is odd
6 600 i is even
7 700 i is odd
8 800 i is even
9 900 i is odd
10 1000 i is even
10 records selected.
The next example uses a cursor to select the five highest paid employees from the emp table.
SQL> SELECT ename, empno, sal FROM emp ORDER BY sal DESC;
ENAME EMPNO SAL
---------- ----------- --------
KING 7839 5000
SCOTT 7788 3000
FORD 7902 3000
JONES 7566 2975
BLAKE 7698 2850
CLARK 7782 2450
ALLEN 7499 1600
TURNER 7844 1500
MILLER 7934 1300
WARD 7521 1250
MARTIN 7654 1250
ADAMS 7876 1100
JAMES 7900 950
SMITH 7369 800
14 records selected.
-- available online in file SAMPLE2
DECLARE
CURSOR c1 is
SELECT ename, empno, sal FROM emp
ORDER BY sal DESC; -- start with highest paid employee
my_ename CHAR(10);
my_empno NUMBER(4);
my_sal NUMBER(7,2);
BEGIN
OPEN c1;
FOR i IN 1..5 LOOP
FETCH c1 INTO my_ename, my_empno, my_sal;
EXIT WHEN c1%NOTFOUND; /* in case the number requested */
/* is more than the total */
/* number of employees */
INSERT INTO temp VALUES (my_sal, my_empno, my_ename);
COMMIT;
END LOOP;
CLOSE c1;
END;
SQL> SELECT * FROM temp ORDER BY col1 DESC;
COL1 COL2 MESSAGE
--------- -------- -------
5000 7839 KING
3000 7902 FORD
3000 7788 SCOTT
2975 7566 JONES
2850 7698 BLAKE
The following example illustrates block structure and scope rules. An outer block declares two variables named x and counter and loops four times. Inside this loop is a sub-block that also declares a variable named x. The values inserted into the temp table show that the two x's are indeed different.
Not applicable.
-- available online in file SAMPLE3
DECLARE
x NUMBER := 0;
counter NUMBER := 0;
BEGIN
FOR i IN 1..4 LOOP
x := x + 1000;
counter := counter + 1;
INSERT INTO temp VALUES (x, counter, 'outer loop');
/* start an inner block */
DECLARE
x NUMBER := 0; -- this is a local version of x
BEGIN
FOR i IN 1..4 LOOP
x := x + 1; -- this increments the local x
counter := counter + 1;
INSERT INTO temp VALUES (x, counter, 'inner loop');
END LOOP;
END;
END LOOP;
COMMIT;
END;
SQL> SELECT * FROM temp ORDER BY col2;
COL1 COL2 MESSAGE
------- -------- -------------
1000 1 OUTER loop
1 2 inner loop
2 3 inner loop
3 4 inner loop
4 5 inner loop
2000 6 OUTER loop
1 7 inner loop
2 8 inner loop
3 9 inner loop
4 10 inner loop
3000 11 OUTER loop
1 12 inner loop
2 13 inner loop
3 14 inner loop
4 15 inner loop
4000 16 OUTER loop
1 17 inner loop
2 18 inner loop
3 19 inner loop
4 20 inner loop
20 records selected.
In the next example the accounts table is modified according to instructions stored in the action table. Each row in the action table contains an account number, an action to be taken (I, U, or D for insert, update, or delete), an amount by which to update the account, and a time tag used to sequence the transactions.
On an insert, if the account already exists, an update is done instead. On an update, if the account does not exist, it is created by an insert. On a delete, if the row does not exist, no action is taken.
SQL> SELECT * FROM accounts ORDER BY account_id;
ACCOUNT_ID BAL
---------- ---------
1 1000
2 2000
3 1500
4 6500
5 500
SQL> SELECT * FROM action ORDER BY time_tag;
ACCOUNT_ID O NEW_VALUE STATUS TIME_TAG
---------- - ---------- ------------------------ ---------
3 u 599 18-NOV-88
6 i 20099 18-NOV-88
5 d 18-NOV-88
7 u 1599 18-NOV-88
1 i 399 18-NOV-88
9 d 18-NOV-88
10 x 18-NOV-88
7 records selected.
-- available online in file SAMPLE4 DECLARE
CURSOR c1 IS
SELECT account_id, oper_type, new_value FROM action
ORDER BY time_tag
FOR UPDATE OF status;
BEGIN
FOR acct IN c1 LOOP -- process each row one at a time
acct.oper_type := upper(acct.oper_type);
/*----------------------------------------*/
/* Process an UPDATE. If the account to */
/* be updated doesn't exist, create a new */
/* account. */
/*----------------------------------------*/
IF acct.oper_type = 'U' THEN
UPDATE accounts SET bal = acct.new_value
WHERE account_id = acct.account_id;
IF SQL%NOTFOUND THEN -- account didn't exist. Create it.
INSERT INTO accounts
VALUES (acct.account_id, acct.new_value);
UPDATE action SET status =
'Update: ID not found. Value inserted.'
WHERE CURRENT OF c1;
ELSE
UPDATE action SET status = 'Update: Success.'
WHERE CURRENT OF c1;
END IF;
/*--------------------------------------------*/
/* Process an INSERT. If the account already */
/* exists, do an update of the account */
/* instead. */
/*--------------------------------------------*/
ELSIF acct.oper_type = 'I' THEN
BEGIN
INSERT INTO accounts
VALUES (acct.account_id, acct.new_value);
UPDATE action set status = 'Insert: Success.'
WHERE CURRENT OF c1;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN -- account already exists
UPDATE accounts SET bal = acct.new_value
WHERE account_id = acct.account_id;
UPDATE action SET status =
'Insert: Acct exists. Updated instead.'
WHERE CURRENT OF c1;
END;
/*--------------------------------------------*/
/* Process a DELETE. If the account doesn't */
/* exist, set the status field to say that */
/* the account wasn't found. */
/*--------------------------------------------*/
ELSIF acct.oper_type = 'D' THEN
DELETE FROM accounts
WHERE account_id = acct.account_id;
IF SQL%NOTFOUND THEN -- account didn't exist.
UPDATE action SET status = 'Delete: ID not found.'
WHERE CURRENT OF c1;
ELSE
UPDATE action SET status = 'Delete: Success.'
WHERE CURRENT OF c1;
END IF;
/*--------------------------------------------*/
/* The requested operation is invalid. */
/*--------------------------------------------*/
ELSE -- oper_type is invalid
UPDATE action SET status =
'Invalid operation. No action taken.'
WHERE CURRENT OF c1;
END IF;
END LOOP;
COMMIT;
END;
SQL> SELECT * FROM accounts ORDER BY account_id;
ACCOUNT_ID BAL
---------- ---------
1 399
2 2000
3 599
4 6500
6 20099
7 1599
6 records selected.
SQL> SELECT * FROM action ORDER BY time_tag;
ACCOUNT_ID O NEW_VALUE STATUS TIME_TAG
---------- - ---------- ------------------------ ---------
3 u 599 Update: Success. 18-NOV-88
6 i 20099 Insert: Success. 18-NOV-88
5 d Delete: Success. 18-NOV-88
7 u 1599 Update: ID not found. 18-NOV-88
Value inserted.
1 i 399 Insert: Acct exists. 18-NOV-88
Updated instead.
9 d Delete: ID not found. 18-NOV-88
10 x Invalid operation. 18-NOV-88
No action taken.
7 records selected.
The following example shows how you can embed PL/SQL in a high-level host language such as C and demonstrates how a banking debit transaction might be done.
SQL> SELECT * FROM accounts ORDER BY account_id;
ACCOUNT_ID BAL
---------- ---------
1 1000
2 2000
3 1500
4 6500
5 500
/* available online in file SAMPLE5 */
#include <stdio.h>
char buf[20];
EXEC SQL BEGIN DECLARE SECTION;
int acct;
double debit;
double new_bal;
VARCHAR status[65];
VARCHAR uid[20];
VARCHAR pwd[20];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
main()
{
extern double atof();
strcpy (uid.arr,"scott");
uid.len=strlen(uid.arr);
strcpy (pwd.arr,"tiger");
pwd.len=strlen(pwd.arr);
printf("\n\n\tEmbedded PL/SQL Debit Transaction Demo\n\n");
printf("Trying to connect...");
EXEC SQL WHENEVER SQLERROR GOTO errprint;
EXEC SQL CONNECT :uid IDENTIFIED BY :pwd;
printf(" connected.\n"); for (;;) /* Loop infinitely */
{
printf("\n** Debit which account number? (-1 to end) ");
gets(buf);
acct = atoi(buf);
if (acct == -1) /* Need to disconnect from Oracle */
{ /* and exit loop if account is -1 */
EXEC SQL COMMIT RELEASE;
exit(0);
}
printf(" What is the debit amount? ");
gets(buf);
debit = atof(buf);
/* ---------------------------------- */
/* ----- Begin the PL/SQL block ----- */
/* ---------------------------------- */
EXEC SQL EXECUTE
DECLARE
insufficient_funds EXCEPTION;
old_bal NUMBER;
min_bal NUMBER := 500;
BEGIN
SELECT bal INTO old_bal FROM accounts
WHERE account_id = :acct;
-- If the account doesn't exist, the NO_DATA_FOUND
-- exception will be automatically raised.
:new_bal := old_bal - :debit;
IF :new_bal >= min_bal THEN
UPDATE accounts SET bal = :new_bal
WHERE account_id = :acct;
INSERT INTO journal
VALUES (:acct, 'Debit', :debit, SYSDATE);
:status := 'Transaction completed.';
ELSE
RAISE insufficient_funds;
END IF;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
:status := 'Account not found.';
:new_bal := -1;
WHEN insufficient_funds THEN
:status := 'Insufficient funds.';
:new_bal := old_bal; WHEN OTHERS THEN
ROLLBACK;
:status := 'Error: ' || SQLERRM(SQLCODE);
:new_bal := -1;
END;
END-EXEC;
/* -------------------------------- */
/* ----- End the PL/SQL block ----- */
/* -------------------------------- */
status.arr[status.len] = '\0'; /* null-terminate */
/* the string */
printf("\n\n Status: %s\n", status.arr);
if (new_bal >= 0)
printf(" Balance is now: $%.2f\n", new_bal);
} /* End of loop */
errprint:
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("\n\n>>>>> Error during execution:\n");
printf("%s\n",sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK RELEASE;
exit(1);
}
Embedded PL/SQL Debit Transaction Demo
Trying to connect... connected.
** Debit which account number? (-1 to end) 1
What is the debit amount? 300
Status: Transaction completed.
Balance is now: $700.00
** Debit which account number? (-1 to end) 1
What is the debit amount? 900
Status: Insufficient funds.
Balance is now: $700.00
** Debit which account number? (-1 to end) 2
What is the debit amount? 500
Status: Transaction completed.
Balance is now: $1500.00
** Debit which account number? (-1 to end) 2
What is the debit amount? 100
Status: Transaction completed.
Balance is now: $1400.00
** Debit which account number? (-1 to end) 99
What is the debit amount? 100
Status: Account not found.
** Debit which account number? (-1 to end) -1
SQL> SELECT * FROM accounts ORDER BY account_id;
ACCOUNT_ID BAL
---------- -----
1 700
2 1400
3 1500
4 6500
5 500
SQL> SELECT * FROM journal ORDER BY date_tag;
ACCOUNT_ID ACTION AMOUNT DATE_TAG
---------- -------------------------- ---------- ---------
1 Debit 300 28-NOV-88
2 Debit 500 28-NOV-88
2 Debit 100 28-NOV-88
This Pro*C program connects to Oracle, prompts the user for a department number, then calls a procedure named get_employees, which is stored in a package named personnel. The procedure declares three index-by tables as OUT formal parameters, then fetches a batch of employee data into the index-by tables. The matching actual parameters are host arrays.
When the procedure finishes, it automatically assigns all row values in the index-by tables to corresponding elements in the host arrays. The program calls the procedure repeatedly, displaying each batch of employee data, until no more data is found.
SQL> SELECT ename, empno, sal FROM emp ORDER BY sal DESC;
ENAME EMPNO SAL
---------- ----------- --------
KING 7839 5000
SCOTT 7788 3000
FORD 7902 3000
JONES 7566 2975
BLAKE 7698 2850
CLARK 7782 2450
ALLEN 7499 1600
TURNER 7844 1500
MILLER 7934 1300
WARD 7521 1250
MARTIN 7654 1250
ADAMS 7876 1100
JAMES 7900 950
SMITH 7369 800
14 records selected.
/* available online in file SAMPLE6 */
#include <stdio.h>
#include <string.h>
typedef char asciz;
EXEC SQL BEGIN DECLARE SECTION;
/* Define type for null-terminated strings. */
EXEC SQL TYPE asciz IS STRING(20);
asciz username[20];
asciz password[20];
int dept_no; /* which department to query */
char emp_name[10][21];
char job[10][21];
float salary[10]; int done_flag;
int array_size;
int num_ret; /* number of rows returned */
int SQLCODE;
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE sqlca;
int print_rows(); /* produces program output */
int sqlerror(); /* handles unrecoverable errors */
main()
{
int i;
/* Connect to Oracle. */
strcpy(username, "SCOTT");
strcpy(password, "TIGER");
EXEC SQL WHENEVER SQLERROR DO sqlerror();
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("\nConnected to Oracle as user: %s\n\n", username);
printf("Enter department number: ");
scanf("%d", &dept_no);
fflush(stdin);
/* Print column headers. */
printf("\n\n");
printf("%-10.10s%-10.10s%s\n", "Employee", "Job", "Salary");
printf("%-10.10s%-10.10s%s\n", "--------", "---", "------");
/* Set the array size. */
array_size = 10;
done_flag = 0;
num_ret = 0;
/* Array fetch loop - ends when NOT FOUND becomes true. */
for (;;)
{
EXEC SQL EXECUTE
BEGIN personnel.get_employees
(:dept_no, :array_size, :num_ret, :done_flag,
:emp_name, :job, :salary);
END;
END-EXEC;
print_rows(num_ret);
if (done_flag)
break;
}
/* Disconnect from Oracle. */
EXEC SQL COMMIT WORK RELEASE;
exit(0);
}
print_rows(n)
int n;
{
int i;
if (n == 0)
{
printf("No rows retrieved.\n");
return;
}
for (i = 0; i < n; i++)
printf("%10.10s%10.10s%6.2f\n",
emp_name[i], job[i], salary[i]);
}
sqlerror()
{
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("\nOracle error detected:");
printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK WORK RELEASE;
exit(1);
}
Connected to Oracle as user: SCOTT
Enter department number: 20
Employee Job Salary
-------- --- ------
SMITH CLERK 800.00
JONES MANAGER 2975.00
SCOTT ANALYST 3000.00
ADAMS CLERK 1100.00
FORD ANALYST 3000.00
