Query to get object (Function) definition

[daljitsb]

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

[davey23uk]

take a look at user_procedures

whats sqltext got to do with users functions?

[daljitsb]

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

[davey23uk]

what?

functions are viewable from the user_procedures view

[daljitsb]

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

[davey23uk]

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

[daljitsb]

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

[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

[davey23uk]

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

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>

[davey23uk]

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