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.
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?
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;
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
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?
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]....
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.
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]...
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
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]....
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?
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
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
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
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.
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.
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?
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
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 $.
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.
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]...
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]....
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.
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.
i have some form and reports, whenever i call report from forms i got rep-300 OCI invalid_handle_error.
View 8 Replies View Relatedi 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
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]....
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]....
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]....
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.