Home » SQL & PL/SQL » SQL & PL/SQL » Error at line 26 (no matter what) (Oracle Database 10g Release 10.2.0.1.0, Linux Red Hat)
Error at line 26 (no matter what) [message #314678] Thu, 17 April 2008 08:21 Go to next message
elbarto
Messages: 11
Registered: July 2007
Location: Buenos Aires
Junior Member
Hi guys... I'm not much of a PL/SQL programmer, but I have this function that I've been struggling with since yesterday, and I can't understand where the problem is.

Every time I run it (with the TOAD or the sqlplus) I get the same error. No matter the amount of lines I write, it always says "PL/SQL: numeric or value error: character string buffer too small at line 26". I added many lines before the 26 and again it says line 26.

I read on other posts that the "characer string buffer too small" error mostly ocurrs when attempting to store a large string on a small size variable, but this does not seem to be the case.

Here is the output of the sqlplus:

SQL> CREATE OR REPLACE FUNCTION INTRAWAY.getLineId (vDN IN VARCHAR2, nSourceCentrexId IN NUMBER)
2 RETURN INTEGER
3 IS
4 PRAGMA AUTONOMOUS_TRANSACTION;
5
6
7
8
9
10 -- Variable declaration
11 nLineId INTEGER;
12 vMatch VARCHAR2 (64);
13 nMatchLength INTEGER;
14 vBestMatch VARCHAR2 (64);
15 nBestMatchLength INTEGER;
16 nNpaId INTEGER;
17
18 -- Cursor that will be used to retrieve all the
19 -- existing NPAs
20 CURSOR cNpaRs
21 IS
22 SELECT npaid, npaposix
23 FROM voip_npa;
24
25 -- Cursor that will be used to get the Line ID
26 -- by the exact DN and Centrex ID
27 CURSOR cLineRs
28 IS
29 SELECT tnid
30 FROM iwaydata.srv_voip_lines
31 WHERE tn = vDN;
32
33 -- Rows
34 rNpaRow cNpaRs%ROWTYPE;
35
36 BEGIN
37
38 -- Set default Line ID value
39 nLineId := NULL;
40
41 -- First try to get the line with the same DN
42 OPEN cLineRs;
43 DBMS_OUTPUT.put_line ('Opened Line cursor for DN = ' || vDN || ' AND CentrexID = ' || nSourceCentrexId);
44 IF cLineRs%FOUND THEN
45 FETCH cLineRs INTO nLineId;
46 DBMS_OUTPUT.put_line ('Found ID: ' || nLineId);
47 END IF;
48 CLOSE cLineRs;
49
50 -- If we didn't find the Line ID, search with the patterns
51 IF nLineId IS NULL THEN
52
53 DBMS_OUTPUT.put_line ('Line ' || nLineId || ' not found like that');
54
55 vBestMatch := NULL;
56 nBestMatchLength := 0;
57
58 -- Open the cursor to the voip_npa table
59 OPEN cNpaRs;
60
61 FETCH cNpaRs
62 INTO rNpaRow;
63
64 -- Loop through the patterns
65 WHILE cNpaRs%FOUND
66 LOOP
67 -- Compare the regexp with the DN
68 DBMS_OUTPUT.put_line ( 'NpaID: ' || rNpaRow.npaid || ' Regexp: ' || rNpaRow.npaposix );
69 vMatch := NULL;
70 nMatchLength := 0;
71 vMatch := REGEXP_SUBSTR (vDN, rNpaRow.npaposix);
72
73
74 -- Positive mach?
75 IF vMatch IS NOT NULL THEN
76 DBMS_OUTPUT.put_line('Match: ' || vMatch);
77 nMatchLength := LENGTH (vMatch);
78
79 -- Check if we don't have a better match
80 IF nMatchLength > nBestMatchLength THEN
81 -- Save the match
82 vBestMatch := vMatch;
83 nBestMatchLength := nMatchLength;
84 nNpaId := rNpaRow.npaid;
85 END IF;
86 END IF;
87
88 FETCH cNpaRs
89 INTO rNpaRow;
90
91 EXIT WHEN cNpaRs%NOTFOUND;
92 END LOOP;
93
94 -- If we got a match, get the Line ID
95 IF vBestMatch IS NOT NULL THEN
96 DBMS_OUTPUT.put_line ('Best match: ' || vBestMatch);
97
98 -- If the match was only with the extension
99 -- we need to make sure that it belongs to the same
100 -- Centrex Group
101 -- Until I find out how to do this better
102 -- check it only if the lenght of the match is
103 -- less than 5
104 IF nBestMatchLength < 5 THEN
105
106 DBMS_OUTPUT.put_line ('Checking within lines of group: ' || nSourceCentrexId);
107
108 SELECT tnid
109 INTO nLineId
110 FROM iwaydata.srv_voip_lines
111 WHERE gcxid = nSourceCentrexId
112 AND npaid = nNpaid
113 AND SUBSTR (tn, -nBestMatchLength) = vBestMatch;
114 ELSE
115
116 DBMS_OUTPUT.put_line ('Checking within all the lines');
117
118 SELECT tnid
119 INTO nLineId
120 FROM iwaydata.srv_voip_lines
121 WHERE npaid = nNpaid
122 AND SUBSTR (tn, -nBestMatchLength) = vBestMatch;
123 END IF;
124
125 DBMS_OUTPUT.put_line ('Found ID: ' || nLineId);
126 END IF;
127
128 CLOSE cNpaRs;
129
130 END IF;
131
132 DBMS_OUTPUT.put_line ('Returning LineID ' || nLineId);
133
134 -- Return the Line ID
135 RETURN nLineId;
136
137 EXCEPTION
138 WHEN TOO_MANY_ROWS THEN
139 DBMS_OUTPUT.put_line ('Found more than one Line ID');
140 RETURN NULL;
141 WHEN NO_DATA_FOUND THEN
142 DBMS_OUTPUT.put_line ('Failed to find a Line ID');
143 RETURN NULL;
144 WHEN OTHERS THEN
145 DBMS_OUTPUT.put_line ('Caught exception: (' || SQLCODE || ') ERROR:' || SQLERRM);
146 RETURN NULL;
147 END getLineId;
148 /
CREATE OR REPLACE FUNCTION INTRAWAY.getLineId (vDN IN VARCHAR2, nSourceCentrexId IN NUMBER)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 26


SQL>
Re: Error at line 26 (no matter what) [message #314683 is a reply to message #314678] Thu, 17 April 2008 08:29 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
ERROR at line 1:
Re: Error at line 26 (no matter what) [message #314690 is a reply to message #314678] Thu, 17 April 2008 08:46 Go to previous messageGo to next message
elbarto
Messages: 11
Registered: July 2007
Location: Buenos Aires
Junior Member
mmm... ok, but if the error is on line 1 which is the problem?
The declaration of the function seems ok.
If I attempt to create the function only with a "RETURN 1" body it works Ok.
The problem is evidently on my code, but I cannot understand where. Can you help me?
Re: Error at line 26 (no matter what) [message #314693 is a reply to message #314690] Thu, 17 April 2008 08:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Nothing leaps out from the code (other than that you need to move the %FOUND check for cursor cLineRs to after the FETCH)

I'd have to recommend commenting out the code one bit at a time until you find the line that causes the problem.
Re: Error at line 26 (no matter what) [message #314703 is a reply to message #314678] Thu, 17 April 2008 09:25 Go to previous messageGo to next message
elbarto
Messages: 11
Registered: July 2007
Location: Buenos Aires
Junior Member
Thx for the correction with the %FETCH.

I tried what JRowbottom said and found something interesting.
If I commented all the lines and tried to compile the code with all the lines commented, it returned the same error. If I removed the commented lines, the function was created.

So I went and tried to compile my entire function on another server (same Oracle version, different box, and maybe some differences in the configuration). And it worked.

So now I assume that the problem is not my code (despite how awfull it may seem), but something in the configuration of the DB. Is there any configuration variable that defines the length of the SQL that can be executed or something like that?

Thanks
Re: Error at line 26 (no matter what) [message #314774 is a reply to message #314678] Thu, 17 April 2008 13:56 Go to previous message
elbarto
Messages: 11
Registered: July 2007
Location: Buenos Aires
Junior Member
It was an error with the configuration of the server on which I was working.

I talked to another person who told me he had the same kinds of problems with that server which needs to be reinstalled.

Sorry for the inconvenience and thanks for the help.
Previous Topic: Display many rows in one row
Next Topic: Tracking which procedure updated the record
Goto Forum:
  


Current Time: Sat Dec 03 18:31:56 CST 2016

Total time taken to generate the page: 0.09797 seconds