View Exhibit1 and examine the structure of the DO table.
View Exhibit2 and examine the code.
The anonymous block gives an error on execution. What is the reason?
A. The assignment in line 7 is not valid.
B. The SQL does not support the Boolean data type.
C. A null value cannot be applied to the bind arguments in the using clause in line 10
D. The names of bind variables must be the same as the using clause bind arguments in line 10
|
Download Printable PDF. VALID exam to help you PASS. |
 |
code compiled without any issue when i tried
Agree that correct answer is B:
https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm#i13057
Example 7-2 Unsupported Data Type in Native Dynamic SQL
“– Fails because SQL does not support BOOLEAN data type”
Test code confirms it:
create table emp(
Empno NUMBER(4) NOT NULL,
Ename VARCHAR2(10),
Job VARCHAR2(9),
Mgr NUMBER(4),
Hiredate DATE,
Sal NUMBER(7,2),
Comm NUMBER(7,2),
Deptno NUMBER(2) );
INSERT INTO emp ( empno, ename, job, mgr, hiredate, sal, comm, deptno) values (100, ’emp_110′, ‘SALESMAN’, null, sysdate-132, 2600, 100, 20);
commit;
create or replace function job_chk(p_empno number)
return boolean is
v_job emp.job%type;
begin
select job into v_job from emp where empno = p_empno ;
if v_job = ‘SALESMAN’ THEN
return true;
ELSE
return false;
END IF;
end;
/
DECLARE
v_job BOOLEAN;
dyn_stmt varchar2(200);
v_comm number := null;
v_empno emp.empno%TYPE;
BEGIN
dyn_stmt := ‘BEGIN :v_job := job_chk(100); END;’ ;
execute IMMEDIATE dyn_stmt using out v_job;
if v_job then
execute IMMEDIATE ‘Update emp set comm = 😡 where empno = :y’
using v_comm, v_empno;
end if;
END;
/
ERROR at line 8:
ORA-06550: line 8, column 38:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 8, column 1:
PL/SQL: Statement ignored
If we change BOOLEAN type to number in the code, it executes successfully.
create or replace function job_chk(p_empno number)
return number is
v_job emp.job%type;
begin
select job into v_job from emp where empno = p_empno ;
if v_job = ‘SALESMAN’ THEN
return 0;
ELSE
return 1;
END IF;
end;
/
DECLARE
v_job number;
dyn_stmt varchar2(200);
v_comm number := null;
v_empno emp.empno%TYPE;
BEGIN
dyn_stmt := ‘BEGIN :v_job := job_chk(100); END;’ ;
execute IMMEDIATE dyn_stmt using out v_job;
if v_job=0 then
execute IMMEDIATE ‘Update emp set comm = 😡 where empno = :y’
using v_comm, v_empno;
end if;
END;
/
execute IMMEDIATE ‘Update emp set comm = 😡 where empno = :y’
using v_comm, v_empno;
end if;
END;
/
Why answer B.
—
create table emp(
Empno NUMBER(4) NOT NULL,
Ename VARCHAR2(10),
Job VARCHAR2(9),
Mgr NUMBER(4),
Hiredate DATE,
Sal NUMBER(7,2),
Comm NUMBER(7,2),
Deptno NUMBER(2) );
INSERT INTO emp ( empno, ename, job, mgr, hiredate, sal, comm, deptno
) values (100, ’emp_110′, ‘SALESMAN’, null, sysdate – 132, 2600, 100, 20 )
;
commit;
create or replace function job_chk(p_empno number)
return boolean is
v_job emp.job%type;
begin
select job into v_job from emp where empno = p_empno ;
if v_job = ‘SALESMAN’ THEN
return true;
ELSE
return false;
END IF;
end;
/
DECLARE
v_job BOOLEAN;
dyn_stmt varchar2(200);
v_comm number := null;
v_empno emp.empno%TYPE;
BEGIN
dyn_stmt := ‘BEGIN :v_job := job_chk(100); END;’ ;
execute IMMEDIATE dyn_stmt using out v_job;
if v_job then
execute IMMEDIATE ‘Update emp set comm = 😡 where empno = :y’
using v_comm, v_empno;
end if;
END;
/
Prior to oracle 12c, this block will fail, but as of oracle 12c, this will run successfully