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
answer b is wrong
test case:
create or replace function test return boolean as
begin
return true;
end;
declare
a boolean;
begin
a := test;
end
it works.
the answer should be a. I tried and it give me error
your test case is pl\sql
Agree that B is wrong…but please explain A
right ansver is B.
See oracle documentation (Example 7-2 Unsupported Data Type in Native Dynamic SQL) : http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm#LNPLS01115
Here is the sample script, is gives an error:
DECLARE
dyn_stmt VARCHAR2(200);
b1 BOOLEAN;
FUNCTION f (x INTEGER)
RETURN BOOLEAN
AS
BEGIN
return 1=1;
END f;
BEGIN
dyn_stmt := ‘BEGIN :b := f(5); END;’;
— Fails because SQL does not support BOOLEAN data type:
EXECUTE IMMEDIATE dyn_stmt USING OUT b1;
END;
A is the answer for this.
Try and see error message… B is True
B is true. see Oracle documentation http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm
While PLSQL does support BOOLEAN, SQL doesn’t. There is nothing wrong in line 7!
The returned error is: PLS-00457: expressions have to be of SQL types
Example 7-2 Unsupported Data Type in Native Dynamic SQL
DECLARE
FUNCTION f (x INTEGER)
RETURN BOOLEAN
AS
BEGIN
…
END f;
dyn_stmt VARCHAR2(200);
b1 BOOLEAN;
BEGIN
dyn_stmt := ‘BEGIN :b := f(5); END;’;
— Fails because SQL does not support BOOLEAN data type:
EXECUTE IMMEDIATE dyn_stmt USING OUT b1;
END;
The answer is B. If you change b1 and return type of the funtcion to varchar2, it’s run.
Should be b:
see here:
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/executeimmediate_statement.htm
…At run time, bind arguments replace corresponding placeholders in the dynamic string. Every placeholder must be associated with a bind argument in the USING clause and/or RETURNING INTO clause. You can use numeric, character, and string literals as bind arguments, but you cannot use Boolean literals (TRUE, FALSE, and NULL).
I think its because execute immediate is designed for executing sql statements. Therefore the bind variables are checked for sql compatibility.
Error occurs because Execute Immediate probably runs as SQL
SQL> declare
2 b1 boolean := TRUE;
3 s1 VARCHAR2(100);
4 begin
5 s1 := ‘BEGIN :b1 := (5>6); END;’;
6 execute immediate s1 using out b1;
7 end
8 ;
9 /
execute immediate s1 using out b1;
*
ERROR at line 6:
ORA-06550: line 6, column 32:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 6, column 1:
PL/SQL: Statement ignored
It helps to read up on the error code itself.
http://pls-00457.ora-code.com/
OK here goes
Kuki kindly posted the following…
see here:
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/executeimmediate_statement.htm
…At run time, bind arguments replace corresponding placeholders in the dynamic string. Every placeholder must be associated with a bind argument in the USING clause and/or RETURNING INTO clause. You can use numeric, character, and string literals as bind arguments, but you cannot use Boolean literals (TRUE, FALSE, and NULL).
I think its because execute immediate is designed for executing sql statements. Therefore the bind variables are checked for sql compatibility.
THIS IS ALL TRUE.
I believe the key to answering this question is to realize that because the code uses a BIND variable in line 7, it can NOT use a Boolean datatype. Please read more about how Bind variables work from below.
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/overview.htm#BABEABGF
I therefore suggest that the answer is A “The assignment in line 7 is not valid”. I must admit though B is a true statement, but it is not the best answer.
B for sure
answer is B. No bind variable can have a datatype that SQL does not support in dynamic SQL.
refer to http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm#LNPLS629
B
Answer is B
Here are all the type admitted in pure SQL (SQL! Not PLSQL!!)
http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm
Look for Boolean word in the document.
You cannot create for example a table with a column boolean. Boolean exists only for plsql.
Execute immediate exec a pure SQL statement in this case. So it’s B.
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 39:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 8, column 2:
PL/SQL: Statement ignored
In Oracle 12c (12.1) the restriction in answer B has been removed, so the Exhibit 2 code actually now executes without errors.
B is correct
Dynamic SQL supports data types that are used in SQL
two further questions
(1) for dns dynamic plsql, is plsql datatype like BOOLEAN supported?
dynamic_plsql_str VARCHAR2(32767) :=
‘ DECLARE
l_variable1 BOOLEAN;
BEGIN
l_variable1 := :bound_variable;
…
END;
‘;
EXECUE IMMEDIATE dynamic_plsql_str USING IN TRUE;
(2) Suppose make use of DBMS_SQL.BIND_VARIABLE(cursor_no, :bound_variable, TRUE) to replace NDS, is plsql datatype like BOOLEAN supported?
In Oracle 12c (12.1) the restriction in answer B has been removed
if you try this code the result will be
No Data Found.
but I think SQL still does not support BOOLEAN data type in 12c Edition.
in the dynamic PL/SQL we can invoke a subprogram that has a formal parameter of the PL/SQL(but not SQL)data type BOOLEAN
so answer B is the rightest one
Ans: B
Tried it and got error on line 8: expressions have to be of SQL types;
Problem is with EXECUTE IMMEDIATE, in that the bind variable (i.e. v_job) should be of type supported by SQL.