Home » SQL & PL/SQL » Client Tools » SQPLUS - How to to see specs/body of package procedures ?
SQPLUS - How to to see specs/body of package procedures ? [message #490832] Thu, 27 January 2011 10:26 Go to next message
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 #490833 is a reply to message #490832] Thu, 27 January 2011 10:29 Go to previous messageGo to next message
cookiemonster
Messages: 13017
Registered: September 2008
Location: Rainy Manchester
Senior Member
query user_source. Or use sqldeveloper instead.
Re: SQPLUS - How to to see specs/body of package procedures ? [message #490847 is a reply to message #490833] Thu, 27 January 2011 12:38 Go to previous messageGo to next message
Quetzalcoatl1
Messages: 33
Registered: January 2011
Member
Excellent, thanks for the fast reply.

I get the following message after the following command:
SELECT line ||' '|| text PROCEDURE FROM user_source WHERE name = 'NameOfProcedure';

"no rows selected"
Re: SQPLUS - How to to see specs/body of package procedures ? [message #490848 is a reply to message #490847] Thu, 27 January 2011 12:43 Go to previous messageGo to next message
BlackSwan
Messages: 25858
Registered: January 2009
Location: SoCal
Senior Member
>FROM user_source
Does logged in USER actually own the PROCEDURE?
procedure names typically default to UPPER_CASE; not NameOfProcedure.

Oracle is too dumb to lie about results.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: SQPLUS - How to to see specs/body of package procedures ? [message #490850 is a reply to message #490847] Thu, 27 January 2011 12:45 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Check the name (and the case).
Are you sure you are the owner? Otherwise query all_sources (if you are allowed to see the source of the procedure).

Regards
Michel
Re: SQPLUS - How to to see specs/body of package procedures ? [message #490862 is a reply to message #490850] Thu, 27 January 2011 14:57 Go to previous messageGo to next message
Quetzalcoatl1
Messages: 33
Registered: January 2011
Member
1- First, i needed to connect to the appropriate environment because the package and store proc are in the production environment not the dev ...

2- Second i queried user_source
like this

SQL> select TEXT from user_source where name='PACKAGENAME.PROCEDURENAME';

But it gives me the following message:
no rows selected

3- I then type the following to query all_sources

SQL> select TEXT from all_sources where name='RDW_XTRCT_RECEPMAG_PKG.RDW_CALL_RE
CEPMAG_XTRCT_MSTR';

But i get the followign error message:
ERROR at line 1:
ORA-00942: table or view does not exist


I am not the owner

Does it means i simply do not have appropriate access privileges yet ?
Re: SQPLUS - How to to see specs/body of package procedures ? [message #490864 is a reply to message #490862] Thu, 27 January 2011 15:22 Go to previous messageGo to next message
BlackSwan
Messages: 25858
Registered: January 2009
Location: SoCal
Senior Member
select distinct name from all_source
where owner = 'HR'
order by 1
/

replace "HR" above with OWNER of desired code
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 Go to previous messageGo to next message
cookiemonster
Messages: 13017
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 #490880 is a reply to message #490873] Fri, 28 January 2011 00:12 Go to previous messageGo to next message
Littlefoot
Messages: 21367
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You mean, USER_ETC and ALL_ETC?
Re: SQPLUS - How to to see specs/body of package procedures ? [message #490998 is a reply to message #490880] Fri, 28 January 2011 07:38 Go to previous messageGo to next message
Quetzalcoatl1
Messages: 33
Registered: January 2011
Member
Hi to all,

HERE IS WHAT WORKED SO FAR:

SQL> select distinct name from all_source
2 where owner = 'OwnerUserName'
3 order by 1
4 /


NAME
------------------------------
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.

SQL>

But somehow, i do not see the names of the package or procedures i'm interested with.

Any other advice from here ?

Thanks !



Thanks !
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 Go to previous messageGo to next message
cookiemonster
Messages: 13017
Registered: September 2008
Location: Rainy Manchester
Senior Member
Quetzalcoatl1 wrote on Fri, 28 January 2011 13:38
NAME
------------------------------
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 #491012 is a reply to message #490998] Fri, 28 January 2011 08:10 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you do not see them then either they do not exist, either you are searching wrong names, either you have not access to them (either you are not in the correct database or maybe you are searching for Sybase procedures in your Oracle database or...).

Regards
Michel

[Updated on: Fri, 28 January 2011 08:11]

Report message to a moderator

Re: SQPLUS - How to to see specs/body of package procedures ? [message #491065 is a reply to message #491012] Fri, 28 January 2011 15:31 Go to previous messageGo to next message
Hutton
Messages: 7
Registered: January 2011
Location: Malay
Junior Member
If you can connect as 'OwnerUserName', try that and run the same query.
Similarly with TOAD (if you have TOAD).
Owner of the object will be able to view it (package- body/specs).
cheers.
Re: SQPLUS - How to to see specs/body of package procedures ? [message #492486 is a reply to message #491065] Mon, 31 January 2011 08:25 Go to previous messageGo to next message
Quetzalcoatl1
Messages: 33
Registered: January 2011
Member
Hi to all,

Unfortunately, i do not have password for schema (owner) neighter a co-worker of mine who is ABLE TO SEE THE SPECS AND BODY of the concerned package (but within TOAD of course).

My co-worker typed the same command:
SQL> select distinct name from all_source
2 where owner = 'OwnerUserName'
3 order by 1
4 /



BUT he gets exactly the same result as mine (19 rows) which is telling me that it might not be a privileges access issue related to my user?

Thanks for any additional advices !
Re: SQPLUS - How to to see specs/body of package procedures ? [message #492489 is a reply to message #492486] Mon, 31 January 2011 08:32 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
is telling me that it might not be a privileges access issue related to my user

And you are wrong.
This query canNOT give you this information as you may be able to see the package spec without being able to see the package body depending on your privileges.
Check the differences you have in roles and privileges between you two.

Regards
Michel
Re: SQPLUS - How to to see specs/body of package procedures ? [message #492617 is a reply to message #492489] Tue, 01 February 2011 08:05 Go to previous messageGo to next message
Quetzalcoatl1
Messages: 33
Registered: January 2011
Member
Ok i understand we need to compare our role and privileges,

But could it be that the security from within SQL*PLUS is more than when using TOAD ?

I mean, how can my co-worker be able to see the specs and body of the concerned package when using TOAD (connected as his user name not the owner) BUT is not able to even see the name of the package when using SQL*PLUS and the commands we typed earlier?

Is it that we are simply not looking in the right place ?

Thanks again for any fast replys!
Re: SQPLUS - How to to see specs/body of package procedures ? [message #492619 is a reply to message #492617] Tue, 01 February 2011 08:08 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But could it be that the security from within SQL*PLUS is more than when using TOAD ?

No.

Quote:
I mean, how can my co-worker be able to see the specs and body of the concerned package when using TOAD (connected as his user name not the owner) BUT is not able to even see the name of the package when using SQL*PLUS and the commands we typed earlier?

Try to execute:
set role all

Regards
Michel
Re: SQPLUS - How to to see specs/body of package procedures ? [message #492623 is a reply to message #492619] Tue, 01 February 2011 08:27 Go to previous messageGo to next message
Roachcoach
Messages: 1513
Registered: May 2010
Location: UK
Senior Member
Wouldn't surprise me if the TOAD gui is using a priv you dont have (but your co-worker does) and your SQL*Plus query isnt testing said priv.
Re: SQPLUS - How to to see specs/body of package procedures ? [message #492666 is a reply to message #492623] Tue, 01 February 2011 12:47 Go to previous messageGo to next message
Quetzalcoatl1
Messages: 33
Registered: January 2011
Member
Hi again,


SQL> set role all
2 /

Role set.

SQL>

After typing:
SQL> select distinct name from all_source
2 where owner = 'RSA'
3 order by 1
4 /

NAME
------------------------------
Package1
Package 2
etc ...

NAME
------------------------------
Package3
Package4
etc ...

19 rows selected.

SQL>




I get the same result as before.


Thanks !
Re: SQPLUS - How to to see specs/body of package procedures ? [message #492675 is a reply to message #492666] Tue, 01 February 2011 13:50 Go to previous messageGo to next message
Quetzalcoatl1
Messages: 33
Registered: January 2011
Member
Any other suggestions or explications anyone ?
Re: SQPLUS - How to to see specs/body of package procedures ? [message #492677 is a reply to message #492675] Tue, 01 February 2011 13:55 Go to previous messageGo to next message
BlackSwan
Messages: 25858
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: SQPLUS - How to to see specs/body of package procedures ? [message #492692 is a reply to message #492677] Tue, 01 February 2011 16:07 Go to previous messageGo to next message
cookiemonster
Messages: 13017
Registered: September 2008
Location: Rainy Manchester
Senior Member
Run
set role all;
select * from session_privs;
set role none;
select * from session_privs;

As your user and your collegues user. Compare the results.
Also check if either user can query dba_source.
Re: SQPLUS - How to to see specs/body of package procedures ? [message #492724 is a reply to message #492666] Wed, 02 February 2011 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quetzalcoatl1 wrote on Tue, 01 February 2011 19:47
Hi again,
SQL> set role all
2 /

Role set.

SQL>

After typing:
SQL> select distinct name from all_source
2 where owner = 'RSA'
3 order by 1
4 /

NAME
------------------------------
Package1
Package 2
etc ...

19 rows selected.


I get the same result as before.
Thanks !

So now you are able to see the package, this solves what you said:
Quote:
I mean, how can my co-worker be able to see the specs and body of the concerned package when using TOAD (connected as his user name not the owner) BUT is not able to even see the name of the package when using SQL*PLUS and the commands we typed earlier?


So what is the problem now?
Restart from the beginning I am lost with what you have or have not.
Anyway, assuling you are in the same database, it is just a matter of privileges. Full stop.

Regards
Michel


Re: SQPLUS - How to to see specs/body of package procedures ? [message #492897 is a reply to message #492724] Thu, 03 February 2011 07:45 Go to previous messageGo to next message
Quetzalcoatl1
Messages: 33
Registered: January 2011
Member
===============
Cookiemonster,
===============

- I will compare soon enough with co-worker
- After having typed what you mentionned earlier here is what i get:

SQL> set role all;

Role set.

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

10 rows selected.

SQL> set role none;

Role set.

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE

SQL>




=========
Michel,
=========
- As mentionned earlier, i am able to see some package names but not the one that i'm interested with.


Thanks to all !
Re: SQPLUS - How to to see specs/body of package procedures ? [message #492898 is a reply to message #492897] Thu, 03 February 2011 08:33 Go to previous messageGo to next message
Quetzalcoatl1
Messages: 33
Registered: January 2011
Member
My co-worker typed the same commands and this is what he gets: he has the following additionnal privilege that i don't have "ALTER SESSION"


SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

11 rows selected


REMINDER: He still isn't able to see the package name that we are interested with when he is using SQL*PLUS.

But he is able to see it when using TOAD.

I don't understand !!

Conclusion = There is additional restrictions when using SQL*PLUS vs when using TOAD or what ??
Re: SQPLUS - How to to see specs/body of package procedures ? [message #492899 is a reply to message #492898] Thu, 03 February 2011 08:46 Go to previous messageGo to next message
BlackSwan
Messages: 25858
Registered: January 2009
Location: SoCal
Senior Member
>Conclusion = There is additional restrictions when using SQL*PLUS vs when using TOAD or what ??
or what
SQL is SQL; regardless of which client invokes it.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
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 Go to previous messageGo to next message
cookiemonster
Messages: 13017
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 #492903 is a reply to message #492901] Thu, 03 February 2011 10:07 Go to previous messageGo to next message
Quetzalcoatl1
Messages: 33
Registered: January 2011
Member
cookiemonster,

1- I understand that if you are allowed to see packages in TOAD, you should at least be able to see the specs of that same package when in SQL*PLUS.

But, as mentionned earlier, my co-worker sees the package specs/boy when in TOAD BUT can NOT event see that same package name/specs when using SQL*PLUS ?

2- What if my co-worker IS able to query the dba_source, BUT what will that tell us ? Because he STILL is not able to see the package when in SQL*PLUS

Thanks to all again !
Re: SQPLUS - How to to see specs/body of package procedures ? [message #492904 is a reply to message #492903] Thu, 03 February 2011 10:26 Go to previous messageGo to next message
Quetzalcoatl1
Messages: 33
Registered: January 2011
Member
cookiemonster,

I did not bother asking my co-worker to query from dba_source because i tried it and i was able to see the pkg_name of the one i'm interested with. GOOD !

Now, i just need to see the specs of such package ? When i try with the following commang this is what i get:

SQL> desc PKG_NAME;
ERROR:
ORA-04043: object "schemaUser"."PKG_NAME" does not exist


Thanks !
Re: SQPLUS - How to to see specs/body of package procedures ? [message #492905 is a reply to message #492903] Thu, 03 February 2011 10:26 Go to previous messageGo to next message
cookiemonster
Messages: 13017
Registered: September 2008
Location: Rainy Manchester
Senior Member
Your co-worker will be able to see everything if he queries dba_source in sqlplus. Assuming he has select privs on it. Just try it.
Re: SQPLUS - How to to see specs/body of package procedures ? [message #492906 is a reply to message #492905] Thu, 03 February 2011 10:41 Go to previous messageGo to next message
cookiemonster
Messages: 13017
Registered: September 2008
Location: Rainy Manchester
Senior Member
The spec is in dba_source too. That view literally contains every line of code of every function/procedure and package in the db.

Desc won't work as that uses the normal privilege set to determine what you can see. The dba views bypass the normal priv set.
Re: SQPLUS - How to to see specs/body of package procedures ? [message #493089 is a reply to message #492906] Fri, 04 February 2011 13:24 Go to previous messageGo to next message
Quetzalcoatl1
Messages: 33
Registered: January 2011
Member
Excellent i'm able to see what i wanted after querying from the dba_source !

Thanks to all in this great forum and chat you soon for other fascinating issues.

Smile
Re: SQPLUS - How to to see specs/body of package procedures ? [message #493404 is a reply to message #493089] Mon, 07 February 2011 14:58 Go to previous messageGo to next message
Quetzalcoatl1
Messages: 33
Registered: January 2011
Member
Hi again,

Don't know if i need to create a new post for this one so just le me know if needed please.

How to know what are packages and what are the tables ?
Is it only by the logic of the names that i can know that, for example a name finishing with _PKG tells me that we are dealing with a PACKAGE etc.

Is there a way to query the dba_source or other source to only show me the list of fact tables for example ?

Thanks !
Re: SQPLUS - How to to see specs/body of package procedures ? [message #493407 is a reply to message #493404] Mon, 07 February 2011 15:07 Go to previous messageGo to next message
BlackSwan
Messages: 25858
Registered: January 2009
Location: SoCal
Senior Member
>Is there a way to query the dba_source or other source to only show me the list of fact tables for example ?
A table is a table.
RDBMS knows nothing about "fact table" as differentiated from non-fact tables.

>How to know what are packages and what are the tables ?
SELECT OBJECT_TYPE, COUNT(*) FROM USER_OBJECTS GROUP BY OBJECT_TYPE ORDER BY 1;
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 Go to previous messageGo to next message
cookiemonster
Messages: 13017
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.
Re: SQPLUS - How to to see specs/body of package procedures ? [message #493442 is a reply to message #493417] Tue, 08 February 2011 00:13 Go to previous messageGo to next message
Littlefoot
Messages: 21367
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps you'll be interesting in seeing what dictionary offers. Go to SQL*Plus and run the following statement:
SQL> column comments format a45
SQL> select table_name, comments from dictionary order by table_name;

Re: SQPLUS - How to to see specs/body of package procedures ? [message #493457 is a reply to message #493442] Tue, 08 February 2011 03:23 Go to previous messageGo to next message
cookiemonster
Messages: 13017
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can also look them up in the documentation: Oracle Database Reference - scroll down to part 2.

EDIT: fixed tags

[Updated on: Tue, 08 February 2011 03:24]

Report message to a moderator

Re: SQPLUS - How to to see specs/body of package procedures ? [message #493487 is a reply to message #493457] Tue, 08 February 2011 07:28 Go to previous messageGo to next message
Quetzalcoatl1
Messages: 33
Registered: January 2011
Member
Hi to all,

1- Thanks for the following command as it gives me a better idea of the number of tables etc ...
SQL> SELECT OBJECT_TYPE, COUNT(*) FROM dba_OBJECTS GROUP BY OBJECT_TYPE ORDER BY 1;

OBJECT_TYPE COUNT(*)
------------------- ----------
CLUSTER 10
CONSUMER GROUP 5
CONTEXT 1
DATABASE LINK 2
DIMENSION 1
DIRECTORY 18
EVALUATION CONTEXT 10
FUNCTION 77
INDEX 2350
INDEX PARTITION 34155
JOB 9

OBJECT_TYPE COUNT(*)
------------------- ----------
JOB CLASS 2
LIBRARY 114
LOB 166
LOB PARTITION 1
MATERIALIZED VIEW 75
OPERATOR 6
PACKAGE 544
PACKAGE BODY 528
PROCEDURE 170
PROGRAM 3
QUEUE 19

OBJECT_TYPE COUNT(*)
------------------- ----------
RESOURCE PLAN 3
RULE 4
RULE SET 11
SCHEDULE 1
SEQUENCE 152
SYNONYM 3817
TABLE 2206
TABLE PARTITION 8037
TRIGGER 19
TYPE 991
TYPE BODY 81

OBJECT_TYPE COUNT(*)
------------------- ----------
UNDEFINED 6
VIEW 3144
WINDOW 2
WINDOW GROUP 1

37 rows selected.


2- Also, after typing the following command i was able to see lots and lots of information that BUT i cannot read very well as it is all bad aligned and doesn't seems to fit the whole command prompt window
select *
from all_tables
where owner = 'OwnerName'
;

Can anyone let me know how to arrange the result so that it can be readable, or even better remind of any display command to send the result to a file that i can then retrieve ?

Thanks !
Re: SQPLUS - How to to see specs/body of package procedures ? [message #493492 is a reply to message #493487] Tue, 08 February 2011 07:44 Go to previous messageGo to next message
cookiemonster
Messages: 13017
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) Can you please read and follow the orafaq forum guide - especially the part on how to format your post. You've been asked 3 times already.

2) Some guides on sqlplus:
SQL*PlusĀ® User's Guide and Reference
SQL*PlusĀ® Quick Reference
Look up spool, set lines and format.

The full doc set can be found here

3) Try selecting a subset of columns, most of them aren't going to interest you.
Re: SQPLUS - How to to see specs/body of package procedures ? [message #493580 is a reply to message #493492] Tue, 08 February 2011 15:00 Go to previous messageGo to previous message
Littlefoot
Messages: 21367
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
BUT i cannot read very well as it is all bad aligned

It is just a machine, it does what you tell it to.

The same goes for your messages. But you are a human and really could put some effort in making your messages easier to read (use [code] tags to do that).

Read documents Cookiemonster suggested. I already gave you a hint in my previous message, but - it seems I used invisible ink. COLUMN FORMAT is an easy way to format output.
Previous Topic: Disable and Enable Constraints
Next Topic: grant user schema to another user
Goto Forum:
  


Current Time: Fri Feb 23 16:47:47 CST 2018

Total time taken to generate the page: 0.09513 seconds