-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathut_truncit.sql
More file actions
94 lines (82 loc) · 1.67 KB
/
Copy pathut_truncit.sql
File metadata and controls
94 lines (82 loc) · 1.67 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
/*file truncit.sp */
CREATE OR REPLACE PROCEDURE truncit (
tab IN VARCHAR2,
sch IN VARCHAR2 := NULL
)
IS
BEGIN
EXECUTE IMMEDIATE 'truncate table ' || NVL (sch, USER) || '.' || tab;
END;
/
/*file tabcount.sf */
CREATE OR REPLACE FUNCTION tabcount (
sch IN VARCHAR2,
tab IN VARCHAR2)
RETURN INTEGER
IS
retval INTEGER;
BEGIN
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM ' || sch || '.' || tab
INTO retval;
RETURN retval;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END;
/
CREATE OR REPLACE PACKAGE ut_truncit
IS
PROCEDURE ut_setup;
PROCEDURE ut_teardown;
-- For each program to test...
PROCEDURE ut_TRUNCIT;
PROCEDURE wtplsql_run;
END ut_truncit;
/
/*file ut_truncit.pkb */
CREATE OR REPLACE PACKAGE BODY ut_truncit
IS
PROCEDURE ut_setup
IS
BEGIN
EXECUTE IMMEDIATE
'CREATE TABLE temp_emp AS SELECT * FROM DUAL';
END;
PROCEDURE ut_teardown
IS
BEGIN
EXECUTE IMMEDIATE
'DROP TABLE temp_emp';
END;
-- For each program to test...
PROCEDURE ut_TRUNCIT IS
BEGIN
TRUNCIT (
TAB => 'temp_emp'
,
SCH => USER
);
utAssert.eq (
'Test of TRUNCIT',
tabcount (USER, 'temp_emp'),
0
);
END ut_TRUNCIT;
--% WTPLSQL SET DBOUT "TRUNCIT:PROCEDURE" %--
PROCEDURE wtplsql_run IS
BEGIN
ut_setup;
ut_TRUNCIT;
ut_teardown;
END wtplsql_run;
END ut_truncit;
/
set serveroutput on size unlimited format word_wrapped
begin
wtplsql.test_run('UT_TRUNCIT');
wt_text_report.dbms_out(in_runner_name => 'UT_TRUNCIT'
,in_detail_level => 30);
end;
/