I've been an Oracle developer for over 20 years, and having had to maintain and write a lot of dynamic SQL over the years, I have three basic rules for using dynamic SQL:
1. Don't use it. There is usually an alternative to dynamic SQL, and many developers (especially from Java where all your SQL is in strings) reach for "dumb" dynamic SQL instead of writing smart static SQL.
2. If you think you have to use it, don't use it. Have another look at your requirement, and at the tools available in PL/SQL (such as using cursor variables to supply alternate SQL cursors to a process) as well as in pure SQL (such as the SQL CASE function) and see if you can find a better approach using static SQL.
3. If you still think you have to use dynamic SQL, do everything you can to minimise its use and reduce your application's exposure to the many problems with dynamic SQL.
Dynamic SQL has its place in your toolbox, but there are a number of serious disadvantages that mean you should only use it where it is absolutely necessary.
Dynamic SQL cannot be pre-parsed. Static SQL is parsed the first time you run (or compile) the SQL statement, then any future executions can use the parsed version which is much more efficient (provided you are also using bind variables properly). So using dynamic SQL can create serious performance problems in some applications.
Dynamic SQL cannot be checked at compile time i.e. you have no way of knowing if your dynamic SQL is valid until you execute it, because as far as Oracle is concerned it doesn't exist until then. This often results in a lot of complicated de-bugging and long term maintenance issues.
Dynamic SQL does not allow dependency checking. Suppose your complex SQL query refers to a column or table that no longer exists. If the query is written as static SQL e.g. in a PL/SQL package, then as soon as the column/table was removed the package would be invalidated so you would know you had to fix it. But if it's dynamic SQL, then you will not know there's a problem until the query is actually executed - perhaps months later.
Dynamic SQL is hard to read or reason about. Complex queries may be tens of lines long, and dynamic SQL obviously has alternative elements that have to be concatenated onto the SQL string at various points. It can be really hard to look at a piece of dynamic SQL and figure out what it will look like at run time or what it's trying to achieve. If an error occurs, it's hard to know what query was actually executed to cause the error. This makes a lot of dynamic SQL almost unmaintainable in many applications.
In case you're still thinking you need to use dynamic SQL, here's a true story. A couple of years ago, I worked on a data-warehousing project where I was hired as a specialist Oracle developer to solve problems that the Java developers were having with the database functionality they'd implemented in PL/SQL. Because they were used to using SQL strings with JDBC, they simply applied the same approach to the PL/SQL code. They created several thousand lines of dynamic SQL, a lot of which had been cut-and-pasted from one function to another because they couldn't figure out which elements they really needed, so they simply copied the whole lot. Of course, all this dynamic SQL was hard to read, hard to maintain, full of bugs and horribly inefficient. I went through every line of it and re-implemented all of it as non-dynamic SQL using routine SQL and PL/SQL functionality, leaving a grand total of about 8 lines that really had to implemented with dynamic SQL. The DB functionality was now faster, cleaner, more maintainable and about half the number of LOC.