pl/sql-native dynamic sql

To process dynamic SQL statements, you use EXECUTE IMMEDIATE or OPEN-FOR, FETCH,

and CLOSE statements. EXECUTE IMMEDIATE is used for single-row SELECT statements, all

DML statements, and DDL statements. OPEN-FOR, FETCH, and CLOSE statements are used

for multirow SELECTs and reference cursors.

 

Part One: execute immediate

EXECUTE IMMEDIATE dynamic_SQL_string

[INTO defined_variable1, defined_variable2, ...]

[USING [IN | OUT | IN OUT] bind_argument1, bind_argument2,

...][{RETURNING | RETURN} field1, field2, ... INTO bind_argument1,

bind_argument2, ...] 

 

DECLARE
    sql_stmt VARCHAR2(100);
    plsql_block VARCHAR2(300);
    v_zip VARCHAR2(5) := '11106';
    v_total_students NUMBER;
    v_new_zip VARCHAR2(5);
    v_student_id NUMBER := 151;
BEGIN
    -- Create table MY_STUDENT
    sql_stmt := 'CREATE TABLE my_student '||
                            'AS SELECT * FROM student WHERE zip = '||v_zip;
    EXECUTE IMMEDIATE sql_stmt;
    
    -- Select total number of records from MY_STUDENT table
    -- and display results on the screen
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM my_student'
    INTO v_total_students;
    
    DBMS_OUTPUT.PUT_LINE ('Students added: '||v_total_students);
    
    -- Select current date and display it on the screen
    plsql_block := 'DECLARE ' ||
                                    ' v_date DATE; ' ||
                                    'BEGIN ' ||
                                    ' SELECT SYSDATE INTO v_date FROM DUAL; '||
                                    ' DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_date,
                                    ''DD-MON-YYYY''))
                                    ;'||
                                    'END;';
    EXECUTE IMMEDIATE plsql_block;
    -- Update record in MY_STUDENT table
    sql_stmt := 'UPDATE my_student SET zip = 11105 WHERE student_id = :1 '||
                            'RETURNING zip INTO :2';
    EXECUTE IMMEDIATE sql_stmt USING v_student_id RETURNING INTO v_new_zip;
    DBMS_OUTPUT.PUT_LINE ('New zip code: '||v_new_zip);
END;

 

 

 

NOte that:
A CREATE TABLE statement is a data definition statement(DDL). Therefore, it cannot accept any bind
arguments. Following example will generate error:
DECLARE
    sql_stmt VARCHAR2(100);
    v_zip VARCHAR2(5) := '11106';
    v_total_students NUMBER;
BEGIN
    -- Drop table MY_STUDENT
    EXECUTE IMMEDIATE 'DROP TABLE my_student';
    -- Create table MY_STUDENT
    sql_stmt := 'CREATE TABLE my_student '||
    'AS SELECT * FROM student '||
    'WHERE zip = :zip';
    EXECUTE IMMEDIATE sql_stmt USING v_zip;
    -- Select total number of records from MY_STUDENT table
    -- and display results on the screen
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM my_student'
    INTO v_total_students;
    DBMS_OUTPUT.PUT_LINE ('Students added: '|| v_total_students);
END; 

 

DECLARE

*

ERROR at line 1:

ORA-01027: bind variables not allowed for data definition operations

ORA-06512: at line 12 

 

 

 

 

DECLARE
    sql_stmt VARCHAR2(100);
    v_zip VARCHAR2(5) := '11106';
    v_total_students NUMBER;
BEGIN
    -- Create table MY_STUDENT
    sql_stmt := 'CREATE TABLE my_student '||
    'AS SELECT * FROM student '|| 'WHERE zip ='|| v_zip;
    EXECUTE IMMEDIATE sql_stmt;
    -- Select total number of records from MY_STUDENT table
    -- and display results on the screen
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM :my_table'
    INTO v_total_students
    USING 'my_student';
    DBMS_OUTPUT.PUT_LINE ('Students added: '|| v_total_students);
END;


When run, this example causes the following error:
DECLARE
*
ERROR at line 1:
ORA-00903: invalid table name
ORA-06512: at line 13

This example causes an error because you cannot pass names of schema objects to dynamic SQL
statements as bind arguments. To provide a table name at runtime, you need to concatenate this
example with the SELECT statement:

EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||my_table
INTO v_total_students;

 

Notice: The semicolon added to the SELECT statement is treated as an invalid character when the statement

is created dynamically. A somewhat similar error is generated when a PL/SQL block is

terminated by a slash: 

 

Part Two： OPEN-FOR, FETCH, and CLOSE Statements

below is an example :

 DECLARE

    TYPE student_cur_type IS REF CURSOR;
    student_cur student_cur_type;
    v_zip VARCHAR2(5) := '&sv_zip';
    v_first_name VARCHAR2(25);
    v_last_name VARCHAR2(25);
BEGIN
    OPEN student_cur FOR
    'SELECT first_name, last_name FROM student '|| 'WHERE zip = :1'
    USING v_zip;
    
    LOOP
        FETCH student_cur INTO v_first_name, v_last_name;
        EXIT WHEN student_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE ('First Name: '||v_first_name);
        DBMS_OUTPUT.PUT_LINE ('Last Name: '||v_last_name);
    END LOOP;
    ...