Forum Stats

  • 3,767,978 Users
  • 2,252,735 Discussions
  • 7,874,399 Comments

Discussions

Automatic SQL formatting (w/ arbori?) after saving the object

AndréB
AndréB Member Posts: 3 Red Ribbon

Hi all

I've been reading a few articles on arbori and how it helps formatting code in SQL Developer. From what I understood, SQL Dev is written in Java, so it probably uses some kind of arbori library.

Without talking too much about the use case: would it be possible to format the code after saving the object (view, package, ...) directly within Oracle?

Like:

  • Type

CREATE VIEW v#my_test AS (SELECT 1 AS a, 2 AS b, 3 AS c FROM dual)

  • Run it
  • Open the declaration and find something like this:
CREATE VIEW v#my_test
AS
    (SELECT 1     AS a
           ,2     AS b
           ,3     AS c
       FROM DUAL);

Would this be possible? We have schema triggers that alter packages and views, so that part is covered. But is there a way to get arbori into Oracle?

Thanks in advance - I will gladly accept a no, but please don't just say what a stupid idea 😉

Cheers

André

Tagged:

Answers

  • Mike Kutz
    Mike Kutz Member Posts: 5,789 Silver Crown

    IMO

    1. Only code from your Code Repository should be allowed in the Production server
    2. If automating, I'd format the code as part of the Code Check-in process
    3. SQLcl is probably the tool you'd use for automation
    4. Formatting is not perfect for me. It will do 90%+ of the job I require. (I format before I Check-in)

    The DB gets the pre-formatted code. Pulling it out, formating it, then recompiling the code seems like a "Bad Idea(tm)".

    I believe SQL*Developer can auto format as you type. If true, the code would be formatted for you before you could compile.

    But, I'd have to double check for that feature. (If not, request it)

  • AndréB
    AndréB Member Posts: 3 Red Ribbon

    Hi Mike

    Thanks for your answer, although it doesn't match to what I asked for. I knew that my question will raise more questions ("why does one want to do this?"). We have quite a sophisticated workflow here; sophisticated and pretty complex. The question was "is it possible", not "does it make sense". But I got your point - I had the same doubts when I started in this company ☺️

    Cheers

    André

  • Mike Kutz
    Mike Kutz Member Posts: 5,789 Silver Crown

    I was satisfying the statement:

    but please don't just say what a stupid idea

    By adding why it might be a "Bad Idea(tm)".

    Answer

    To answer your quesrion: No.

    There are no hooks within the database to auto format DDL statements.

    Problem I See with Trigger

    1. You'll need access to the text code in order to format it.
    2. Your trigger on Create/Replace Object would probably have to be AFTER compilation to allow access to the new text.
    3. You can get the text via get_ddl
    4. Then, you can format it.
    5. After you format, you'll need to compile it
    6. GOTO step 2
    7. This step isn't reached

    Potential Workaround

    See if SQL*Developer can Format as You Type. (I can't check right now if that's possible)

  • Mike Kutz
    Mike Kutz Member Posts: 5,789 Silver Crown

    Additional note

    Yes, it would be nice to have a package in Oracle that you can run that will format a text string.

    It would also be nice to have a (different) package to find certain code designs (like custom highlighting). AFAIK - this is known as Code Sniffing.

    Alternate Answer

    The Arbori engine is in Java.

    Theoretically: YES. You can have a formatter in Orable DB.

    I don't know how far @Vadim Tropashko-Oracle has gotten on this idea or even if he has touched it at all.

  • Philipp Salvisberg
    Philipp Salvisberg Member Posts: 155 Silver Badge

    Would this be possible?

    Yes.

    We have schema triggers that alter packages and views, so that part is covered. But is there a way to get arbori into Oracle?

    You have to load dbtools-common.jar and the necessary resources (formatter configuration .xml and .arbori) into the database. Then you should be able to write a PL/SQL wrapper function which returns the formatted result.

    The https://github.com/Trivadis/plsql-formatter-settings/blob/main/tests/src/test/java/com/trivadis/plsql/formatter/settings/ConfiguredTestFormatter.java reveals the necessary details.