| Error in procedure when called [message #323381] |
Wed, 28 May 2008 06:22  |
matpj
Messages: 115
Registered: March 2006
Location: London, UK
|
Senior Member |
|
|
Hi all,
I have created a procedure (well, stolen it and amended it slightly) that I call in another bit of code to create a comma seperated list of values, that would optherwise be returned on idnividual rows.
the procedure code is:
CREATE OR REPLACE FUNCTION NIKU.dsti_get_resource_id (trans_id varchar2) return varchar2 is
hold_str varchar2(2000);
sep varchar2(2);
begin
for r in (select r.UNIQUE_NAME from niku.prteam te,
niku.srm_projects p,niku.srm_resources r where te.PRPROJECTID = p.id and te.PRRESOURCEID = r.ID and p.UNIQUE_NAME = trans_id) loop
hold_str := hold_str||sep||r.UNIQUE_NAME;
sep := ', ';
end loop;
return hold_str;
end;
/
this gets created without any problem
When I try to call it using this code:
select unique_name, dsti_get_resource_id(unique_name)
from niku.srm_projects
after a second I receive an error:
ORA-06052: PL/SQL: numeric or value error
ORA-06512: at "NIKU.DSTI_GET_RESOURCE_ID", line 7
I am using rapid SQL, so can see the results window DOES have several rows where it has returned values correctly.
Can anyone help me diagnose this, as i'm not 100% what I am doing.
thanks in advance,
Matt
|
|
|
|
|
|
| Re: Error in procedure when called [message #323388 is a reply to message #323381] |
Wed, 28 May 2008 06:33  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
Most probably, you try to fill with HOLD_STR variable with more than 2000 characters. See the example:
SQL> declare
2 l_str varchar2(10);
3 begin
4 l_str := '12345678901';
5 end;
6 /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4
SQL> |
|
|
|
| Re: Error in procedure when called [message #323390 is a reply to message #323381] |
Wed, 28 May 2008 06:34 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I reckon you're trying to build a string longer than 200 chrs.
Change hold_str to a varchar2(32767)
Also, have a search for CONCAT_ALL - it does whay you're trying to do much more efficiently
|
|
|
|
| Re: Error in procedure when called [message #323417 is a reply to message #323381] |
Wed, 28 May 2008 07:59 |
matpj
Messages: 115
Registered: March 2006
Location: London, UK
|
Senior Member |
|
|
hmm, it seems if I increase the size of hold_str, I get the
"ORA-06502: PL/SQL: numeric or value error: character string buffer too small" error at line 10.
the value returned by the SQL in the procedure is a varchar field, so I cant understand why there is a datatype issue..
|
|
|
|
|
|
| Re: Error in procedure when called [message #323429 is a reply to message #323381] |
Wed, 28 May 2008 08:14 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
> I cant understand why there is a datatype issue..
It is not (and never was) datatype issue, it is just the length issue.
As VARCHAR2 in SQL is limited with 4000 characters, you cannot return longer string than that.
SQL> create or replace function f1( p_len in number ) return varchar2 is
2 begin
3 return lpad( 'a', p_len, 'x' );
4 end;
5 /
Function created.
SQL> select f1( 100 ) from dual;
F1(100)
--------------------------------------------------------------------------------
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxa
SQL> select f1( 4001 ) from dual;
select f1( 4001 ) from dual
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "XXX.F1", line 3
SQL>
If you really expect longer strings than 4000 characters, you shall return CLOB instead.
|
|
|
|
| Re: Error in procedure when called [message #323432 is a reply to message #323381] |
Wed, 28 May 2008 08:19 |
matpj
Messages: 115
Registered: March 2006
Location: London, UK
|
Senior Member |
|
|
thats the thing, the error I am getting is NOT a "character string buffer too small"
its simply just the "numeric or value error"
if I INCREASE the hold_str, I receive the error you are talking about..
|
|
|
|
| Re: Error in procedure when called [message #323433 is a reply to message #323381] |
Wed, 28 May 2008 08:19 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
| matpj wrote on Wed, 28 May 2008 07:22 | H
CREATE OR REPLACE FUNCTION NIKU.dsti_get_resource_id (trans_id varchar2) return varchar2 is
hold_str varchar2(2000);
sep varchar2(2);
begin
for r in (select r.UNIQUE_NAME from niku.prteam te,
niku.srm_projects p,niku.srm_resources r where te.PRPROJECTID = p.id and te.PRRESOURCEID = r.ID and p.UNIQUE_NAME = trans_id) loop
hold_str := hold_str||sep||r.UNIQUE_NAME;
sep := ', ';
end loop;
return hold_str;
end;
/
|
Please note that you are using SEP before it has a value, therefore it is NULL.
|
|
|
|
| Re: Error in procedure when called [message #323437 is a reply to message #323381] |
Wed, 28 May 2008 08:31 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
> its simply just the "numeric or value error"
The exact error message may depend on Oracle version. Mine is 10gR1, so it is probably more precise than in 8i.
But the main point is there: it is numeric or value error.
http://ora-06502.ora-code.com/:
| Quote: | ORA-06502: PL/SQL: numeric or value error string
Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).
Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.
|
|
|
|
|
| Re: Error in procedure when called [message #323440 is a reply to message #323433] |
Wed, 28 May 2008 08:36 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I think that's deliberate, to avoid a leading comma on the string.
Go to Ask Tom.
There you will find the CREATE TYPE, CREATE TYPE BODY and CREATE FUNCTION commands to implement the STRAGG (short for STRing AGGregator). That will do what you are trying to do.
|
|
|
|
|
|
| Re: Error in procedure when called [message #323445 is a reply to message #323437] |
Wed, 28 May 2008 08:39 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I'm just about certain it's a string length issue, rather than a value conversion problem.
SQL> declare
2 v_str varchar2(20);
3 begin
4 v_str := rpad('A',18);
5 v_str := v_str||'ABCDEFG';
6 end;
7 /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 5 |
|
|
|
| Re: Error in procedure when called [message #323448 is a reply to message #323381] |
Wed, 28 May 2008 08:45 |
matpj
Messages: 115
Registered: March 2006
Location: London, UK
|
Senior Member |
|
|
it seems that limiting the dataset in my query that CALLS the procedure prevents this error from happening.
the way I have tested this implies that it is having problems with the volumne of data being returned.
not sure if this IS the case, but thats the way it looks at the moment.
|
|
|
|
| Re: Error in procedure when called [message #323455 is a reply to message #323448] |
Wed, 28 May 2008 09:04 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Replace your function with this, and let's see what happens:CREATE OR REPLACE FUNCTION NIKU.dsti_get_resource_id (trans_id varchar2) return varchar2 is
hold_str varchar2(4000);
sep varchar2(2);
begin
for r in (select r.UNIQUE_NAME from niku.prteam te,
niku.srm_projects p,niku.srm_resources r where te.PRPROJECTID = p.id and te.PRRESOURCEID = r.ID and p.UNIQUE_NAME = trans_id) loop
if length(hold_str)+length(r.unique_name)+2 > 4000 then
hold_str := '**** Concatenated String Too Long ****'
exit;
end if;
hold_str := hold_str||sep||r.UNIQUE_NAME;
sep := ', ';
end loop;
return hold_str;
end; |
|
|
|
| Re: Error in procedure when called [message #323459 is a reply to message #323448] |
Wed, 28 May 2008 09:06 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
| Quote: | it seems that limiting the dataset in my query that CALLS the procedure prevents this error from happening.
|
I would guess that this is because the rows that return too much data are not in your limited dataset.
|
|
|
|
| Re: Error in procedure when called [message #323483 is a reply to message #323455] |
Wed, 28 May 2008 10:14 |
matpj
Messages: 115
Registered: March 2006
Location: London, UK
|
Senior Member |
|
|
tried changing the procedure as suggested, but when I run the script I get this error:
Error: PLS-00103: Encountered the symbol "EXIT" when expecting one of the following:
followed by a load of symbols
with this at the end:
The symbol "." was subsituted for "EXIT" to continue.
hmmm...
cant work that out..
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Error in procedure when called [message #323505 is a reply to message #323429] |
Wed, 28 May 2008 10:55 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
| matpj wrote on Wed, 28 May 2008 17:47 | if I change the length of hold_str to 5000, I get the buffer too small error.
why is that?
|
| flyboy wrote on Wed, 28 May 2008 15:14 | As VARCHAR2 in SQL is limited with 4000 characters, you cannot return longer string than that.
...
If you really expect longer strings than 4000 characters, you shall return CLOB instead.
|
Maybe a little correction: you may return longer string, but you are unable to process it in SQL (SELECT statement is SQL).
Realize, that there is difference between SQL and PL/SQL.
Just out of curiosity, I looked into 11g documentation to make sure, that this limit persists: http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/sql_elements001.htm#i45694.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
