Oracle PL/SQL Programming: A Developer's Workbook

Expert

Q:
21-43.	You can do (a), (c), (d), and (f) with DBMS_SQL. You can’t do any of the following: b. Set a role by executing the ALTER SESSION SET ROLE DDL statement. You cannot alter a role for the current session from within a PL/SQL stored procedure at all, either through DBMS_SQL or through a call to DBMS_SESSION.SET_ROLE (you can only call this procedure from an anonymous block!). e. Declare a cursor variable based on a REF CURSOR type and retrieve that variable’s value. You cannot declare a cursor variable and manipulate it dynamically. g. Connect to another database session with the CONNECT statement. The CONNECT command is not a part of SQL; rather, it is a SQL*Plus command.
Q:
21-44.	This functionality is similar to “indirect referencing” in Oracle Forms, where you use COPY and NAME_IN to write and read, respectively, the value of a GLOBAL variable or block’s item by referencing its name. One possible solution follows: it constructs, in a straightforward manner and completely with concatenation, the assignment statement. It then executes the statement: /* Filename on web page: assign.sp */ CREATE OR REPLACE PROCEDURE assign ( val_in IN VARCHAR2, varname_in IN VARCHAR2) IS cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR; fdbk PLS_INTEGER; assign_string VARCHAR2(2000) := 'BEGIN ' || varname_in || ' := ''' || val_in || '''; END;'; BEGIN DBMS_OUTPUT.PUT_LINE (assign_string); DBMS_SQL.PARSE ...