Dynamic SQL | PL/SQL

Question

I've run into trouble with Dynamic SQL, I want to be able to display the department_id:

CREATE OR REPLACE PROCEDURE pro_two_tables
 (p_input VARCHAR2) IS
 v_department_id employees.department_id%TYPE;
BEGIN
 EXECUTE IMMEDIATE
 'SELECT department_id
 /* MYSTERY FOR ME */
 FROM ' || p_input || '
 WHERE manager_id = 205';
DBMS_OUTPUT.PUT_LINE('You selected ID from the table' || ' ' || p_input || ' ' || 'ID is' ||' ' || v_department_id);
EXCEPTION
 WHEN NO_DATA_FOUND THEN
 DBMS_OUTPUT.PUT_LINE('No data found');
 WHEN OTHERS THEN
 DBMS_OUTPUT.PUT_LINE(' I petty the fool who wrote this');
END; 

I seem to be unable to understand how would one get to use the "INTO" clause in this procedure. I'm reading up on the issue and doing some "tests" although I believe it to be great help if someone could explain this to me.

Answer 1

The INTO goes behind the execute immediate as in

declare
 foo number;
 bar number;
begin
 execute immediate 'select 1,2 from dual' into foo, bar;
 dbms_output.put_line(foo ||','||bar);
end;
/

This prints:

1,2

Note that if you find youeself using dynamic SQL, it is often a good idea to review your design.

• Novice: Thank you, I thought I tried it, but most of made some mistake while "trying" it, now it works, cheers.

Answer 2

CREATE OR REPLACE PROCEDURE pro_two_tables (i_table_name VARCHAR2)
IS
 v_record_id NUMBER;
 v_record_name VARCHAR2 (500) := 'Name Record'; -- Insert your Name Value
 sql_stmt VARCHAR2 (500);
BEGIN
 sql_stmt :=
 'SELECT id
 FROM ' || i_table_name || ' WHERE name = :1';
 EXECUTE IMMEDIATE sql_stmt INTO v_record_id USING v_record_name;
 DBMS_OUTPUT.PUT_LINE( 'You selected ID from the table'
 || ' '
 || i_table_name
 || ' '
 || 'ID is'
 || ' '
 || v_record_id);
EXCEPTION
 WHEN NO_DATA_FOUND
 THEN
 DBMS_OUTPUT.PUT_LINE ('No data found');
 WHEN OTHERS
 THEN
 DBMS_OUTPUT.PUT_LINE (' I petty the fool who wrote this');
END;

With EXECUTE IMMEDIATE you can USE:

1. INTO for Return Single Value o BULK COLLECT INTO for Multiple Value.
2. USING for replace Bind Variable in your query statement that you place in your sql with format : [name_field]