DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Query to get object (Function) definition

  1. #1
    Join Date
    Jul 2003
    Posts
    136

    Question 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

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    take a look at user_procedures

    whats sqltext got to do with users functions?

  3. #3
    Join Date
    Jul 2003
    Posts
    136
    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

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    what?

    functions are viewable from the user_procedures view

  5. #5
    Join Date
    Jul 2003
    Posts
    136
    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

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    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

  7. #7
    Join Date
    Jul 2003
    Posts
    136
    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

  8. #8
    Join Date
    Oct 2005
    Location
    Indianapolis
    Posts
    100
    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
    "False data can act only as a distraction. Therefore. I shall refuse to perceive you." - Bomb #20

  9. #9
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    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>

  10. #10
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width