DBA Hub

📋Steps in this guide1/3

DEPRECATE Pragma for PL/SQL in Oracle Database 12c Release 2 (12.2)

Oracle database 12.2 introduced the DEPRECATE pragma to mark code as deprecated and display compiler warnings if deprecated code is referenced by any newly compiled code.

oracle 12cconfigurationintermediate
by OracleDba
12 views
1

Basics

Compiler warnings must be enabled for us to see the results of the pragma. The pragma has two variations. Without an additional literal the standard messages are displayed. Not surprisingly, when we compile the deprecated code we see a message. More importantly, if we write some code that references the deprecated procedure we will see a warning. We can use a literal string to alter the compiler warning message, which can be used to indicate the API we should have called rather than the deprecated API.

Code/Command (click line numbers to comment):

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
-- Enable just deprecated code warnings.
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:(6019,6020,6021,6022)';

-- Enable all warnings.
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

CREATE OR REPLACE PROCEDURE my_proc AUTHID DEFINER AS
PRAGMA DEPRECATE(my_proc);
BEGIN
  NULL;
END;
/
SP2-0804: Procedure created with compilation warnings

SQL> SHOW ERRORS
Errors for PROCEDURE MY_PROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/3	 PLW-06019: entity MY_PROC is deprecated
SQL>

CREATE OR REPLACE PROCEDURE my_calling_proc AUTHID DEFINER AS
BEGIN
  my_proc;
END;
/
SP2-0804: Procedure created with compilation warnings

SQL> SHOW ERRORS
Errors for PROCEDURE MY_CALLING_PROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/3	 PLW-06020: reference to a deprecated entity: MY_PROC declared in
	 unit MY_PROC[1,11]

SQL>

CREATE OR REPLACE PROCEDURE my_proc AUTHID DEFINER AS
PRAGMA DEPRECATE(my_proc, 'Use MY_PROC2 instead of MY_PROC.');
BEGIN
  NULL;
END;
/
SP2-0804: Procedure created with compilation warnings

SQL> SHOW ERRORS
Errors for PROCEDURE MY_PROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/3	 PLW-06019: entity MY_PROC is deprecated
SQL>


CREATE OR REPLACE PROCEDURE my_calling_proc AUTHID DEFINER AS
BEGIN
  my_proc;
END;
/
Errors for PROCEDURE MY_CALLING_PROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/3	 PLW-06020: reference to a deprecated entity: MY_PROC declared in
	 unit MY_PROC[1,11].
Use MY_PROC2 instead of MY_PROC.
SQL>
2

Packages

A whole package can be deprecated as follows. We can mark individual items in the package as deprecated, rather than the whole package. We can also mark package cursors, types, variables and exceptions as deprecated.

Code/Command (click line numbers to comment):

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
CREATE OR REPLACE PACKAGE my_package AUTHID DEFINER AS
PRAGMA DEPRECATE(my_package);
PROCEDURE my_procedure;
  PROCEDURE my_procedure(p_in IN NUMBER);

  FUNCTION  my_function RETURN NUMBER;
END my_package;
/
SP2-0808: Package created with compilation warnings

SQL> SHOW ERRORS
Errors for PACKAGE MY_PACKAGE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/3	 PLW-06019: entity MY_PACKAGE is deprecated
SQL>

CREATE OR REPLACE PACKAGE my_package AUTHID DEFINER AS
  PROCEDURE my_procedure;
PRAGMA DEPRECATE(my_procedure);
PROCEDURE my_procedure(p_in IN NUMBER);

  FUNCTION  my_function RETURN NUMBER;
END my_package;
/
SP2-0808: Package created with compilation warnings

SQL> SHOW ERRORS
Errors for PACKAGE MY_PACKAGE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/3	 PLW-06019: entity MY_PROCEDURE is deprecated
SQL>

CREATE OR REPLACE PACKAGE my_package AUTHID DEFINER AS
  CURSOR c_cursor IS
    SELECT dummy FROM dual;
PRAGMA DEPRECATE(c_cursor);
TYPE t_rec IS RECORD (
    id           NUMBER,
    description  VARCHAR2(50)
  );
PRAGMA DEPRECATE(t_rec);
g_var NUMBER := 1;
PRAGMA DEPRECATE(g_var);
ex_my_exception EXCEPTION;
PRAGMA DEPRECATE (ex_my_exception);
END my_package;
/
SP2-0808: Package created with compilation warnings

SQL> SHOW ERRORS
Errors for PACKAGE MY_PACKAGE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0	 PLW-05021: exception EX_MY_EXCEPTION does not have a pragma
	 EXCEPTION_INIT

4/3	 PLW-06019: entity C_CURSOR is deprecated
10/3	 PLW-06019: entity T_REC is deprecated
12/9	 PLW-06026: package specification exposes global variable
13/3	 PLW-06019: entity G_VAR is deprecated
16/3	 PLW-06019: entity EX_MY_EXCEPTION is deprecated
SQL>
3

Object Types

We can deprecate object types and their member functions in a similar way to packages. The following example deprecates the whole object type. The following example deprecates individual properties and member functions. For more information see: - DEPRECATE Pragma - Compile-Time Warnings Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

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
CREATE OR REPLACE TYPE t_my_type AUTHID DEFINER AS OBJECT (
PRAGMA DEPRECATE(t_my_type),
id          NUMBER,
  description VARCHAR2(50),
  MEMBER PROCEDURE my_procedure,
  MEMBER FUNCTION my_function RETURN NUMBER
);
/
SP2-0816: Type created with compilation warnings

SQL> SHOW ERRORS
Errors for TYPE T_MY_TYPE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/3	 PLW-06019: entity T_MY_TYPE is deprecated
5/10	 PLW-07203: parameter 'SELF' may benefit from use of the NOCOPY
	 compiler hint

SQL>

CREATE OR REPLACE TYPE t_my_type AUTHID DEFINER AS OBJECT (
  id          NUMBER,
  description VARCHAR2(50),
PRAGMA DEPRECATE(description),
MEMBER PROCEDURE my_procedure,
PRAGMA DEPRECATE(my_procedure),
MEMBER FUNCTION my_function RETURN NUMBER
);
/
SP2-0816: Type created with compilation warnings

SQL> SHOW ERRORS
Errors for TYPE T_MY_TYPE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3	 PLW-06019: entity DESCRIPTION is deprecated
5/10	 PLW-07203: parameter 'SELF' may benefit from use of the NOCOPY
	 compiler hint

6/3	 PLW-06019: entity MY_PROCEDURE is deprecated
SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!