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


ADVERTISEMENT

PL/SQL :: REGEXP_LIKE ORA-00920 Invalid Relational Operator

Apr 18, 2013

I am trying to use the REGEXP_LIKE and I am getting ORA-00920: invalid relational operator.

I have run very simplified code to try to sort it out but no success:

SELECT id, address
FROM test_lob_tab
WHERE REGEXP_LIKE(address,'^A');

select 1 from dual where regexp_like('123', '^[0-9]$');

same result from both.

I am using Oracle 11 and freetoad 11.

View 5 Replies View Related

PL/SQL :: Using Regexp_instr In A Where Clause - Invalid Relational Operator

Oct 23, 2013

Whey I try to run this query in TOAD I get an ORA-00920: invalid relational operator error. It's part of a 10g stored procedure. When I highlight it and run it it prompts me for the missing values and then the error pops up. The AND in line 4 is highlighted. 

select CRIME_CLASSIFICATION_ID, crime_type, nvl(count(CRIME_CLASSIFICATION_ID),0) as CRIMECNTFrom vaps.vw_offenses where  regexp_instr(valoc,to_char(location_id)) AND ( fromdate is null or offense_date between to_date(fromdate, 'mm/dd/yyyy')  AND to_date(todate,'mm/dd/yyyy'))group by crime_classification_id, crime_type

View 3 Replies View Related

PL/SQL :: Relational Operators Without Between Clause

Jun 19, 2013

I have below query which uses between clause. For some constraints I cannot use between clause so this need this to converted to use only relational operator.

select * from TABLE1
where "+ width +" between start_width " +  "and end_width and "+ height +" between start_height " +  "and end_height " +  "and id = "+ id

View 2 Replies View Related

SQL & PL/SQL :: Creation Of A Object Relational Database

Feb 25, 2012

i am working on a project that requires the creation of a object relational database. A number of different methods are required in order to insert different toy types into the appropriate toy related Object Table(s). Within this there will be the following fields:

Toy_name
Cost
Age_Range
rdy_allocation

and then some more specific information. The specific information for each toy is:

Toy_Type.
The toy_type can be electronic, game, or wooden.
For electronic I need to store Battery_Type and Number_of_Batteries.
For type Game, I need to store, Type, this could card_game, board_game or other_game.
For type wooden, I need to store Painted(Y/N).

The toys are requested by a child and created by elfs. This information also needs to be recorded but im more concerned with the creation of the toy class itself first. Now this was my first attempt at the solution but I cant seem to link the specific toy information into the same table. Will i have to create three separate tables then link them together?:

DROP TABLE TOY_OBJTAB;
DROP TYPE electronic_toy;
DROP TYPE wooden_toy;
DROP TYPE board_toy;
DROP TYPE toy_typ;
[code]....

View 9 Replies View Related

SQL & PL/SQL :: Insert Data Into Object Relational Table

Dec 26, 2010

I had my previous table model as below.

CREATE TABLE Rel_Module (
Id NUMBER (6) PRIMARY KEY,
Type CHAR (7) NOT NULL,

[Code]...

How I can easily load data into this new REL_MODULE table either using SQL or PL/SQL (e.g., Procedures, Functions, and Triggers). I don't need to write so many INSERT statement.

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

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

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

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

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

Forms :: Getting Ora-00911 Invalid Character Error

Feb 12, 2013

I am using this query in MERGE statement , result is also shows here

select TO_CHAR(emp_id) EMP_ID ,TO_DATE(at_date,'RRRR/MM/DD') AT_DATE,TO_CHAR(time_in) TIME_IN,TO_CHAR(time_out) TIME_OUT,TO_CHAR(status) STATUS from (
select emp_code emp_id,at_date,at_time Time_in, Null Time_out, status from (
select a.*,substr(data_row,24,5) Emp_code,substr(data_row,3,8) AT_Date, substr(data_row,11,4) At_Time,
substr(data_row,15,2) Shift, substr(data_row,17,2) STatus
from data_load a

[code].....

using this query in MERGE statement giving a error ora-00911 invalid character

MERGE INTO Attendance I
USING (
select TO_CHAR(emp_id) EMP_ID ,TO_DATE(at_date,'RRRR/MM/DD') AT_DATE,TO_CHAR(time_in) TIME_IN,TO_CHAR(time_out) TIME_OUT,TO_CHAR(status) STATUS from (
select emp_code emp_id,at_date,at_time Time_in, Null Time_out, status from (
select a.*,substr(data_row,24,5) Emp_code,substr(data_row,3,8) AT_Date, substr(data_row,11,4) At_Time,

[code].....

View 3 Replies View Related

PL/SQL :: Error / ORA-29280 / Invalid Directory Path

Apr 9, 2013

When i am running the below script i am getting error like Invalid directory path. how to check the path by using dbms_ouput.

error : ORA-29280: invalid directory path
ORA-06512: at line 34

DECLARE
l_out_file  UTL_FILE.file_type;
g_convert_crlf  BOOLEAN := TRUE;
P_DATA VARCHAR2(32767);
  l_buffer    VARCHAR2(32767);
  l_amount    BINARY_INTEGER := 32767;

[code]....

View 11 Replies View Related

PL/SQL :: Table Out From A Function / Invalid Datatype Error

May 9, 2013

I am getting an ORA-00902: invalid datatype error when I am trying call the below function from a select statement. Here I am trying to get a table out from a function.

create or replace package pkg10
as
type tabletype1 is table of table1%rowtype
index by binary_integer;
function func1 return tabletype1;
end pkg10;create or replace package body pkg10
as

[code]....

View 2 Replies View Related

XE :: Error ORA-01017 Invalid Username / Password

Feb 15, 2013

I just finish to install Oracle XE on Fedora 18 so when I try to Start SQL, I get this error.

[root@fedora ~]# sqlplus system

SQL*Plus: Release 11.2.0.2.0 Production on Fri Feb 15 02:18:14 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.

Enter password: ERROR: ORA-01017: invalid username/password; logon denied.

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

Oracle Sys Index Rebuild After Getting Invalid Error Ora-01410

May 26, 2012

Oracle 11.1 version

Oracle sys Index rebuild list
CTXSYS
EXFSYS
FLOWS_030000
FLOWS_FILES
MDSYS
OLAPSYS
ORDSYS
OWBSYS
SI_INFORMTN_SCHEMA
TSMSYS
WKSYS
MDSYS

After rebuild above index im getting error while inserting,update,delete table rows

Error like 'INVALID ROWID'

View 3 Replies View Related

SQL & PL/SQL :: Creating Table - Missing Or Invalid Option Error

Jun 10, 2011

I am getting error while trying to create a table

SQL>
SQL> CREATE OR REPLACE TABLE CEE_OSPCM_DETAILS
2 (
3 ORD_NBR VARCHAR2(10) NOT NULL,
4 ORD_APPNDX NUMBER(2) NOT NULL,
5 FRCOI_NBR NUMBER,
6 PRINT_NBR VARCHAR2(4),

[code]....

View 3 Replies View Related







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