Steven has posted a nice example, but it is too long to go through and get it fully.
Earlier, we had a restriction to use only 32 KB string with NDS (Execute Immediate), that is relaxed now, so we can use CLOB as a parameter.
In MYHO, we can dynamically frame unknown number of input and output variables as a String and still execute with NDS.
So, I would like to understand and very keen to know, when DBMS_SQL really helps, can someone put in a nutshell and give some example? I'm sorry if I'm asking more and troubling, thank you.
I do understand fully that when I apply "INTO" or "USING" clause, I need to have the known output and input variables during the development phase itself.
DBMS_SQL helps to overcome this, I fully agree, anything else I need to remember to go with this package, please advise, thank you.
That's the main usage of DBMS_SQL, you can find that explained in the documentation:
Oracle lets you write stored procedures and anonymous PL/SQL blocks that use dynamic SQL. Dynamic SQL statements are not embedded in your source program; rather, they are stored in character strings that are input to, or built by, the program at runtime. This enables you to create more general-purpose procedures. For example, dynamic SQL lets you create a procedure that operates on a table whose name is not known until runtime.
Native Dynamic SQL is an alternative to
DBMS_SQLthat lets you place dynamic SQL statements directly into PL/SQL blocks. In most situations, Native Dynamic SQL is easier to use and performs better than
DBMS_SQL. However, Native Dynamic SQL itself has certain limitations:
- There is no support for so-called Method 4 (for dynamic SQL statements with an unknown number of inputs or outputs)
- There are some tasks that can only be performed using
DBMS_SQL.For tasks that require
DBMS_SQL, see Oracle Database PL/SQL Language Reference.
Please if you have a couple of minutes change your display name to something meaningful rather than a number, making simpler for everyone to followup a conversation: