View the exhibit and examine the structure of the EMPLOYEE table.
EMPLOYEE_SEQ is an existing sequence.
Examine the following block of code:
Which statement is true about the above block of code?
A.
It consists of two transactions
B.
It consists of a single transaction,
C.
The data is automatically committed after the block execution ends,
D.
It gives an error on execution because sequences cannot be used in anonymous blocks.
B
it is a single transaction.
A transaction ends when any of the following actions occurs:
A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause.
In a commit, a user explicitly or implicitly requested that the changes in the transaction be made permanent. Changes made by the transaction are permanent and visible to other users only after a transaction commits. The transaction shown in Figure 10-1 ends with a commit.
A user runs a DDL command such as CREATE, DROP, RENAME, or ALTER.
The database issues an implicit COMMIT statement before and after every DDL statement. If the current transaction contains DML statements, then Oracle Database first commits the transaction and then runs and commits the DDL statement as a new, single-statement transaction.
A user exits normally from most Oracle Database utilities and tools, causing the current transaction to be implicitly committed. The commit behavior when a user disconnects is application-dependent and configurable.
Note:
Applications should always explicitly commit or undo transactions before program termination.
A client process terminates abnormally, causing the transaction to be implicitly rolled back using metadata stored in the transaction table and the undo segment.
B
B
result A is correct
declare
v_num number(5);
begin
begin
select ORDERS_SEQ.nextval into v_num from dual;
end;
begin
select ORDERS_SEQ.nextval into v_num from dual;
end;
dbms_output.put_line(v_num);
end;
what’s the matter of the sequence here.. we’re talkin’ about transactions.
Here we have just two things:
1) one transaction
2) another wrong reply to a right question
LOL, Obviously the correct answer is B
SQL> begin
2 begin
3 insert into emp(empno,ename,hiredate) values
4 (103,’Adam’,sysdate);
5 end;
6 begin
7 insert into emp(empno,ename,hiredate) values
8 (500,’Jones’,sysdate);
9 end;
10 end;
11 /
PL/SQL procedure successfully completed.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———- ————– ————
7845 Clara Engineer 102 01-SEP-13 4000 500 20
500 Jones 30-NOV-15
100 King Engineer 101 17-JUN-87 24000 400 10
103 Adam 30-NOV-15
195 Sara Engineer 102 01-OCT-14 2800 200 20
101 John Clerk 102 01-AUG-11 2000 300 10
102 Santa Manager 0 01-OCT-07 3000 300 10
7898 Smitha Engineer 102 01-OCT-14 4000 300 20
8 rows selected.
not yet replied, when you exit session, you will be prompted to commit or rollback.\
Answer is B)
so answer is A and C
wrong C: U must have auto commit activated for thr session.
Only B is correct.
B
What about sequence.nextval – thats an autonomous transaction….
So there re there 3 trasactions in play here.
C & D are plain wrong.
Leaving A and B as possible solutions. As Oracle doen’t ask if there are 3 transactions they must not be refering to the 2 autonomous transactions, but rather the single transaction in the block of code.
“best” Answer A.