Error Code 6502 Oracle
Contents |
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle PostersOracle Books Oracle Scripts Ion Excel-DB Don Burleson Blog
ora 6502 oracle error ORA-06502: PL/SQL: numeric or value error oracle error 6502 in fdpstp Oracle Database Tips by Burleson Consulting Question on error "ORA-06502: PL/SQL: numeric or value error": I created the function p_test but oracle error 6502 in afscpcon when I try to run it I get the following error: ERROR at line 1: ORA-06502: PL/SQL: numeric or value error ORA-06512: at "SYS.OWA_UTIL", line 323 ORA-06512: at "JUAN.TEST_IP", line 6 ORA-06512: at line app-fnd-01564 oracle error 6502 1. Answer: The docs note this on the ORA-06502 error: 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 theOra-06502 Character To Number Conversion Error
data, how it is manipulated, or how it is declared so that values do not violate constraints. Answer by Edward Stoever: does owa_util exist and do you have access to it? You can find out by describing owa_util SQL> desc sys.owa_util But it looks like you do have access to it, because you are getting an error on a specific line of that package, which means that you entered it. The OWA_UTIL.get_cgi_env function is made for web browser accessing dynamic web content served up through an web server. I don't think it will work with 6i forms. That is why I suggested you look at THIS web page. There is a java bean package created to get the IP address of the browser that is accessing a 6i form through a browser there. It says: Get Client Information interacts with the client machine to get the username, hostname and IP address. (ReadMe) I have never used it, I don't even know how it works. But it is worth reading about to decide if you can make it work for your application. More info on ORA-6502: A user on Oracle DBA Forums encountered ORA-6502 as related to this SQL: connected to: Oracle 9i Enterprise Edit
UTC Answered by: Tom Kyte – Last updated: April 21, 2009 - 2:05 pm UTC Category: Developer
Ora-06502: Pl/sql: Numeric Or Value Error: Character String Buffer Too Small
– Version: 9.2.0.8.0 Latest Followup You Asked Hi Tom, ora-06502: pl/sql: numeric or value error: null index table key value I have a package which concatenates the data and inserts into a file. I ora-06502: pl/sql: numeric or value error: number precision too large am getting the error "ORA-06502: PL/SQL: numeric or value error: character string buffer too" while executing the package. Please help me.. Thanks in http://www.dba-oracle.com/t_ora_06502_pl_sql_numeric_value_error.htm advance. I am using the following package and a function which concatenates the data with a comma, the function and package are below: CREATE OR REPLACE FUNCTION concat_Str ( p_cursor sys_refcursor, p_del VARCHAR2 := ',' ) RETURN VARCHAR2 IS l_value VARCHAR2(32767); l_result VARCHAR2(32767); BEGIN LOOP FETCH p_cursor https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1593784700346018746 INTO l_value; EXIT WHEN p_cursor%NOTFOUND; IF l_result IS NOT NULL THEN l_result := l_result || p_del; END IF; l_result := l_result || l_value; END LOOP; CLOSE p_cursor ; RETURN l_result; END concat_Str; / CREATE OR REPLACE PACKAGE BODY pkg_test AS CURSOR c1(pFromDate DATE,pToDate DATE) IS SELECT answer_code, answer_text, NVL((SELECT concat_Str(CURSOR( SELECT so.source_code FROM SR so, sy_sr ss WHERE ss.survey_id = s.survey_id AND so.source_id = ss.source_id)) FROM dual), '') target FROM survey s WHERE s.rec_create_date > pfromdate AND s.rec_create_date < ptodate ORDER BY 1; FUNCTION Target(vTrec IN SurveyTarget%ROWTYPE ) RETURN VARCHAR2 IS vTargetRecord VARCHAR2(20000):=NULL; BEGIN vTargetRecord := vTrec.answer_code||'|'||vTrec.answer_text||'|'||vTrec.target; RETURN vTargetRecord; EXCEPTION WHEN OTHERS THEN RAISE ; END; PROCEDURE insertRecord(pTRecord IN SurveyTarget%ROWTYPE := NULL, pFptr IN UTL_FILE.FILE_TYPE, pTargetRecord IN VARCHAR2 ) IS BEGIN UTL_FILE.PUT (pFptr, pTargetRecord || CHR(13) || CHR(10)); UTL_FILE.FFLUSH(pFptr); WHEN OTHERS THEN RAISE ; END; PROCEDURE start_point(pFromDate DATE,pToDate
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About http://stackoverflow.com/questions/21711559/ora-6502-numeric-or-value-error-insert-empty-value-to-a-table-which-has-not-nul Us Learn more about Stack Overflow the company Business Learn more about hiring http://stackoverflow.com/questions/25840561/stored-proc-hitting-ora-06502-pl-sql-numeric-or-value-error developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Ora-6502 Numeric or value error, Insert oracle error Empty value to a table which has NOT NULL column up vote 0 down vote favorite I'm using a below BULK COMMIT proc to insert records from TABLESOURCE to TABLE1. COLUMN1 on TABLE1 is defined as NOT NULL. Sometimes my TABLE_SOURCE returns zero rows, and in that case my PROC is failing with error: Ora - 6502 Numeric or value error I tried to ignore the oracle error 6502 error using the NO_DATA_FOUND in exception but it didn't work either. Could you please let me know how to resolve this? create or replace PROCEDURE PROC1 AS v_ErrNum NUMBER (20); v_ErrMsg VARCHAR2 (270); v_Last_Record PLS_INTEGER; CURSOR c_Get_Record IS SELECT Column1, Column2, Column3 FROM Table1; TYPE Target_Rec IS TABLE OF Table1%ROWTYPE INDEX BY PLS_INTEGER; a_Target_Rec Target_Rec; TYPE FetchSet IS TABLE OF c_Get_Record%ROWTYPE; a_Fetch_Set FetchSet; TYPE cursor_var_type IS REF CURSOR; CV cursor_var_type; BEGIN OPEN CV FOR Select Column1, Column2, Column3 from TABLE_SOURCE where Column1 is NOT NULL; FETCH CV BULK COLLECT INTO a_Fetch_Set; CLOSE CV; v_Last_Record := a_Fetch_Set.LAST; FOR i IN 1 .. v_Last_Record LOOP a_Target_Rec (i).Column1 := a_Fetch_Set (i).Column1; a_Target_Rec (i).Column2 := a_Fetch_Set (i).Column2; a_Target_Rec (i).Column3 := a_Fetch_Set (i).Column3; END LOOP; v_Last_Record := 0; v_Last_Record := a_Target_Rec.LAST; FORALL j IN 1 .. v_Last_Record INSERT INTO TABLE1 VALUES a_Target_Rec (j); COMMIT; -- *** ** Error Handling ** *** EXCEPTION WHEN OTHERS THEN v_ErrNum := SQLCODE ; v_ErrMsg := SUBSTR( SQLERRM, 0, 250) ; RAISE_APPLICATION_ERROR( -20067 , 'ERROR WHILE LOADING TABLE1 Error Msg: ' || dbms_utility.format_error_stack|| dbms_utility.format_error_backtrace) ; ROLLBACK ; END PROC1; oracle plsql ora-06502 share|improve this question edited Feb 11 '14 at 21:49 marc_s 452k938641029 asked Feb
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Stored Proc hitting ORA-06502: PL/SQL: numeric or value error up vote 0 down vote favorite I'm having a problem calling a stored procedure from OSB 11g thru JCA Adapter. The error I'm getting is: ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 1 The error is as short as it is without any appends behind the error description. It only happens in production environment and there are no problems when I point the database to the UAT one. More surprisingly, it will work in production envrionement if one of the variables I pass in is less than 128 characters. (or else I will hit the error above). The stored procedure type and length are defined correctly. having the table column able to cope for 4000 characters. Both Oracle 9i DB sharing the same major minor build revision. The stored proc is located in a package. I extracted out and compared the stored proc from both DB environments using winmerge and it shows they mirror each other. Executing the stored procedure manually in production environment works, provides that I declare a big enough varchar size. I manage to simulate the ORA-06502 error by declaring a variable size lesser than the data length I'm passing in. I even went as far as capturing the network dump from both executions and found there are slightly differences. (Note: I'm reusing the same datasource and only change the ip and username in WLS and did a restart before each executions) When I point to production environment(the 1 having trouble), the tcpdump looks something like below: BEGIN packagename.stored_proc_name(V_value1=>:1 , v_value2=>:2, v_value3=>:3); END .... {variable1}... {variable2}... {variable3}) When I point to UAT environment(the successful 1), the dump looks shorter and without the BEGIN; END tag .... {variable1}... {variable2}... {variable3}) What else could have gone wrong? Appreciate for any helps! oracle stored-procedures weblogic11g osb jca share|improve this question asked Sep 15 '14 at 3:47 You Qi 1,78521536 add a comment| 1 Answer 1 active oldest votes up vote 0 down vote "The stored proc is located in a package. I extracted out and compared the stored proc from both DB en