Forum Stats

  • 3,827,637 Users
  • 2,260,802 Discussions
  • 7,897,323 Comments

Discussions

How to suppress schema in liquibase genobject/genschema output?

3020583
3020583 Member Posts: 2
edited Sep 11, 2020 6:01AM in SQLcl

Sqlcl liquibase genobject and genschema include the schema in the object definitions and references.  How can I suppress this?

E.g.

lb genobject -type table -name address

yields

<changeSet id="3fd0a442e9c1114948de539f2f334845800bdc65" author="Generated" failOnError="false"  >

   <n0:createSxmlObject objectName="ADDRESS" objectType="table" ownerName="CSTDEV"  >

   <n0:source><![CDATA[<?xml version="1.0" encoding="UTF-8" standalone="no"?><TABLE xmlns="http://xmlns.oracle.com/ku" version="1.0">

  <SCHEMA>CSTDEV</SCHEMA>

  <NAME>ADDRESS</NAME>

  <RELATIONAL_TABLE>

I'd prefer this without the <SCHEMA> definition since we deploy to several different schemas.  This seems like a bug given the presence of the -emit_schema option.

I've tried 'set ddl emit_schema off' but that doesn't work either.  I get this in 19.4 and 20.2.

Any ideas?

Answers

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Sep 9, 2020 11:39PM

    That is expected behavior.

    Approach the "issue" the other way round,  when you deploy the change to the target schema by using "lb update xmlfile.xml false" as the documentation states

    5. Create the object in the current schema.    Note: As the schema name has changed, you must set include schema to false or it will try and create the object in the HR schema. 

    Oracle 19.2 Using Liquibase with Sqlcl

  • 3020583
    3020583 Member Posts: 2
    edited Sep 11, 2020 6:01AM

    Our pipeline uses liquibase directly, so this isn’t available to me. I’ve been using liquibase much longer than SQLcl. Including the schema in source control generates confusing noise in a multi-schema dev environment. This is also contrary to the behaviour the existing SQLcl DDL command, the DMBS_METADATA package (which we already use), and the docs on the -emit_schema command line option.

    Nice start, but this is needlessly different from our existing practice and the behaviour of the existing tools. The only value here for us was avoiding having to write a query to order the table ddls for constraint dependencies, but SO solved that for me yesterday, and I’ve moved on. If "sqlcl ddl schema” existed, I’d wouldn’t have looked at genschema. My team is much more comfortable with sql text.

    Thanks for the suggestion. Pass.

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,617 Employee

    There is a bug, the -emit schema flag wasn't intended to be included/supported for the genschema command, only on updates.

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,617 Employee

    >> If "sqlcl ddl schema” existed

    You can make that happen with a bit of js and and SCRIPT command - basically loop through the contents of user_objects and feed it to the DDL command, throw in a spool.