Identify situations in which the DBMS_SQL package is the only applicable method of processing dynamic SQL. (Choose all that apply.)
A.
When a query returns multiple rows
B.
When a column name in a where clause is unknown at compile time.
C.
When the number of columns selected in a query is not known until run time
D.
When a table needs to be created based on an existing table structure at run time
E.
When privileges need to be granted to a new user to access an existing schema at run time
Plz add more questions for 144 exam….only 36 questions are there….in exam there will be 80 questions…!!
Why ‘EXECUTE IMMEDIATE’ cannot be used in ‘B’ ?
hi aashish here is site for some pl/sql questions go through this site and if u find any 1Z0-144 links post the site.
ok surprise.. you are right
execute immedita can be used in ‘B’
p.s i’ve sadly failed the exam
1z0144
have u cleared the exam by now?…if so pls help me with the dumps
B C are correct.
why?
execute immediate ‘select ‘||’ v_columns ||’ from tbl_abc where abc = 12′ into v_out_… ok, this doesnt work, right.
execute immediate ‘select column1, column2 from tbl_abc where ‘|| v_column || ‘ = 12’ into v_out_1, v_out_2 -> this seems to be ok.
So far, so good. We have answer c) as correct answer.
Lets take a look at a)
How can you execute immediate a query with more than one result row? With Bulk Collect: execute immediate ‘select varchar_number(v,i) from vn’
bulk collect into ret;
d): It’s working with ‘create table yyy as select * from xxx’ also with execute immediate
my result: only c)
I’ve passed the exam today. I answered C, but on the end of exam system shows me a statistic where this question was marked as not answered. So system don’t allow only one answer for questions with “Choose all that apply”. So I answered B, C (I’m not sure whether that is the right answer, but only C – is false)
B and C
B is because the following code does not update table:
CREATE TABLE TEST_VK
AS SELECT DUMMY
FROM DUAL;
/
DECLARE
input VARCHAR(100);
BEGIN
EXECUTE IMMEDIATE ‘UPDATE test_vk SET DUMMY = ”Y” WHERE :1 = ”X” ‘ USING :input;
END;
/
SELECT * FROM TEST_VK;
We can use concatenation in this example.
Columns in tables don’t change too often, so don’t worry about hard parses and ousting plans from shared cache.
declare
l_colname varchar(30);
begin
select column_name into l_colname
from all_tab_columns where …..;
EXECUTE IMMEDIATE ‘UPDATE test_vk SET DUMMY = ”Y” WHERE ‘|| l_colname ||’ = ”X” ‘;
end;
B,C
EXECUTE IMMEDIATE ‘UPDATE my_table SET ‘ || v_column = ‘abcde’; works.
we can have no value of v_column during compiling time.
G und X sind richtig!