question about pl/sql stored program text

Question

I use TOAD to do my PL/SQL development. In TOAD when i type a procedure name and press f4, I can see this procedure's source code. I think TOAD get the source code from v$sqltext view. To confirm my thought, I wrote a query:

select * from v$sqltext

but when I execute the upper query, Oracle give me an error:

ORA-00942: table or view does not exist 00942. 00000 - "table or view does not exist" *Cause:
*Action: Error at Line: 29 Column: 15

So I think TOAD get the procedure's source from other place instead of v$sqltext view. Anyone can tell me about this? Great thanks.

Answer 1

The full query for a stored procedure (not in a package):

select text
from   all_source
where  owner = 'MYSCHEMA'
and    type = 'PROCEDURE'
and    name = 'MY_PROCEDURE'
order by line;

If you are connected as user MYSCHEMA than you can use USER_SOURCE:

select text
from   user_source
where  type = 'PROCEDURE'
and    name = 'MY_PROCEDURE'
order by line;

Other values for TYPE are:

• TYPE BODY
• FUNCTION
• TRIGGER
• TYPE
• JAVA SOURCE
• PACKAGE BODY
• PACKAGE

Answer 2

select * from all_source

See Database Reference for ALL_SOURCE and V$SQLTEXT.

Answer 3

If you have select priv on DBA* tables, then do check out select * from dba_source. This table will have the entire source code.