SOLVED

ORA-06512 Error Message

Asked by OracleDba••13 views•oracle
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
ORA-06512 Error Message

ORA-06512 ERROR MESSAGE

Description

When you encounter an ORA-06512 error, the following error message will appear:

ORA-06512: at line

Cause

This error is caused by the stack being unwound by unhandled exceptions in your PLSQL code.

The options to resolve this Oracle error are:

1.Fix the condition that is causing the unhandled error.

2.Write an expention handler for this unhandled error.

3.Contact your DBA for help.

The ORA-06512 error message indicates the line number of the unhandled error in the PLSQL code. This is quite useful when troubleshooting.

Re
#oracle#error

Solutions(1)

Accepted Solution
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
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
Option #1 – Fix the Error Condition

Let’s look at an example of how to resolve an ORA-06512 error by fixing the error condition.

For example, if you created a procedure called TestProc as follows:

SQL> CREATE OR REPLACE PROCEDURE TestProc

  2  AS

  3    v_number number(2);

  4  BEGIN

  5    v_number := 100;

  6  END;

  7  /

Procedure created.

This procedure was successfully created. But when we try to execute this procedure, we will get an ORA-06512 error as follows:

SQL> execute TestProc();

BEGIN TestProc(); END;

*

ERROR at line 1:

ORA-06502: PL/SQL: numeric or value error: number precision too large

ORA-06512: at "EXAMPLE.TESTPROC", line 5

ORA-06512: at line 1

The first line of the error message (ie: ORA-06502) indicates the error that occurred, while the second line of the error message (ie: ORA-06512) indicates that the error occurred at line 5 of the PLSQL code.

In this example, you’ve tried to assign a 3 digit number to a variable called v_number that can only handle 2 digits. You could correct this error by redefining the v_number variable as number(3).

SQL> CREATE OR REPLACE PROCEDURE TestProc

  2  AS

  3    v_number number(3);

  4  BEGIN

  5    v_number := 100;

  6  END;

  7  /

Procedure created.

And now when we execute our TestProc procedure, the ORA-06512 error has been resolved

SQL> execute TestProc();

PL/SQL procedure successfully completed.

Option #2 – Write an Exception Handler

Let’s look at an example of how to resolve an ORA-06512 error by writing an exception handler.

For example, if you created a procedure called TestProc as follows:

SQL> CREATE OR REPLACE PROCEDURE TestProc

  2  AS

  3    v_number number(2);

  4  BEGIN

  5    v_number := 100;

  6  END;

  7  /

Procedure created.

This procedure was successfully created. But when we try to execute this procedure, we will get an ORA-06512 error as follows:

SQL> execute TestProc();

BEGIN TestProc(); END;

*

ERROR at line 1:

ORA-06502: PL/SQL: numeric or value error: number precision too large

ORA-06512: at "EXAMPLE.TESTPROC", line 5

ORA-06512: at line 1

The first line of the error message (ie: ORA-06502) indicates the error that occurred, while the second line of the error message (ie: ORA-06512) indicates that the error occurred at line 5 of the PLSQL code.

In this example, you’ve tried to assign a 3 digit number to a variable called v_number that can only handle 2 digits. You could correct this by writing an exception handler to set the v_number variable to 99 (so that it is only 2 digits) when this error occurs.

SQL> CREATE OR REPLACE PROCEDURE TestProc

  2  AS

  3    v_number number(2);

  4  BEGIN

  5    v_number := 100;

  6 EXCEPTION

  7   WHEN OTHERS THEN

  8      v_number := 99;

  9 END;

 10 /

Procedure created.

And now when we execute our TestProc procedure, the ORA-06512 error has been resolved.

SQL> execute TestProc();

PL/SQL procedure successfully completed.
OracleDba•

Post Your Solution