Re: Creating tables & synonyms with Des2k.
From: Valeri Sorokine <vsorokin_at_dd.ru>
Date: Mon, 23 Aug 1999 19:21:02 +0400
Message-ID: <37C166DE.BF1B748B_at_dd.ru>
Frank van Bortel wrote:
>
> Bill Williams wrote:
> <snip>
>
> > Designer is not allowing me to create a synonym with the same name as
> > the table.
>
> Bill, sorry to say, but it's a bug (assuming you're using 2.1.1). There's a bit of history
> here: building release 2, which can load stuff directtly into the DB, a developer
> tested this, and got the 'Name is already in use by an exiting object' when testing the
> table+synonym stuff. Figures (you cannot own a table, and create a synonym for it,
> (...).
> There is a way to disable that, but need to dig into the triggers. --
> Frank van Bortel
>
> Technical Consultant Oracle
> V&L Informatica BV
> -------------------------------------------------
> Work Home
> Postbus 545 Hunzestraat 4
> 7500 AM Enschede 7555 WB Hengelo
> (31)53.434.1500 (31)74.242.5046
set heading off
set linesize 400
set pagesize 300
set verify off
set feedback off
spool off
spool off
spool off
spool off
spool off
spool off
spool off
Date: Mon, 23 Aug 1999 19:21:02 +0400
Message-ID: <37C166DE.BF1B748B_at_dd.ru>
Frank van Bortel wrote:
>
> Bill Williams wrote:
> <snip>
>
> > Designer is not allowing me to create a synonym with the same name as
> > the table.
>
> Bill, sorry to say, but it's a bug (assuming you're using 2.1.1). There's a bit of history
> here: building release 2, which can load stuff directtly into the DB, a developer
> tested this, and got the 'Name is already in use by an exiting object' when testing the
> table+synonym stuff. Figures (you cannot own a table, and create a synonym for it,
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> unless public). So, a check was built in to Des2k to prevent this from happening
^^^^^^^^^^^^^
> (...).
> There is a way to disable that, but need to dig into the triggers. --
> Frank van Bortel
>
> Technical Consultant Oracle
> V&L Informatica BV
> -------------------------------------------------
> Work Home
> Postbus 545 Hunzestraat 4
> 7500 AM Enschede 7555 WB Hengelo
> (31)53.434.1500 (31)74.242.5046
You can generate a script for creating public synonyms and remove PUBLIC keywords from the script by any text editor with Find/Replace feature.
There is also a script to generate private synonyms for public synonym declarations (see below).
But does anybody know any other solution for the problem?
Thanks in advance.
- SQL*Plus script:
PROMPT
PROMPT -------------------------------------------------------------------; PROMPT -- Private synonyms generation from Public synonym declarations --; PROMPT -- --; PROMPT -- 1 - Application name --; PROMPT -- 2 - Version of Application (single number) --; PROMPT -- 3 - Database Name --; PROMPT -- 4 - Objects Owner --; PROMPT -- --; PROMPT -- (c) Ivan Mugalev --; PROMPT -------------------------------------------------------------------;PROMPT set echo off
set heading off
set linesize 400
set pagesize 300
set verify off
set feedback off
ACCEPT app_name CHAR PROMPT '1) Application Name : ' ACCEPT app_ver CHAR PROMPT '2) Version : ' ACCEPT db_name CHAR PROMPT '3) Database : ' ACCEPT user_name CHAR PROMPT '4) Objects owner : 'set termout off
spool syn_tbl.sql
select 'create synonym ' || DS.name || ' for ' ||
'&user_name' || '.' || TD.name || chr(10)||'/'||chr(10)
from CI_DATABASE_SYNONYMS DS,
CI_TABLE_DEFINITIONS TD,
CI_TABLE_IMPLEMENTATIONS TI,
ci_database_users US,
ci_databases DB,
ci_application_systems AP
where
(AP.name = Upper('&app_name'))
and (AP.version = &app_ver)
and (DB.application_system_owned_by = AP.id)
and (DB.name = Upper('&db_name'))
and (US.database_reference = DB.id)
and (US.name = Upper('&user_name'))
and (TI.database_user_reference = US.id)
and (TI.object_implementation_type = 'TBI')
and (TD.id = TI.table_definition_reference)
and (DS.table_definition_reference = TD.id)
and (DS.synonym_for = 'TBL')
and (DS.scope = 'PUBLIC');
spool off
spool syn_vw.sql
select 'create synonym ' || DS.name || ' for ' ||
'&user_name' || '.' || VD.name || chr(10)||'/'||chr(10)
from CI_DATABASE_SYNONYMS DS,
CI_VIEW_DEFINITIONS VD,
CI_VIEW_IMPLEMENTATIONS VI,
ci_database_users US,
ci_databases DB,
ci_application_systems AP
where
(AP.name = Upper('&app_name'))
and (AP.version = &app_ver)
and (DB.application_system_owned_by = AP.id)
and (DB.name = Upper('&db_name'))
and (US.database_reference = DB.id)
and (US.name = Upper('&user_name'))
and (VI.database_user_reference = US.id)
and (VI.object_implementation_type = 'VWI')
and (VD.id = VI.view_definition_reference)
and (DS.view_definition_reference = VD.id)
and (DS.synonym_for = 'VW')
and (DS.scope = 'PUBLIC');
spool off
spool syn_seq.sql
select 'create synonym ' || DS.name || ' for ' ||
'&user_name' || '.' || SD.name || chr(10)||'/'||chr(10)
from CI_DATABASE_SYNONYMS DS,
CI_SEQUENCES SD,
CI_SEQUENCE_IMPLEMENTATIONS SI,
ci_database_users US,
ci_databases DB,
ci_application_systems AP
where
(AP.name = Upper('&app_name'))
and (AP.version = &app_ver)
and (DB.application_system_owned_by = AP.id)
and (DB.name = Upper('&db_name'))
and (US.database_reference = DB.id)
and (US.name = Upper('&user_name'))
and (SI.database_user_reference = US.id)
and (SI.object_implementation_type = 'SQI')
and (SD.id = SI.sequence_reference)
and (DS.sequence_reference = SD.id)
and (DS.synonym_for = 'OSQ')
and (DS.scope = 'PUBLIC');
spool off
spool syn_sna.sql
select 'create synonym ' || DS.name || ' for ' ||
'&user_name' || '.' || ND.name || chr(10)||'/'||chr(10)
from CI_DATABASE_SYNONYMS DS,
CI_SNAPSHOT_DEFINITIONS ND,
CI_SNAPSHOT_IMPLEMENTATIONS NI,
ci_database_users US,
ci_databases DB,
ci_application_systems AP
where
(AP.name = Upper('&app_name'))
and (AP.version = &app_ver)
and (DB.application_system_owned_by = AP.id)
and (DB.name = Upper('&db_name'))
and (US.database_reference = DB.id)
and (US.name = Upper('&user_name'))
and (NI.database_user_reference = US.id)
and (NI.object_implementation_type = 'SNI')
and (ND.id = NI.snapshot_definition_reference)
and (DS.snapshot_definition_reference = ND.id)
and (DS.synonym_for = 'SNP')
and (DS.scope = 'PUBLIC');
spool off
spool syn_pck.sql
select 'create synonym ' || DS.name || ' for ' ||
'&user_name' || '.' || PD.name || chr(10)||'/'||chr(10)
from CI_DATABASE_SYNONYMS DS,
CI_PLSQL_MODULES PD,
CI_PLSQL_MOD_IMPLEMENTATIONS PI,
ci_database_users US,
ci_databases DB,
ci_application_systems AP
where
(AP.name = Upper('&app_name'))
and (AP.version = &app_ver)
and (DB.application_system_owned_by = AP.id)
and (DB.name = Upper('&db_name'))
and (US.database_reference = DB.id)
and (US.name = Upper('&user_name'))
and (PI.database_user_reference = US.id)
and (PI.object_implementation_type = 'PMI')
and (PI.scope in ('DATABASE', 'BOTH'))
and (PD.plsql_module_type = 'PACKAGE')
and (PD.id = PI.plsql_module_reference)
and (DS.plsql_module_reference = PD.id)
and (DS.synonym_for = 'MOD')
and (DS.scope = 'PUBLIC');
spool off
spool syn_fun.sql
select 'create synonym ' || DS.name || ' for ' ||
'&user_name' || '.' || FD.name || chr(10)||'/'||chr(10)
from CI_DATABASE_SYNONYMS DS,
CI_PLSQL_MODULES FD,
CI_PLSQL_MOD_IMPLEMENTATIONS FI,
ci_database_users US,
ci_databases DB,
ci_application_systems AP
where
(AP.name = Upper('&app_name'))
and (AP.version = &app_ver)
and (DB.application_system_owned_by = AP.id)
and (DB.name = Upper('&db_name'))
and (US.database_reference = DB.id)
and (US.name = Upper('&user_name'))
and (FI.database_user_reference = US.id)
and (FI.object_implementation_type = 'PMI')
and (FI.scope in ('DATABASE', 'BOTH'))
and (FD.plsql_module_type = 'FUNCTION')
and (FD.id = FI.plsql_module_reference)
and (DS.plsql_module_reference = FD.id)
and (DS.synonym_for = 'MOD')
and (DS.scope = 'PUBLIC');
spool off
spool syn_prc.sql
select 'create synonym ' || DS.name || ' for ' ||
'&user_name' || '.' || PD.name || chr(10)||'/'||chr(10)
from CI_DATABASE_SYNONYMS DS,
CI_PLSQL_MODULES PD,
CI_PLSQL_MOD_IMPLEMENTATIONS PI,
ci_database_users US,
ci_databases DB,
ci_application_systems AP
where
(AP.name = Upper('&app_name'))
and (AP.version = &app_ver)
and (DB.application_system_owned_by = AP.id)
and (DB.name = Upper('&db_name'))
and (US.database_reference = DB.id)
and (US.name = Upper('&user_name'))
and (PI.database_user_reference = US.id)
and (PI.object_implementation_type = 'PMI')
and (PI.scope in ('DATABASE', 'BOTH'))
and (PD.plsql_module_type = 'PROCEDURE')
and (PD.id = PI.plsql_module_reference)
and (DS.plsql_module_reference = PD.id)
and (DS.synonym_for = 'MOD')
and (DS.scope = 'PUBLIC');
spool off
set verify on
set termout on
set feedback on
-- Valeri Sorokine ProSoft, Russia, Moscow, Information Systems Division Phone: +7 (095) 234 0636 (6 lines) FAX: +7 (095) 234 0640 E-mail: vsorokin_at_dd.ru OR vsorokin_at_prosoft.ru http://www.dd.ruReceived on Mon Aug 23 1999 - 17:21:02 CEST