SQL & PL/SQL :: ORA-00923 - FROM Keyword Not Found Where Expected

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


ADVERTISEMENT

ORA-00923 - FROM Keyword Not Found Where Expected

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

ORA-00923 / FROM Keyword Not Found Where Expected

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

PL/SQL :: ORA-00923 - FROM Keyword Not Found Where Expected

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

PL/SQL :: ORA-00923 / FROM Keyword Not Found Where Expected

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

SQL & PL/SQL :: ORA-00923 - FROM Keyword Not Found Where

Jul 12, 2012

SELECT CREWACTIONFACTID
,CrewKey
,EventID

[Code].....

View 3 Replies View Related

Security :: How To Replace No Data Found Keyword In Oracle

May 30, 2013

i have a table that contains employee id, employee name , so if i gave the correct employee id in where clause of select statement it will show employee name, in case if i give the employee id that does not exist in the table it will show 'Employee name is not found'..

View 2 Replies View Related

SQL & PL/SQL :: Trigger - Non-numeric Character Was Found Where Numeric Was Expected

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

SQL & PL/SQL :: ORA-01858 / Non-numeric Character Was Found Where Numeric Was Expected

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

Non-numeric Character Found Where Numeric Was Expected?

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

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

SQL & PL/SQL :: ORA-00923 - Difference Between 9i And 10g

Aug 25, 2010

I encountered this problem when we recently migrated to 10g. The table nametab has two columns roll(number datatype) and name(varchar2(10) datatype).Following are the queries used and I have deliberately not given space between the "select" clause and the "from" clause.

In 9i
SELECT name,1FROM nametab where rownum < 2
NAME 1
---------- ----------
Bharath 1

Same query in 10g
SELECT name,1FROM nametab where rownum < 2
ORA-00923: FROM keyword not found where expected

Here, I have interchanged the select columns.

In 9i
SELECT 1,nameFROM nametab where rownum < 2
ORA-00923: FROM keyword not found where expected

In 10g
SELECT 1,nameFROM nametab where rownum < 2
ORA-00923: FROM keyword not found where expected

View 7 Replies View Related

Application Express :: Display Not-found.png For Image Type When Data Not Found?

May 22, 2013

I have an Image Type on a forum page. I want a default "not-found" image to display if the BLOB column value is null or if there is no data for that search value. The image is stored with the app: #APP_IMAGES#not-found.png

APEX 4.2 (with listener) on Oracle 11gR2

View 10 Replies View Related

Parallel Keyword In Expdp

Dec 17, 2012

i am trying to export table using datapump in oracle 10g, this expdp takes 5 hours time, so i want use use parallel keyword in expdp,
my question is how should i know number of parallels can i use...?

View 3 Replies View Related

PL/SQL :: Use A Subquery After Update Keyword?

Oct 18, 2012

Can we use a subquery after the update keyword??

For example

UPDATE ( Select col1 from test)
set col1='x';

In short can we replace the table name after an update and use a select statement instead??

View 3 Replies View Related

SQL & PL/SQL :: ORA-00969 / Missing ON Keyword

Jan 11, 2012

I issued the command

grant select(employee_id, job_id) on employees to scott;

But it returns error as

HR:orcl > grant select(employee_id, job_id) on employees to classuser;
select(employee_id, job_id) on employees to classuser
*
ERROR at line 1:
ORA-00969: missing ON keyword

View 5 Replies View Related

ORA-00905 Missing Keyword Error

Apr 9, 2008

Oracle and I am receiving ora-00905 missing keyword error.what could possibly be wrong with this SQL statement?

Select
T1.*,
T2.*
From
Table1 T1 INNER JOIN
Table2 T2 On
[code]....

I am trying to select the maximum dates from 2 different tables and I am using Oracle 10.

View 8 Replies View Related

SQL & PL/SQL :: Analytical Function With Distinct Keyword

Mar 8, 2012

can we use distinct keyword with the count and sum analytical functions?

View 5 Replies View Related

SQL & PL/SQL :: Search Documentation In Oracle With Keyword (Top-N)?

Feb 12, 2011

I have questions on Top N.Before post this thread, I have read these: One select in TOP Na thread very similar with my problem

Search the documentation in Oracle with key word "Top-N", but what I mostly got is about: "Top-N Frequency Categorical Binning"

SQL> select ename, sal
2 from emp
3 where sal in
4 (
5 select distinct sal

[code]...

Actually, I am not missing parenthesis, and I can not see any syntax error.what does the issue occur?

View 6 Replies View Related

PL/SQL :: Pivot In 11g Producing Keyword Error?

Dec 19, 2012

I just so happen to be the one trail blazing the pivot function for the section of the company I work in. (Needless to say, a Sesame Street style answer will not be offensive.) We are literally in the process of upgrading to 11g (11.2.0.1.0). Sadly, none of our more experienced programmers now anything about the pivot function. Not really surprising to me since we've been working in 10g. Anyway, I am using SQL Developer version 3.0.04 which I know is not the newest but I don't yet have permission to upgrade. I used [URL] to get me as far as I am on this function.

The script I am having problems with is:

SELECT *
FROM
(SELECT

[Code]....

The error I'm getting is:

ORA-01738: missing IN keyword
01738. 00000 - "missing IN keyword"
*Cause:   
*Action:
Error at Line: 16 Column: 2

The error indication bounces between line 15 and 16. If I put IN at the end of 15 I then have a missing right parenthesis error...

View 6 Replies View Related

PL/SQL :: Get Expected Results From Pivot?

Jan 31, 2013

with t1 as
(
select 'eff_date' param_name, 'mb256_type' param_type,'01-01-1970' param_value from dual
union all
select 'disc_date' param_name, 'mb256_type' param_type,'31-12-9999' param_value from dual
union all
select 'initial val' param_name, 'mb256_type' param_type,'30' param_value from dual)
select param_name,param_type,param_value from t1;

desired output:

need output in a row in three different columns

param_value
01-01-1970    31-12-9999 30

I tried below query

SELECT *   
FROM   (
with t1 as
(
select 'eff_date' param_name, 'mb256_type' param_type,'01-01-1970' param_value from dual
union all
select 'disc_date' param_name, 'mb256_type' param_type,'31-12-9999' param_value from dual

[code]...

and am getting output as

param_type      eff_date_param_value   disc_date_param_value   initial_ignoring_param_value
mbn256_type    <null>                                 <null>                           <null>

View 2 Replies View Related

Forms :: ORDER SIBLINGS Keyword No Accepted

Dec 5, 2010

This code run fine in Toad BUT giving me error in forms.

SELECT
LEVEL LOC_NAME,
LOCATION_NAME LOC_ID,
LOCATION_ID HLOC_ID,
HEAD_LOC_ID MUDRA
FROM AS_LOCATION_HDR
CONNECT BY PRIOR LOCATION_ID = HEAD_LOC_ID
START WITH HEAD_LOC_ID is null
ORDER SIBLINGS BY LOC_ID

The error is: "Encountered sysbol "SIBLINGS" when expecting one of the following:
by
The symbol "by inserted before the "SIBLINGS" to continue.

Is the SIBLINGS keyword not acepted in forms? What would be the alternative?

View 2 Replies View Related

Enterprise Search :: Configuring Tags For A Keyword?

Jun 21, 2013

We have an Oracle UCM and Oracle SES implementation.Currently there are too many tags returned for certain keywords by SES which breaks the UI,some of the tags being irrelevant too.Is there any configuration variable in SES by which these tags can be limited to say 20 most relevant tags.?

View 0 Replies View Related

PL/SQL :: WITH AS Results In ORA-00928 - Missing SELECT Keyword?

Aug 19, 2013

We are running on Oracle 10g. The following script results in ORA-00928: missing SELECT keyword. what causes this error? Both 'select' statements when run by themselves, complete successfully.

 [code]WITH A1 AS  WITH A1      AS (  SELECT MIN (VAPS_RPT_INTV_DMSN.INTV_DT),                   VAPS_RPT_INTV_DMSN.RPT_ID,                   VAPS_RPT_INTV_DMSN.RPT_INTV_ID              FROM APS.VAPS_RPT_INTV_DMSN, APS.VAPS_RPT_CL_INTV_DMSN             WHERE     APS.VAPS_RPT_INTV_DMSN.RPT_INTV_ID =                         

[code]./..

View 4 Replies View Related

SQL & PL/SQL :: Error In Query - ORA-00905 - Missing Keyword?

Jun 1, 2010

Check out following query

SELECT LOCALTIMESTAMP,SYSTIMESTAMP,EXTRACT(hour FROM LOCALTIMESTAMP) +2,
CASE WHEN EXTRACT(HOUR FROM LOCALTIMESTAMP) +2 Between 9 and 17 OR
(EXTRACT(HOUR FROM LOCALTIMESTAMP)+2 = '5' AND EXTRACT(MINUTE FROM LOCALTIMESTAMP)+2 > '60')
THEN TO_CHAR(FROM_TZ(LOCALTIMESTAMP,'GMT') AT TIME ZONE '+05:30','DD-MON-YYYY HH24:MI:SS')
CASE WHEN EXTRACT(HOUR FROM LOCALTIMESTAMP)+2 < '9'
THEN TO_CHAR(FROM_TZ(SYSTIMESTAMP,'GMT')+2 AT TIME ZONE '+05:30','DD-MON-YYYY HH24:MI:SS')
END
FROM dual;

ORA-00905: missing keyword

View 8 Replies View Related

SQL & PL/SQL :: Rebuild And Move Not Working As Expected

Nov 11, 2011

I ran below query and got the indexes to be rebuild:

SELECT (CASE
WHEN b.partitioned = 'NO'
THEN 'alter index '
|| b.owner
|| '.'
|| b.index_name
|| ' rebuild online; '
--|| b.initial_extent
[code]....

Why the fragmented size is not reducing.

View 14 Replies View Related

SQL & PL/SQL :: ORA-00932 / Inconsistent Datatypes / Expected - Got -

Mar 6, 2012

I have writen PL/SQL packages for data loging through pipe lined function for better peformance.The below packages has been compiled sucessfully but during the run time it shows an error
like "ORA-00932: inconsistent datatypes: expected - got -".

CREATE OR REPLACE PACKAGE pkg_mkt_hub_load
AS
PROCEDURE sp_final_load_mkt_hub;
FUNCTION fnc_pipe_tot_lvl_idx_mon_hub
(pi_input_cur IN SYS_REFCURSOR)
RETURN tot_lvl_idx_mon_tt
PIPELINED;

[code]...

SHOW ERRORS

Error:

ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at "GPAIHMKTDTA.PKG_MKT_HUB_LOAD", line 33
ORA-06512: at "GPAIHMKTDTA.PKG_MKT_HUB_LOAD", line 55
ORA-06512: at "GPAIHMKTDTA.PKG_MKT_HUB_LOAD", line 92
ORA-06512: at line 1

types scripts:

create or replace type tot_lvl_idx_mon_ot as object
(SSIA_INDEX_ID VARCHAR2(60),
start_date date,
CURRENCY VARCHAR2(10),
LEVEL1 NUMBER(31,11),
TYPE VARCHAR2(31) ,

[code].....

View 2 Replies View Related

SQL & PL/SQL :: Required Expected Output Using Decode

Apr 2, 2012

Oracle version : Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

create table plc_dw_dry_run_fic_rfsh (dry_run_fic_id number,ltr_code varchar2(10),
next_cpn_lvl_id1 number,next_intrvl_code varchar2(10), next_intrvl_value varchar2(120));

begin
insert into plc_dw_dry_run_fic_rfsh values (424740,'1','','LTML','48000');
insert into plc_dw_dry_run_fic_rfsh values (424736,'1','','LTML','32000');
insert into plc_dw_dry_run_fic_rfsh values (424738,'1','','LTML','128000');
insert into plc_dw_dry_run_fic_rfsh values (424783,'1','','LTML','96000');
insert into plc_dw_dry_run_fic_rfsh values (424789,'2','','LTML','96000');
insert into plc_dw_dry_run_fic_rfsh values (424750,'1',198,'LTML','10000');
insert into plc_dw_dry_run_fic_rfsh values (424760,'1',199,'LDFM','20000');
insert into plc_dw_dry_run_fic_rfsh values (424770,'1','','LTML','192000');
end;

commit;

Expected output
---------------
DRY_RUN_FIC_ID LTR_CODE NEW_LTR_CODE
424740 '1' '1b'
424736 '1' '1'
424738 '1' '1'
424783 '1' '1a'
424789 '2' '1a'
424750 '1' '1a'
424760 '1' '1a'
424770 '1' '1a'

Rules
---------
1) First next_cpn_lvl_id1 should be considered (next_intrvl_code can be anything), if next_cpn_lvl_id1 is 198 or 199 then new_ltr_code should have 1a.

2) If next_cpn_lvl_id1 is not 198 or 199, then next_intrvl_code should be considered

a) if next_intrvl_code is LTML and mod of next_intrvl_value and 96000 is zero then new_ltr_code should be 1a
b) if next_intrvl_code is LTML and mod of next_intrvl_value and 48000 is zero then new_ltr_code should be 1b

3) If 1 & 2 are not satisfied, ltr_code should be assigned to new_ltr_code.

View 14 Replies View Related

SQL & PL/SQL :: How To Retrieve CLOB Data Using DISTINCT Keyword For Other Columns

Aug 2, 2012

I have one query regarding how to retrieve the CLOB data.

The requirement is something that in the select statement there are around 20+ columns which i need to retrieve from around 5 tables after joining.

Since the result set after joining also will get duplicate values i need to use distinct keyword to filter the resultset. But in the 20+ columns there are 2 CLOB data columns which i need to retrieve.

Whenever i use DISTINCT i'm getting ORA-00932: inconsistent datatypes: expected - got CLOB error. I know that DISTINCT keyword cannot be used for CLOB datatypes.

View 15 Replies View Related

Forms :: 6i - Getting Ora-00928 Missing Select Keyword Ora-06512

May 3, 2013

I am importing data from excel to database table with column mapping.I have created package with a procedure. I am calling this procedure from oracle forms 6i. while executing the package procedure,I am getting following error ora-00928 missing select keyword ora-06512 at" abcd.pk_ excel_ to_ db" line 26 i.e befor forms_ddl.there is no problem in code and my form gets compiled but at run time i am getting this error.I also want to check, array is populating with anything or not.

PACKAGE PK_EXCEL_TO_DB IS
TYPE tKeyValue IS RECORD (
CROUTE VARCHAR2(255),
VROUTE VARCHAR2(1000),
CTRNDATE VARCHAR2(255),
VTRNDATE VARCHAR2(1000),
CTTIME VARCHAR2(255),
VTTIME VARCHAR2(1000),
CTID VARCHAR2(255),
VTID VARCHAR2(1000));
TYPE tDataList IS TABLE OF tKeyValue
index by binary_integer;
[code].....

View 4 Replies View Related







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