OraFAQ Forum: SQL & PL/SQL » SELECT query gives error

Home » SQL & PL/SQL » SQL & PL/SQL » SELECT query gives error (Oracle 11g R2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SELECT query gives error [message #607949]

Thu, 13 February 2014 18:15

rchenna
Messages: 25
Registered: February 2008

Junior Member

Hi All,

After importing the data, I get the following error whenever I ran a SQL query. This happens to only few tables and other tables are giving proper result.

SELECT * FROM FR_BONUS_TYPES WHERE rownum < 2

ORA-00936: missing expression

Please help me

Thanks in advance

Report message to a moderator

Re: SELECT query gives error [message #607950 is a reply to message #607949]

Thu, 13 February 2014 18:18

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SELECT OWNER, OBJECT_TYPE, STATUS FROM ALL_OBJECTS WHERE OBJECT_NAME = 'FR_BONUS_TYPES';

post results from SQL above

Report message to a moderator

Re: SELECT query gives error [message #607951 is a reply to message #607950]

Thu, 13 February 2014 18:41

rchenna
Messages: 25
Registered: February 2008

Junior Member

SELECT OWNER, OBJECT_TYPE, STATUS FROM ALL_OBJECTS WHERE OBJECT_NAME = 'FR_BONUS_TYPES';
DEMO_QA TABLE VALID

Report message to a moderator

Re: SELECT query gives error [message #607953 is a reply to message #607951]

Thu, 13 February 2014 19:28

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

select owner, trigger_name, trigger_type from all_triggers where table_name = 'FR_BONUS_TYPES';

post results from SQL above

Report message to a moderator

Re: SELECT query gives error [message #607958 is a reply to message #607953]

Thu, 13 February 2014 22:28

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Looking for a trigger on SELECT? OP already posted it is a table, not a view.

SY.

[Updated on: Thu, 13 February 2014 22:40]

Report message to a moderator

Re: SELECT query gives error [message #607963 is a reply to message #607949]

Thu, 13 February 2014 23:04

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Are you running a script file? If so, most likely there is some non-printable character. For example:

SQL> -- create defective script
SQL>
SQL> declare
  2      v_file utl_file.file_type;
  3  begin
  4      v_file := utl_file.fopen(
  5                               'TEMP',
  6                               'bad.sql',
  7                               'w'
  8                              );
  9      utl_file.put_line(
 10                        v_file,
 11                        'select ' || chr(255) || '* from dual;'
 12                       );
 13      utl_file.fclose(v_file );
 14  end;
 15  /

PL/SQL procedure successfully completed.

SQL>
SQL> -- run defective script
SQL>
SQL> set echo on
SQL> @c:\temp\bad.sql
SQL> select  * from dual;
select  * from dual
          *
ERROR at line 1:
ORA-00936: missing expression


SQL>

SY.

Report message to a moderator

Re: SELECT query gives error [message #608025 is a reply to message #607953]

Fri, 14 February 2014 06:32

rchenna
Messages: 25
Registered: February 2008

Junior Member

select owner, trigger_name, trigger_type from all_triggers where table_name = 'FR_BONUS_TYPES';

DEMO_QA FR_BONUS_TYPES_TRG AFTER EACH ROW

Report message to a moderator

Re: SELECT query gives error [message #608027 is a reply to message #607963]

Fri, 14 February 2014 06:35

rchenna
Messages: 25
Registered: February 2008

Junior Member

Hi,

I am not running any script file. I simply login to this DB user and run the SQL and get the expression error.

Report message to a moderator

Re: SELECT query gives error [message #608033 is a reply to message #608027]

Fri, 14 February 2014 07:46

John Watson
Messages: 8930
Registered: January 2010
Location: Global Village

Senior Member

Could there be a Fine Grained Audit policy on the table? Check DBA_AUDIT_POLICIES

Report message to a moderator

Re: SELECT query gives error [message #608070 is a reply to message #607949]

Sat, 15 February 2014 00:13

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

correct query is:-
SELECT * FROM FR_BONUS_TYPES WHERE rownum <=2

Report message to a moderator

Re: SELECT query gives error [message #608071 is a reply to message #608033]

Sat, 15 February 2014 00:26

rchenna
Messages: 25
Registered: February 2008

Junior Member

We don't have any Fine Grained Audit Policy on this table.

Report message to a moderator

Re: SELECT query gives error [message #608078 is a reply to message #608070]

Sat, 15 February 2014 01:22

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

jgjeetu wrote on Sat, 15 February 2014 07:13

correct query is:-
SELECT * FROM FR_BONUS_TYPES WHERE rownum <=2

Is it? Could you explain why?

Report message to a moderator

Re: SELECT query gives error [message #608080 is a reply to message #608078]

Sat, 15 February 2014 01:34

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

i think he is selecting from table , n i have just provided correct query
/forum/fa/11700/0/

Attachment: Untitled.jpg
(Size: 28.47KB, Downloaded 924 times)

Report message to a moderator

Re: SELECT query gives error [message #608083 is a reply to message #608080]

Sat, 15 February 2014 02:12

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What is not correct in OP's query?

Report message to a moderator

Previous Topic:	How to loop through a Date Range ?
Next Topic:	how to check the details of any function or procedure etc

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 06:00:21 CDT 2024