Help Center > > Developer Guide> Stored Procedures> Dynamic Statements> Executing Dynamic Non-query Statements

Executing Dynamic Non-query Statements

Updated at: Jul 14, 2021 GMT+08:00

Syntax

Figure 1 shows the syntax diagram.

Figure 1 noselect::=

Figure 2 shows the syntax diagram for using_clause.

Figure 2 using_clause-2

The above syntax diagram is explained as follows:

USING IN bind_argument is used to specify the variable that transfers values to dynamic SQL statements. It is used when a placeholder exists in dynamic_noselect_string. That is, a placeholder is replaced by the corresponding bind_argument when a dynamic SQL statement is executed. Note that bind_argument can only be a value, variable, or expression, and cannot be a database object such as a table name, column name, and data type. If a stored procedure needs to transfer database objects through bind_argument to construct dynamic SQL statements (generally, DDL statements), you are advised to use double vertical bars (||) to concatenate dynamic_select_clause with a database object. In addition, a dynamic PL/SQL block allows duplicate placeholders. That is, a placeholder can correspond to only one bind_argument.

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
-- Create a table:
CREATE TABLE sections_t1
(
   section       NUMBER(4) ,
   section_name  VARCHAR2(30),
   manager_id    NUMBER(6),
   place_id      NUMBER(4) 
)  
DISTRIBUTE BY hash(manager_id);

--Declare a variable:
DECLARE 
   section       NUMBER(4) := 280; 
   section_name  VARCHAR2(30) := 'Info support'; 
   manager_id    NUMBER(6) := 103;
   place_id      NUMBER(4) := 1400;
   new_colname   VARCHAR2(10) := 'sec_name';
BEGIN 
-- Execute the query:
    EXECUTE IMMEDIATE 'insert into sections_t1 values(:1, :2, :3, :4)' 
       USING section, section_name, manager_id,place_id; 
-- Execute the query (duplicate placeholders):
    EXECUTE IMMEDIATE 'insert into sections_t1 values(:1, :2, :3, :1)' 
       USING section, section_name, manager_id; 
-- Run the ALTER statement. (You are advised to use double vertical bars (||) to concatenate the dynamic DDL statement with a database object.)
    EXECUTE IMMEDIATE 'alter table sections_t1 rename section_name to ' || new_colname;
END; 
/

-- Query data:
SELECT * FROM sections_t1;

--Delete the table.
DROP TABLE sections_t1;

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel