#2 - Dynamic SQL

What is Dynamic SQL ?

• The Dynamic SQL programming technique enables you to build SQL dynamically during run time. Many flexible applications can be created using Dynamic SQL because the complete information about the SQL statement may be unknown during the compilation.
• One can create more general purpose, flexible applications by using dynamic SQL because the full text of a SQL statement may be unknown at compilation (For example, dynamic SQL lets you create a procedure that operates on a table whose name is not known until runtime).

---

• Dynamic SQL is used in cases when you do not know the exact SQL statements that should be executed and may depend upon the user input or the processing work done by the program, making it It is more flexible as compared to the static SQL (discussed later - click to view) .
• Different SQL statements can be executed for each input row using Dynamic SQL.

---

 Use

• Many types of applications need to use dynamic queries, including:

  • Applications that allow users to input or choose query search or sorting criteria at runtime
  • Applications that allow users to input or choose optimizer hints at run time
  • Applications that query a database where new tables created frequently OR where the data definitions of tables are constantly changing

---

Example 

DECLARE
   l_count   INTEGER;
BEGIN
   EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM user_objects' 
      INTO l_count;
   DBMS_OUTPUT.put_line ('Object count = ' || l_count);  

---

Recommended resources for extended reading 

Oracle Docs (Dynamic SQL) 

[Learn about Native Dynamic SQL, statements and examples]

---

---

Click on the topic name below to navigate to other pages  

---

NEXT :-

#3 - What is Static or Embedded SQL 

PREVIOUS :-

#1 - Introduction