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
ADVERTISEMENT
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
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
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
Nov 11, 2010
I am having some difficulties with this trigger. It keeps giving me the error "ERROR at line 5: PL/SQL: ORA-00923: FROM keyword not found where expected" when I am not even using a SELECT before the line it says the error is on? Here is the trigger that I am attempting to create.
CREATE OR REPLACE TRIGGER ClassRestraint
BEFORE INSERT ON Enrolled
FOR EACH ROW
DECLARE
numCourses NUMBER :=0;
myException EXCEPTION;
BEGIN
[code]...
I am getting the error on line 5.
View 5 Replies
View Related
Jun 23, 2011
I have this script which should find tablespaces and their size, joined with free bytes. Trying to run this gives me the SQL Error: ORA-00923: FROM keyword not found where expected.
I have two questions:
1. Where should the FROM be?
2. Is there something wrong with the join.
==============================================
set linesize 120
col "TOTAL (KB)" format 99999999999999999
col "FREE (KB)" format 9999999999999999
col TSNAME format a35
col "% FREE" format a10;
SELECT a.tablespace_name TSNAME, sum(a.bytes/1024) "TOTAL (KB)",
Sum(b.bytes/1024) "FREE (KB)"
To_char(round((sum(a.bytes/1024)/sum(a.bytes/1024))*100),2), 'FM99990D999999')
|| ' % ' "% FREE"
FROM dba_data_files a, dba_free_space b
Where a.tablespace_name = b.tablespacename
Group by a. tablespace_name
[/i]
=============================================
I used the script from [URL]
It worked great but I'm not sure how to use the arithmetic functions to show me MB instead of bytes.
View 1 Replies
View Related
Jun 29, 2007
The following syntax gives me the error:
ORA-00923: FROM key word not found where expected
if this is so, why use 'from' in TRIM function? Or is my syntax incorrect?
SYNTAX: select trim leading ('0' from (substr(to_char(polref_nbr),9,10))) "TRIM example" from tbl_vg_adhoc;
View 2 Replies
View Related
Aug 29, 2012
I am trying to compile this block for updating a record. In the P_ADD_LOV_SQL column, I have to update the following select statment, but when ever I am compiling it it shows error in the Select statement as : ORA-00923: FROM keyword not found where expected. rearrange the select statement so that it doesn't show the error.The coding is :
DECLARE
P_ADD_KEY NUMBER;
P_ADD_CODE VARCHAR2(50);
P_ADD_DESCRIPTION VARCHAR2(75);
P_ADD_MODULE_KEY NUMBER;
P_ADD_PROMPT VARCHAR2(50);
P_ADD_REQUIRED VARCHAR2(1);
P_ADD_FORMAT VARCHAR2(1);
P_ADD_SIZE NUMBER;
[code]....
The column is updated properly. But I can't update by compiling the block.
View 11 Replies
View Related
May 17, 2013
I want to get the top 10% of salaries in employees table. But I got error:
SQL> select top 10 percent salary
2 from employees
3 order by salary desc;
ORA-00923: FROM keyword not found where expected How can I get the top 10% percent?
View 6 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
