Skip to Main Content

SQLcl: MCP Server & SQL Prompt

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQLcl project-command: filter out grants received on export

Moritz I.Jul 4 2025

Hello dear SQLcl experts,

I am using SQLcl 25.1 and connecting to an Oracle 19c database.

Currently i am struggeling to exclude grants made to my current schema from export. I dont want to exclude object grants in general because i want to keep the possibility to create grants in the deployment process for new objects just having been created.
I tought tweaking the project.filters would be a good idea, but what's valid in the last subquery of the exports /* Query ALL_TAB_PRIVS: */, breaks all other queries.
Here is what i added to project.filters:

-- prevent exporting grants made to local schema
and grantee not in ('MY_DEV_SCHEMA'),

Resulting error:

Parsing project.filters:Syntax Error at line 53, column 59

not (object_type = 'TABLE' and object_name like '%TEST' ),
                                                          ^^^
Expected: 'ANY',

java.lang.Exception: Parsing project.filters:Syntax Error at line 53, column 59

not (object_type = 'TABLE' and object_name like '%TEST' ),
                                                          ^^^
Expected: 'ANY',

       at oracle.dbtools.extension.project.commands.export.Filters.parse(Filters.java:71)
       at oracle.dbtools.extension.project.commands.export.Export.<init>(Export.java:130)
       at oracle.dbtools.extension.project.commands.export.ExportCommand.run(ExportCommand.java:78)
       at oracle.dbtools.extension.project.commands.handler.CommandHandler.ExportCommand(CommandHandler.java:49)
       at oracle.dbtools.extension.project.commands.handler.ProjectCommand.handleEvent(ProjectCommand.java:69)
       at oracle.dbtools.raptor.newscriptrunner.util.command.ParsedCommandListener.handleEvent(ParsedCommandListener.java:62)
       at oracle.dbtools.raptor.newscriptrunner.CommandRegistry.fireListeners(CommandRegistry.java:448)
       at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.lambda$run$0(ScriptRunner.java:241)
       at oracle.dbtools.raptor.newscriptrunner.ScriptRunnerContext.runWithStoredContext(ScriptRunnerContext.java:835)
       at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:124)
       at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:364)
       at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:245)
       at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:1413)
       at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.runSqlcl(SqlCli.java:1637)
       at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:403)

Is there any way for me to include filter conditions for a certain export-query only?
It seems to me that all conditions in project.filters are only applied to ALL_OBJECTS query, correct?

Alternatively, i would need to exclude grants completly. Besides the grantor scripts from other schemas that would need to run before lb update, i would then need a grantor script (which i would need to maintain) of my dev_schema to always run after lb update. Sounds reasonable?

This post has been answered by Roel Hartman on Jul 6 2025
Jump to Answer
Comments
Post Details
Added on Jul 4 2025
2 comments
66 views