Home » SQL & PL/SQL » SQL & PL/SQL » How to get apps view DDL statement from non-apps schema (Oracle R12)
How to get apps view DDL statement from non-apps schema [message #673579] Mon, 26 November 2018 02:44 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67650
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
cookiemonster
Messages: 13895
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 Go to previous messageGo to next message
John Watson
Messages: 8458
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 Go to previous messageGo to next message
cookiemonster
Messages: 13895
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13895
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67650
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13895
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Bill B
Messages: 1969
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 Go to previous messageGo to next message
cookiemonster
Messages: 13895
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67650
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous message
Solomon Yakobson
Messages: 3019
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:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Jan 24 00:19:49 CST 2021
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.