0

I have this query as a part of powershell script and it runs ok on Sql server but fails on Oracle

select case when OBJECT_ID (N'INTERFACE_DEFINITION', N'U') is null then 0 else 1 end

Here is error: ORA-00923: FROM keyword not found where expected

Is there some change required to make this work?

Thanks.

DoomerDGR8
  • 4,840
  • 6
  • 43
  • 91

1 Answers1

2
select case when 'a' > 'b' then 0 else 1 end
from DUAL;

The DUAL table is present in every Oracle database. This special table contains just one column. The column is named DUMMY, and it has a datatype of VARCHAR2(1). The DUAL table contains only one row. That row has a value in DUMMY of ‘X’.

The purpose of DUAL is simple—to have something to run a SELECT statement against when you don’t wish to retrieve any data in particular but instead simply wish to run a SELECT statement to get some other task accomplished.

Verifying that an object exists:

OBJECT_ID (N'INTERFACE_DEFINITION', N'U')

In Oracle you may use this query:

select count(*) from all_objects where object_name = 'INTERFACE_DEFINITION';

all_objects doesn't necessarily shows you all the objects in the database. It only shows you the objects on which a current user has any priviledges.

Multisync
  • 8,657
  • 1
  • 16
  • 20
  • I'm getting Unknown command error and it doesn't say what is wrong exactly. I tried SYS.ALL_OBJECTS as well. I'm under user/schema/db called VTassure_dbo – DoomerDGR8 Nov 04 '14 at 16:03
  • @Hassan Gulzar What command have you tried? In order to check that an object exists you just need to run "select count(*) from all_objects where object_name = 'INTERFACE_DEFINITION';" – Multisync Nov 04 '14 at 16:06
  • I tried 'select count(*) from all_objects where object_name = 'INTERFACE_DEFINITION';' on my account and the system account. Same result – DoomerDGR8 Nov 04 '14 at 16:18
  • @Hassan Gulzar Maybe there are some special characters: http://stackoverflow.com/questions/11631901/powershell-sqlplus-error-sp2-0042-unknown-command-%E2%96%A0-rest-of-line-ignored – Multisync Nov 04 '14 at 16:23