Multiple choice technology databases

Which of the following queries return the names of programs (without duplication) defined in the currently-connected schema whose source contains a call to DBMS_OUTPUT.PUT_LINE (assume that this program name does not appear inside comments)?

  1. SELECT name FROM USER_DEPENDENCIES WHERE referenced_name = 'DBMS_OUTPUT.PUT_LINE'

  2. SELECT name from ALL_SOURCE WHERE owner = USER AND name = 'DBMS_OUTPUT.PUT_LINE'

  3. SELECT DISTINCT name from ALL_SOURCE WHERE INSTR (text, 'DBMS_OUTPUT.PUT_LINE') > 0

  4. SELECT DISTINCT name from USER_SOURCE WHERE INSTR (UPPER (text), 'DBMS_OUTPUT.PUT_LINE') > 0

Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

USER_SOURCE contains PL/SQL source code for your schema. INSTR(UPPER(text), 'DBMS_OUTPUT.PUT_LINE') searches within the code body for calls to that procedure. DISTINCT removes duplicates. Option A uses dependencies (not body content). B searches wrong column and uses ALL_SOURCE. C uses ALL_SOURCE instead of USER_SOURCE.