Query to get object (Function) definition

What query can be run to get the full definition (full text) of particular user's particular Function. What table/view to join with v$sqltext?

-D

take a look at user_procedures

whats sqltext got to do with users functions?

Quote:

---

Originally Posted by davey23uk

take a look at user_procedures

whats sqltext got to do with users functions?

---

I guess there is nothing like user_procedures that you can query on on oracle?

-D

what?

functions are viewable from the user_procedures view

Quote:

---

Originally Posted by davey23uk

what?

functions are viewable from the user_procedures view

---

There is no view on oracle by the name 'user_procedures'.
I cannot see from user, system or sys login.

-D

connect as anybody and do select * from user_procedures

it is a view owned by sys, granted to public which lets you see you own procedures - you do not own the actual view

So try selecting from it

Here is what I get.
SQL> select * from user_procedures;

select * from user_procedures

ORA-00942: table or view does not exist

-D

SQL> select * from sys.user_procedures;

select * from sys.user_procedures

ORA-00942: table or view does not exist

are you on an older Oracle version - 8i or prior? That view isn't defined on my 8i, is on 9i.

Try looking at dba_source

then something is up with your database as select * from user_procedures should work

Code:

---

SQL> create user dave identified by dave;

User created.

SQL> grant create session to dave;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Beta

C:\Documents and Settings\david.sharples>sqlplus dave/dave

SQL*Plus: Release 10.2.0.1.0 - Beta on Fri Jan 13 19:17:34 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Beta

SQL> select * from user_procedures;

no rows selected

SQL> connect hr/hr
Connected.
SQL> select count(*) from user_procedures;

  COUNT(*)
----------
        3

SQL>

---

Quote:

---

Originally Posted by tomcat

are you on an older Oracle version - 8i or prior? That view isn't defined on my 8i, is on 9i.

Try looking at dba_source

---

aaahh pesky unsupported versions, ban them all

user_source would be the better option here though

Use USER_SOURCE or DBA_SOURCE if you do not see USER_PROCEDURES.

Dba_Source and User_Source views
was what I was looking for. Thanks Tomcat & Dave23uk.

I should have mentioned oracle version (8.1.7) but thought that this was such a generic question.

Anyway user_procedures view in 10g will not show us definition, so we need User_Source or dba_source to get the object defintion.

Thanks again
-D