Home » SQL & PL/SQL » SQL & PL/SQL » Urgent help on ORA-06502. (Oracle 8I)
Urgent help on ORA-06502. [message #356749] Sun, 02 November 2008 01:22 Go to next message
omkar100
Messages: 10
Registered: November 2008
Junior Member
I am getting ORA-06502 on a PLSQL & I need urgent help as I am stuck up.
Below is the SQL statement which is errored out in PLSQL.

INSERT INTO
AS400_OUTBIL
(OBLENT,OBCTCD,OBCUCC,OBBILL,OBTFIL)
VALUES
(LEGAL_ENT,'A',CUST_COUNTRY,BILLTO,'TSOUT')


THESE ARE DECLARATIONS

LEGAL_ENT VARCHAR2(3)
BILLTO NUMBER(6)
CUST_COUNTRY VARCHAR2(6)


the only varialble billto which is neumeric is evaluated as
billto := NVl(substr(foreign_place_id,7,6),'000000')


Then also I am getting an error.

foreign_place_id is a alphaneumeric field from another file.


The OBBILL is a neumeric field in file as400_outbil.


Is there a way I can check if substr(foreign_place_id,7,6) has any alphaneumeric value. Plese let me know how will i write a sql to check if substr(foreign_place_id,7,6) has any alpha value.

Looks like this is only possibility as billto is 6 digits and we are doing substring for 6 only. so more than 6 digits should not go.....this error is appearing in this PLSQL since last tuesday.earler it was fine..



Re: Urgent help on ORA-06502. [message #356751 is a reply to message #356749] Sun, 02 November 2008 01:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The most urgent thing if you want a quick answer is to read OraFAQ Forum Guide and use SQL*Plus and copy and paste your session in a formatted way as explained and requested in "How to format your post?" section.

So read it and repost as requested, urgently.

Regards
Michel

[Updated on: Sun, 02 November 2008 01:52]

Report message to a moderator

Re: Urgent help on ORA-06502. [message #356754 is a reply to message #356749] Sun, 02 November 2008 02:03 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This question - so far - doesn't seem to belong to JDeveloper, Java & XML; therefore, I will move it to SQL Forum. It might be returned back later, if necessary.

As of your question: we have an INSERT statement, but we don't know how the target table looks like.

The "billto" variable is neumeric (neumeric?); why do you use SUBSTR function against a number?

Then you've said that "OBBILL is a neumeric field in file as400_outbil". In a file? What file? Do you mean a table?

If nothing changed in code and the error appears since last week, it must be data that is causing the problem.

So, who is responsible for entering incorrect values into the table? Is it a user, or is it someone whose design was flawed (in a way)?

First you should find invalid data (8i? Looping through last week's values using the TO_NUMBER and logging those that will fail?), fix them and think about redesign (will it be ALTER TABLE or CREATE TRIGGER or something else, I wouldn't know).
Re: Urgent help on ORA-06502. [message #356772 is a reply to message #356754] Sun, 02 November 2008 04:21 Go to previous messageGo to next message
omkar100
Messages: 10
Registered: November 2008
Junior Member
Many thanks for your reply....
The "billto" variable is neumeric (neumeric?); why do you use SUBSTR function against a number? ----This is existing code and substr is used as field "foreign_place_id" is alpha neumeric and we are extracting starting from 7 next 6 digits and putting in billto variable which is neumeric.


You are very correct actually I also think that data is incorrect and field "foreign_place_id" is likely to have some alpha neumeric data from 7 next 6 positions.

billto := NVl(substr(foreign_place_id,7,6),'000000')

This variable is from a file called place.so I want to run a query on place file to test if the field "foreign_place_id" is having any alpha neumeric data from 7 next 6 positions.
If so that is causing the issue as PLSQL was working fine till tuesday.
So some user has entered incorrect data field foreign_place_id from 7 to next 6 positions..


I want to detect that record and then delete it.so I can rerun the PLSQL..

This is production issue.. Please help ASAP..
Re: Urgent help on ORA-06502. [message #356774 is a reply to message #356772] Sun, 02 November 2008 04:40 Go to previous messageGo to next message
omkar100
Messages: 10
Registered: November 2008
Junior Member
I could find that this error comes also if we are moving say here in variable BILLTO NUMBER(6) value which is of more than 6 in length..
Will that be a problem but as the field foreign_place_id is if varchar2(12) and we are using "foreign_place_id" that is 6 digits from 7th place..
so can any one comment on this please...
Re: Urgent help on ORA-06502. [message #356777 is a reply to message #356774] Sun, 02 November 2008 05:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sun, 02 November 2008 07:42
The most urgent thing if you want a quick answer is to read OraFAQ Forum Guide and use SQL*Plus and copy and paste your session in a formatted way as explained and requested in "How to format your post?" section.

So read it and repost as requested, urgently.

Regards
Michel


Re: Urgent help on ORA-06502. [message #356782 is a reply to message #356772] Sun, 02 November 2008 06:16 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
Please help ASAP

I am quite surprised that ASAP started to use Oracle. Good choice in my opinion Wink
Quote:
we are using "foreign_place_id" that is 6 digits from 7th place..

Why are you convinced that there are digits at that position in all rows?
Re: Urgent help on ORA-06502. [message #356802 is a reply to message #356782] Sun, 02 November 2008 08:42 Go to previous messageGo to next message
omkar100
Messages: 10
Registered: November 2008
Junior Member
Hi,

Thanks for the reply..

The reason being I am convinced is as per design there should be numeric value in field "foreign_place_id" in file PLACE from 7 upto 6 positions....

So as this error is coming I expect there is some alpha value in
substr(foreign_place_id,7,6)....



billto := NVl(substr(foreign_place_id,7,6),'000000')

Re: Urgent help on ORA-06502. [message #356805 is a reply to message #356749] Sun, 02 November 2008 09:13 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Hi, I have four things for you.

First some complaints:

1) if you are going to paste an error message (and if you get one of course you should), then you need to paste then entire messsage. You started out only pasting in the number, leaving me and everyone else who might help you to first look up the error. What a pain in the butt. If you feel your problem is not important enough to paste a full error message, then why should anyone feel your problem is important enough to solve? Which of these two do you think if more helpful?

ORA-06502

ORA-06502: PL/SQL: numeric or value error string 


Quote:
Indeed, even this is insufficient, because you said it was a plsql error, so you should be posting the entire error stack that pops up.


2) when you say there is an issue with code, then just like the error message, you should post the code. You offer up an insert statement, but not the procedure it is housed in. Maybe you think you are helping by reducing the amount of material we have to look at but by definition you are having a problem which means you dont' really know where you problem is. You might well be right in your choice, but then again, by excluding your full code, you may have infact not posted the code that has the problem.

3) when you post code, use the code formatter to format it first. Then use the CODE tags to make it stand out. It is much easier to debug formatted code. After all, which of these looks better to you?

INSERT INTO
AS400_OUTBIL
(OBLENT,OBCTCD,OBCUCC,OBBILL,OBTFIL)
VALUES
(LEGAL_ENT,'A',CUST_COUNTRY,BILLTO,'TSOUT')

INSERT INTO As400_Outbil
           (obLent,
            obctcd,
            obcucc,
            obBill,
            obtfil)
VALUES     (Legal_ent,
            'A',
            cUst_Country,
            BillTo,
            'TSOUT')


Now, as to your problem:

4) This error message has two most common causes:

Quote:
a) you tried to put a character string into a number column/variable.

b) you tried to put 10 bytes worth of string into a five byte column/variable.


I suggest you use the old method of error trapping. Comment out lines of your plsql code, till the error goes away, then uncomment lines back in till it returns. That will tell you exactly where the error is. From there is should be a simple matter of looking at the data causing your error.

You have several options for seeing the data:

Quote:
1) use DBMS_OUTPUT
2) write an automonous transaction and write the data to a file or message table
3) if the error is in DML, use the LOG ERRORS clause
4) use a plsql debugger tool


If the error is indeed the insert, and you are having trouble seeing the bad data, try using the LOG ERRORS clause. Read up about it. Start here:

10gR2 New Feature: DML Error Logging

Good luck, Kevin

[Updated on: Sun, 02 November 2008 09:17]

Report message to a moderator

Re: Urgent help on ORA-06502. [message #356814 is a reply to message #356805] Sun, 02 November 2008 11:01 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Kevin
try using the LOG ERRORS clause. Read up about it. Start here: 10gR2 New Feature: DML Error Logging

Unfortunately, Omkar100 uses Oracle 8i so - no error logging available (that's why I have said a few words about possible workaround in message #3 (loop, TO_NUMBER, exception handler, bla bla)).
Re: Urgent help on ORA-06502. [message #356819 is a reply to message #356749] Sun, 02 November 2008 11:14 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Yes of course. However, I would point out:

1) to my knowledge, 8i is no longer supported by Oracle. Last date I saw was end of support in October 2004. So if he intends to stay on 8i, he is in trouble.

2) although the OP may be using 8i, he is not the only one who will be reading this thread. Many people will get this error often over their oracle careers. They will read this thread and be looking for an answer. It is better to provide the most up-to-date information possible.

Thanks for making it clear to the OP that LOG ERRORS is not an 8i feature.

Kevin
Re: Urgent help on ORA-06502. [message #356891 is a reply to message #356819] Mon, 03 November 2008 00:51 Go to previous messageGo to next message
omkar100
Messages: 10
Registered: November 2008
Junior Member
/* ******************************************************************************/
/* */
/* PROCEDURE: TSIT_OUTBIL */
/* */
/* DESCRIPTION: Update OUTBIL for Billto's which must not be Deactivated In MF */
/* */
/* DATE WRITTEN: 03/02/01 */
/* */
/* WRITTEN BY: Gavin Hales */
/* */
/* ******************************************************************************/
CREATE or REPLACE PROCEDURE tsit_outbil IS


-- Define work variables
-- =====================
legal_ent VARCHAR2(3);
billto NUMBER(6);
cust_country VARCHAR2(6);
billto_found VARCHAR2(1);


-- Cursor 1: Get all Billtos
-- =========================
cursor c1 is
select
place_id,
foreign_place_id,
county_id
from
PLACE
where
whos_place = 'BILL';
r1 c1%Rowtype;


-- Cursor 2: Get Products owned by Billto
-- ======================================
cursor c2 is
select
place_id_owned_by
from
PRODUCT
where
place_id_owned_by = r1.place_id;
r2 c2%Rowtype;


-- Cursor 3: Get Requests for Billto which have been created/updated in last 12 months
-- ===================================================================================
cursor c3 is
select
*
from
request
where
place_id_to_bill = r1.place_id AND
req_status <> 'CANCELED' AND
ADD_MONTHS(last_updated_dt, 12) > sysdate;
r3 c3%Rowtype;


-- Cursor 4: Get Contracts for Billto which are active OR expired less than 1 year old
-- ===================================================================================
Cursor c4 is
Select
*
From
contract
Where
place_id_bill_to = r1.place_id AND
contract_status <> 'CA' AND
ADD_MONTHS(end_dt, 12) > sysdate;
r4 c4%Rowtype;


BEGIN

-- First remove all current data in WISE file OUTBIL
-- =================================================
Delete from as400_outbil where obtfil = 'TSOUT';


-- Read all BILLTOS
-- ================
Open c1;
Loop

Fetch c1 into r1;
Exit When c1%Notfound;

billto_found := 'N';

-- 1. Check if Billto owns any Equipment
-- =====================================
Open c2;
Fetch c2 into r2;
If c2%Found Then

billto_found := 'Y';
legal_ent := r1.county_id;
cust_country := NVL(substr(r1.foreign_place_id, 1, 6),'??????');
billto := NVL(substr(r1.foreign_place_id, 7, 6),'000000');

Insert into
as400_outbil
(OBLENT, OBCTCD, OBCUCC, OBBILL, OBTFIL)
Values
(legal_ent, 'A', cust_country, billto, 'TSOUT');
End if;
Close c2;

-- 2. Check if Billto has been used on a Request in the last 12 months
-- ===================================================================
If billto_found = 'N' Then
Open c3;
Fetch c3 into r3;
If c3%Found Then

billto_found := 'Y';
legal_ent := r1.county_id;
cust_country := NVL(substr(r1.foreign_place_id, 1, 6),'??????');
billto := NVL(substr(r1.foreign_place_id, 7, 6),'000000');

Insert into
as400_outbil
(OBLENT, OBCTCD, OBCUCC, OBBILL, OBTFIL)
Values
(legal_ent, 'A', cust_country, billto, 'TSOUT');
End if;
Close c3;
End if;


-- 3. Check if Billto has been used on a Contract in the last 12 months
-- ====================================================================
If billto_found = 'N' Then
Open c4;
Fetch c4 into r4;
If c4%Found Then

billto_found := 'Y';
legal_ent := r1.county_id;
cust_country := NVL(substr(r1.foreign_place_id, 1, 6),'??????');
billto := NVL(substr(r1.foreign_place_id, 7, 6),'000000');

Insert into
as400_outbil
(OBLENT, OBCTCD, OBCUCC, OBBILL, OBTFIL)
Values
(legal_ent, 'A', cust_country, billto, 'TSOUT');
End if;
Close c4;
End if;

End Loop;
Close c1;

Commit;

END;
/

The error message is :

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "OU_DBA.TSIT_OUTBIL", line 90
ORA-06512: at line 1
ORA-02067: transaction or savepoint rollback required
ORA-02067: transaction or savepoint rollback required
ORA-02067: transaction or savepoint rollback required
ORA-02067: transaction or savepoint rollback required
Re: Urgent help on ORA-06502. [message #356892 is a reply to message #356891] Mon, 03 November 2008 00:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: Urgent help on ORA-06502. [message #356893 is a reply to message #356891] Mon, 03 November 2008 00:52 Go to previous messageGo to next message
omkar100
Messages: 10
Registered: November 2008
Junior Member
I Missed one thing here.. The Line no90 after removing comments comes as:-

Insert into
as400_outbil
(OBLENT, OBCTCD, OBCUCC, OBBILL, OBTFIL)
Values
(legal_ent, 'A', cust_country, billto, 'TSOUT');
Re: Urgent help on ORA-06502. [message #356895 is a reply to message #356893] Mon, 03 November 2008 00:53 Go to previous messageGo to next message
omkar100
Messages: 10
Registered: November 2008
Junior Member
Sorry I missed formatting the code earlier..Please see below now..
/* ******************************************************************************/
/* 										*/
/* PROCEDURE:    TSIT_OUTBIL							*/
/* 										*/
/* DESCRIPTION:	 Update OUTBIL for Billto's which must not be Deactivated In MF	*/
/* 										*/
/* DATE WRITTEN: 03/02/01							*/
/*  										*/
/* WRITTEN BY:	 Gavin Hales							*/
/* 										*/
/* ******************************************************************************/
CREATE OR REPLACE PROCEDURE tSit_Outbil
IS
-- Define work variables
-- =====================
  Legal_ent     VARCHAR2(3);
  BillTo        NUMBER(6);
  cUst_Country  VARCHAR2(6);
  BillTo_Found  VARCHAR2(1);
  -- Cursor 1: Get all Billtos
  -- =========================
  CURSOR c1 IS 
    SELECT Place_Id,
           Foreign_Place_Id,
           County_Id
    FROM   Place
    WHERE  Whos_Place = 'BILL';
  r1            c1%ROWTYPE;
  -- Cursor 2: Get Products owned by Billto
  -- ======================================
  CURSOR c2 IS 
    SELECT Place_Id_Owned_By
    FROM   Product
    WHERE  Place_Id_Owned_By = r1.Place_Id;
  r2            c2%ROWTYPE;
  -- Cursor 3: Get Requests for Billto which have been created/updated in last 12 months
  -- ===================================================================================
  CURSOR c3 IS 
    SELECT *
    FROM   Request
    WHERE  Place_Id_To_Bill = r1.Place_Id
           AND req_Status <> 'CANCELED'
           AND Add_months(Last_Updated_dt,12) > SYSDATE;
  r3            c3%ROWTYPE;
  -- Cursor 4: Get Contracts for Billto which are active OR expired less than 1 year old
  -- ===================================================================================
  CURSOR c4 IS 
    SELECT *
    FROM   Contract
    WHERE  Place_Id_Bill_To = r1.Place_Id
           AND Contract_Status <> 'CA'
           AND Add_months(End_dt,12) > SYSDATE;
  r4            c4%ROWTYPE;
BEGIN
-- First remove all current data in WISE file OUTBIL
-- =================================================

  DELETE FROM As400_Outbil
  WHERE       obtfil = 'TSOUT';
  -- Read all BILLTOS
  -- ================
  
  OPEN c1;
  
  LOOP
    FETCH c1 INTO r1;
    
    EXIT WHEN c1%NOTFOUND;
    
    BillTo_Found := 'N';
    -- 1. Check if Billto owns any Equipment
    -- =====================================
    
    OPEN c2;
    
    FETCH c2 INTO r2;
    
    IF c2%FOUND THEN
      BillTo_Found := 'Y';
      
      Legal_ent := r1.County_Id;
      
      cUst_Country := nvl(Substr(r1.Foreign_Place_Id,1,6),'??????');
      
      BillTo := nvl(Substr(r1.Foreign_Place_Id,7,6),'000000');
      
      INSERT INTO As400_Outbil
                 (obLent,
                  obctcd,
                  obcucc,
                  obBill,
                  obtfil)
      VALUES     (Legal_ent,
                  'A',
                  cUst_Country,
                  BillTo,
                  'TSOUT');
    END IF;
    
    CLOSE c2;
    -- 2. Check if Billto has been used on a Request in the last 12 months
    -- ===================================================================
    
    IF BillTo_Found = 'N' THEN
      OPEN c3;
      
      FETCH c3 INTO r3;
      
      IF c3%FOUND THEN
        BillTo_Found := 'Y';
        
        Legal_ent := r1.County_Id;
        
        cUst_Country := nvl(Substr(r1.Foreign_Place_Id,1,6),'??????');
        
        BillTo := nvl(Substr(r1.Foreign_Place_Id,7,6),'000000');
        
        INSERT INTO As400_Outbil
                   (obLent,
                    obctcd,
                    obcucc,
                    obBill,
                    obtfil)
        VALUES     (Legal_ent,
                    'A',
                    cUst_Country,
                    BillTo,
                    'TSOUT');
      END IF;
      
      CLOSE c3;
    END IF;
    -- 3. Check if Billto has been used on a Contract in the last 12 months
    -- ====================================================================
    
    IF BillTo_Found = 'N' THEN
      OPEN c4;
      
      FETCH c4 INTO r4;
      
      IF c4%FOUND THEN
        BillTo_Found := 'Y';
        
        Legal_ent := r1.County_Id;
        
        cUst_Country := nvl(Substr(r1.Foreign_Place_Id,1,6),'??????');
        
        BillTo := nvl(Substr(r1.Foreign_Place_Id,7,6),'000000');
        
        INSERT INTO As400_Outbil
                   (obLent,
                    obctcd,
                    obcucc,
                    obBill,
                    obtfil)
        VALUES     (Legal_ent,
                    'A',
                    cUst_Country,
                    BillTo,
                    'TSOUT');
      END IF;
      
      CLOSE c4;
    END IF;
  END LOOP;
  
  CLOSE c1;
  
  COMMIT;
END;
/


------------------------------------------------------

The error message here:-

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "OU_DBA.TSIT_OUTBIL", line 90
ORA-06512: at line 1
ORA-02067: transaction or savepoint rollback required
ORA-02067: transaction or savepoint rollback required
ORA-02067: transaction or savepoint rollback required
ORA-02067: transaction or savepoint rollback required

[EDITED by LF: added [code] tags]

[Updated on: Mon, 03 November 2008 02:45] by Moderator

Report message to a moderator

Re: Urgent help on ORA-06502. [message #356998 is a reply to message #356749] Mon, 03 November 2008 08:14 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Posting the code helps, but let me suggest the following as well. The error message gives a line number. There are two things that will hurt your post when posting code:

Quote:
1) formatting the code after you have gotten the error. This will naturally change the line spacing and format such that the lines no longer match the error.

2) not also posting the line number.

Unless you are goin to format the code, recompile the formatted code, and then run the code again to get a new line number, do not format it. Just paste it as is off the database. Otherwise we can't track the lines.

Additionally, take the code from user_source. You can use this from sqlplus.

set linesize 100
col text format a80 word

select line,text
from user_source
where name = upper('tSit_Outbil')
order by line
/


The idea is to give people what they need to help you find your issue in the code.

So... now to the issue.

Quote:
character string buffer too small

Is telling you that your are putting 10 pounds of stuff into a 5 pound bag. In this case, your are putting 10 characters into a five byte column/variable.

Your problem is not putting a character into a number column/variable.

SQL> declare av varchar2(1); begin av := '12'; end;
  2  /
declare av varchar2(1); begin av := '12'; end;
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1


SQL> declare av number; begin av := 'a'; end;
  2  /
declare av number; begin av := 'a'; end;
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 1


SQL> 

If the insert is the location of the error, then you need to compare the definition of your table to the size of values you are putting into it. You will need to alter your code to add debugging lines (dbms_output.put_line, or calling an autonomous transaction to write the data out somewhere).

Do you know how to do this? Can you modify the code?

Kevin

Kevin
Re: Urgent help on ORA-06502. [message #357001 is a reply to message #356749] Mon, 03 November 2008 08:26 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
And this is a problem too:
FOO SCOTT>col zoo new_value zoo
FOO SCOTT>select 5 zoo from dual;

       ZOO
----------
         5

FOO SCOTT>select nvl(to_number('&zoo'),'bye') from dual;
old   1: select nvl(to_number('&zoo'),'bye') from dual
new   1: select nvl(to_number('         5'),'bye') from dual
select nvl(to_number('         5'),'bye') from dual
                                   *
ERROR at line 1:
ORA-01722: invalid number


Both arguments to NVL need to be of the same TYPE.
Re: Urgent help on ORA-06502. [message #357011 is a reply to message #356998] Mon, 03 November 2008 09:20 Go to previous messageGo to next message
omkar100
Messages: 10
Registered: November 2008
Junior Member
Hi Kevin,

Many Thanks for the help.

I have deleted the comments and found out the line number.and then found that it came as 90.

Actually the strange thing is the code was working fine till 29th Oct 08...SO nothing has been changed in code...
So changing the code ..My boss wont allow:-(

So Bit worried.I am beginer in PLSQL..
Could you please let me know what kind of program modification will be required..
Re: Urgent help on ORA-06502. [message #357012 is a reply to message #357011] Mon, 03 November 2008 09:21 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
That depends. What is the error? you have not told us what you think the error is.
Re: Urgent help on ORA-06502. [message #357013 is a reply to message #357011] Mon, 03 November 2008 09:22 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
If you cannot change the code in order to debug it, then you must use a plsql debugger. This is a long learning curve. Ask your boss how he expects you to debug the code if you can't modify it in a test environment.
Re: Urgent help on ORA-06502. [message #357032 is a reply to message #357011] Mon, 03 November 2008 11:11 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@omkar100,

omkar100 wrote on Mon, 03 November 2008 20:50

Actually the strange thing is the code was working fine till 29th Oct 08...SO nothing has been changed in code...



Then the only chance I think is that the data might have changed. Can you describe the tables you used in your cursors? My bet is on the table 'PLACE' used in your cursor c1. (But you never know!!!)

Regards,
Jo
Re: Urgent help on ORA-06502. [message #357040 is a reply to message #357001] Mon, 03 November 2008 11:45 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
joy_division wrote on Mon, 03 November 2008 19:56
Both arguments to NVL need to be of the same TYPE.


SQL> col zoo new_value zoo
SQL> select 5 zoo from dual;

       ZOO
----------
         5

SQL> select nvl(to_number('&zoo'), '10') from Dual;
old   1: select nvl(to_number('&zoo'), '10') from Dual
new   1: select nvl(to_number('         5'), '10') from Dual

NVL(TO_NUMBER('5'),'10')
------------------------
                       5



So i will add that all the values/expressions should be the same data-types or should be implicitly convertible to the same data-type. http://img2.mysmiley.net/imgs/smile/winking/winking0001.gif

Regards,
Jo

Re: Urgent help on ORA-06502. [message #357092 is a reply to message #357032] Mon, 03 November 2008 21:12 Go to previous messageGo to next message
omkar100
Messages: 10
Registered: November 2008
Junior Member
Hi I also think that there is a problem with data in Place file and that to in place.foreign_place_id and place.county_id...

Can anyone help me as how will I built a query for the records that will exactly get processed in when cursor C4 is opened....

Re: Urgent help on ORA-06502. [message #357205 is a reply to message #357092] Tue, 04 November 2008 05:00 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@omkar100,

For the last time, can you please describe your tables (especially the PLACE table)?
Check the (maximum)LENGTH of the data in each of the required column(start with COUNTRY_ID in PLACE table). Read and understand the suggestions given to you.

You can't expect any "real" help if you are reluctant to supply the information expected from you.

Regards,
Jo

Re: Urgent help on ORA-06502. [message #357428 is a reply to message #357205] Wed, 05 November 2008 05:31 Go to previous messageGo to next message
omkar100
Messages: 10
Registered: November 2008
Junior Member
PLACE_ID
U_VERSION
SECURITY_CODE
WHOS_PLACE
GLOBAL_NAME
NAME
REST_OF_NAME
ADDRESS
SECOND_ADDRESS
THIRD_ADDRESS
FOURTH_ADDRESS
CITY
STATE_PROV
ZIPPOST
COUNTRY
EMAIL_ADDRESS
X_COORDINATE
Y_COORDINATE
TRAVEL_ZONE
PHONE
ALTERNATE_PHONE
FAX
GMT_OFFSET
OBSERVES_DST
DAYLIGHT_CODE
TEMPORARY_PLACE
REPAIR_CENTER
CURRENCY
LANGUAGE_CODE
GL_ACCT_REVENUE
GL_ACCT_COGS
GL_ACCT_PART_VAL
GL_ACCT_PRODUCT
PERSON_ID_PRIMARY
PERSON_ID_SECNDRY
PERSON_ID_TERTIARY
PERSON_ID_DFLT_QUE
GROUP_ID_DFLT_QUE
PLACE_ID_FOR_STOCK
LOCATION_FOR_STOCK
PLACE_ID_BILL_FROM
PLACE_ID_TO_RCV
PLACE_ID_TO_SHP
STATUS_CONDITION
HOLD_CONDITION
CREDIT_HOLD
CUSTOMS_ID
FREIGHT_TERMS_RCV
FREIGHT_TERMS_SHP
SHIP_VIA_RCV
SHIP_VIA_SHP
PROOF_OF_PURCHASE
PERSON_ID_SALES
LOCATION_FROM_RCV
LOCATION_TO_RCV
LOCATION_FROM_SHP
LOCATION_TO_SHP
EXPCT_LEAD_TM_STK
PRIORITY
ESCALATION_RTE
PLACE_TYPE
PLACE_GROUP
PLACE_CLASS
PLACE_CATEGORY
SALES_GRP_A
SALES_GRP_B
SALES_GRP_C
SALES_GRP_D
SALES_GRP_E
PHY_SVC_GRP_A
PHY_SVC_GRP_B
PHY_SVC_GRP_C
PHY_SVC_GRP_D
PHY_SVC_GRP_E
ORG_SVC_GRP_A
ORG_SVC_GRP_B
ORG_SVC_GRP_C
ORG_SVC_GRP_D
ORG_SVC_GRP_E
ALLOW_CONTRACTS
SEND_MAILERS
ALLOW_PRODUCTS
STOCK_PARTS
CONSOLIDATE_INVCES
MAIN_SIC_CODE
SECOND_SIC_CODE
CREDIT_STATUS
CREDIT_TERMS_CD
COUNTY_ID
CITY_ID
BLANKET_PO
PO_REQUIRED
TAX_EXEMPT_CODE
TAX_EXEMPT_ID
VAT_FOR_PLACE
VAT_REGISTRATION
PART_ADJ_PCT
LABOR_ADJ_PCT
TRAVEL_ADJ_PCT
CONTRACT_ADJ_PCT
ACCOUNT_STATUS
PLACE_ID_TO_BILL
PLACE_ID_FOR_ROLUP
FOREIGN_PLACE_ID
CONTACT_RESTRICTED
CRITICAL_ACCOUNT
SAFETY_OSHA_REQT
COMM_INTERVAL_MIN
CALENDAR_ID
FORCE_DETAIL_PRINT
DUNS_NUMBER
ORACLE_ADDR_ID
ORACLE_CUST_ID
BILLING_PLACE
SHIP_TO_PLACE
BUYING_GROUP
STATUS
USER_DEF_1
USER_DEF_2
USER_DEF_3
EXTRACT_PART_NEED
PUT_AWAY_PROC
CREATED_ID
CREATED_DT
CREATED_TM
LAST_UPDATED_ID
LAST_UPDATED_DTTM
Re: Urgent help on ORA-06502. [message #357437 is a reply to message #357428] Wed, 05 November 2008 05:50 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@omkar100,

I have no idea what you posted now. I meant something like this:

SQL> desc employees;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                          VARCHAR2(25)
 E_MAIL                                             VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                          DATE
 JOB_ID                                             VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(10,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)
 COMMM                                              VARCHAR2(50)



Also as already suggested by Michel and Kevin, Please read OraFAQ Forum Guide, especially "How to format your post?" section.
[***Added:]
joicejohn wrote on Tue, 04 November 2008 16:30
@omkar100,
Check the (maximum)LENGTH of the data in each of the required column(start with COUNTRY_ID in PLACE table).


Regards,
Jo

[Updated on: Wed, 05 November 2008 05:52]

Report message to a moderator

Re: Urgent help on ORA-06502. [message #357451 is a reply to message #356749] Wed, 05 November 2008 06:28 Go to previous messageGo to next message
msmallya
Messages: 66
Registered: March 2008
Location: AHMEDABAD, GUJARAT
Member
Hi @omkar100,

You can select substr(col,7,1), substr(col,8,1)....into a temporary table and select distinct each column values, so that you will come to know what data it holds and finally those records you can pull out. This is just a workaround.

Thanks and regards,

MSMallya

[Updated on: Wed, 05 November 2008 06:29]

Report message to a moderator

Re: Urgent help on ORA-06502. [message #357459 is a reply to message #357428] Wed, 05 November 2008 07:05 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@omkar100,

My apologies I misread COUNTY_ID as COUNTRY_ID.
Can you please run the following query and paste the output here:
 SELECT Place_Id,
           Foreign_Place_Id,
           County_Id, 
    FROM   Place
    WHERE  Whos_Place = 'BILL';


Regards,
Jo
Previous Topic: Grouping rows based on value - pl/sql
Next Topic: Oracle newbie
Goto Forum:
  


Current Time: Sun Dec 04 02:44:20 CST 2016

Total time taken to generate the page: 0.11345 seconds