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
B is correct answser
Everything that you specify in EXECUTE IMMEDIATE is processed By SQL engine and it doesn’t apply boolean datatype
Yes, SQL does not support the BOOLEAN type. The correct answer is B
b
SQL, unlike PL/SQL, has no BOOLEAN data type. Most people who want to use functions like yours in SQL statements write them to return the NUMBER values 1 or 0 (or the VARCHAR2 values ‘T’ or ‘F’) instead of the BOOLEAN values TRUE or FALSE.
In SQL (not PL/SQL) the function will return not return boolean data type (true or false)
B, C
B
B is the correct answer and A is the reason behind 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))
SQL> create or replace function job_chk(p_empno number)
2 return BOOLEAN is
3
4 v_job emp.job%type;
5 begin
6 select job into v_job from emp where empno= p_empno;
7 if v_job=’sm’ then
8 RETURN true;
9 else
10 RETURN FALSE;
11 end if;
12 end job_chk;
13 /
Function created.
SQL> declare
2 v_job BOOLEAN;
3 dyn_stmt VARCHAR2(200);
4 v_comm NUMBER:=null;
5 v_empno emp.empno%type;
6 begin
7 dyn_stmt :=’BEGIN :v_job:=job_chk(100);end;’;
8 EXECUTE IMMEDIATE dyn_stmt using out v_job;
9 if v_job then
10 EXECUTE IMMEDIATE ‘UPDATE emp set comm =: x where empno= :y’
11 using v_comm, v_empno;
12 end if;
13 end;
14 /
EXECUTE IMMEDIATE dyn_stmt using out v_job;
*
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
–Corrected
SQL> declare
2 v_job BOOLEAN;
3 dyn_stmt VARCHAR2(200);
4 v_comm NUMBER:=null;
5 v_empno emp.empno%type;
6 begin
7 dyn_stmt :=’declare v1_job boolean; BEGIN v1_job:=job_chk(100);end;’;
8 EXECUTE IMMEDIATE dyn_stmt;
9 if v_job then
10 EXECUTE IMMEDIATE ‘UPDATE emp set comm =: x where empno= :y’
11 using v_comm, v_empno;
12 end if;
13 end;
14 /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-01403: no data found
ORA-06512: at “HR.JOB_CHK”, line 6
ORA-06512: at line 1
ORA-06512: at line 8
B
CREATE TABLE PEPE (
N BOOLEAN
)
Error at line 1
ORA-00902: invalid datatype
Error ORA-06550 on line 7. So Answer A
I am running it and no error is showing up 🙁
———-
DECLARE
v_job BOOLEAN;
dyn_stmt VARCHAR2(200);
v_comm NUMBER := NULL;
v_empno employees.employee_id%type;
BEGIN
dyn_stmt := ‘BEGIN :v_job := job_chk(100); END;’;
IF v_job THEN
EXECUTE immediate ‘UPDATE employees SET commission_pct = 😡 WHERE employee_id = :y’ USING v_comm, v_empno;
END IF;
END;
Result:
———–
PL/SQL procedure successfully completed.
right, i think b is correct. question is in runtime.
The correct answer is B!
https://docs.oracle.com/cloud/latest/db112/LNPLS/datatypes.htm#LNPLS348
https://docs.oracle.com/cloud/latest/db112/LNPLS/datatypes.htm#LNPLS348
After I initially commented I clicked the -Notify me when new comments are added- checkbox and now every time a remark is added I get four emails with the same comment. Is there any manner you possibly can remove me from that service? Thanks!
http://highprofilecallgirlsindelhi.in/narelacallgirls.html
B