PL/SQL :: ORA-01722 - Invalid Number Error

Jul 6, 2012

Am getting invalid number error for the query ..

select ROUND( ( SUBSTR(' m1058,1672|1090,1672|1090,1716|1058,1716 x e', 2, instr(' m1058,1672|1090,1672|1090,1716|1058,1716 x e',',',1,1)- 2)        
+ SUBSTR(' m1058,1672|1090,1672|1090,1716|1058,1716 x e', instr(' m1058,1672|1090,1672|1090,1716|1058,1716 x e','|',1,2)+1, instr(' m1058,1672|1090,1672|1090,1716|1058,1716 x e',',',1,3) - (instr(' m1058,1672|1090,1672|1090,1716|1058,1716 x e','|',1,2)+1))      
)/ 2) AS x from dual;

View 3 Replies


ADVERTISEMENT

ERROR - ORA-01722 - Invalid Number

Mar 9, 2007

INSERT INTO t_category m(
m.service_id,
m.customer_id)
SELECT
u.service_id,
(SELECT p.add_data
FROM t_add p
WHERE
p.service_id=u.service_id AND p.add_type='CUSTOMER_ID')
FROM t_iservice u

I got this error: ORA-01722: invalid number. The problem is m.customer_id has data type NUMBER, p.add_data is VARCHAR.

View 1 Replies View Related

SQL & PL/SQL :: Error - ORA-01722 - Invalid Number?

Jul 26, 2011

I have a problem when executing the statement below querying a specific ID# in view

select * from VW_MML_LTR_MSTR where LTR_ID = 26 order by CLNT_CDE ,LTR_GRP, ltr_purp_id, LTR_CDE

I checked the data type acquired by the view and the table involved and confirmed that it was of number type.Weird thing for me is that I changed the where statement as

where LTR_ID LIKE 26
where LTR_ID != 25

Is this on the database settings? What should be done here?

View 9 Replies View Related

ORA-01722 / Invalid Number

Mar 26, 2012

my table having doj 30-jan-2003

SQL> select to_char(date_of_joining,'yyyy') from t1;
select to_char(date_of_joining,'yyyy') from t1
*
ERROR at line 1:
ORA-01722: invalid number

View 1 Replies View Related

Client Tools :: Update Failed ORA-01722 - Invalid Number

May 18, 2011

Enviroment is Embarcadero RAD Studio XE. Work with DataSnap WebBroker Application. Its Server methods ancestor is TDSServerModule.

I've got SQLConnection (dbx), SQLDataSet, DataSetProvider on server side and SQLConnection, DSProviderConnection, ClientDataSet plus DataSource, DBGrid, DBNavigator on client side.
SQLConnection on server side uses Oracle driver - dbxora.dll. DB: Oracle 11g.
SQLConnection on client side uses Datasnap driver.
SQLDataSet has DbxCommandType set to Dbx.SQL, CommandText: "Select * from Table1".
All fields except indexed one have their pfInWhere set to false.
updateMode of DataSetProvaider is set to upWhereKeyOnly.

All is well up to point where ApplayUpdates is fired. It does nothing. HandleReconcileError shows ORA-01722: invalid number.

Oracle explains: ORA-01722:invalid number

Cause: The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.

Action: Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation. But all updated fields are of character strings type. There is no need for conversion. I suppose the automatically created SQL has some extra checking. But I cannot see those SQLs. I guess must be a way of controlling those SQLs thru params, but don't know which. May be comparison old value - new value of index column is depending on this conversion?

View 14 Replies View Related

SQL & PL/SQL :: Load CSV File Into External Table / ORA-01722 / Invalid Number

Apr 26, 2011

I'm trying to load a csv file into an external table and when I select the table 0 rows is the result.

The log file has the following errors:

KUP-04021: field formatting error for field DEPTNO
KUP-04023: field start is after end of record
KUP-04101: record 1 rejected in file /usr/tmpclie.csv
error processing column EMPNO in row 2 for datafile /usr/tmpclie.csv
ORA-01722: invalid number

This is the script for the table:

create table emp_ext (
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,

[code]....

And this is csv:

7369,SMITH,CLERK,7902,17-DEC-80,800,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10

[code]....

View 37 Replies View Related

SQL & PL/SQL :: Invalid Number Error

May 9, 2011

I'm getting the following error in my DB, what would be the reason.

select * from Table_Name where rownum<10
                          *
ERROR at line 1: ORA-01722: invalid number

and I'm getting this error for every query.

View 4 Replies View Related

SQL & PL/SQL :: Dynamic Binding - Invalid Number Error?

Jun 10, 2013

I have a pkg with a procedure that uses dbms_sql to process a varchar2_table. Each record in the table is a delimited string, such as "Priority^2", or "Destination^7". The goal is to split that string on the hat and update a record in the panelfield_users table. It works perfectly if I replace the first substr/instr on :pColumn with a hardcoded number, so I know that the binding is working to that point. It is only when I try to get the latter half of the string that it chokes. I keep getting a "invalid number" error. The displayorder field is an integer field and all values will be 3 digits or less. If I pull the sql string out into an editor, it runs just lovely.

The pertinent code is:
C := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(C,'update panelfield_users set displayorder = to_number(substr(:pColumn, instr(:pColumn, ''^'') + 1)) where userid = :pUID and fieldid = (select fieldid from panel_fields where upper(panelname) = upper(:pPanel) and upper(modulename) = upper(:pModule) and upper(field_displayname) = upper(substr(:pColumn, 1, instr(:pColumn, ''^'')-1)) )

[code]...

View 4 Replies View Related

Application Express :: Invalid Number Error

Oct 15, 2012

REVNUMBER is a VARCHAR2. I've set it to increment by letter in alphabet to note the revision of a document, i.e. IR*, A, B, C... . This works beautifully.

VERNUMBER is a VARCHAR2. I've set it to increment by number but starts from null to 'IR' to 1, 2, 3...

Until now, I've been incrementing VERNUMBER without problem if, instead of IR as the first incremented value I leave the field null. The user has asked that IR display. I've tried using IR as the default but I still need to increment from there so I don't see it making a difference between that or dumping it as the first condition of the following. The problem is I get an invalid number error either way.

*'IR' = 'Initial Release'

SELECT CASE
WHEN VERNUMBER IS NULL THEN 'IR'
WHEN VERNUMBER = 'IR' THEN '1'
ELSE REGEXP_REPLACE(VERNUMBER, '[0-9]+$', REGEXP_SUBSTR(UPPER(VERNUMBER), '[0-9]+$') + 1)
END NEW_VERSION
FROM   DOC_INFO
WHERE  DOC_INFO_ID = :P3_DOC_INFO_ID

View 4 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

SQL & PL/SQL :: Invalid Number

Dec 3, 2012

select to_char(to_date(mcih_date, 'dd-mm-yyyy')),
mcih_terms from I_MEMO_CONF_H;
TO_CHAR(TO_DATE(MCIH_DATE,'DD-MCIH_TERMS
118-OCT-1245
206-NOV-1222

[Code]...

why i am getting this error?

View 3 Replies View Related

SQL & PL/SQL :: Invalid Number While Concatenating?

May 3, 2011

I'm having some troubles while concatenating fields.

If I make a simple join it works right:

select t1.nombre_archivo, t2.periodo
from proc_lotes t1, proc_procesos t2
where t1.lote_id = t2.lote_id;

NOMBRE_ARCHIVO PERIODO
------------------------------ ------------------------------
OSDE 201101
OSDE 201102
IOMA 201101
IOMA 201102
PAMI 201101
PAMI 201102

But... when I try to concatenate both fields it doesn't work. Nombre_archivo and periodo data type is varchar(30)

select t1.nombre_archivo + '_' + t2.periodo
from proc_lotes t1, proc_procesos t2
where t1.lote_id = t2.lote_id;

Error starting at line 1 in command:
select t1.nombre_archivo + '_' + t2.periodo
from proc_lotes t1, proc_procesos t2
where t1.lote_id = t2.lote_id
Error report:
SQL Error: ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause:
*Action:

PS. I'm using SQLDeveloper 1.5.3

View 4 Replies View Related

PL/SQL :: ORA-01465 - Invalid Hex Number

Nov 26, 2012

I have simple table and one column type raw I have simple insert query but not working

INSERT INTO xDESCRIPTION (errorcode,XDESCRIPTION) VALUES ( '0x0002','Test'); and I see this error ORA-01465: invalid hex number

View 17 Replies View Related

PL/SQL :: Invalid Number With VARCHAR2

Apr 3, 2013

I keep getting this error when I run my update statement. Here is what the coding looks like. I'm running Oracle 11g.

CREATE TABLE A (
SUPP_CD_EIM VARCHAR2 (20),
SUPP_CD VARCHAR2(20),
DSN_FAC_CD VARCHAR2(5));
[code]......

I want the output to look like

A1_1234
A2_2345
A3_3456

View 2 Replies View Related

SQL & PL/SQL :: Exception Handler For ORA-1722 Invalid Number

Oct 8, 2012

I have to ask a pretty basic question (my pl/sql skills need serious work). I need to validate that a string can be converted to a number, and if it can't be, substitute something else. This works:orcl> create or replace function jw_to_number(str varchar2) return number is

2 n number;
3 begin
4 n := to_number(str);
5 return n;
6 exception when others then return 0;
7 end;
8 /

Function created.

orcl> select jw_to_number('a') from dual;
JW_TO_NUMBER('A')
-----------------
0
orcl>

but of course I don't want to use WHEN OTHERS. So I tried this:orcl> create or replace function jw_to_number(str varchar2) return number is

2 n number;
3 begin
4 n := to_number(str);
5 return n;
6 exception when INVALID_NUMBER then return 0;
7 end;
8 /

Function created.

orcl> select jw_to_number('a') from dual;
select jw_to_number('a') from dual
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "SCOTT.JW_TO_NUMBER", line 4
orcl>I

thought this would replace the standard error handler with my own, but it doesn't. I tried redeclaring the pre-defined exception, same result.

View 4 Replies View Related

Client Tools :: Substr Invalid Number Of Parameters

Dec 27, 2012

I am using the following substr and it works fine on Toad but when i am trying to use within an ETL tool, there getting the error:

substr(PBBDT,length(PBBDT)-1)

Calling <substr> with <2> parameters, but <3> are expected.

View 2 Replies View Related

Count Number Of Combinations Of Country / State Invalid For Driving Licenses

May 21, 2012

I'm trying to do a count on the number of combinations of country/state codes that are invalid (in Australia) for driving licenses.

select COUNTRY_CODE, STATE_CODE, count(*) from
(select COUNTRY_CODE, STATE_CODE from CUSTOMER_TABLE
where DRIVING_LICENCE is not null
and not (COUNTRY_CODE in ('AUST') AND STATE_CODE in ('VIC', 'NSW', 'SA', 'QLD', 'NT', 'TAS', 'WA', 'ACT')))
group by COUNTRY_CODE, STATE_CODE

The output is okay...for example I get these results:

INTINT
NZSI
NZINT
AUSTINT
NZNSW
NZ <null>

However, what I am missing is the combination of "AUST" & <null> for country/state respectively. Am I writing the code correctly?

View 1 Replies View Related

SQL & PL/SQL :: Error In Trigger - Invalid

Mar 30, 2012

How to overcome this error

SQL> create or replace trigger trig_insert

2 before insert on insuredpersons for each row
3 begin
4 insert into sms_icare(insuredid)values(:new.id);
5 end;

when i am trying to insert it is throwing error is

ORA-04098:trigger "marbke.trig.sms' is invalid and failed re-validation

View 2 Replies View Related

PL/SQL :: Getting Invalid Identifier Error

Jul 24, 2012

select to_char(123.5,'fm$99999.00') from dual

when I execute this I get an output of $123.50...I want to use other speacial characters like @,* instead of $...But I am getting invalid identifier error if I use any special character other than $.

View 5 Replies View Related

Error ORA 920 / Invalid Relational Operator

Jul 30, 2010

The following runs no problem

SELECT
ACCO.SEQUENCE,
ACCO.DESCRIPTION,
MAX (ACCO.AUDIT_DTE)
FROM
PAS.AUDIT_CLINICAL_CARE_OPTIONS ACCO
WHERE
ACCO.AUDIT_DTE < :AuditDate AND

[code]....

However, when I try to add extra conditions to the status' in the sub select i get the error. This is one way I tried:

SELECT
ACCO.SEQUENCE,
ACCO.DESCRIPTION,
MAX (ACCO.AUDIT_DTE)
FROM
PAS.AUDIT_CLINICAL_CARE_OPTIONS ACCO
WHERE

[code]....

I've tried repeating the sub select for each of the extra status parts but everytime i hit the same problem.

View 1 Replies View Related

SQL & PL/SQL :: Package Error - Invalid Identifier?

Mar 4, 2012

way give error in package below

SQL> CREATE OR REPLACE PACKAGE DATA_BKG AS
2
3 TYPE OBJ_DEPT IS RECORD
4 (
5 DEPT_ID NUMBER(10),

[code]...

View 7 Replies View Related

SQL & PL/SQL :: Error / ORA-00911 / Invalid Character

Nov 17, 2012

I am receiving the SQL Error when trying to do a simple insert statement..

Below is the supposed errant SQL..WHERE?

SQL
insert into (CUSTOM_TBL)
Select
'123456' --CHARTFIELD2
,'PR' --EXAMPLE COMMENT
,'123456_01' --EXAMPLE COMMENT -

SEE ERROR STATEMENT BELOW:

Error at Command Line:5 Column:10
Error report:
SQL Error: ORA-00911: invalid character
00911. 00000 - "invalid character"

[code]....

View 4 Replies View Related

SQL & PL/SQL :: Invalid Cursor Error In Procedure

Feb 2, 2011

I have this stored procedure as part of package.

PROCEDURE XCOM_X060_UPDATEOHBFAILURE( in_CALL_ID IN NUMBER,
in_SPLY_REORD_NO IN CHAR,
in_OHB_QTY_CARTONS IN NUMBER,
in_OHB_QTY_UNITS IN NUMBER,
in_SPLY_TOT_OHB_QTY IN NUMBER,
in_OHB_INPUT_CTNS_MIN IN NUMBER,
in_OHB_INPUT_CTNS_MAX IN NUMBER,
in_UNITS_PER_CARTON IN NUMBER,
in_OHB_INPUT_UNIT_CONSTANT IN NUMBER,
in_TOTAL_CARTONS IN NUMBER,
out_ALLOW_OHB_INPUT_FLAG OUT CHAR,
out_ERR_CODE OUT NUMBER,
out_ERR_MESSAGE OUT VARCHAR2)

When the stored procedure is executed it is throwing following exception OTHERS EXCEPTION in XCOM_X060_UPDATEOHBFAILURE - SQL -1001 SQLERRM: ORA-01001: invalid cursor

Here is the execution script
DECLARE
IN_CALL_ID NUMBER;
IN_SPLY_REORD_NO VARCHAR2(32767);
IN_OHB_QTY_CARTONS NUMBER;
IN_OHB_QTY_UNITS NUMBER;
IN_SPLY_TOT_OHB_QTY NUMBER;
IN_OHB_INPUT_CTNS_MIN NUMBER;
[code]...

There is no cursor used in the procedure. It just inserts records in the table for given input values.

View 10 Replies View Related

SQL & PL/SQL :: Invalid Identifier Error / ORA-00904

Jan 13, 2011

The following query gives me "Invalid Identifier error: ORA-00904.

SELECT TMADMIN.pkg_twatch_invdb.Max_Trk_Date(tmadmin.sites.study_id,tmadmin.sites.site_id, 1000280)) "Qualified Date"
FROM
tmadmin.sites
"tmadmin' is the Schema Name
"pkg_twatch_invdb" is the package name
"max_trk_date" is a function inside the package (returns date)

I am using the same format for other function, it doesn't give any errors.

View 4 Replies View Related

Forms :: Rep-300 OCI Invalid Handle Error?

May 8, 2013

i have some form and reports, whenever i call report from forms i got rep-300 OCI invalid_handle_error.

View 8 Replies View Related

SQL & PL/SQL :: Getting ORA-00911 / Invalid Character Error

Jul 16, 2010

i was written query for nth highest salary from emp table...i get error

1 select distinct (a.sal) from emp a where
2* &N = (select count (distinct(b.sal) from emp b where a.sal <= b.sal);
3 /
Enter value for n: 2
old 2: &N = (select count (distinct(b.sal) from emp b where a.sal <= b.sal);
new 2: 2 = (select count (distinct(b.sal) from emp b where a.sal <= b.sal);
2 = (select count (distinct(b.sal) from emp b where a.sal <= b.sal);
*
ERROR at line 2:ORA-00911: invalid character

View 21 Replies View Related

PL/SQL :: 00900 Invalid SQL Statement Error?

Jul 30, 2012

I'm teaching myself to write stored procedures, working in TOAD 10.5 with Oracle 10g. I keep getting error ORA-00900: invalid SQL Statement. Here's the code, cut down to bare minimum sample size. I can't see where I'm doing anything wrong.

If I cut out the cursor (taking it down to just "Begin" and "End"), it does run, but I can't see anything wrong with the cursor.

CREATE OR REPLACE PROCEDURE IN_PROCESS_CASES_BOS
IS
V_HELLO VARCHAR2(10);
CURSOR C_MAIN IS
SELECT 'HELLO' FROM DUAL;

[code]....

View 6 Replies View Related

Creating Table Error - Invalid Character

Mar 3, 2011

I am trying to create two tables and I am getting the "ORA-00911: invalid character" Error. I have been trying to work it out for last past 30 minutes to no success.

CREATE TABLE special_rate
(
rate_id VARCHAR(3),
rate VARCHAR(5),
rate_expiry_date NUMBER(12),
PRIMARY KEY( rate_id ),
FOREIGN KEY( contract_id ) REFERENCES contract(contract_id)
[code]....

View 5 Replies View Related

SQL & PL/SQL :: Error - PLS-00487 - Invalid Reference To Variable

Jul 8, 2013

I am using oracle 10G version. when i compiled the code I got the error "PLS-00487: Invalid reference to variable 'PROGRAM.MWPRG_PROGRAM_ID%TYPE'

correct the below PLSQL error code (marked Bold)?

PLSQL procedure:
===================
create or replace
PROCEDURE SPT_PROGRAM
IS

[code]....

View 2 Replies View Related

SQL & PL/SQL :: Invalid Identifier Error In Select Statement?

Mar 18, 2011

Where I run this update query, I get the error:

Error report:
SQL Error: ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"
*Cause:
*Action:

UPDATE XXX_CURR_EOM SET ID =
(select CAPITALPLAN.ID from capitalplan, XXX_CURR_EOM
where
XXX_CURR_EOM.ID = CAPITALPLAN_id)

don't know what it means.

View 10 Replies View Related







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