Section 12 Using Dynamic SQL

Test: Quiz: Using Dynamic SQL Bekijk uw antwoorden, feedback en scores hieronder. Een asterisk (*) geeft een goed antwoord aan. Section 1 (Beantwoord alle vragen in deze sectie.) 1. When SQL statements are included within a procedure, the statements are parsed when the procedure is compiled. True or False?

Markeren voor nakijken (1) Punten

Waar (*) Niet waar Correct 2. Name two reasons for using Dynamic SQL.


(Kies alle goede antwoorden.) Avoid errrors errrors at at compile compile time of DML statemen statements. ts. Create a SQL statement with varying column data, or different conditions. (*) Enables data-definition statements to be written and executed from PL/SQL. (*) Enables system control statements to be written and executed from PL/SQL. Correct 3. A programmer programmer wants to code a procedur procedure e which will will create create a table table with a single column. The datatype of the column will be chosen by the user who invokes the procedure. The programmer writes the following code:


CREATE OR REPLACE PROCEDURE create_tab (p_col_datatype IN VARCHAR2) IS BEGIN CREATE TABLE newtab (only_col p_col_datatype); END; Why will this procedure not compile successfully? Because you cannot create a table inside a procedure Because the invoking user may not have CREATE TABLE privilege Because when the procedure is compiled, Oracle cannot check if the parameter value passed into the procedure is a valid column datatype (*) Because table NEWTAB may already exist None of the above; the procedure will compile successfully Correct 4. he DBMS_SQL package is easier to use than EXECUTE IMMEDIATE. True or False?

Waar Niet waar (*) Correct


5. Examine the following procedure, which drops a table whose name is passed as an IN parameter:


CREATE OR REPLACE PROCEDURE drop_tab (p_table_name IN VARCHAR2) IS v_sql_statement VARCHAR2(100); BEGIN ... END; Which of the following will work correctly when coded in the procedure's executable section? (Choose two.) (Kies alle goede antwoorden.) EXECUTE IMMEDIATE 'DROP TABLE p_table_name'; EXECUTE IMMEDIATE 'DROP TABLE ' || p_table_name; (*) v_sql_statement := 'DROP TABLE ' || p_table_name; EXECUTE IMMEDIATE v_sql_statement; (*) v_sql_statement := 'DROP TABLE ' || p_table_name; EXECUTE IMMEDIATE 'v_sql_statement'; v_sql_statement := 'DROP TABLE '; EXECUTE IMMEDIATE v_sql_statement p_table_name; Correct 6. For which of the following is it necessary to use Dynamic SQL? (Choose three.)


(Kies alle goede antwoorden.) ALTER (*) GRANT (*) SAVEPOINT UPDATE DROP (*) Correct 7. What happens when a SQL statement is parsed? (Choose three.)


(Kies alle goede antwoorden.) he user's required privileges are checked. (*) he syntax of the statement is checked. (*) he statement is executed. he results of the statement are returned to the user. Oracle queries the Data Dictionary to make sure that the tables referenced in the SQL statement exist. (*) Correct 8. Only one call to DBMS_SQL is needed in order to drop a table. True or False?

Markeren voor nakijken

(1) Punten Waar Niet waar (*) Correct 9. What will happen when the following procedure is invoked?

CREATE OR REPLACE PROCEDURE do_some_work IS CURSOR c_curs IS SELECT object_name FROM user_objects WHERE object_type = 'FUNCTION'; BEGIN FOR v_curs_rec IN c_curs LOOP EXECUTE IMMEDIATE 'ALTER FUNCTION ' || v_curs_rec.object_name || ' COMPILE'; EXIT WHEN c_curs%ROWCOUNT > 2; END LOOP; END; All functions in the user's schema will be recompiled. he first two functions in the user's schema will be recompiled. he first three functions in the user's schema will be recompiled. (*) he procedure will not compile successfully because you cannot ALTER functions using Dynamic SQL. he procedure will not compile successfully because the syntax of the ALTER FUNCTION statement is incorrect. Correct


Section 12 Using Dynamic SQL

Recommend Documents