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


ADVERTISEMENT

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

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

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 :: 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

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

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

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

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

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

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

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

Reports & Discoverer :: How To Find Page Number And Total Number Of Pages

Jan 26, 2010

i am using oracle developer 6i report builder i required this type of query

example

if (:page number LIKE '1')
then
srw.set_text_color('darkred');
end if;

return (TRUE);
end;

but page number is not my table database item how can i use builtan page &<pagenumber> use for conditional format.

View 34 Replies View Related

SQL & PL/SQL :: Replacing 4 Digit Number In A Given String With The Same Number Incremented By 10000?

Jun 17, 2010

i want to replace 4 digit number in a given string with the same number incremented by 10000.

That mean in the given sting 1201 should be replace by 11201 (Icremented BY 10000).

Input String:

<query><matchAll>true</matchAll><row><columnId>1201</columnId><dataType>31</dataType><op>Like</op><val>North America - Houston</val></row><row><columnId>1212</columnId><dataType>31</dataType><op>!=</op><val>Agreement Date Mismatch</val></row><row><columnId>1212</columnId><dataType>31</dataType><op>!=</op><val>Facility Type Mismatch</val></row><row><columnId>1224</columnId><dataType>31</dataType><op>Like</op><val>y</val></row></query>

Required output :

<query><matchAll>true</matchAll><row><columnId>11201</columnId><dataType>31</dataType><op>Like</op><val>North America - Houston</val></row><row><columnId>11212</columnId><dataType>31</dataType><op>!=</op><val>Agreement Date Mismatch</val></row><row><columnId>11212</columnId><dataType>31</dataType><op>!=</op><val>Facility Type Mismatch</val></row><row><columnId>11224</columnId><dataType>31</dataType><op>Like</op><val>y</val></row></query>

View 7 Replies View Related

SQL & PL/SQL :: Extract Number And Previous Character From Where 5 Digit Number Starting

Oct 21, 2011

I have a text field and if the text field has 5 consecutive numbers then I have to extract the number and the previous character from where the 5digit number starting

For example i/p asdfasfsdS251432dasdasd o/p should be S251432

View 10 Replies View Related

Express Edition (XE) :: Why Maximum Number Of Voting Disk Is Even Number (32)

Oct 3, 2013

I gone through many forums and found that the number of voting disks should be always in odd number. Then why the maximum number of voting disk is 32?

View 1 Replies View Related

SQL & PL/SQL :: Count A List Of Number Value And Find Maximum Number?

May 21, 2010

how do I count a list of number value eg 1,1,1,1,3,3,6,4 and find the one with maximum number which is 1

View 5 Replies View Related

SQL & PL/SQL :: How To Generate Number (not Sequence Number) In Query

Mar 9, 2011

I have the following select query that works perfectly fine. Returns 25 rows based on the descending order of the price.But, I want add one more expression to this list of columns in this query (apart from customer_id).

the expression should look like Cust-01 for the first customer from the below query all the way to Cust-25 for the last customer.But how can I can generate 01 to 25 in oracle?

select customer_id from
(select customer_id from capitalPLAN
where member_status = 'MEMBER' AND customer_id NOT in ('156','201','1385','2125','3906','165')
order by price desc
)
where rownum <= 25

View 4 Replies View Related

PL/SQL :: Insert Number Into Column With Type  NUMBER(10,0)

Sep 28, 2012

my column type is NUMBER(10,0) ,it accept the input value from text field I using TO_NUMBER(?) to insert value into table, is the a way to handle if the input is 'aaaaaaaaaa' not digit?

View 6 Replies View Related

SQL & PL/SQL :: Number Generation MINUS Number Used

Feb 19, 2011

Here is script of tables

Quote:drop table p;
create table p (qty number(3), beg_no number(5));
insert into p values(5, 110);
insert into p values(8, 786);

drop table s;

create table s (used_no number(5));
insert into s values(111);
insert into s values(113);
insert into s values(791);

Table p: it has ticket quantity and ticket begining number. Thus according to first record ticket number will begin at 110 and will end at 110+5 (Beg_no +qty). According to second record ticket number will begin at 786 and will end at 786+8 (Beg_no +qty). This table can have many records.

Table s: it has ticket numbers which are sold. The ticket will always be any number from table and will lay in any record in this format between beg_no and beg_no+qty

Required: I want "p MINUS s" information. i.e.

Quote:
110
112
114
786
787
788
789
790
792
793

I am not expert in level by command.

Oracle version: 9
OS: Windows XP

View 3 Replies View Related

Reports & Discoverer :: How To Compare Current Page Number With Total Page Number

Feb 19, 2013

I have a report created in Reports6i. I have two fields at the bottom

1.Page Total
2.Voucher Total

I do not want to print the Page Total if the total number of pages = 1.

How can I achieve this?

View 1 Replies View Related

SQL & PL/SQL :: Invalid Row-id?

Jul 9, 2013

I CREATE A COLUMN AS 'STATUS VARCHAR2(40)' IN EMP1 TABLE IN SCOTT USER

EMP1 TABLE IS A DUPLICATE TABLE OF EMP
CREATE OR REPLACE PACKAGE SAL_STATUS IS
PROCEDURE SAL_STATUS;
END SAL_STATUS;

[code]...

PACKAGE CREATED SUCCESSFULLY...

WHEN I EXECUTE THIS PACKAGE]
BEGIN
SAL_STATUS.LOAN_STATUS;
END;

IT THROW ERROR AS
BEGIN
*
ERROR at line 1:
ORA-01410: invalid ROWID
ORA-06512: at "SCOTT.SAL_STATUS", line 21
ORA-06512: at line 2

HOW CAN I OVERCOME THIS ERROR..

View 7 Replies View Related

SQL & PL/SQL :: All Invalid Records

Jun 12, 2013

I have the following sample data:

Sample Data

WITH DATA AS
(
SELECT '100' GRP, '01-JAN-2012' EFFECTIVE_DATE, '30-JUN-2012' TERMINATION_DATE from DUAL
UNION ALL
SELECT '100' GRP, '01-JUL-2012' EFFECTIVE_DATE, '31-JUL-2012' FROM DUAL
union all
[code]......

Query Result

10001-JAN-201230-JUN-2012
10001-JUL-201231-JUL-2012
10031-JUL-201205-AUG-2012
10001-AUG-201215-AUG-2012
10017-AUG-201231-AUG-2012

Expected Output

10031-JUL-201205-AUG-2012
10017-AUG-201231-AUG-2012

The above mentioned output is produced by using the following business rules:

-- row no 1 = Valid records
-- row no 2 = effective date is 1 day after termination date of row no 1. Means valid records
-- row no 3 = effective date is equal to the row no 2 termination date -- Means invalid record
-- row no 4 = effective date is 1 day after termination date of row no 2 Means valid records
-- row no 5 = The gap between row no 4 termination date and row no 5 effective date is more than 1 day. Means record is invalid.

Query always compare effective date with previous valid record termination date by using the above mentioned condition and return result accordingly.

Summary is that there should be 1 day gap between termination date and next effective date as well as effective date
is always less than termination date in same rows. In addition, if record is invalid then next record check with previous valid record termination date.

View 15 Replies View Related

SQL & PL/SQL :: Invalid Table Name

Mar 8, 2011

create or replace TRIGGER "tddev.stage_column" BEFORE
INSERT ON SRC_STG_INDEX_MAP_TABLE
FOR EACH ROW
DECLARE

[Code]....

The trigger fails saying invalid table_name for all_tab_columns.

View 9 Replies View Related

SQL & PL/SQL :: Invalid Identifier?

Oct 30, 2011

I am trying to do an update with an anonymous PL/SQL block because it has been absolutely impossible to get by the infamous "Single-row subquery returns multiple rows" Whatever.So this code below work... Obviously, I am updating it to a constant.. OK, duh.. Of course it works, it's easy.... Now, I really need to update it to a value in another table that is in my cursor. I believe that I probably need to declare a secondary cursor.

I'm just getting back in the DBA saddle, so I'm a little rusty..

set serveroutput on;
DECLARE
numrows NUMBER := 0;
total NUMBER := 0;
CURSOR upd_record_cur IS SELECT m.rowid, m.swcm_cycle, t.newcycle, p.aprem_no from toad.fswcmas m,

[code]...

Not so good here

ORA-06550: line 20, column 53:
PL/SQL: ORA-00904: "TOAD"."TTP43425_LOAD"."NEWCYCLE": invalid identifier
ORA-06550: line 20, column 10:
PL/SQL: SQL Statement ignored

Doesn't work...
BEGIN
FOR upd_rec IN upd_record_cur LOOP
update toad.fswcmas sw set sw.swcm_cycle = toad.ttp43425_load.newcycle
WHERE rowid = upd_rec.rowid;
total := total + 1;

View 17 Replies View Related

SQL & PL/SQL :: Cause Of Invalid Package?

Mar 8, 2012

There are 4 packages got invalid 2 days back. when I analyze the database I came to know that there are 5 tables got truncated and 2 tables got altered during the issue period through the code. Those truncated tables have the indirect relationship with these 4 packages.but there is no any relation between these packages and altered table.

Also during that time I got the below error in my alert log.I am sure the cause this error is the invalid packages.

ORA-00600: internal error code, arguments: [kkxprpic8], [], [], [], [], [], [], []

I know if any alteration happens in a table, the refrence package will be getting as invalid. Apart from this, is there anyother cause to bring the package into invalid status? How to proceed further to find the root cause of thses invalid package?

View 6 Replies View Related







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