placeholders with DY_SQL

To call the procedure from a dynamic PL/SQL block, you must specify the IN OUT
mode for the bind argument associated with formal parameter deptid, as shown in
Example 7–3 Using IN OUT Bind Arguments to Specify Substitutions
===================================================================================
DECLARE
plsql_block VARCHAR2(500);
new_deptid NUMBER(4);
new_dname VARCHAR2(30) := 'Advertising';
new_mgrid NUMBER(6) := 200;
new_locid NUMBER(4) := 1700;
BEGIN
plsql_block := 'BEGIN create_dept(:a, :b, :c, :d); END;';
EXECUTE IMMEDIATE plsql_block
USING IN OUT new_deptid, new_dname, new_mgrid, new_locid;
END;
================================================================================

CREATE PROCEDURE calc_stats(w NUMBER, x NUMBER, y NUMBER, z NUMBER) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(w + x + y + z);
END;
/
DECLARE
a NUMBER := 4;
b NUMBER := 7;
plsql_block VARCHAR2(100);
BEGIN
plsql_block := 'BEGIN calc_stats(:x, :x, :y, :x); END;';
EXECUTE IMMEDIATE plsql_block USING a, b;
END;
/============================================================

Dynamic SQL With PLSQL Records and Collections

DECLARE
TYPE EmpCurTyp IS REF CURSOR;
emp_cv EmpCurTyp;
emp_rec employees%ROWTYPE;
sql_stmt VARCHAR2(200);
v_job VARCHAR2(10) := 'ST_CLERK';
BEGIN
sql_stmt := 'SELECT * FROM employees WHERE job_id = :j';
OPEN emp_cv FOR sql_stmt USING v_job;
LOOP
FETCH emp_cv INTO emp_rec;
EXIT WHEN emp_cv%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Name: ' || emp_rec.last_name || ' Job Id: ' ||
emp_rec.job_id);
END LOOP;
CLOSE emp_cv;
END;
/

Dynamic SQL Inside FORALL Statement

DECLARE
TYPE NumList IS TABLE OF NUMBER;
TYPE NameList IS TABLE OF VARCHAR2(15);
empids NumList;
enames NameList;
BEGIN
empids := NumList(101,102,103,104,105);
FORALL i IN 1..5
EXECUTE IMMEDIATE
'UPDATE employees SET salary = salary * 1.04 WHERE employee_id = :1
RETURNING last_name INTO :2'
USING empids(i) RETURNING BULK COLLECT INTO enames;
END;

Cursor Attributes with Dynamic SQL

BEGIN
EXECUTE IMMEDIATE 'DELETE FROM employees WHERE employee_id > 1000';
DBMS_OUTPUT.PUT_LINE('Number of employees deleted: ' || TO_CHAR(SQL%ROWCOUNT));
end;
--------------------------------------------------------------------------
Accessing %ROWCOUNT For an Explicit Cursor
DECLARE
TYPE cursor_ref IS REF CURSOR;
c1 cursor_ref;
TYPE emp_tab IS TABLE OF employees%ROWTYPE;
rec_tab emp_tab;
rows_fetched NUMBER;
BEGIN
OPEN c1 FOR 'SELECT * FROM employees';
FETCH c1 BULK COLLECT INTO rec_tab;
rows_fetched := c1%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('Number of employees fetched: ' || TO_CHAR(rows_fetched));
END;
/

DBA TABLES

select * from v$instance;

select name from v$datafile;

select * from v$database;

select * from v$parameter;  --user_dump_dest  --sql_trace

select * from all_tables;

select * from dba_tables;

select * from V$NLS_PARAMETERS;

SELECT object_name, original_name, createtime FROM recyclebin;

select * from PRODUCT_COMPONENT_VERSION;

select * from NLS_DATABASE_PARAMETERS;

select * from ALL_LOBS;

select * from DBA_DB_LINKS;

select * from ALL_TAB_COLUMNS;

Dynamic SQL2

Dynamic SQL Procedure that Accepts Table Name and WHERE Clause
-------------------------------------------------------------------
CREATE TABLE employees_temp AS SELECT * FROM employees;
CREATE OR REPLACE PROCEDURE delete_rows (
table_name IN VARCHAR2,
condition IN VARCHAR2 DEFAULT NULL) AS
where_clause VARCHAR2(100) := ' WHERE ' || condition;
v_table VARCHAR2(30);
BEGIN
-- first make sure that the table actually exists; if not, raise an exception
SELECT OBJECT_NAME INTO v_table FROM USER_OBJECTS
WHERE OBJECT_NAME = UPPER(table_name) AND OBJECT_TYPE = 'TABLE';
IF condition IS NULL THEN where_clause := NULL; END IF;
EXECUTE IMMEDIATE 'DELETE FROM ' || v_table || where_clause;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('Invalid table: ' || table_name);
END;
/

BEGIN
delete_rows('employees_temp', 'employee_id = 111');
END;
/

Dynamic SQL1

CREATE OR REPLACE PROCEDURE raise_emp_salary (column_value NUMBER,
emp_column VARCHAR2, amount NUMBER) IS
v_column VARCHAR2(30);
sql_stmt VARCHAR2(200);
BEGIN
-- determine if a valid column name has been given as input
SELECT COLUMN_NAME INTO v_column FROM USER_TAB_COLS
WHERE TABLE_NAME = 'EMPLOYEES' AND COLUMN_NAME = emp_column;
sql_stmt := 'UPDATE employees SET salary = salary + :1 WHERE '
|| v_column || ' = :2';
EXECUTE IMMEDIATE sql_stmt USING amount, column_value;
IF SQL%ROWCOUNT > 0 THEN
DBMS_OUTPUT.PUT_LINE('Salaries have been updated for: ' || emp_column
|| ' = ' || column_value);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('Invalid Column: ' || emp_column);
END raise_emp_salary;
/
=====================================================================================
DECLARE
plsql_block VARCHAR2(500);
BEGIN
-- note the semi-colons (;) inside the quotes '...'
plsql_block := 'BEGIN raise_emp_salary(:cvalue, :cname, :amt); END;';
EXECUTE IMMEDIATE plsql_block USING 110, 'DEPARTMENT_ID', 10;
EXECUTE IMMEDIATE 'BEGIN raise_emp_salary(:cvalue, :cname, :amt); END;'
USING 112, 'EMPLOYEE_ID', 10;
END;
/
================================================================================
DECLARE
sql_stmt VARCHAR2(200);
v_column VARCHAR2(30) := 'DEPARTMENT_ID';
dept_id NUMBER(4) := 46;
dept_name VARCHAR2(30) := 'Special Projects';
mgr_id NUMBER(6) := 200;
loc_id NUMBER(4) := 1700;
BEGIN
-- note that there is no semi-colon (;) inside the quotes '...'
EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';
sql_stmt := 'INSERT INTO departments VALUES (:1, :2, :3, :4)';
EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, mgr_id, loc_id;
EXECUTE IMMEDIATE 'DELETE FROM departments WHERE ' || v_column || ' = :num'
USING dept_id;
EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
EXECUTE IMMEDIATE 'DROP TABLE bonus';
END;
/