In 19c: https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/dynamic-sql.html#GUID-4E1FD47B-7E1A-42E9-9792-210F13C75A1B
"
You must use the DBMS_SQL package to run a dynamic SQL statement if any of the following are true:
- You do not know the SELECT list until run time.
- You do not know until run time what placeholders in a SELECT or DML statement must be bound.
- You want a stored subprogram to return a query result implicitly (not through an OUT REF CURSOR parameter), which requires the DBMS_SQL.RETURN_RESULT procedure.
In these situations, you must use native dynamic SQL instead of the DBMS_SQL package:
- The dynamic SQL statement retrieves rows into records.
- You want to use the SQL cursor attribute %FOUND, %ISOPEN, %NOTFOUND, or %ROWCOUNT after issuing a dynamic SQL statement that is an INSERT, UPDATE, DELETE, MERGE, or single-row SELECT statement.
"
So looks like it's still BC
None of the three listed cases talk about 'WHERE' clause mentioned in answer B. #1 is about fields in SELECT ... FROM, #2 is about placeholders, #3 is about returning query result.
We use DBMS_SQL with (SQL statement with unknown number of
select- list items or input host variables.) so i can use unkonwn where clause as unkonwn input host variable untill runtime.
Example
CREATE OR REPLACE FUNCTION delete_rows
( p_salary number) RETURN NUMBER IS
v_cur_id INTEGER;
v_rows_del NUMBER;
v_stmt VARCHAR2(200);
BEGIN
v_stmt := 'DELETE FROM '||' emp_copy'|| ' where salary = '||'(:cid)';
v_cur_id := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cur_id,v_stmt , DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE (v_cur_id, ':cid', p_salary);
v_rows_del := DBMS_SQL.EXECUTE (v_cur_id);
DBMS_SQL.CLOSE_CURSOR(v_cur_id);
RETURN v_rows_del;
END;
/
BEGIN
DBMS_OUTPUT.PUT_LINE ( delete_rows(8000));
END;
/
so i think the answer is B and C
B works fine in 12c using execute immediate;
As far as 12c is concerned, the right answer to this question is only C
upvoted 2 times
...
This section is not available anymore. Please use the main Exam Page.1z0-144 Exam Questions
Log in to ExamTopics
Sign in:
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.
Upvoting a comment with a selected answer will also increase the vote count towards that answer by one.
So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.
Limak665
3 years, 11 months agoneziniukas
3 years, 10 months agoAiham
3 years, 10 months agoAiham
3 years, 10 months agoytadros
4 years, 6 months ago