ORA-06502 PL/SQL: numeric or value error

[LaurenM]

Hi Everyone, I have this function I am building so I can parse long datatypes. I keep getting a ORA-06502 error when I come to the following line

select rls.text into rsqlcode from report_level_sql rls where rls.id = r_id;

rls.text is the column with the long datatype,
rsqlcode is declared as report_level_sql.text%type,
and r_id is the only parameter that is passed in.

Any clues as to why I keep getting this error?

Please help,

Lauren McDonald, B.Sc.
Programmer Analyst
Saskatchewan Healthcare Employees' Pension Plan

 

[SantaMufasa]

Lauren,

Without the code in your function, it's virtually impossible for us to diagnose the problem you face. Please post your function and/or any other code that relates to this issue.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[sem]

Is your real text size? Is it greater than 32K?
Whate are report_level_sql.id and r_id datatypes?

And is this code executed on server or by some client-side pl/sql interpreter (e.g. Oracle Forms or Reports)?

Regards, Dima

http://seminihin.narod.ru

 

[LaurenM]

here is code for the function.

create or replace function long_instr(r_id in report_level_sql.id%type) return varchar2 is  
    rsqlcode report_level_sql.text%type;
    Result varchar2(10);
begin
    select rls.text into rsqlcode from report_level_sql rls where rls.id = r_id;
    if instr(rsqlcode, '%YMPE%') > 0 then 
         Result := 'Y';
    else 
         Result := 'N';
    end if;
    return(Result);
end long_instr;

The datatype for report_level_sql.id is number, and r_id is of the same datatype as report_level_sql.id.
This code is run on the server side.
I have an anonymous block that goes through the table report_level_sql and calls this function for each record in the table.
What I am trying to do is see if the letters YMPE appear in the text column. If you have a better or faster way of parsing a long column let me know.

Lauren McDonald, B.Sc.
Programmer Analyst
Saskatchewan Healthcare Employees' Pension Plan

 

[sem]

You didn't answer about text length.
The issue is that in pl/sql you can not hold more than 32K, thus to work with longer values you need something different, e.g. OCI (or VB).

The better way is to migrate to CLOB ASAP

Regards, Dima

http://seminihin.narod.ru

 

[LaurenM]

I'm not the DBA, so I can't migrate to CLOB. The only thing that I know about the text column is that it is a long datatype and that it holds sql code which is used generate pdf reports on the company website, so my guess is that it could run greater than 32K, but I am not sure. I will try using the CLOB datatype in the function and parsing the CLOB.

Lauren McDonald, B.Sc.
Programmer Analyst
Saskatchewan Healthcare Employees' Pension Plan

 

[LaurenM]

I have been unsuccessful in converting the LONG to CLOB, I will be doing this a different and longer way.
Thanks Santa and sem for your help.

Lauren McDonald, B.Sc.
Programmer Analyst
Saskatchewan Healthcare Employees' Pension Plan