SQPLUS - How to to see specs/body of package procedures ? [message #490832] |
Thu, 27 January 2011 10:26 |
|
Quetzalcoatl1
Messages: 33 Registered: January 2011
|
Member |
|
|
Hi,
1- I do not have access to TOAD for Oracle yet
2- I can connect to SQLPLUS: Release 9.2.0.1.0
3- We have many stored procedures in packages that are available from the TOAD for Oracle interface
4- I need to be able to see the specs/body of some packages containing some procedures.
5- I am connected to the appropriate DataBase1 (for example), but from here what to do from SQLPLUS command prompt ?
For example:
SchemaName1.PackageName1.ProcedureName1
Thanks in advance !
|
|
|
|
|
|
|
|
|
Re: SQPLUS - How to to see specs/body of package procedures ? [message #490873 is a reply to message #490864] |
Thu, 27 January 2011 18:15 |
cookiemonster
Messages: 13938 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
name will be the package name only. It does not have seperate entries for procedures in packages.
The view is all_source not all_sources. A lot of dictionary views come in three forms - user, all and dba.
So there is user_source and all_source and user_tables and all_tables and user_views and all_views. etc. etc.
|
|
|
|
|
Re: SQPLUS - How to to see specs/body of package procedures ? [message #491007 is a reply to message #490998] |
Fri, 28 January 2011 08:01 |
cookiemonster
Messages: 13938 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Quetzalcoatl1 wrote on Fri, 28 January 2011 13:38NAME
------------------------------
List of procedures or functions o packages i assume by looking at the end of the names
NAME
------------------------------
Lits of other functions or proc pr packages i guess by looking at the end of names
19 rows selected.
Not sure what you mean there.
All_source shows all code you have access to. So it'll show packages in other schemas if you have been granted the execute privilege. It'll only show the package spec though. If you want to see the body you need to be logged in as the owner.
Assuming you have queried the view correctly, and it's hard to tell, you either don't have execute permissions on the package or are looking in the wrong schema.
Also please note that oracle object names (packages, procedures, tables) and users and normally stored in the DB in upper case not mixed case.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: SQPLUS - How to to see specs/body of package procedures ? [message #492901 is a reply to message #492899] |
Thu, 03 February 2011 09:40 |
cookiemonster
Messages: 13938 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
TOAD can only obtain information on db objects by querying the data dictionary views (like all_source). Consequently anything you are allowed to see in TOAD you are allowed to query in sqlplus.
Check if your co-worker can select from the view dba_source. I suspect toad will use that in preference to all_source if the user you are logged into has select privs on it.
That view shows all package specs and bodies from all users, which is why you don't normally get access to it.
|
|
|
|
|
|
|
|
|
|
Re: SQPLUS - How to to see specs/body of package procedures ? [message #493417 is a reply to message #493407] |
Mon, 07 February 2011 17:18 |
cookiemonster
Messages: 13938 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Also dba_source does not list tables. As previously stated:
That view literally contains every line of code of every function/procedure and package in the db.
It contains nothing else.
Most object types in a db have their own dictionary views, as well as appearing in the *_objects views.
So you can get the list of tables from all_tables.
|
|
|
|
|
|
|
|