PL/SQL :: Getting Error ORA-6502 - Numeric Or Value At Line 11?

Apr 13, 2013

declare
type idstab is table of employees.employee_id%type;
type nametab is table of employees.last_name%type;
ids idstab;

[code]...

View 4 Replies


ADVERTISEMENT

Forms :: ORA - 6502 (numeric Or Value Error String) / FRM - 40735?

Mar 14, 2013

I have made a report with sql injection. there is no problem with the report. but when i run from the form with following string it gives error.

:global.where_clause := 'where request_type = 5 and request_status in (3, 4, 5) and list_approval_date is null';

And no error is there if i passed the string as

:global.where_clause := 'where request_status in (3, 4, 5) and list_approval_date is null';

REQUEST_TYPE is a database column and it is of number type.

View 9 Replies View Related

PL/SQL :: Missing Expression Error during EXECUTE IMMEDIATE?

Jun 6, 2012

CREATE TABLE  "T_LIDER"
   (     "LIDER" NUMBER(5,0),
     "TRR" NUMBER(8,0),
                "SUG_I"  NUMBER(2,0),
      "LIDER_TAY" NUMBER(2),
   
[code]...

i've tried to simplify the problem and i saw that the function returns this error what seems to be the problem ?

View 11 Replies View Related

SQL & PL/SQL :: Trigger - Non-numeric Character Was Found Where Numeric Was Expected

Oct 10, 2011

I have an sqlldr process running loading data into my database. I have created a trigger to run before inserts on each row to start gathering summary data from the basic underlying data. The trigger compiles ok and the procedures the trigger is calling compile ok, but when the sqlldr process runs I get errors in the log files.

Here is the sqlldr control file:

LOAD data
APPEND INTO TABLE cdr.day_tables
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
RecordCode
,CdrStart DATE 'YYYY DDD SSSSS'
[code].......,

Next is my trigger

create or replace TRIGGER BNUMBER_SUMMARY_INS
BEFORE INSERT ON DAY_TABLES
FOR EACH ROW
DECLARE
[code]......

Next are the procedures that are called by the trigger:

create or replace PROCEDURE BNUMBER_SUMMARY
( BNUMBER IN VARCHAR2
, CALLDATE IN DATE
, CALLDURATION IN NUMBER
) AS
record_found NUMBER;
BEGIN
[code].......

The error messages I am getting are:

Record 1: Rejected - Error on table CDR.DAY_TABLES, column CDREND.
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "CDR.BNUMBER_SUMMARY_INS", line 6
ORA-04088: error during execution of trigger 'CDR.BNUMBER_SUMMARY_INS'

I need to find out what field it is complaining about, especially since I am not even using the cdrend field from the input record?

View 14 Replies View Related

SQL & PL/SQL :: ORA-01858 / Non-numeric Character Was Found Where Numeric Was Expected

Apr 22, 2013

select ORDER_NUMBER from OE_ORDER_HEADERS_ALL
WHERE ordered_date=to_char(to_date(substr(ORDERED_DATE,1,10),'YYYY/MM/DD'),'DD-MON-YYYY');

Error:-ORA-01858: a non-numeric character was found where a numeric was expected

View 13 Replies View Related

Non-numeric Character Found Where Numeric Was Expected?

Jun 1, 2007

I get the error message mentioned in the subject with this SELECT-statement

....where (t.cfonte=14 and t.data_ultima_modifica between sysdate -4000/(24*60*60) and sysdate ) or (t.data_ultima_modifica > to_date('%TIMESTAMP%','ddmmyyhh24miss'))]]>

If I substitute %TIMESTAMP% with 310507143709 then it works

View 6 Replies View Related

SQL & PL/SQL :: Non-numeric Found Where Numeric Was Expected

Dec 7, 2010

We are getting an error : a non numeric was found where a numeric was expected sometimes when this statement is executed:

INSERT INTO training select * from temp_training where class_id='xyz';
all columns and their datatypes are the same in both the tables

however if i replace the * with the column names as shown below it seems to work fine without giving an error

insert into training (a,b,c) select a,b,c from temp_training where class_id='xyz'

wanted to understand the subtle difference between the 2 statements

View 5 Replies View Related

Spatial :: Convert Multi-line To Simple Line

Feb 13, 2013

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0

We are processing spatial data from another source to display in our GIS environment. The data is a set of multi lines. The gtype is 2006. A typical geometry looks like:

SDO_GTYPE     2006
SDO_SRID      31370
SDO_POINT.X   NULL     
SDO_POINT.Y   NULL
SDO_POINT.Z   NULL
SDO_ELEM_INFO (1,2,1, 7,2,1)
SDO_ORDINATES (105094.84, 195084.96,
105094.54, 195080.22,
105094.84, 195084.96,
105094.84, 195084.96,
105094.68, 195082.47 )

Now, this is not an actual multiline... it's just encoded as a multi line, but if you look at the coordinates you'll see that the end point of the first line is the same as the beginning of the second line (105094.84, 195084.96).

A better way to encode this geometry would be:

SDO_GTYPE     2002 <---
SDO_SRID      31370
SDO_POINT.X   NULL     
SDO_POINT.Y   NULL
SDO_POINT.Z   NULL
SDO_ELEM_INFO (1,2,1)
SDO_ORDINATES (105094.84, 195084.96,
105094.54, 195080.22,
105094.84, 195084.96, <---
105094.68, 195082.47 )

Is there a standard function in Oracle that does this conversion for me? Or do I have to write my own :)

View 4 Replies View Related

NLS-NUMERIC CHARACTERS Changes?

Jun 20, 2011

I have set NLS_NUMERIC_CHARACTERS to ',.' but somehow during my java application life-cycle it got changed to '.,'! Is there any way to find what causes this? I can't find what or who change it. I have ordinary Java app which connects to Oracle 11.2.0.1.0 DB and as far as I know NLS_NUMERIC_CHARACTERS is not set explicitly or any other NLS_XXX setting. Is there any way to look in some logs for this?

View 1 Replies View Related

ORA-06502 - PL/SQL - Numeric Or Value Error

Jul 29, 2011

I have a requirement in which I have to append LOBs and I have to insert the LOB into a table column. I am facing problem when the data exceeds certain limit. (please note that my program logic is given below, not the exact program)

DECLARE

final_html CLOB;
int_html CLOB;
v_str VARCHAR2(32767);
i number:=0;
BEGIN
dbms_lob.createtemporary(lob_loc => int_html, CACHE => TRUE, dur => dbms_lob.CALL);
dbms_lob.createtemporary(lob_loc => final_html, CACHE => TRUE, dur => dbms_lob.CALL);
DBMS_LOB.OPEN(int_html, DBMS_LOB.LOB_READWRITE);
DBMS_LOB.OPEN(final_html, DBMS_LOB.LOB_READWRITE);
dbms_output.ENABLE(1000000);
[code]....

when the looping is done for lesser value say for e.g FOR i IN 1..10 loop, the program works fine, but when the looping is done for more values I am getting "-6502-ORA-06502: PL/SQL: numeric or value error" error message.

View 1 Replies View Related

SQL & PL/SQL :: ORA-06502 - Numeric Or Value Error?

Dec 18, 2012

create or replace package pckname is
Procedure VALIDATE_USER(p out credentials.pc%TYPE,
u in credentials.uc%TYPE,
c in credentials.sc%TYPE);
end pckname;

I m getting error at line 1 and line 8:

View 1 Replies View Related

SQL & PL/SQL :: Convert To Numeric / Date

Jan 30, 2013

I currently have a table with a VARCHAR column which is used to store notes. Currently the notes read something like 'Verified 01/01/2012'. I am trying to convert it to a date column so I can run reports using the date (select between dates etc).

I have tried with the substr function but since the records are all different doesn't really work. (plus that doesn't make it a date so not sure it would work for searching).

how to proceed?

View 3 Replies View Related

ORA-20507 Invalid Numeric Value N

Jun 13, 2011

I am using APEX 4.0.2.00.06 on a Linux server running Oracle 11g R2 and have the following problem:

I have a page with two forms for creation of new rows in either the sites table or the site-projects table. Site-projects is defined as follows with four numbers and two character fields:

Site_id number(10,0) PK1
Cruise_id number(10,0) PK2
Sequence_number number(10,0) PK3
Project_id number(10,0)
Assigned_flag varchar2(1) default �N�
Planned-site_flag varchar2(1) default �N�

The entry form uses select lists for many of the fields, but it is the assigned_flag that seems to be causing problems. It�s LOV definition is static2:UNASSIGNED;N,ASSIGNED;Y
When I tap the create button, I get :

ORA-20507: Invalid numeric value N for column ASSIGNED_FLAG Error Unable to process row of table SITES.

This suggests that something is going on at the sites table even though I use the create button associated with the site-projects table. Am I invoking the row processing for the sites table as well? Why does it run into invalid number when the field is varchar2? Actually, I get the exact same error if I use the create button on the sites portion of the page.

The site table has the following columns:

Site_id number(10,0) PK1
Parent_site_id number(10,0) nullable
Stratum_id number(10,0)
Area_id number(10,0)
Site_type_id number(10,0)
Name varchar2(50)

View 2 Replies View Related

Forms :: Numeric Input In 6i

Jul 25, 2005

I have an issue in oracle form 6i. I want to create an input box that accepts only number. As the user press any key other than allowed for numbers it should not accept.

View 4 Replies View Related

SQL & PL/SQL :: Convert Date To Numeric

Oct 8, 2012

I currently have a field which stores a date (date employer started with the company). I want to create a view which stores this as number of days employed (e.g. if star date 1st October view will say 8). How to convert a date to a number?

View 14 Replies View Related

PL/SQL :: Delete Non Numeric Values

Dec 20, 2012

I have a set of data within a table with a column called telephone_numbers. What I have noticed is that for some reason I have data in there that is not numerical values only i.e. LLSSUU. Is it possible to delete all non numerical values from this column?

The problem is I have over 1000 fields to go through and was wondering if there is a query that i can write.

View 12 Replies View Related

PL/SQL :: SQL Query Concat Numeric Or Value

Jan 3, 2013

I am getting numeric or value error when concatenating sql script..

Here is the sample, I have in the stored proc.. firstpart:_ works fine if I comment the secondpart:_. (I modified the query because I am posting in the public forums)

V_SAMPLEQUERY VARCHAR2(2000);
     
firstpart:_

V_SAMPLEQUERY := '      
SELECT
AB.ABCDID AS ABCDID,
CD.MEMBERID AS MEMBERID,

[Code]..

secondpart:_ ***ERROR PART***

V_SAMPLEQUERY := V_SAMPLEQUERY || ' ORDER BY AB.USER'; -- Here I am getting error saving ORA-06502 - numeric or value error

View 2 Replies View Related

Numeric Precision Specifier Is Out Of Range (1 To 38)

Mar 10, 2004

I am trying to run copy command to copy data from one database to other.I 'm getting this error while running copy command. "ORA-01727: numeric precision specifier is out of range (1 to 38)"

View 1 Replies View Related

SQL & PL/SQL :: Numeric Or Value Error In Stored Package

Oct 28, 2010

I am getting the below error in a Stored Package.

SQL> BEGIN
2 NCOTE.OTEGENERATOR;
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "GENEVA_ADMIN.NCOTE", line 3
ORA-06512: at line 2

I must be trying to do a select or put some other value into a variable that is too small. The Odd thing is that when I run the stored procedure again (as long as I don't exit from sqlplus)...

SQL> BEGIN
2 NCOTE.OTEGENERATOR;
3 END;
4 /
PL/SQL procedure successfully completed.

why the process is successful the second (or third, etc) call from the sqlplus but not the first. If I exit sqlplus and then start it again, once more I get the error again.

View 6 Replies View Related

SQL & PL/SQL :: Query Resulting In Numeric Overflow

Aug 23, 2013

I am writing a query and the query is resulting in Numeric Overflow.

SELECT

(power((Trlg_5Yr_Anl_Ror_Pt/100+1),5)-1)*100 AS Trlg_5Yr_Cum_Ror_Pt

FROM Cdp
where ....
and .....

In this above query,the power function is throwing error. Since the value in the column "Trlg_5Yr_Anl_Ror_Pt" is very big,applying power function is throwing Numeric Error. What can I do to handle this?

IN TABLE Cdp,column "Trlg_5Yr_Anl_Ror_Pt" is defined as NUMBER. Will Changin it to FLOAT resolve the issue.

View 19 Replies View Related

SQL & PL/SQL :: Last Numeric Character In Alphanumeric String

Feb 8, 2011

How can I find the last numeric value in the alphanumeric string?

Example:

LS14160220SPAD show me 0
MN23160224N show me 4
SP34524442 show me 2

View 9 Replies View Related

SQL & PL/SQL :: Replace Non Alpha Numeric Characters

Jul 1, 2004

I have a function that will replace the contents of the input parameter and replace any non-numeric characters.� I just want to know if there is a more efficient way to code this (oracle 8i or higher).

function strip_non_numeric(p_string in varchar2) return varchar2 is
Result varchar2(100) := '' ;
x_length number;
begin
SELECT LENGTH(p_string) INTO X_LENGTH FROM DUAL;
FOR i in 1..X_LENGTH LOOP

[Code]....

View 6 Replies View Related

SQL & PL/SQL :: How To Convert A String Into Numeric Other Than To_number

Dec 30, 2012

I have a column which has has all numeric values 3 digits to 7 digits in length but all in string format, I want to use seomthing other than to_number function.

Would like to use within the ETL tool, where to_number function isn't getting recognized.

select EMP_NO from employees;

View 2 Replies View Related

Forms :: Numeric Listbox Items?

Apr 12, 2010

I've got a colleague that is trying to populate a list box item with a group of numeric values that represent highway route numbers.

The database view defines the route as a "number".

How can we create a numeric list box? The built-in functions populate_group, and populate_list both expect to get varchar2 parameters.

View 1 Replies View Related

SQL & PL/SQL :: Getting ORA-01426 / Numeric Overflow Error?

Oct 31, 2010

SQL> ed
Wrote file afiedt.buf
1 begin
2 for i in 3159572060..3159572274 loop
3 insert into recmsisdn(msisdn)
4 values(i);
5 end loop;
6* end;
7 /
begin
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 2

View 4 Replies View Related

SQL & PL/SQL :: Convert The Field Value Into Numeric Form?

Apr 27, 2010

I want to convert the field value into numeric form using the formula:

∑[((radix)^ position * alphvalue) mod PR]
where alphvalue A= 10, B = 11, C = 12, D = 13,E = 14............. S = 28, ...........Z = 35, radix = 36, PR= 731
The value of position is marked from right to left starting with 0.

For Example:
ASIN
Let PR = 731 (large prime number)

∑[((36)^3 * 10)mod 731) + ((36)^2 * 28)mod 731)+
((36)^1 * 18)mod 731) + ((36)^0 * 22)mod 731) ]
= 182+ 469 + 648 + 22
= 1321

is it possible that it pick the field value and convert it into numeric form.

View 34 Replies View Related

PL/SQL :: Changing Format From Numeric To Char

Jul 26, 2012

I want to get the last month of my table “a” (Which is in number format) and after that I want to change it to a date format and insert into my table “b”.

I have already done the max month query:

SELECT MAX(MONTH_ID) FROM Table_a;

SELECT DISTINCT MONTH_ID ---This data is the one that I would like to change format "mon-yyyy"
FROM Table_a
WHERE MONTH_ID = (SELECT MAX(MONTH_ID) FROM Table_a);

I am getting trouble with the to_char function, I have been reviewing how it works and how to handle it.

View 3 Replies View Related

PL/SQL :: ORA-01722 / Numeric Or Value Error String

Jul 25, 2012

I have an error with this request :

SELECT SEQ_ENTITE.nextval, ENT_CODE_ENT, ENT_GPL_ID, TO_NUMBER('2012')+1,
pkg_etl_fdx.f_getseqexe('LPG_TYE', 'TYE_CODE_CON', ENT_CODE_TEN, 'TYE_SEQ', TO_NUMBER('2012')),
ENT_LIB_ENT, ENT_SEUIL, 'O', 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, ENT_CODE_UNI, ENT_CODE_MERE, ENT_IND_FIN, 0,
0, 0, ENT_NIVEAU, ENT_CODE_TEN, CHAR_TO_LABEL('EXERCICE', 'PUB:EXE'||TO_NUMBER('2012')+1||':'), OLS_GRP
FROM ENTITE
WHERE ENT_OLS_EXE = TO_NUMBER('2012');How to fix ORA-01722 PL/SQL: numeric or value error string ?

The description of the table Entite :

desc entite
Nom           NULL     Type             
------------- -------- -----------------
ENT_SEQ       NOT NULL NUMBER(38)       
ENT_CODE_ENT  NOT NULL VARCHAR2(6 CHAR) 
ENT_GPL_ID    NOT NULL NUMBER(10)       
ENT_OLS_EXE   NOT NULL NUMBER(38)       
ENT_TYE_SEQ            NUMBER(38)       

[code]....

View 3 Replies View Related

PL/SQL :: Strip Comma From A Numeric Field

Aug 20, 2012

providing sql statement to strip commas from a number/numeric field in the BI repository logical column? I do not want to use cast function to convert to character because we need to keep that field as numeric. I could only find string expressions to remove commas.

View 12 Replies View Related

PL/SQL :: Numeric Value In Words (for Cheque Printing)?

Oct 13, 2012

To convert numeric value in words (for Cheque printing), I created two functions in Forms and reports 6i.

1) FUNCTION Spell (val number) RETURN CHAR IS
sp varchar2(100);
BEGIN

[code]...

2) function SPELLED_AMOUNTFormula return Char is
cents number;
c_str varchar2(80);
val number;

[code]...

This convert value up to thousands. How to convert the value more than 1 lac.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved