Moving Soon!

 Hey Guys, I've started a website of my own and will be publishing articles there moving forward.

Take a look if you're interested: www.migsisip.com/

Thanks for all your support throughout the years!

Stop using Dynamic SQL! Seriously.

From my experience, research and general knowledge, Dynamic SQL should be avoided as much as possible,

There are multiple reliable sources that say this, even Oracle’s revered top DBA, Tom Kyte, and Oracle’s own PL/SQL Evangelist and Oracle ACE, Steven Feuerstein has mentioned this multiple times in AskTOM and Oracle Blogs.

To list some of the disadvantages of Dynamic SQL (from research and my own inputs):

It cannot be detected by the Oracle Enterprise Manager to have bad statistics because it is not parsed before it is run. It’s basically invisible to the DB parser.

The query this is not being “recorded” in the pool, therefore, we cannot predict if this certain piece of code will perform badly or not, we cannot quantify its statistics.

There are great efficiencies to be gained by using static sql, as PLSQL will cacheopen cursors and reuse statements, even before they are run.

Dynamic SQL is not scalable. Static SQL is scalable.

Does not really have any big advantage over static SQL apart from being “flexible”, but the disadvantages greatly outweighs these advantages.

It’s actually not true that Dynamic SQL performs better, in fact: dynamic SQL is more complex than static SQL, it executes more slowly than static SQL. Especially when done wrong.

We should Re-use code whenever possible, but only when that is appropriate. And I believe we can re-use code without doing Dynamic SQL. Do we REALLY need that SQL to be dynamic? 

Dynamic SQL is only really considered when we don’t know what parameters are used before runtime, something like in OBIEE, where the user will be the one to input the columns they want and need, then run the query.

I believe we can re-write these programs to use fixed, structured, and static queries instead.

Dynamic SQL is prone to SQL-Injection in General. I don’t know if there will be a time where we will encounter this issue, but it will be a huge mess if it does happen.

Personally, I find them extremely hard to debug, hard to read, and impractical because we only find out the actual query at runtime.

We cannot see the dependencies between programs because as I mentioned, they are invisible and only appear during runtime.

It will be hard to see which programs get impacted when we change a certain code, which in turn, will result in another bug, etc. 

I can list down more Pros and Cons, but I believe these are enough details to establish that the disadvantages of Dynamic SQL outweighs the advantages.

I strongly urge that our dev teams start to write Static SQL instead of Dynamic SQL.

To be edited further...

Converting Financial Reports' Amounts to Words with Currencies

If ever you need to add currencies into Words and have to include their Decimals (i.e. Cents), this query below demonstrates that using to TO_CHAR(TO_DATE(X, 'J'), 'JSP') trick:

SELECT  AMOUNT_SIGN||AMOUNT_IN_WORDS COMPLETE_AMOUNT_IN_WORDS
FROM    (SELECT  AMOUNT
        ,   CASE WHEN SIGN_AMT is not null then SIGN_AMT||' ' END AMOUNT_SIGN
        ,   CASE WHEN DOLLAR_AMT IS NOT NULL AND CENT_AMT IS NOT NULL THEN
                DOLLAR_AMT||' AND '||CENT_AMT
                 WHEN DOLLAR_AMT IS NULL AND CENT_AMT IS NOT NULL THEN
                CENT_AMT
                 WHEN DOLLAR_AMT IS NOT NULL AND CENT_AMT IS NULL THEN
                DOLLAR_AMT
            END AMOUNT_IN_WORDS
    FROM    (select  AMOUNT
                ,   CASE WHEN INSTR(AMOUNT, '-') > 0 THEN
                        'NEGATIVE'
                    END SIGN_AMT
                ,   CASE WHEN ABS(amount) >= 1 THEN -- ONLY THE ONES WITH WHOLE NUMBERS
                        to_char( to_date(TRUNC(ABS(amount)), 'J'), 'JSP') || ' BAHT' 
                    END DOLLAR_AMT
                ,   CASE WHEN INSTR(AMOUNT, '.') > 0 THEN -- ONLY THE ONES WITH DECIMALS
                        TO_CHAR(to_date(RPAD(SUBSTR(AMOUNT, INSTR(AMOUNT, '.') + 1), 2, '0'), 'J'), 'JSP') || ' SATANG'
                    END CENT_AMT
            from    (select 30 amount from dual
                    union 
                    select 30.500 amount from dual
                    union 
                    select 0.501 amount from dual
                    union
                    select -30 amount from dual
                    union 
                    select -30.500 amount from dual
                    union 
                    select -0.501 amount from dual
                    )
                )
            );

This sample above shows six scenarios:

1. Positive Whole Numbers only
2. Positive Whole Numbers with Decimals
3. Positive Decimals Only
4. Negative Whole Numbers only
5. Negative Whole Numbers with Decimals
6. Negative Decimals Only

Just change the Currencies if needed.

For more full-detailed Tutorials and Tips, check out #TheOracleProdigy at https://lifeofanoracleprodigy.blogspot.com/
Follow The Oracle Prodigy on Facebook (https://www.facebook.com/theOracleProdigy/) and Twitter (https://twitter.com/D_OracleProdigy)