Sore about the source code of database objects

Imagine this situation: the development team is working on the program. At the same time, the source code of the application is not stored anywhere. Each programmer with the help of a special decompiler unloads the necessary code from the binary, works with it, and then collects it again and gives it to colleagues for further development.
Do you think this is a normal situation? I think no.
But for some reason, this approach is often used in the development of database applications.

Objects are created and modified “profitably” directly in the database. Most specialized IDEs provide a wealth of “convenient” tools for this — finding the desired object using the navigation tree, modifying it with a few mouse clicks, etc. At the same time, few people think about the source code - and when building a version, utilities are often used, which, based on the code of the current database and the code of the production database, generate a diff script (for me it still remains a mystery how, since the alter (tables for example) form in many ways, it all depends on the specific case, on the logic of change, etc.).
It turns out that the source code of the database does not exist, as it were, it is considered to be a kind of machine code that is difficult for a person to perceive and is completely relegated to visual tools for development. The programmer does not work with the functionality of his DBMS, but with the functionality of a specific IDE, a kind of beautiful and (as it seems at first time) convenient intermediate layer. In this case, it is necessary to study the IDE, and not the DBMS itself.
It is worth remembering that after you use the actual written code (verified, formatted according to corporeal standards, decorated with comments) to create a database object, this code will not be saved anywhere, you will lose it, and in return you will receive what the machine collects for you ( IDE, special utilities or libraries), based on the database data dictionary. At the same time, the history of changing objects is lost (it becomes impossible to find out / remember who, when and why created / deleted / changed one or another object), conflicts may arise during joint development and a lot more.

You can read about this in “PLSQL Standards Developed for the PLSQL Starter Framework” in the “Source Code Control” chapter:

Remember to never modify the PL / SQL stored in the database. Work from the source code file instead. Yes, modifying the compiled code inside the database is technically feasible. Java bytecode or C object files .
')
and in the chapter “Data Models and DDL”:

Most DDL is still created by hand. Make it clean, commented and readable, just like source code .

Finally, I will give the code for creating a simple table in Oracle and what comes out of it after unloading from the database with various utilities.

The source code for creating the table:
-- This is test_table
CREATE TABLE test_table (
id NUMBER(38), -- PK
value NUMBER(10,2), -- main value
width NUMBER(5,0) -- main width value
);

DBMS_METADATA:
CREATE TABLE "OBJECTMAN"."TEST_TABLE"
( "ID" NUMBER(38,0),
"VALUE" NUMBER(10,2),
"WIDTH" NUMBER(5,0)
)

Tora:
CREATE TABLE objectman.test_table
(
id NUMBER(38)
, value NUMBER(10,2)
, width NUMBER(5)
)

TOAD Eclpise plugin:
create table test_table
(
id number(38),
value number(10,2),
width number(5)
)

As you can see, the hodgepodge is full, who is ready for that.

Of course, this does not mean that you need to completely abandon modern visual tools and work in the console editor. The most important thing is to work with the source code of your database application, control it yourself, do not give it to the machine. The source code of database applications is no worse than Java or C ++ application code and requires the same treatment: formatting, comments, version control, etc.

A few recommendations:
1. All database code (DML, DDL, DCL, TCL, etc.) must be stored in the repository (one object = one file);
2. Proceeding from the first item, it should be possible to assemble a “clean” base ready for use “from scratch” using source codes from the repository (there is no need to make database dumps, cut out unnecessary (test) data, etc.)
3. Edit and compile the object code of the database you need from the file (so instead of navigating through the database of your IDE, the navigator for the project files in the repository will become relevant - find the file, open it for editing, roll to the database).