Export stored procedures and functions in system tables
The following statement exports all the names of stored procedures used in the HIS system to a text file, and then runs to generate the corresponding synonyms
-
Select
'CREATE OR REPLACE PUBLIC SYNONYM' | |'"| | object_name | |'" | | 'FOR' | |
'"| | owner | |'" | |'. | |'. | |'"| | object_name | |'" | |';'
From dba_procedures
Where owner in (select username from dba_users where account_status = 'OPEN' and default_tablespace like' TSP%')
And object_type = 'PROCEDURE'
-
The following statement exports all the names of the functions used in the HIS system to a text file, and then runs to generate the corresponding synonyms
Select
'CREATE OR REPLACE PUBLIC SYNONYM' | |'"| | object_name | |'" | | 'FOR' | |
'"| | owner | |'" | |'. | |'. | |'"| | object_name | |'" | |';'
From dba_procedures
Where owner in (select username from dba_users where account_status = 'OPEN' and default_tablespace like' TSP%')
And object_type = 'FUNCTION'
-
The following statement exports all the names of the tables used in the HIS system to a text file, and then runs to generate the corresponding synonyms
Select
'CREATE OR REPLACE PUBLIC SYNONYM' | |'"| | SYNONYM_NAME | |'" | | 'FOR' | |
'"| | TABLE_OWNER | |'" | |'. | |'. | |'"| | TABLE_NAME | |'" | |';'
From dba_synonyms
Where TABLE_OWNER in (select username from dba_users where account_status = 'OPEN' and default_tablespace like' TSP%')
