Sterling OMS Custom Table Creation

By | 02/15/2018

Sterling OMS Custom Table Creation

In this post we are going to see how to do Sterling OMS Custom Table Creation

Sterling OMS Custom Table Creation

Sterling OMS Custom Table Creation

Requirement: Create custom table with given below data structure


Other Table Creation Requirements
1) Table should have history table
2) Table should have audit records for insert, update, delete operations
3) Table should generate API’s (CRUD Operations) automatically.

What is difference between Entitydeployer vs Dbverify?

Entitydeployer: Use this tool to build database extensions. The entitydeployer calls dbverify, applies all the SQLs generated by dbverify, and then rebuilds the entities.jar file.

Command : <Install Directiry>/bin/deployer.cmd -t entitydeployer

Note : Quick Question what does -t means in above line: Ant script target name from <Install Directory>\properties\deployer.xml

DBVerify: Dbverify performs database schema creation, verification, and correction. Dbverify is used to ensure database schema integrity. Dbverify is used during the following tasks:

  • Installation
  • Upgrade
  • Customization

Command : <Install Directiry>/bin/dbverify.cmd

Lets learn about few installation properties values related to database

sandbox.cfg Parameter What happens when entitydeployer command called Remarks


New resources.jar file gets created

DB Verify program called and DDL statements gets created

New entities.jar gets created

DB will NOT be updated with newly added column/changes

From Development point of view this option is good. Make change in XML file get the DDL by running entitydeployer command and validate the changes and manually run the DDL on database.


New resources.jar file gets created

DB Verify program called and DDL statements gets created

New entities.jar gets created

DB will be directly updated with DDL created part of DB verify program

This option is good with controlled environment (Quality testing, User Acceptance testing)


New resources.jar file gets created

DB Verify program NOT called and DDL statements not created

New entities.jar gets created

No change to DB because DDL statements not created

Manually need to run dbverify.cmd to generate DDL statements and manually execute in the database.

How to modify database related properties in sandbox.cfg?

  1. From <Install Directory>/properties folder take a copy of sandbox.cfg as sandbox.cfg.original
  2. Search for following values in sandbox.cfg
    • NO_DBVERIFY=false
    • REINIT_DB=true
  1. First make change in sandbox.cfg file
    • LOAD_FACTORY_SETUP=true (no change)
    • NO_DBVERIFY=false (DDL scripts created automatically)
    • REINIT_DB=false (Database update will not happen automatically; Have to run the DDL script manually)
  1. Go to <Install Directory>/bin in command prompt
  2. Run <Install Directory>/bin/setupfiles.cmd file

How to add new custom table

  • Create new file install_dir/extensions/global/etc/datatypes.xml with below content

  • Create new file install_dir/extensions/global/entities/Custom_Table_Demo.xml with below content

  • Run command from bin folder : deployer.cmd -t entitydeployer
  • Query your database to see if column added? (No; because we updated the sandbox properties updated with REINIT_DB=false)
  • Open file \repository\scripts\EFrame_TableChanges.sql

How to test ?

Need to create 5 different services to perform database operations.









Insert Record : Using HTTP API tester call createXYZPersonInfo Service with below input

Fetch Record : Using HTTP API tester call below Services with below input and template xml.

Service Name Input XML Template XML
getXYZPersonInfo <PersonInfo EmpID=’1′ /> <PersonInfo />
getXYZPersonInfoList <PersonInfoList /> <PersonInfoList TotalNumberOfRecords=”>
<PersonInfo />

Interview Questions Related to Custom Table Creation

  1. Why extended database API can not be called directly ? Why to create services to access extended database API ? Answer : YIFApi interface does not extend APIs for custom/hang-off tables
  2. How to generate Extended API’s automatically for custom table ? Answer : ApiNeeded=Y
  3. How to make sure audit records gets generated for any activity on custom table ? Answer : AuditRequired=Y
  4. How to generate history table (table_name_h) ? Answer : HasHistory=Y
  5. How to generate primary key for column ? Which datatype has to be used ? Answer : DataType=Key
  6. How to override or create new datatype ? Answer : Add new file  install_dir/extensions/global/etc/datatypes.xml
  7. How to create Unique Index ?

Conclusion : This post helps to create new custom table into existing OMS database easily. Please share your feedback at OR comment in the post.

Register with us to get more OMS updates

OMS Interview Questions

25 thoughts on “Sterling OMS Custom Table Creation

    1. admin Post author

      Thanks lot for your appreciation. Please keep sharing your feedback to improve us better.

  1. Bala V S Satyam

    This is very important feature that every sterling OMS resource should know about. Well explained by ActiveKite.

    1. admin Post author

      Thanks Bala for your comment. Please share you valuable feedback on each post.

  2. Satheeshkumar Thangaraj

    Thanks for the nice & informatice topics. I have a question on this topic.

    1.How do you define purge criteria for Custom tables ?
    Does Sterling provides configuration options for this? Or Should purging data from custom tables be part of custom implementation?

    1. admin Post author

      For custom tables we have to write custom purge. Purge criteria has to be defined part of Criteria Parameter configuration (Agent Configuration)

      1. Vishal

        Can you please post an article on how to write custom purge for custom table ? It would be really helpful.

  3. Venkat

    Thank you so much

    This is very good information is provided here, this would be more helpful for learners.

    1. admin Post author

      Thanks so much Venkat. Please share your feedback on each post. This helps every one. Happy learning !!!

  4. ABC

    What all steps to be followed if I want to delete this custom table?

    1. admin Post author

      Thanks lot for your question. To remove custom table or extended column
      1) First remove the entry from entity XML (extensions\global\entities)
      2) Do entity build
      3) Based on the configuration script will generated to remove the custom table / new column
      4) Run the DDL script into DB
      5) Create new smscfs.ear
      6) Deploy the EAR
      7) New table or added column removed

      Note: Even after we remove the custom table entities.jar has reference to custom table. Old Class files not completely removed from entities.jar

      Hope this helps !!! Happy Learning

  5. Yogesh

    Hi Team,
    Can you also explain the below topics:
    1. sourcing and scheduling
    2. How to expose webservice in OMS
    3. How to write complex query
    4. How to read attribute value from Java Code for TaskQ or non-TaskQ table

    1. admin Post author


      Thanks for your request. We will update few post with answer to your questions. Keep learning !!!

  6. Navni

    This is very helpful, sometime you work with these commands without knowing exactly what they do, thanks for shedding light on them.

    1. admin Post author

      Thanks for nice comment. Happy to know that our posting helps. These comments Keeps us motivated. Keep learning. Cheers !!!

  7. Satheeshkumar Thangaraj

    Is there a way to get total number of records that match input criteria when using get List API for any custom/hang-off table? like we have for OOB List Apis (getOrderList/getItemList/getShipmentList).

    I tried by setting TotalNumberOfRecords in output template but it didn’t work.

  8. Vamshi

    Thanks a lot for this post. When we create a new Extension table, where will these API’s store? I believe we will have 5 API’s(create, modify, get, change, delete) generated when we have this new table created.

    1. admin Post author

      these api created part of entities.jar file. we don’t believe this information stored in any table.

  9. harshacham

    Cann someone help me to understand this……As we made the changes for existing table or creating new table will ru n entitydeployer command but after executing entitydeployer command why New resources.jar file will create…??

  10. Toni

    What is the Input to delete a Row from Custom Table

    1. admin Post author

      To delete row you will be calling custom service created for delete. internally service configured with Extended database API component with delete API. Input for this request will be Primary key for the row delete or unique values which helps to identify the row.

      For example,
      In Student table if you had student id as primary key and email address as unique key

      Above both can be used as input xml for delete. Hope this helps !!!

      Happy learning !!!


Leave a Reply

Your email address will not be published. Required fields are marked *