Skip to content

Commit e1a3fa7

Browse files
jgebaljgebal
committed
Updated install/uninstall scripts to improve readability.
1 parent 31461ae commit e1a3fa7

7 files changed

Lines changed: 97 additions & 31 deletions

‎source/create_synonyms_and_grants_for_public.sql‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -17,13 +17,13 @@
1717
Create all necessary grant for the user who owns test packages and want to execute utPLSQL framework
1818
*/
1919

20+
@@define_ut3_owner_param.sql
21+
2022
set echo off
2123
set feedback on
2224
set heading off
2325
set verify off
2426

25-
define ut3_owner = &1
26-
2727
prompt Granting privileges on UTPLSQL objects in &&ut3_owner schema to PUBLIC
2828

2929
whenever sqlerror exit failure rollback

‎source/create_synonyms_and_grants_for_user.sql‎

Lines changed: 22 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -17,14 +17,33 @@
1717
Create all necessary grant for the user who owns test packages and want to execute utPLSQL framework
1818
*/
1919

20+
@@define_ut3_owner_param.sql
21+
22+
column 2 new_value 2 noprint
23+
select null as "2" from dual where 1=0;
24+
spool params.sql.tmp
25+
select
26+
case
27+
when '&&2' is null then q'[ACCEPT ut3_user CHAR PROMPT 'Provide schema name where synonyms for the utPLSQL v3 should be created ']'
28+
else 'define ut3_user=&&2'
29+
end
30+
from dual;
31+
spool off
32+
set termout on
33+
@params.sql.tmp
34+
set termout off
35+
/* cleanup temporary sql files */
36+
--try running on windows
37+
$ del params.sql.tmp
38+
--try running on linux/unix
39+
! rm params.sql.tmp
40+
set termout on
41+
2042
set echo off
2143
set feedback on
2244
set heading off
2345
set verify off
2446

25-
define ut3_owner = &1
26-
define ut3_user = &2
27-
2847
prompt Granting privileges on UTPLSQL objects in &&ut3_owner schema to user &&ut3_user
2948

3049
whenever sqlerror exit failure rollback

‎source/define_ut3_owner_param.sql‎

Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,36 @@
1+
set serveroutput on size unlimited format truncated
2+
whenever oserror continue
3+
4+
set heading off
5+
set linesize 1000
6+
set pagesize 0
7+
8+
set verify off
9+
set define on
10+
11+
set termout off
12+
set timing off
13+
set feedback off
14+
15+
column line_separator new_value line_separator noprint
16+
select '--------------------------------------------------------------' as line_separator from dual;
17+
18+
column 1 new_value 1 noprint
19+
select null as "1" from dual where 1=0;
20+
spool params.sql.tmp
21+
select
22+
case
23+
when '&&1' is null then q'[ACCEPT ut3_owner CHAR DEFAULT 'UT3' PROMPT 'Provide schema for the utPLSQL v3 (UT3)']'
24+
else 'define ut3_owner=&&1'
25+
end
26+
from dual;
27+
spool off
28+
set termout on
29+
@params.sql.tmp
30+
set termout off
31+
/* cleanup temporary sql files */
32+
--try running on windows
33+
$ del params.sql.tmp
34+
--try running on linux/unix
35+
! rm params.sql.tmp
36+
set termout on

‎source/install.sql‎

Lines changed: 15 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -14,23 +14,20 @@
1414
See the License for the specific language governing permissions and
1515
limitations under the License.
1616
*/
17-
prompt Installing utplsql framework
1817

19-
set serveroutput on size unlimited
20-
set feedback on
21-
set timing off
22-
set verify off
23-
set define &
18+
@@define_ut3_owner_param.sql
2419

2520
spool install.log
2621

27-
define ut3_owner = &1
22+
prompt &&line_separator
23+
prompt Installing utPLSQL v3 framework
24+
prompt &&line_separator
25+
2826
whenever sqlerror exit failure rollback
2927
whenever oserror exit failure rollback
30-
set serveroutput on size unlimited format truncated
3128

32-
set feedback off
3329
prompt Switching current schema to &&ut3_owner
30+
prompt &&line_separator
3431
alter session set current_schema = &&ut3_owner;
3532
alter session set plsql_warnings = 'ENABLE:ALL', 'DISABLE:(5004,5018,6000,6001,6003,6009,6010,7206)';
3633
--set define off
@@ -89,8 +86,11 @@ alter session set plsql_warnings = 'ENABLE:ALL', 'DISABLE:(5004,5018,6000,6001,6
8986
@@install_component.sql 'core/ut_expectation_processor.pks'
9087
@@install_component.sql 'core/ut_expectation_processor.pkb'
9188

92-
--installing profiler tabs if they dont exist
89+
prompt Installing PLSQL profiler objects into &&ut3_owner schema
90+
prompt You will see "ORA-00955" errors if they already exist
91+
prompt &&line_separator
9392
whenever sqlerror continue
93+
set feedback on
9494
@@core/coverage/proftab.sql
9595
whenever sqlerror exit failure rollback
9696

@@ -266,21 +266,24 @@ column text format a100
266266
column error_count noprint new_value error_count
267267

268268
prompt Validating installation
269-
select name, type, sequence, line, position, text, count(1) over() error_count
269+
prompt &&line_separator
270+
set heading on
271+
select type, name, sequence, line, position, text, count(1) over() error_count
270272
from all_errors
271273
where owner = upper('&&ut3_owner')
272274
and name not like 'BIN$%' --not recycled
273275
and (name = 'UT' or name like 'UT\_%' escape '\')
274276
-- errors only. ignore warnings
275277
and attribute = 'ERROR'
278+
order by name, type, sequence
276279
/
277280
278281
begin
279282
if to_number('&&error_count') > 0 then
280283
raise_application_error(-20000, 'Not all sources were successfully installed.');
281284
else
282285
dbms_output.put_line('Installation completed successfully');
283-
dbms_output.put_line('--------------------------------------------------------------');
286+
dbms_output.put_line('&&line_separator');
284287
end if;
285288
end;
286289
/

‎source/install_component.sql‎

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,8 @@
11
set heading off
22
set feedback off
33
exec dbms_output.put_line('Installing component '||upper(regexp_substr('&&1','\/(\w*)\.',1,1,'i',1)));
4+
set feedback on
45
@@&&1
5-
show errors
6-
exec dbms_output.put_line('--------------------------------------------------------------');
6+
set feedback off
7+
exec dbms_output.put_line('&&line_separator');
78

‎source/install_headless.sql‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -18,8 +18,8 @@ define ut3_user = ut3
1818
define ut3_password = XNtxj8eEgA6X6b6f
1919
define ut3_tablespace = users
2020

21-
@@create_utplsql_owner.sql &&ut3_user &&ut3_password &&ut3_tablespace
22-
@@install.sql &&ut3_user
23-
@@create_synonyms_and_grants_for_public.sql &&ut3_user
21+
@@create_utplsql_owner.sql &&ut3_owner &&ut3_password &&ut3_tablespace
22+
@@install.sql &&ut3_owner
23+
@@create_synonyms_and_grants_for_public.sql &&ut3_owner
2424

2525
exit

‎source/uninstall.sql‎

Lines changed: 16 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -14,15 +14,15 @@
1414
See the License for the specific language governing permissions and
1515
limitations under the License.
1616
*/
17-
prompt Uninstalling UTPLSQL v3 framework
18-
set serveroutput on size unlimited format truncated
19-
set verify off
17+
@@define_ut3_owner_param.sql
18+
2019
set feedback on
21-
set define on
2220

2321
spool uninstall.log
2422

25-
define ut3_owner = &1
23+
prompt &&line_separator
24+
prompt Uninstalling UTPLSQL v3 framework
25+
prompt &&line_separator
2626

2727
alter session set current_schema = &&ut3_owner;
2828
set echo on
@@ -250,17 +250,22 @@ drop type ut_varchar2_rows force;
250250

251251
set echo off
252252
set feedback off
253+
declare
254+
i integer := 0;
253255
begin
256+
dbms_output.put_line('Dropping synonyms pointing to non-existing objects in schema '||upper('&&ut3_owner'));
254257
for syn in (
255258
select
256259
case when owner = 'PUBLIC'
257260
then 'public synonym '
258261
else 'synonym ' || owner || '.' end || synonym_name as syn_name,
259262
table_owner||'.'||table_name as for_object
260-
from all_synonyms
263+
from all_synonyms s
261264
where table_owner = upper('&&ut3_owner') and table_owner != owner
265+
and not exists (select 1 from all_objects o where o.owner = s.table_owner and o.object_name = s.table_name)
262266
)
263267
loop
268+
i := i + 1;
264269
begin
265270
execute immediate 'drop '||syn.syn_name;
266271
dbms_output.put_line('Dropped '||syn.syn_name||' for object '||syn.for_object);
@@ -269,12 +274,14 @@ begin
269274
dbms_output.put_line('FAILED to drop '||syn.syn_name||' for object '||syn.for_object);
270275
end;
271276
end loop;
277+
dbms_output.put_line('&&line_separator');
278+
dbms_output.put_line(i||' synonyms dropped');
272279
end;
273280
/
274281
begin
275-
dbms_output.put_line('--------------------------------------------------------------');
276-
dbms_output.put_line('Uninstall complete');
277-
dbms_output.put_line('--------------------------------------------------------------');
282+
dbms_output.put_line('&&line_separator');
283+
dbms_output.put_line('Uninstall complete');
284+
dbms_output.put_line('&&line_separator');
278285
end;
279286
/
280287

0 commit comments

Comments
 (0)