QUESTION 73
--------------------------------------------------------------
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 emp_rec employees%ROWTYPE;
3 BEGIN
4 SELECT * INTO emp_rec FROM employees WHERE employee_id = 123;
5 IF SQL%NOTFOUND THEN
6 DBMS_OUTPUT.PUT_LINE('record not found');
7 ELSE
8 DBMS_OUTPUT.PUT_LINE('Employee ' || emp_rec.first_name || ' ' ||
9 emp_rec.last_name|| 'salary is ' || emp_rec.salary);
10 END IF;
11 END;
12 /
Employee Shanta Vollmansalary is 6500
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 emp_rec employees%ROWTYPE;
3 BEGIN
4 SELECT * INTO emp_rec FROM employees WHERE employee_id = 223;
5 IF SQL%NOTFOUND THEN
6 DBMS_OUTPUT.PUT_LINE('record not found');
7 ELSE
8 DBMS_OUTPUT.PUT_LINE('Employee ' || emp_rec.first_name || ' ' ||
9 emp_rec.last_name|| 'salary is ' || emp_rec.salary);
10 END IF;
11* END;
SQL> /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4
ANS:B
------------------------------------------------------------------------------
question 75
-------------------------------------------------------------------------------
1 CREATE OR REPLACE PROCEDURE wording IS
2 TYPE Definition IS RECORD(word VARCHAR2(20),
3 meaning VARCHAR2(200));
4 lexicon definition;
5 PROCEDURE add_entry(word_list IN OUT Definition) IS
6 BEGIN
7 word_list.word := 'aardvark';
8 lexicon.word := 'aardwolf';
9 END add_entry;
10 BEGIN
11 add_entry(lexicon);
12 DBMS_OUTPUT.PUT_LINE(word_list.word);
13 DBMS_OUTPUT.PUT_LINE(lexicon.word);
14* END wording;
SQL> /
Warning: Procedure created with compilation errors.
SQL> show error
Errors for PROCEDURE WORDING:
LINE/COL ERROR
-------- -----------------------------------------------------------------
12/1 PL/SQL: Statement ignored
12/22 PLS-00201: identifier 'WORD_LIST.WORD' must be declared
SQL>
ANS : A
-----------------------------------------------------------------------------------
----
question 76
-----------------------------------------------------------------------------------
---
SQL> DECLARE
2 cursor c_emp_cursor IS
3 SELECT employee_id,last_name FROM employees
4 WHERE department_id = 30;
5 BEGIN
6 FOR emp_record IN c_emp_cursor
7 LOOP
8 DBMS_OUTPUT.PUT_LINE(emp_record.employee_id || ' ' || emp_record.last_name);
9 END LOOP;
10 END;
11 /
114 Raphaely
115 Khoo
116 Baida
117 Tobias
118 Himuro
119 Colmenares
PL/SQL procedure successfully completed.
--------------------------------------------------------------
QUESTION 80
-------------------------------------------------------------
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 emp_name employee.last_name%TYPE;
3 emp_job employee.job_id%type;
4 CURSOR c1 IS
5 SELECT last_name,job_id FROM employees
6 WHERE job_id LIKE '%CLERK%' AND manager_id > 120;
7 BEGIN
8 FOR emp_name,emp_job IN c1 LOOP
9 DBMS_OUTPUT.PUT_LINE('Name = ' || emp_name || ',Job = '|| emp_job);
10 END LOOP;
11 END;
12 /
FOR emp_name,emp_job IN c1 LOOP
*
ERROR at line 8:
ORA-06550: line 8, column 13:
PLS-00103: Encountered the symbol "," when expecting one of the following:
in
ANSWER : D
--------------------------------------------------------
question 82
---------------------------------------------------------
SQL> DECLARE
2 emp_column VARCHAR2(30):= 'last_name';
3 table_name VARCHAR2(30):= 'emp';
4 temp_var VARCHAR2(30);
5 BEGIN
6 temp_var := emp_column;
7 SELECT column_name INTO temp_var FROM user_tab_cols
8 WHERE table_name = 'employees';
9 AND column_name = UPPER(emp_column);
10 temp_var := table_name;
11 SELECT object_name INTO temp_var FROM user_objects
12 WHERE object_name = UPPER(table_name)
13 AND
14 object_type = 'TABLE';
15 EXCEPTION
16 WHEN NO_DATA_FOUND THEN
17 DBMS_OUTPUT.PUT_LINE('No data found for SELECT on || temp_var');
18 END;
19 /
AND column_name = UPPER(emp_column);
*
ERROR at line 9:
ORA-06550: line 9, column 1:
PLS-00103: Encountered the symbol "AND" when expecting one of the following:
( begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << continue close current delete fetch lock
insert open rollback savepoint set sql execute commit forall
merge pipe purge
ORA-06550: line 9, column 36:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
. ( ) , * % & - + / at mod remainder rem <an exponent (**)>
and or || multiset
ORA-06550: line 18, column 4:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the
following:
end not pragma final instantiable order overriding static
member constructor map
ANSWER : B
-----------------------------------------------------------------
Question 83
-------------------------------------------------------------
1 CREATE OR REPLACE PROCEDURE raise_salary(emp_id IN NUMBER,
2 amount IN NUMBER,extra IN NUMBER DEFAULT 50)
3 IS
4 BEGIN
5 UPDATE employees
6 SET salary = salary + NVL(amount,0) + extra
7 WHERE employee_id = emp_id;
8* END raise_salary;
SQL> /
Procedure created.
1 DECLARE
2 emp_num NUMBER(6) := 7900;
3 bonus NUMBER(6);
4 merit NUMBER(4);
5 BEGIN
6 raise_salary(121,NULL,25);
7 raise_salary(111,extra => 25, amount => NULL);
8* END;
9 /
PL/SQL procedure successfully completed.
---------------------------------------------
SQL> DECLARE
2 emp_num NUMBER(6) := 7900;
3 bonus NUMBER(6);
4 merit NUMBER(4);
5 BEGIN
6 raise_salary(7845);
7 raise_salary(emp_num,extra => 25);
8 raise_salary(7845,NULL,25);
9 raise_salary(emp_num,extra => 25, amount => NULL);
10 END;
11 /
raise_salary(7845);
*
ERROR at line 6:
ORA-06550: line 6, column 1:
PLS-00306: wrong number or types of arguments in call to 'RAISE_SALARY'
ORA-06550: line 6, column 1:
PL/SQL: Statement ignored
ORA-06550: line 7, column 1:
PLS-00306: wrong number or types of arguments in call to 'RAISE_SALARY'
ORA-06550: line 7, column 1:
PL/SQL: Statement ignored
ANS:c,D
