How show function code from package in ORACLE?

Question

Hi,

I have newbie question, what is best way to show function FUNCTION1 from package PACK ?

DESC PACK.FUNCTION1 does not work :(

Thanks

Answer 1

May be function FUNCTION1 does exists in package PACK?? Because it works.

  SQL> desc dbms_output.put_line
   Parameter Type     Mode Default? 
   --------- -------- ---- -------- 
   A         VARCHAR2 IN            

SQL> desc dbms_random;

  Element    Type      
  ---------- --------- 
  SEED       PROCEDURE 
  VALUE      FUNCTION  
  NORMAL     FUNCTION  
  STRING     FUNCTION  
  INITIALIZE PROCEDURE 
  RANDOM     FUNCTION  
  TERMINATE  PROCEDURE 
  NUM_ARRAY  TYPE      

SQL> desc dbms_random.value

  Parameter Type   Mode Default? 
  --------- ------ ---- -------- 
  (RESULT)  NUMBER               
  (RESULT)  NUMBER               
  LOW       NUMBER IN            
  HIGH      NUMBER IN        

Answer 2

What do you mean by saying "to show"? You can see a package body implementation this way:

select text 
  from all_source a 
 where a.type = 'PACKAGE BODY' 
   and a.name = 'YOUR_PACKAGE' 
 order by line asc

though you can't extract only a certain function from a package this way. You can do the same with standalone functions by setting a.type = 'FUNCTION'.

Answer 3

Well, the best way is probably using something like SQLDeveloper (or TOAD) that has a schema browser, syntax highlighting, etc OR extracting the source out into text files that you can throw into your favourite editor.

Both options beat querying and reformatting the source in USER_SOURCE just to find a function spec.

Pretty poor going on Oracle's part as DESC PACK.FUNCTION1 used to work on Oracle 7, but they switched it over to DESC PACK only.

Answer 4

Thanks.

It is really unpleasant to manage ORACLE server (7v worst).

Finally, I established SSH tunnel to this server and used ORACLE DBA software.