Thus always make sure to enclose your SQL statement which you want run using Execute Immediate into a pair of Single quotes. First: Always enclose your SQL statement into a pair of Single QuotesĪlways remember that Execute Immediate treats DDL or DML or any other supported SQL statements which you want to execute dynamically as a string of VARCHAR2 data type and in PL/SQL we enclose any character or VARCHAR2 string into a pair of single quotes. In either of the ways you just have to take care of a few things while writing the DDL for Execute Immediate. For this you just have to first write the reserved phrase Execute Immediate followed by the DDL statement which you want to execute dynamically.
Oracle apex sql statement separator code#
The alternative approach to the above code is to write the DDL statement right within your Execute Immediate statement. Sure, there is nothing wrong in learning something new. But Manish, I want to learn the other way of writing the DDL statement as well. I like to use the former approach where we use Variable for holding the DDL because first of all it makes your code looks neat and clean moreover it also makes the error tracing a lot easier. If you want you can also write your DDL directly after the phrase ‘Reserved Phrase’. To execute a DDL dynamically, you first have to write the reserved phrase ‘Execute Immediate’ followed by the variable name into which you have stored your DDL as we did above. The Second statement is the Execute Statement. Statement 2: The Mighty Execute Immediate statement In the first statement we have stored our Create Table DDL into the variable ddl_qry using the assignment operator (In PL/SQL the assignment operator is the combination of colons and equal to sign). These statements are: Statement 1: Assignment statement In the execution section of this PL/SQL block we have two executable statements. One thing which you have to make sure here is that, the variable which you will be using for holding the SQL statement which you want to run with your EXECUTE IMMEDIATE statement must always be of VARCHAR2 data type and have plenty of data width so that your DDL could easily fit into it. We used this variable for holding our DDL statement which we prepared in the step 1. In the above PL/SQL block we declared a variable with the name ddl_qry. Once you have your DDL prepared next you have to write your PL/SQL program. Step 2: Run your DDL through PL/SQL program using Execute Immediate. Mind here, do not end your SQL statement (the one which you want to run with EXECUTE IMMEDIATE) with a semicolon. This DDL statement will create a table with the name tut_82 with two columns tut_num of NUMBER data type and tut_name of VARCHAR2 data type. In our case, we want to execute a CREATE TABLE DDL so let’s write a DDL for creating a table first. Though it is not necessary, but I suggest you to prepare your DDL beforehand, that way you can at least eliminate the chances of getting syntactic error because of the syntax of your DDL statement.
Oracle apex sql statement separator how to#
How to execute CREATE TABLE DDL using Execute Immediate in Oracle Database? Step 1: Prepare your DDL beforehand. The latter option is the most used one, specially now a days because of its better performance and easy to learn syntax. You can execute a DDL through PL/SQL program either by using DBMS_SQL package or by using the Execute Immediate statement of Native Dynamic SQL. So, what is the proper way of executing a DDL through PL/SQL program? On execution you will get an error which looks something like the one shown in the picture below.
To execute a DDL statement using PL/SQL we either need to take help of DBMS_SQL package or the advanced and improved way of the NATIVE DYNAMIC SQL.
There is nothing wrong with this program, the only problem here is that PL/SQL does not directly support DDL statements. I mean, this looks perfect, what could possibly go wrong? I know this approach would have been your first thought if I had asked you to create a table through a PL/SQL program. If you are still wondering if executing DDL statement with PL/SQL is that complicated, then let’s try creating a table through a PL/SQL program. Why can’t we execute DDL through a PL/SQL program? Native Dynamic SQL enables us to execute SQL statements like DDL or DML right through our PL/SQL.