In Example 6-1, a PL/SQL anonymous block declares three PL/SQL variables and uses them in the static SQL statements DROP TABLE employees_temp; CREATE TABLE employees_temp AS SELECT employee_id, first_name, last_name FROM employees; DECLARE emp_id employees_temp.employee_id%TYPE := 299; emp_first_name employees_temp.first_name%TYPE := 'Bob'; emp_last_name employees_temp.last_name%TYPE := 'Henry'; BEGIN .However, there are limitations on the use of pseudocolumns, including the restriction on the use of some pseudocolumns in assignments or conditional tests.
Now, I need to change all of the contract names (not many) into anonymous identifiers so I can use screenshots without revealing sensitive data, but I need to maintain the relationships so the application still works.
Essentially, I want to change all instances of contract='Some Company' to contract='example1' in all of the tables returned by the above query.
A PL/SQL static SQL statement can have a PL/SQL identifier wherever its SQL counterpart can have a placeholder for a bind argument.
The PL/SQL identifier must identify either a variable or a formal parameter, not a function that is visible only in the PL/SQL unit that includes the static SQL statement.
PUT_LINE ('Number of employees deleted: ' || TO_CHAR( After the cursor closes, you can neither fetch records from the result set nor see the cursor attribute values.
You cannot assign a value to an explicit cursor, use it in an expression, or use it as a formal subprogram parameter.
Explicit cursors follow the same scoping rules as variables (see "Scope and Visibility of Identifiers").
To list the session cursors that each user session currently has opened and parsed, query the dynamic performance view or DML statement might be in a different scope.
To save an attribute value for later use, assign it to a local variable immediately.
Otherwise, other operations, such as subprogram invocations, might change the value of the attribute before you can test it.
The implicit cursor attributes are: DROP TABLE dept_temp; CREATE TABLE dept_temp AS SELECT * FROM departments; CREATE OR REPLACE PROCEDURE p ( dept_no NUMBER ) AUTHID DEFINER AS BEGIN DELETE FROM dept_temp WHERE department_id = dept_no; IF DROP TABLE employees_temp; CREATE TABLE employees_temp AS SELECT * FROM employees; DECLARE mgr_no NUMBER(6) := 122; BEGIN DELETE FROM employees_temp WHERE manager_id = mgr_no; DBMS_OUTPUT.