Forum Stats

  • 3,838,561 Users
  • 2,262,383 Discussions
  • 7,900,687 Comments

Discussions

How to automate sql file execution?

Ranjeet Deshmukh
Ranjeet Deshmukh Member Posts: 8 Blue Ribbon
edited Jul 5, 2022 2:56PM in SQL & PL/SQL

Hi,

We had CICD in place for our application but now we are planning to automate DB script execution as well. PFB the requirement.

For example, we have create_tables.sql and alter_tables.sql files.

create_tables.sql contains:

CREATE TABLE EMPLOYEE_DETAILS (

  EMP_ID     VARCHAR2(128 CHAR) NOT NULL,

  FIRSTNAME    VARCHAR2(128 CHAR),

  LASTNAME    VARCHAR2(128 CHAR),

  CONSTRAINT REQUESTSUBMITTERS_PK PRIMARY KEY ( EMP_ID ),

);


alter_tables.sql contains:

--v0.1

ALTER TABLE EMPLOYEE_DETAILS MODIFY FIRSTNAME VARCHAR2(256);

COMMIT;

--v0.2

ALTER TABLE EMPLOYEE_DETAILS MODIFY FIRSTNAME VARCHAR2(256) NOT NULL;

ALTER TABLE EMPLOYEE_DETAILS MODIFY LASTNAME VARCHAR2(256) NOT NULL;

COMMIT;


Now, consider that we have an environments ABC. This environment has all latest changes available in create_tables.sql file but only --v0.1 change from alter_tables.sql file. Now we just want to promote --v0.2 change to environment ABC. So, in General we will be having --v0.xxx as a unique indicator to specify which change to execute.


Can you please let me know what could be the way to automate with this approach. Also, please feel free to suggest any other solution which you think is better than above one.


Thanks,

Ranjeet

Answers

  • Arun Kumar Gupta
    Arun Kumar Gupta Member Posts: 1,029 Gold Badge

    First thing first. Commit is not required after a DDL. DDL itself auto commits.


    Few questions.

    1. How do you know that a environment has changes applied upto --v0.1 and not --v0.2
    2. Any issue if --v.01 changes executed again ? first alter of --v.01 will fail and then script continues to apply changes from --v0.2

    Regards

    Arun

  • Ranjeet Deshmukh
    Ranjeet Deshmukh Member Posts: 8 Blue Ribbon

    Thank you, I will remove commit statements after DDL.

    How do you know that a environment has changes applied upto --v0.1 and not --v0.2

    [Ranjeet] - Our operations team keeps track of changes getting deployed in each env also we as DEV will be giving release notes which will contain which version to execute from particular file

    Any issue if --v.01 changes executed again ? first alter of --v.01 will fail and then script continues to apply changes from --v0.2

    [Ranjeet] - There may be or may not be any issue if --v0.1 gets executed again. We are still at QA phase so cannot be so sure on this. That is the reason we thought to find some way to execute just the specific version and not entire file.

    As I mentioned earlier, we are in discussions phase for automating this requirement. So, any alternate solution will also be helpful for us to reach to conclusion.


    Thanks,

    Ranjeet

  • Arun Kumar Gupta
    Arun Kumar Gupta Member Posts: 1,029 Gold Badge

    Its not recommended but based on current scenario where you have multiple versions of changes in one file I would suggest to go with below option based on certain assumptions.

    Assuming your file has a unique string indicating the version( --v0.1, --v.02 etc) to be applied and you know changes has to be applied starting from a specific version, you can use utl_file to create a temp file having content starting from specific version till the end of file and then execute the code from that temp file.


    Basically say your file is like this

    alter_tables.sql contains:
    ----------------------------
    --v0.1
    ALTER TABLE EMPLOYEE_DETAILS MODIFY FIRSTNAME VARCHAR2(256);
    COMMIT;
    
    --v0.2
    ALTER TABLE EMPLOYEE_DETAILS MODIFY FIRSTNAME VARCHAR2(256) NOT NULL;
    ALTER TABLE EMPLOYEE_DETAILS MODIFY LASTNAME VARCHAR2(256) NOT NULL;
    COMMIT;
    

    You need to write a small PLSQL block which reads the content of this file using utl_file and write it into another script say, alter_table_temp.sql where only content written is starting from --v.02

    You also may want to delete this temp file post execution.

    Regards

    Arun