Home » SQL & PL/SQL » SQL & PL/SQL » Error in procedure when called (Oracle 8i)
Error in procedure when called [message #323381] Wed, 28 May 2008 06:22 Go to next message
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 #323384 is a reply to message #323381] Wed, 28 May 2008 06:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The message says that you are trying to use a non numeric data in the place where a numeric is requested.
Investigate where it can be. Check you datatype. Avoid implicit conversions.

Regards
Michel
Re: Error in procedure when called [message #323388 is a reply to message #323381] Wed, 28 May 2008 06:33 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #323422 is a reply to message #323417] Wed, 28 May 2008 08:03 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
But what about all of your linking columns?
Re: Error in procedure when called [message #323429 is a reply to message #323381] Wed, 28 May 2008 08:14 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
joy_division
Messages: 4640
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
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 #323443 is a reply to message #323440] Wed, 28 May 2008 08:39 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
But of course! I didn't see that hold_str was also NULL. 2 points off for me.
Re: Error in procedure when called [message #323445 is a reply to message #323437] Wed, 28 May 2008 08:39 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #323485 is a reply to message #323381] Wed, 28 May 2008 10:20 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Don't you see the missing semicolon?
Re: Error in procedure when called [message #323494 is a reply to message #323485] Wed, 28 May 2008 10:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
D'Oh! Embarassed

REATE 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 #323495 is a reply to message #323485] Wed, 28 May 2008 10:38 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

ahaa! yes I DO

put that in, ran the script, then ran my SQL and it works!!
Smile

thanks for that, JRowbottom.

now, I DO have several rows where it has that "conatenated string too long" message

Re: Error in procedure when called [message #323496 is a reply to message #323381] Wed, 28 May 2008 10:38 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

didn't see JRowbottoms reply whilst I was writing mine.

this is working fine now.

thank you all so much for your help.

Matt
Re: Error in procedure when called [message #323500 is a reply to message #323381] Wed, 28 May 2008 10:47 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

out of interest,
if I change the length of hold_str to 5000, I get the buffer too small error.

why is that?
Re: Error in procedure when called [message #323505 is a reply to message #323429] Wed, 28 May 2008 10:55 Go to previous message
flyboy
Messages: 1832
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.
Previous Topic: how can i unit 3 sql query to one query?
Next Topic: How do I view the package's body
Goto Forum:
  


Current Time: Sun Dec 04 00:12:24 CST 2016

Total time taken to generate the page: 0.13467 seconds