How to execute dynamic sql with insert statement in oracle?

by filiberto , in category: MySQL , 21 days ago

How to execute dynamic sql with insert statement in oracle?

Facebook Twitter LinkedIn Telegram Whatsapp

1 answer

by edmond_brakus , 21 days ago

@filiberto 

To execute dynamic SQL with an insert statement in Oracle, you can use the EXECUTE IMMEDIATE statement. Here is an example of how you can do this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
DECLARE
  v_sql_statement VARCHAR2(1000);
BEGIN
  v_sql_statement := 'INSERT INTO your_table_name (column1, column2) VALUES (:1, :2)';
  
  EXECUTE IMMEDIATE v_sql_statement USING 'value1', 'value2';
  
  COMMIT;
END;
/


In this example, we first declare a variable v_sql_statement to store the insert statement. We then use the EXECUTE IMMEDIATE statement to execute the dynamic SQL statement stored in the v_sql_statement variable. The USING clause is used to provide values for the bind variables in the insert statement.


After executing the dynamic insert statement, we commit the transaction using the COMMIT statement.