sqlcl (22.1.1) liquibase (4.9.1): how to exclude temp tables from genschema command

Robert__H

Hi,

I want to run  'lb genschema -split -sql'.

But problem is that due to existing materialized views I have some temp tables existing.

((MLOG$_T_CICD_USER3, RUPD$_T_CICD_USER3))

In an earlier version both temp tables were extracted but now with 22.1.1 only RUPD$..

If I don't exclude them they will be duplicated lateron when deploying the changelog to the target. This is because when creating the mlog, these tables are created automatically also. But since they already exist due to liquibase deploying them before the mlog, another new one will be created.

(MLOG$_T_CICD_USER31, RUPD$_T_CICD_USER31)

We had opened an Oracle SR for this with the result that its working as expected.

So the big question now is how to best exclude these objects when creating the changelog with 'lb genschema' command.

Any ideas?

Robert__H

I found a way by accident:

SQL> lb genschema -split -sql -filter "NOT LIKE 'RUPD$_%'"

or

SQL> lb genschema -split -sql -filter "!='RUPD$_T_CICD_USER3'"

But I am very confused. I can't find any documentation on the -filter parameter.

The official docu just lists the following

liquibase(lb) genschema [-noreport] [-synonyms] [-grants] 
[-context CONTEXT] [-label LABEL] [-emit_schema] 
[-fail] [-replace] [-runonchange] [-runalways] [-debug]

😓 Please help me to find out which parameters are actually possible to be used with lb genschema command.

Robert__H

Hi all,

I think I found the place..

when I open SQLcl and run lb help genschema I get the missing parameters.

Still not clear how FILTER, LABEL, CONTEXT, NAME have to look like.

I think I will try to open an Oracle SR to get the official docu updated and improved. This really cost me lots of time which could have easily been avoided with better documentation 😥

SQL> lb help genschema

--Starting Liquibase at 13:29:10 (version 4.9.1 #0 built at 2022-05-03 17:23+0000)

usage: liquibase|lb genschema [-noreport] [-synonyms] [-grants] [-split] [-sql] [-filter FILTER] [-context CONTEXT]

         [-label LABEL] [-fail] [-replace] [-runonchange] [-runalways] [-debug] [-dbChangeLogTable NAME]

Generate changelogs and controller for connected schema

named arguments:

 -noreport             all screen output suppressed (default: true)

 -synonyms             include public synonyms (default: false)

 -grants               include grant (default: false)

 -split                split files into directories based on object type (default: false)

 -sql                  generate an addition file containing the creation ddl. (default: false)

 -filter                                user any dbms_metadata NAME_EXPR filter

Common Generator Arguments:

 -context CONTEXT      ChangeSet contexts to execute

 -label LABEL          Filter the changelog using labels

 -fail                 sets failOnError value in changeset (default: false)

 -replace              sets replaceIfExists value in changeset (default: false)

 -runonchange          sets runOnChange value in changeset (default: false)

 -runalways            sets runAlways value in changeset (default: false)

 -debug                Generate and display additional debug output (default: false)

 -dbChangeLogTable     Select name of internal tables used by extension and liquibase.

Robert__H

I found a way by accident:

SQL> lb genschema -split -sql -filter "NOT LIKE 'RUPD$_%'"

or

SQL> lb genschema -split -sql -filter "!='RUPD$_T_CICD_USER3'"

But I am very confused. I can't find any documentation on the -filter parameter.

The official docu just lists the following

liquibase(lb) genschema [-noreport] [-synonyms] [-grants] 
[-context CONTEXT] [-label LABEL] [-emit_schema] 
[-fail] [-replace] [-runonchange] [-runalways] [-debug]

😓 Please help me to find out which parameters are actually possible to be used with lb genschema command.

Robert__H

Hi all,

I think I found the place..

when I open SQLcl and run lb help genschema I get the missing parameters.

Still not clear how FILTER, LABEL, CONTEXT, NAME have to look like.

I think I will try to open an Oracle SR to get the official docu updated and improved. This really cost me lots of time which could have easily been avoided with better documentation 😥

SQL> lb help genschema

--Starting Liquibase at 13:29:10 (version 4.9.1 #0 built at 2022-05-03 17:23+0000)

usage: liquibase|lb genschema [-noreport] [-synonyms] [-grants] [-split] [-sql] [-filter FILTER] [-context CONTEXT]

         [-label LABEL] [-fail] [-replace] [-runonchange] [-runalways] [-debug] [-dbChangeLogTable NAME]

Generate changelogs and controller for connected schema

named arguments:

 -noreport             all screen output suppressed (default: true)

 -synonyms             include public synonyms (default: false)

 -grants               include grant (default: false)

 -split                split files into directories based on object type (default: false)

 -sql                  generate an addition file containing the creation ddl. (default: false)

 -filter                                user any dbms_metadata NAME_EXPR filter

Common Generator Arguments:

 -context CONTEXT      ChangeSet contexts to execute

 -label LABEL          Filter the changelog using labels

 -fail                 sets failOnError value in changeset (default: false)

 -replace              sets replaceIfExists value in changeset (default: false)

 -runonchange          sets runOnChange value in changeset (default: false)

 -runalways            sets runAlways value in changeset (default: false)

 -debug                Generate and display additional debug output (default: false)

 -dbChangeLogTable     Select name of internal tables used by extension and liquibase.