OraFAQ Forum: SQL & PL/SQL » How to Search a string in the TEXT column of dba

Home » SQL & PL/SQL » SQL & PL/SQL » How to Search a string in the TEXT column of dba_views (oracle11g,windows2007)

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

How to Search a string in the TEXT column of dba_views [message #469485]

Wed, 04 August 2010 23:35

nehaverma
Messages: 80
Registered: January 2010
Location: JAIPUR

Member

Hi
I want to select the view names from text column in dba_views.

Regards//
Neha

Report message to a moderator

Re: How to Search a string in the TEXT column of dba_views [message #469487 is a reply to message #469485]

Wed, 04 August 2010 23:41

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I want to select the view names from text column in dba_views.
What view_names from TEXT column?
Why must any VIEW_NAME exist in TEXT column?
Exactly how will you identify SUBSTR as VIEW_NAME?

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: How to Search a string in the TEXT column of dba_views [message #469488 is a reply to message #469487]

Wed, 04 August 2010 23:48

nehaverma
Messages: 80
Registered: January 2010
Location: JAIPUR

Member

Hi
Thanks for replying..
I want to select source of all views in my schema so that I used dba_views data dictionary but I want to search all the names of views independently. Example I have 5 views so I want to know all the table and view name which are used in thee views so is it possible with the help of some query.
Kindly help me.

//Neha

Report message to a moderator

Re: How to Search a string in the TEXT column of dba_views [message #469489 is a reply to message #469488]

Thu, 05 August 2010 00:03

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

  1  select name,  REFERENCED_OWNER,  REFERENCED_name,	REFERENCED_type
  2* from user_dependencies where type = 'VIEW'
SQL> /

NAME			       REFERENCED_OWNER 	      REFERENCED_NAME						       REFERENCED_TYPE
------------------------------ ------------------------------ ---------------------------------------------------------------- ------------------
EMP_DETAILS_VIEW	       HR			      COUNTRIES 						       TABLE
EMP_DETAILS_VIEW	       HR			      DEPARTMENTS						       TABLE
EMP_DETAILS_VIEW	       HR			      EMPLOYEES 						       TABLE
EMP_DETAILS_VIEW	       HR			      JOBS							       TABLE
EMP_DETAILS_VIEW	       HR			      LOCATIONS 						       TABLE
EMP_DETAILS_VIEW	       HR			      REGIONS							       TABLE

Report message to a moderator

Re: How to Search a string in the TEXT column of dba_views [message #469492 is a reply to message #469489]

Thu, 05 August 2010 00:20

nehaverma
Messages: 80
Registered: January 2010
Location: JAIPUR

Member

Really thanks a lottt...
Best regards//
Neha verma

Report message to a moderator

Re: How to Search a string in the TEXT column of dba_views [message #592339 is a reply to message #469492]

Tue, 06 August 2013 10:59

sqlking
Messages: 1
Registered: August 2013
Location: United States

Junior Member

Hi - although not totally related, there is a way you can also query search across all source code in the DBA_VIEWS view. It involves using the DBMS_METADATA.GET_DDL function to convert the LONG datatype to something that can be used with a wildcard search predicate.

http://www.fromthefiefdom.com/2013/08/searching-oracle-view-source-w-wildcard.html

Thought I'd provide as it would be helpful to others like me who did quite a few searches to find this capability.

Report message to a moderator

Re: How to Search a string in the TEXT column of dba_views [message #592343 is a reply to message #592339]

Tue, 06 August 2013 11:13

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

Senior Member

Just FYI, in 12C oracle added TEXT_VC VARCHAR2(4000) column to USER/ALL/DBA_VIEWS, so you coulf query TEXT_VC for views where TEXT_LENGTH <= 4000.

SY.

Report message to a moderator

Re: How to Search a string in the TEXT column of dba_views [message #592349 is a reply to message #592339]

Tue, 06 August 2013 12:30

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

Senior Member

sqlking wrote on Tue, 06 August 2013 11:59

Thought I'd provide as it would be helpful to others like me who did quite a few searches to find this capability.

You have to be careful with this solution. DBMS_METADATA.GET_DDL returns not just view text but complete definition. Assume I have a view:

SQL> create or replace
  2    view v1(
  3            first_name,
  4            surname,
  5            department_id
  6           )
  7      as
  8        select  first_name,
  9                last_name,
 10                department_id
 11          from  employees
 12  /

View created.

If I check USER_VIEWS.TEXT for this view I see:

SQL> select  text
  2    from  user_views
  3    where view_name = 'V1'
  4  /

TEXT
--------------------------------------------------------------------------------
select  first_name,
              last_name,
              department_id
        from  employees


SQL>

But if I use DBMS_METADATA.GET_DDL I see:

SQL> select  dbms_metadata.get_ddl('VIEW','V1')
  2    from  dual
  3  /

DBMS_METADATA.GET_DDL('VIEW','V1')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "SCOTT"."V1" ("FIRST_NAME", "SURNAME", "DEPARTMEN
T_ID") AS
  select  first_name,
              last_name,
              department_id
        from  employees


SQL>

As you can see text and metadata are different. I I'd be looking for surname I'd find it in metadata but not in text. Now assume I am checking for views selecting from table departments. Checking both view text and metadata for DEPARTMENTS will find no match. However, what if view has a foreign key:

SQL> alter view v1
  2    add constraint v1_fk1
  3      foreign key(department_id)
  4      references departments
  5      disable
  6  /

View altered.

SQL>

Text didn't change:

SQL> select  text
  2    from  user_views
  3    where view_name = 'V1'
  4  /

TEXT
---------------------------------
select  first_name,
              last_name,
              department_id
        from  employees


SQL>

But look at metadata:

SQL> select  dbms_metadata.get_ddl('VIEW','V1')
  2    from  dual
  3  /

DBMS_METADATA.GET_DDL('VIEW','V1')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "SCOTT"."V1" ("FIRST_NAME", "SURNAME", "DEPARTMEN
T_ID",
         CONSTRAINT "V1_FK1" FOREIGN KEY ("DEPARTMENT_ID")
          REFERENCES "SCOTT"."DEPARTMENTS" ("DEPARTMENT_ID") DISABLE) AS
  select  first_name,
              last_name,
              department_id
        from  employees


SQL>

And now search will tell me view V1 is selecting from table departments while it doesn't.

SY.

Report message to a moderator

Previous Topic:	Store date time from .csv file
Next Topic:	SP2-0809: Warning: Package Body created with compilation errors.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Aug 03 10:42:02 CDT 2025