CREATE TABLE employees
AS
SELECT * FROM hr.employees
Table created.
Concatenate Table Name, Bind One Variable, Return Two Values
DECLARE
l_last_name employees.last_name%TYPE;
l_salary employees.salary%TYPE;
PROCEDURE show_value (table_in IN VARCHAR2, id_in IN INTEGER)
IS
BEGIN
EXECUTE IMMEDIATE
'SELECT last_name, salary FROM '
|| sys.DBMS_ASSERT.sql_object_name (table_in)
|| ' WHERE employee_id = :id_value'
INTO l_last_name, l_salary
USING id_in;
DBMS_OUTPUT.put_line (l_last_name || ' Earning ' || l_salary);
END;
BEGIN
show_value ('EMPLOYEES', 138);
END;
Stiles Earning 3200
DECLARE
l_employee employees%ROWTYPE;
BEGIN
EXECUTE IMMEDIATE 'SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = :empid'
INTO l_employee
USING 138;
DBMS_OUTPUT.put_line (l_employee.last_name);
END allrows_by;
Stiles
Use BULK COLLECT with EXECUTE IMMEDIATE
DECLARE
TYPE employee_ntt IS TABLE OF employees%ROWTYPE;
l_employees employee_ntt;
BEGIN
EXECUTE IMMEDIATE 'SELECT * FROM employees' BULK COLLECT INTO l_employees;
DBMS_OUTPUT.put_line (l_employees.COUNT);
END allrows_by;
107
Use OPEN FOR to Fetch From Dynamic SELECT
DECLARE
l_cursor SYS_REFCURSOR;
TYPE employee_ntt IS TABLE OF employees%ROWTYPE;
l_employees employee_ntt;
BEGIN
OPEN l_cursor FOR 'SELECT * FROM employees';
FETCH l_cursor BULK COLLECT INTO l_employees;
DBMS_OUTPUT.put_line (l_employees.COUNT);
CLOSE l_cursor;
END allrows_by;
107
And With a LIMIT Clause
DECLARE
l_cursor SYS_REFCURSOR;
TYPE employee_ntt IS TABLE OF employees%ROWTYPE;
l_employees employee_ntt;
BEGIN
OPEN l_cursor FOR 'SELECT * FROM employees';
LOOP
FETCH l_cursor BULK COLLECT INTO l_employees LIMIT 100;
EXIT WHEN l_employees.COUNT = 0;
DBMS_OUTPUT.put_line (l_employees.COUNT);
END LOOP;
CLOSE l_cursor;
END allrows_by;
100
7