question about pl/sql stored program text

ora-00942, oracle, plsql, stored-procedures, toad

Solution

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

Problem

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.

Original source