4

I have a sql function in oracle
create or replace function testfunc.....

Compile it succesfully. When i verify all_procedures system table it's not there. select * from all_procedures where procedure_name like '%testfunc%';

Not sure whether i am looking at the correct system table

Arav
  • 4,957
  • 23
  • 77
  • 123
  • If you actually want the function to be named `testfunc`, you need to use `create or replace function "testfunc"` - although you probably don't want to... – Jeffrey Kemp Nov 08 '10 at 04:23

2 Answers2

13

Unless you are using double-quoted identifiers to enforce case-sensitivity (something you almost certainly don't want to do), Oracle will always store identifiers in upper case in the data dictionary. So you would want

SELECT *
  FROM all_procedures
 WHERE procedure_name = 'TESTFUNC'
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Did you find your procedure in all_procedures view??? I can't find it,when I created...:? strange..It shows me just package specifications and package bodies... Please inform me did you find or not? Thank you – kupa Nov 10 '10 at 05:30
2

Log in as system or sys as sysdba and query:

SELECT *
FROM dba_objects 
WHERE object_name LIKE '%TESTFUNC%'
AND object_type='FUNCTION';

or

Log in as your user and query:

SELECT *
FROM all_objects
WHERE object_name LIKE '%TESTFUNC%'
kupa
  • 1,861
  • 5
  • 25
  • 41