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!

Project export skips synonyms for objects form another schema.

Oleh Tyshchenko4 days ago — edited 4 days ago

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

I have noticed an issue with project export when it seems to skip my synonyms for objects from a schema that is not part of the project. To verify this I created a simple testcase consisting of two schemes, one with a table, and another one with a synonym for this table.

create user schema1 identified by schema1;
create user testcase identified by testcase;
create table schema1.tbl(id number);
grant select on schema1.tbl to testcase;
create synonym testcase.syn for schema1.tbl;

Running project export produces

SQL> project init -directory . -name testcase -schemas testcase
...
SQL> project export -verbose -debug
…
-------------------------------
GRANT                         1
-------------------------------
Exported 1 objects
…
SQL> !tree
.
├── dist
│   └── install.sql
├── README.md
└── src
   └── database
       ├── schema1
       │   └── object_grants
       │       └── object_grants_as_grantor.schema1.table.tbl.sql
       └── testcase
6 directories, 3 files

Full output testcase.txt

I tried to run the query I believe SQLcl uses to get synonyms (obtained from -debug output) and it's work ok

SQL> select * from (SELECT 
 2  SYNONYM_NAME OBJECT_NAME, 
 3  SYNONYM_NAME NAME, 
 4   OWNER, 
 5   OWNER OBJECT_OWNER, 
 6   OWNER SYNONYM_OWNER, 
 7  TABLE_NAME, 
 8  TABLE_OWNER, 
 9  'SYNONYM' OBJECT_TYPE 
10   , 'ALL_SYNONYMS' EXPORT_TYPE 
11  FROM  Dba_synonyms) Dba_synonyms     where 1=1
12  --- filters ---
13      and (not (/*object_type in ('TABLE','VIEW','SEQUENCE') and*/ object_name like 'AQ$%')) --<-- sqlcl internal.fixed.filters
14      and (export_type not in ('USER')) --<-- project.filters
15      and (not (object_type = 'TABLE' and object_name like 'MLOG$\_%' escape '\'
16                              /* object_name in (select log_table from all_mview_logs))*/ )) --<-- sqlcl internal.fixed.filters
17      and (not (object_type = 'TRIGGER' and object_name ='DATABASECHANGELOG_ACTIONS_TRG')) --<-- project.filters
18      and (Dba_synonyms.owner in ('TESTCASE','PUBLIC')) --<-- config schemas
19      and (not (export_type = 'ALL_QUEUES' and name like 'AQ$%')) --<-- sqlcl internal.fixed.filters
20      and (object_name not like 'I_MLOG$%') --<-- project.filters
21      and ((Dba_synonyms.table_owner,Dba_synonyms.table_name) in (select owner,object_name from Dba_objects where oracle_maintained = 'N')) --<-- sqlcl internal.fixed.filters
22      and (not (object_type = 'VIEW'    and object_name ='DATABASECHANGELOG_DETAILS')) --<-- project.filters
23      and (object_name not like 'DM$%') --<-- project.filters
24      and (table_name not like 'BIN$%') --<-- sqlcl internal.fixed.filters
25      and (object_type != 'TABLE' or object_name not in ('DATABASECHANGELOG',
26                                                'DATABASECHANGELOGLOCK',
27                                                'DATABASECHANGELOG_ACTIONS'
28                                                )) --<-- project.filters
29      and (object_name not like 'I_SNAP$%') --<-- project.filters
30      and (not (object_type = 'TABLE' and object_name like 'RUPD$\_%' escape '\' )) --<-- sqlcl internal.fixed.filters
31* /
OBJECT_NAME    NAME    OWNER        OBJECT_OWNER    SYNONYM_OWNER    TABLE_NAME    TABLE_OWNER    OBJECT_TYPE    EXPORT_TYPE     
______________ _______ ____________ _______________ ________________ _____________ ______________ ______________ _______________ 
SYN            SYN     TESTCASE     TESTCASE        TESTCASE         TBL           SCHEMA1        SYNONYM        ALL_SYNONYMS