OraFAQ Forum: SQL & PL/SQL » How to Resolve error: ORA-00932

Home » SQL & PL/SQL » SQL & PL/SQL » How to Resolve error: ORA-00932 (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)

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

How to Resolve error: ORA-00932 [message #645706]

Sun, 13 December 2015 04:12

msol25
Messages: 396
Registered: June 2011

Senior Member

Dear,

How to Resolve error: ORA-00932 for below mentioned query:


select  *
from    all_views
where   text like '%TEMP%'

Report message to a moderator

Re: How to Resolve error: ORA-00932 [message #645707 is a reply to message #645706]

Sun, 13 December 2015 04:15

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

Senior Member

select *
from all_views
where text_vc like '%TEMP%'

Report message to a moderator

Re: How to Resolve error: ORA-00932 [message #645708 is a reply to message #645707]

Sun, 13 December 2015 04:18

msol25
Messages: 396
Registered: June 2011

Senior Member

Giving text_vc invalid identifier...

Report message to a moderator

Re: How to Resolve error: ORA-00932 [message #645709 is a reply to message #645706]

Sun, 13 December 2015 04:54

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

TEXT is of LONG data type, you can do nothing on LONG column but SELECT it.
You can write a PL/SQL function to convert the LONG to a VARCHAR2 or CLOB and query "WHERE f(text) LIKE ..." but you will be limited to 32K anyway.
If data is large then you have to do it outside PL/SQL, in another programming language.

Report message to a moderator

Re: How to Resolve error: ORA-00932 [message #645710 is a reply to message #645709]

Sun, 13 December 2015 06:52

msol25
Messages: 396
Registered: June 2011

Senior Member

Dear Michel,

I have used like this and able to resolve my Activity:


CREATE TABLE  test_l 
AS
    SELECT   owner
            ,view_name
            ,to_lob(text) as txt
    FROM     all_views


SELECT *
FROM   test_l
WHERE  txt LIKE '%TEMP%'

Report message to a moderator

Re: How to Resolve error: ORA-00932 [message #645711 is a reply to message #645710]

Sun, 13 December 2015 07:22

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

OK but you have to drop and recreate the table each time you want to query the view otherwise you can't be sure it is upi to date.

Report message to a moderator

Re: How to Resolve error: ORA-00932 [message #645712 is a reply to message #645711]

Sun, 13 December 2015 07:59

msol25
Messages: 396
Registered: June 2011

Senior Member

Michel,

This is metadata table.So,I hope these type of requirements required only one time at project level.Otherwise,I need to create seperate procedure as you suggested.

Thanks for your support Michel !!!

Report message to a moderator

Re: How to Resolve error: ORA-00932 [message #645721 is a reply to message #645712]

Mon, 14 December 2015 03:12

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

View creation scripts ought to be in source control. If they are you can just search the files there for what you want.

Report message to a moderator

Re: How to Resolve error: ORA-00932 [message #645728 is a reply to message #645721]

Mon, 14 December 2015 04:50

msol25
Messages: 396
Registered: June 2011

Senior Member

Dear Cookie,

According to you, If we have 5000 view scripts under source control.Then we have to search particular string and check one by one?

I hope this is time consuming activity.

Report message to a moderator

Re: How to Resolve error: ORA-00932 [message #645733 is a reply to message #645728]

Mon, 14 December 2015 05:10

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

There are search programs that can search multiple files for text.
Using one isn't much different to querying all_views.

Have you really got 5000 different views or have you got a smaller amount but they belong to multiple schemas?

Report message to a moderator

Re: How to Resolve error: ORA-00932 [message #645739 is a reply to message #645733]

Mon, 14 December 2015 05:40

msol25
Messages: 396
Registered: June 2011

Senior Member

Dear Cookie,

We have more than 5k views in same schema.We are part of Operational data store and we are core team for providing access to different teams.So,using views we are giving Access.In our case we need to find out Grant Privileges to various user's related to particular schema table's.So,We have done this Activity.

Report message to a moderator

Previous Topic:	+ 0 in the where clause
Next Topic:	Materialised view won't refresh

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jun 29 06:48:14 CDT 2026