OraFAQ Forum: SQL & PL/SQL » How to get apps view DDL statement from non-apps schema

Home » SQL & PL/SQL » SQL & PL/SQL » How to get apps view DDL statement from non-apps schema (Oracle R12)

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

How to get apps view DDL statement from non-apps schema [message #673579]

Mon, 26 November 2018 02:44

ajayhareesh
Messages: 24
Registered: September 2016

Junior Member

I do not have apps schema credentials but need DDL of a view which has been created in APPS schema. The schema that I logged on currently has SYNONYM of the view.

I have tried get_ddl and ALL_VIEWS but no luck.

DBA_VIEWS and USER_VIEWS are not accessible.

Report message to a moderator

Re: How to get apps view DDL statement from non-apps schema [message #673580 is a reply to message #673579]

Mon, 26 November 2018 04:05

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

Senior Member
Account Moderator

If you have no access to the view you have no access to its DDL (unless you have access to Oracle catalog or dictionary).

Quote:

I have tried get_ddl and ALL_VIEWS but no luck.

Do not just tell, SHOW.
Before, read How to use [code] tags and make your code easier to read.

Report message to a moderator

Re: How to get apps view DDL statement from non-apps schema [message #673582 is a reply to message #673580]

Mon, 26 November 2018 04:11

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

Senior Member

If you need access ask someone who can grant you access to do so, instead of trying to hack your way round your lack of access.

Report message to a moderator

Re: How to get apps view DDL statement from non-apps schema [message #673583 is a reply to message #673579]

Mon, 26 November 2018 04:13

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

Senior Member

If you can use EXPLAIN PLAN (which does itself require some privileges) on a query against the view you may be able to reverse engineer it.

Report message to a moderator

Re: How to get apps view DDL statement from non-apps schema [message #673586 is a reply to message #673583]

Mon, 26 November 2018 04:51

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

Senior Member

Only if it's very simple and doesn't in turn call other views, otherwise you've got no chance.

The correct solution to access problems is pretty much always to either get the access granted or get someone with access to give you the information you need.
If you're trying to work round that it just begs question of why.

Report message to a moderator

Re: How to get apps view DDL statement from non-apps schema [message #673588 is a reply to message #673586]

Mon, 26 November 2018 05:02

ajayhareesh
Messages: 24
Registered: September 2016

Junior Member

Thanks for your response. I am not trying to access something which I should not. I have been given a schema where I have synonym of apps view. If I use 'Find DB Object' I am able to see the select statement behind the view script but not the actual DDL.

[Updated on: Mon, 26 November 2018 05:02]

Report message to a moderator

Re: How to get apps view DDL statement from non-apps schema [message #673590 is a reply to message #673588]

Mon, 26 November 2018 05:26

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

Senior Member

If you can see the select what do you need the actual DDL for?

Report message to a moderator

Re: How to get apps view DDL statement from non-apps schema [message #673592 is a reply to message #673588]

Mon, 26 November 2018 05:38

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

Senior Member
Account Moderator

Michel Cadot wrote on Mon, 26 November 2018 11:05

If you have no access to the view you have no access to its DDL (unless you have access to Oracle catalog or dictionary).

Quote:
I have tried get_ddl and ALL_VIEWS but no luck.
Do not just tell, SHOW.
Before, read How to use [code] tags and make your code easier to read.

Report message to a moderator

Re: How to get apps view DDL statement from non-apps schema [message #673595 is a reply to message #673590]

Mon, 26 November 2018 06:54

ajayhareesh
Messages: 24
Registered: September 2016

Junior Member

I need to copy actual view script and create view in a different database. But here I am able to see only select statement and not the actual "CREATE OR REPLACE VIEW...." script.

Report message to a moderator

Re: How to get apps view DDL statement from non-apps schema [message #673597 is a reply to message #673595]

Mon, 26 November 2018 07:19

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

Senior Member

So get someone who's got access to give you the script.

Report message to a moderator

Re: How to get apps view DDL statement from non-apps schema [message #673599 is a reply to message #673597]

Mon, 26 November 2018 07:24

ajayhareesh
Messages: 24
Registered: September 2016

Junior Member

Thank you everyone.

Report message to a moderator

Re: How to get apps view DDL statement from non-apps schema [message #673600 is a reply to message #673599]

Mon, 26 November 2018 07:30

Bill B
Messages: 1971
Registered: December 2004

Senior Member

if you have the select behind the view, you have the ddl already. For example if my select is

select owner,table_name
from all_tables;

then the DDL will simply be

create or replace my_view as
select owner,table_name
from all_tables;

A view is just a stored query.

[Updated on: Mon, 26 November 2018 07:30]

Report message to a moderator

Re: How to get apps view DDL statement from non-apps schema [message #673603 is a reply to message #673600]

Mon, 26 November 2018 07:41

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

Senior Member

In a lot cases yes, but just look at the doc page to see all the things you can add to create view statement past the actual select statement.

[Updated on: Mon, 26 November 2018 07:41]

Report message to a moderator

Re: How to get apps view DDL statement from non-apps schema [message #673604 is a reply to message #673595]

Mon, 26 November 2018 07:42

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

Senior Member
Account Moderator

Maybe you can do it but you just do it wrong but as you REFUSE to show us what you did I put you in my kill file and you will no more be bother with my help.

Report message to a moderator

Re: How to get apps view DDL statement from non-apps schema [message #673606 is a reply to message #673579]

Mon, 26 November 2018 08:39

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

Senior Member

And it is done for a reason. I can let you drive my car but it doesn't mean you can dig through my glove compartment or look under the hood. Anyway, you need DEBUG privilege to see the code.

SY.

Report message to a moderator

Previous Topic:	How to run the procedure that have array output?
Next Topic:	DBMS_SCHEDULER

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Sep 22 00:14:55 CDT 2026