Home » SQL & PL/SQL » SQL & PL/SQL » ORA-6502 error
ORA-6502 error [message #39215] |
Wed, 26 June 2002 10:37 |
kim
Messages: 116 Registered: December 2001
|
Senior Member |
|
|
I've been receiving the Oracle error message 6502 from a PL/SQL procedure. The procedure is run by a web application. The error occurs daily, but only for 5-30 minutes at a time, other than that the procedure runs for the rest of the day just fine. Also if the procedure is run 10 minutes later with the same data, it passes.
Running the procedure from the backend while the errors are occuring from the web tool will work without the 6502 error, and will usually correct the issue from the front end web application such that the error no longer occurs, about 95% of the time.
I've verified the variable type and length for all passed variables. Sometimes its because the note_t table gets locked, but not always.
Web application is written in Pro*C
Oracle8 Enterprise Edition Release 8.0.4.4.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.4.0 - Production
Any input into tracking for the error would be greatly appreciated.
PL/SQL procedure
=================
Definition :
Created by Eric Dulaurans
Date created 03-Jun-2000
Purpose: This Procedure is creating an order item into order_item_t table.
The status of the insert is NONE
Note: .
Usage: 1. Call by account_product, service PL/SQL packages for now.
2. SQL +
3.
Usage Parameters:
order_id : Parent Order belongs this order_item
name : Name of the Order_item
service_id : Master link to one service
account_product_id : Account_product_id of the service by default if you don't have the service_id
service_type_id : Service_type_id of the service_id by default if you don't have the service_id
core_equip_dns_hostname : The core Equipement DNS hostname (trt-gw2.netcom.ca)
access_specialist_person_id : The person who ordered acces services from Telco
telco_isp_id : The Telco
telco_access_order_num : The order Number generated by Telco
is_delayed_by_cust : Has the customer decided to delay the order completion
is_expedite_requested : Has the customer requested this order item be expedited
on_net_cd : ON NET / OFF NET Concept
modify_by : Who add services
Tables: nc_core.account_product_t
nc_core.order_t
nc_core.person_t
nc_core.service_t
nc_core.status_t
Sequences: nc_core.order_seq
Proc/function used : .
Rules : - If we provide the account_product_id, we check if the account_product_id is not ARCHIVED
- We do not linked to an CANCELLED or COMPLETE order_id
- If we provide the service_id it's not necessary to provide the account_product_id,
and if we do, we force values to input parameters.
- The service_id is not mandatory, default to 999
- The account_product_id is not mandatory, default to 999
- The service_type_id is not mandatory, default to o (zero)
- Check if access_specialist_person_id is not a CANCELLED person
- Check if on_net_cd is not a CANCELLED row N
- The modify_by is mandatory
Limits : Validation integrety
We do not check the integrety relation betwen service_id, acocunt_product_id and service_type_id,
We suppose that the association is correct
--------------------------------------------------------------------------------
How to call it :
BEGIN
nc_core.orders.add_note (
order_id => '10009876', -- mandatory
name => 'My Order Item name',
account_product_id => '10009879',
service_id => '10009885',
core_equip_dns_hostname => 'CORE_DNS',
access_specialist_person_id => '999',
telco_isp_id => '252147',
telco_access_order_num => 'Num Telco',
is_delayed_by_cust => 'N',
is_expedite_requested => 'Y',
service_type_id => '0',
on_net_cd => 'NONE',
modify_by => 'my_account@corp.attcanada.ca', -- mandatory'
);
END;
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
General Steps :
/** Step 1 : Call the ADD_NOTE Procedure in the Library **/
nc_core.orders_lib.add_note (
row_id => row_id,
table_nm => table_nm,
note_txt => note_txt,
is_show_to_customer => is_show_to_customer,
created_by_person_id => created_by_person_id,
modify_by => modify_by );
/** Step 2 : Commit Everything **/
COMMIT;
--------------------------------------------------------------------------------
Library function
================
Purpose: This Procedure is creating an row into NOTE_T table.
The status of the insert is NONE
Note: This code can only add a NOTE to order and order_item.
Usage: Call by PL/SQL.
Order Tracking tool
Usage Parameters:
row_id : This is the Primary key of the parent that refere that note
table_nm : This is the table name of the parent that refere that note
note_txt : All the note you want to enter
is_show_to_customer : Do you want to show this note to the customer ?
created_by_person_id : Who create this note ? (FK to thge PERSONB_T table)
modify_by : Who did it
Tables: nc_core.note_t
nc_core.person_t
nc_core.service_t
nc_core.status_t
Sequences: nc_core.order_seq
Proc/function used : .
--------------------------------------------------------------------------------
How to call it :
BEGIN
nc_core.orders_lib.add_note (
row_id => '10009876', -- mandatory
table_nm => 'ORDER_T',
note_txt => 'This is a Test note',
is_show_to_customer => 'N',
created_by_person_id => 123456798'',
modify_by => 'my_account@corp.attcanada.ca', -- mandatory'
);
END;
--------------------------------------------------------------------------------
Local variables :
-- Local variables allowed from parameters
ln_row_id NUMBER(38) := NULL;
l_table_nm VARCHAR2(30) := NULL;
l_note_txt VARCHAR2(4000) := NULL;
l_is_show_to_customer VARCHAR2(30) := 'N';
ln_created_by_person_id NUMBER(38) := NULL;
l_modify_by VARCHAR2(50) := NULL;
-- Local variables for the procedure
l_procedure_nm VARCHAR2(60) := 'ORDERS_LIB.ADD_NOTE';
ln_note_id NUMBER(38) := NULL;
l_status_cd VARCHAR2(20) := NULL;
ln_status_id NUMBER(38) := NULL;
--------------------------------------------------------------------------------
Basic validation
/* Force Default values of table_nm to order_t, and check if less than 30 digits */
IF LENGTH (table_nm) > 30 THEN
Raise_application_error(-20021, l_procedure_nm||' - The table name of the note is more than 30 digits.');
ELSE
IF table_nm IS NULL THEN
l_table_nm := 'ORDER_ITEM_T';
ELSE
l_table_nm := UPPER(table_nm);
/* We only allow in this package to add a note to an order or an oder_item */
IF l_table_nm NOT IN ('ORDER_T',
'ORDER_ITEM_T',
'ORDER_ITEM_PROCESS_DATE_T',
'ORDER_EXPEDITE_REQUEST_T') THEN
Raise_application_error(-20021, l_procedure_nm||' - The procedure only add a note to an orders tables, and not '||l_table_nm||'.');
END IF;
END IF;
END IF;
/* Check to make sure it's a valid row_id and the status is not 'CANCELLED' */
IF row_id IS NULL THEN
Raise_application_error(-20012, l_procedure_nm||' - Missing value of row_id.');
ELSE
ln_row_id := row_id;
/** If we add a note to an order,
* We check if it is a valid row and not cancelled
**/
IF l_table_nm = 'ORDER_T' THEN
BEGIN
SELECT sta.status_cd
INTO l_status_cd
FROM nc_core.order_t ord,
nc_core.status_t sta
WHERE ord.order_id = ln_row_id
AND ord.status_id = sta.status_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
Raise_application_error(-20021, l_procedure_nm||' - Order_id '||ln_row_id||' is not found.');
WHEN TOO_MANY_ROWS THEN
Raise_application_error(-20021, l_procedure_nm||' - Duplicate Order_id found for row_id '||ln_row_id);
END;
/** We can not linked to a Cancelled order_id
* We can add NOTE to a COMPLETE order.
**/
IF l_status_cd IN ('CANCELLED') THEN
Raise_application_error(-20021, l_procedure_nm||' - Can not add a note linked to the order_id '||ln_row_id||', because it is '||l_status_cd||'.');
END IF;
END IF;
/** If we add a note to an order item,
* We check if it is a valid row and not cancelled
**/
IF l_table_nm = 'ORDER_ITEM_T' THEN
BEGIN
SELECT sta.status_cd
INTO l_status_cd
FROM nc_core.order_item_t ord,
nc_core.status_t sta
WHERE ord.order_item_id = ln_row_id
AND ord.status_id = sta.status_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
Raise_application_error(-20021, l_procedure_nm||' - Order_item_id '||ln_row_id||' is not found.');
WHEN TOO_MANY_ROWS THEN
Raise_application_error(-20021, l_procedure_nm||' - Duplicate Order_item_id found for row_id '||ln_row_id);
END;
/* We can not linked to a Cancelled order_item_id */
IF l_status_cd IN ('CANCELLED') THEN
Raise_application_error(-20021, l_procedure_nm||' - Can not add a note linked to the order_item_id '||ln_row_id||', because it is '||l_status_cd||'.');
END IF;
--> Do we check if the order_id is not Cancelled ?
END IF;
/** If we add a note to an order_item_process_date,
* We check if it is a valid row and not cancelled
**/
IF l_table_nm = 'ORDER_ITEM_PROCESS_DATE_T' THEN
BEGIN
SELECT sta.status_cd
INTO l_status_cd
FROM nc_core.order_item_process_date_t ord,
nc_core.status_t sta
WHERE ord.order_item_process_date_id = ln_row_id
AND ord.status_id = sta.status_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
Raise_application_error(-20021, l_procedure_nm||' - Order_item_process_date_id '||ln_row_id||' is not found.');
WHEN TOO_MANY_ROWS THEN
Raise_application_error(-20021, l_procedure_nm||' - Duplicate Order_item_process_date_id found for order_item_process_date_id '||ln_row_id);
END;
/** We can not linked to a Cancelled order_item_process_date_id.
**/
IF l_status_cd IN ('CANCELLED') THEN
Raise_application_error(-20021, l_procedure_nm||' - Can not add a note linked to the order_item_process_date_id '||ln_row_id||', because it is '||l_status_cd||'.');
END IF;
--> Do we check if the order_id and order_item_id are not Cancelled ?
END IF;
/** If we add a note to an order_expedite_request,
* We check if it is a valid row and not cancelled.
**/
IF l_table_nm = 'ORDER_EXPEDITE_REQUEST_T' THEN
BEGIN
SELECT sta.status_cd
INTO l_status_cd
FROM nc_core.order_expedite_request_t ord,
nc_core.status_t sta
WHERE ord.order_expedite_request_id = ln_row_id
AND ord.status_id = sta.status_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
Raise_application_error(-20021, l_procedure_nm||' - Order_expedite_request_id '||ln_row_id||' is not found.');
WHEN TOO_MANY_ROWS THEN
Raise_application_error(-20021, l_procedure_nm||' - Duplicate Order_expedite_request_id found for order_expedite_request_id '||ln_row_id);
END;
/** We can not linked to a Cancelled order_expedite_request_t
**/
IF l_status_cd IN ('CANCELLED') THEN
Raise_application_error(-20021, l_procedure_nm||' - Can not add a note linked to the order_expedite_request_id '||ln_row_id||', because it is '||l_status_cd||'.');
END IF;
--> Do we check if the order_id and order_item_id are not Cancelled ?
END IF;
END IF;
/* Check if note_txt less than 4000 digits, and NOT NULL */
IF note_txt IS NULL THEN
Raise_application_error(-20021, l_procedure_nm||' - The note is is NULL.');
ELSE
IF LENGTH (note_txt) > 4000 THEN
Raise_application_error(-20021, l_procedure_nm||' - The note is more than 4000 digits.');
ELSE
l_note_txt := note_txt;
END IF;
END IF;
/* Check that is_show_to_customer is one Digit, and force the default value to N */
IF is_show_to_customer IS NULL THEN
l_is_show_to_customer := 'N';
ELSE
IF is_show_to_customer NOT IN ('Y','N') THEN
Raise_application_error(-20021, l_procedure_nm||' - The is_show_to_customer should be Y or N rather than '||is_show_to_customer||'.');
ELSE
l_is_show_to_customer := is_show_to_customer;
END IF;
END IF;
/* Check if created_by_person_id is not an UNACTIVE person */
IF created_by_person_id IS NULL THEN
ln_created_by_person_id := '999';
ELSE /* Check to make sure it's a valid created_by_person_id and the status is not 'UNACTIVE' */
ln_created_by_person_id := created_by_person_id;
BEGIN
SELECT sta.status_cd
INTO l_status_cd
FROM nc_core.person_t p,
nc_core.status_t sta
WHERE p.person_id = ln_created_by_person_id
AND p.status_id = sta.status_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
Raise_application_error(-20021, l_procedure_nm||' - Created_by_person_id '||ln_created_by_person_id||' is not found.');
WHEN TOO_MANY_ROWS THEN
Raise_application_error(-20021, l_procedure_nm||' - Duplicate Person found for created_by_person_id '||ln_created_by_person_id);
END;
/* We can not linked to a UNACTIVE person_id */
IF l_status_cd IN ('UNACTIVE') THEN
Raise_application_error(-20021, l_procedure_nm||' - Can not add a note linked to the person '||ln_created_by_person_id||', because it is '||l_status_cd||' status.');
END IF;
END IF;
/* Validate that the modify_by is not null. */
IF modify_by IS NULL THEN
Raise_application_error(-20012, l_procedure_nm||' - Missing value of modify_by.');
ELSE
/* Check if modify_by is less than 50 digits */
IF LENGTH (modify_by) > 50 THEN
Raise_application_error(-20021, l_procedure_nm||' - The parameter modify_by is more than 50 digits.');
ELSE
l_modify_by := modify_by;
END IF;
END IF;
--------------------------------------------------------------------------------
General Steps :
/** Step 1 : Select appropriates values **/
/** Select the sequence for the note row **/
SELECT NC_CORE.ORDER_SEQ.NEXTVAL
INTO ln_note_id
FROM DUAL;
/** Select the status_id ACTIVE **/
BEGIN
SELECT sta.status_id
INTO ln_status_id
FROM nc_core.status_t sta
WHERE sta.status_cd = 'ACTIVE'
AND sta.table_nm = 'note_t';
EXCEPTION
WHEN NO_DATA_FOUND THEN
Raise_application_error(-20021, l_procedure_nm||' - Status ACTIVE is not found for table '||l_table_nm||'.');
WHEN TOO_MANY_ROWS THEN
Raise_application_error(-20021, l_procedure_nm||' - Duplicate status ACTIVE found for table '||l_table_nm||'.');
END;
/** Step 2 : Insert into the Note_t table **/
BEGIN
INSERT INTO nc_core.note_t (
note_id,
row_id,
table_nm,
note_txt,
is_show_to_customer,
created_by_person_id,
status_id,
update_dt,
insert_dt,
modify_by )
VALUES (ln_note_id,
ln_row_id,
l_table_nm,
l_note_txt,
l_is_show_to_customer,
ln_created_by_person_id,
ln_status_id,
SYSDATE,
SYSDATE,
l_modify_by );
EXCEPTION
WHEN OTHERS THEN
Raise_application_error(-20021, l_procedure_nm||' - Insert into note_t failed.');
END ;
Web Application implementation
==============================
int oracle_add_note() {
char *row_id = ' ',
*note_txt= get_arg("note_txt"),
*table_nm = get_arg("table_nm"),
*modify_by = get_arg("_modify_by"),
message[[8192]];
char created_by_person_id[[128]];
/* This function calls the PL/SQL function to update order, order item notes
* depends on the table_nm parameter.
*/
row_id = get_arg("row_id");
if (alldigits(row_id) == 1) {
query_errors("ordertracking_edit ", "oracle_add_note", getenv("REMOTE_ADDR"));
ASSERTCONNECTION(-1);
EXEC SQL AT DB_NAME
EXECUTE
BEGIN
nc_core.orders.add_note (
row_id => :row_id,
table_nm => :table_nm,
note_txt => :note_txt,
is_show_to_customer => 'N',
created_by_person_id => 999,
modify_by => :modify_by);
END;
END-EXEC;
} else {
return -1;
}
query_errors("done oracle_add_note() retval:", (char *)(itoa(SQLCODE)), get_arg("_modify_by"));
if (SQLCODE != 0) {
sprintf(message, "PLSQL failed for user: %s n order ID: %s n "
"order item ID: %s n strlen of order id is %d n table name: %s n note text: %s n "
"running procedure: oracle_add_note n application error: %d n "
"error message: %s n", modify_by, row_id, get_arg("order_item_id"), strlen(row_id),
table_nm, note_txt, SQLCODE, SQLCODEERR);
mail_for_errors (message, row_id, modify_by);
}
return (SQLCODE);
}
Error message returned
======================
PLSQL failed for user: username@attcanada.ca ordertracking build 1.0
order ID: 273198
order item ID: 273200
strlen of order id is 6
table name: ORDER_T
note text: called and spoke to Kevin he gave me onsite name and number
running procedure: oracle_add_note
application error: -6502
error message: ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 1
|
|
|
Re: ORA-6502 error [message #39218 is a reply to message #39215] |
Wed, 26 June 2002 15:43 |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
This is a long shot - but try confirming what you get from getenv("REMOTE_ADDR") in your code. There is a similar issue when using a similar Oracle pl/sql gateway package where the same error occurs.
1.) If your call is not over a tcp/ip network connection then REMOTE_ADDR could be null.
2.) change add_note in the following way:
* add a local variable (say v_step number :=0;)
* Set v_step to a new value (starting from 1) for each new statement in the pl/sql.
* When the exception occurs - print out the value of v_step. That'll show which statement is causing the error.
You could also turn sql_trace on for that session, capture the trace file and then run it through TKPROF with "record=replay.sql" option. That file will have the sequence of statements as they ran.
|
|
|
Re: ORA-6502 error [message #39675 is a reply to message #39215] |
Thu, 01 August 2002 06:03 |
kim
Messages: 116 Registered: December 2001
|
Senior Member |
|
|
After months of research we narrowed down the issue to being not a problem with the CGI, Oracle or the PL/SQL, leaving the client and the network as possible options. After upgrading our apache configuration on the load balanced web servers to resolve a possible exploit of the previous version we found that the new version improved memory allocation and the issue with the 6502 error has not occurred since. Another note of interest is that one of the variables could be up to 4000 characters in length, which could have caused problems.
Experience from another PL/SQL developer has indicated that memory allocation is often the problem with this error when another logical solution can not be found (not including this incident as it has not be verified, he's 2 for 2 so far). I'm still researching if this indeed was the resolution and will continue to monitor the issue.
I thought it prudent to post the resolution as I hadn't seen it during my research on the web.
Thanks
|
|
|
Goto Forum:
Current Time: Fri Mar 29 09:29:46 CDT 2024
|