Today I received a request from an application owner who wanted to know the size of all the CLOB and BLOB columns in all the non-system tables so that they have a good idea of the size involved for a migration. Tables with BLOB/CLOB columns The SQL to find the tables with the BLOB/CLOB columns … Continue reading Oracle: Find The Total Size Of All Of Your CLOB & BLOB Columns In All Tables
SOURCE CODE ATTRIBUTION: Before I proceed, I want to give credit to the original procedure by sfrejofsky based on which I created this procedure (mostly improvements). The source of this procedure is https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query I had a need to do dynamic PIVOTing for one of my projects and instead of reinventing the wheel, I used the … Continue reading SQL Server – Dynamically PIVOT ANY Column On ANY Table in ANY Database For ANY Aggregation With ANY Condition
Caution: If there is no need to use a dynamic SQL, you should always use regular non-dynamic SQL. This is a bad usage of dynamic SQL and I am only doing so to illustrate the use of bind variables. The task: Again, I just wanted to provide a basic example that shows the usage of EXECUTE … Continue reading Oracle Tip – EXECUTE IMMEDIATE With Bind Variable Input In Dynamic SQL
Dynamic SQL – SQL that is constructed by putting together strings to form executable code that gets parsed for the first time by the compiler at run-time. Justification for Dynamic SQL? There should be relatively few instances where dynamic SQL is the only option. Generally, regular SQL whose syntax can be checked by the compiler … Continue reading SQL Server Dynamic SQL – Use Bind Variables
This is one of those ancient tricks that everybody discovers for themselves before knowing that it was common-knowledge already. Here is a simple scenario: How to efficiently create a table structure (with no data) based on a SQL SELECT statement which could potentially return millions of rows? Our Million Row Table - EMP_TEST Let us … Continue reading Oracle – Quick Tip – Zero=One Trick To Generate Table Structures Dynamically From SELECT Statements!