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  |
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 #356754 is a reply to message #356749] |
Sun, 02 November 2008 02:03   |
 |
Littlefoot
Messages: 21823 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   |
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   |
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 #356802 is a reply to message #356782] |
Sun, 02 November 2008 08:42   |
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   |
 |
Kevin Meade
Messages: 2103 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: 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 #356891 is a reply to message #356819] |
Mon, 03 November 2008 00:51   |
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 #356893 is a reply to message #356891] |
Mon, 03 November 2008 00:52   |
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   |
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   |
 |
Kevin Meade
Messages: 2103 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   |
joy_division
Messages: 4963 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   |
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 #357032 is a reply to message #357011] |
Mon, 03 November 2008 11:11   |
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 #357092 is a reply to message #357032] |
Mon, 03 November 2008 21:12   |
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   |
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   |
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   |
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   |
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  |
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
|
|
|
Goto Forum:
Current Time: Fri Feb 14 18:36:25 CST 2025
|