Link to home
Start Free TrialLog in
Avatar of afytech1
afytech1

asked on

How can I find/list all the stored procedures and functions in my Oracle DB?

Hi,

I'm running Oracle 7.3.4.  I have a user getting an error when trying to perform an "order entry" type function.  The error text indicates the error is on line 65 in script "fu_allocate_inventory".  How do I locate the script?  How can I view the script?  I'm using sqlplus.

Thanks in advance
Avatar of ram_0218
ram_0218
Flag of United States of America image

you can get the whole text of that procedure using all_source or user_source.

Do this,

set pagesize 1000
set linesize 500
select text from all_source where name like 'FU_ALLOCATE_INVENTORY'

should give you the source code.
ASKER CERTIFIED SOLUTION
Avatar of ram_0218
ram_0218
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of afytech1
afytech1

ASKER

Thanks...

I listed all the procedures and don't see one named 'fu_allocate_inventory'.  I also use a package called TOAD to examine the database and I see the same list of procedures there.  I'm thinking this is a FUnction, but TOAD shows no functions in the database.  I'm at a loss as to what this "fu_allocate_inventory" is and where to find it.

Any ideas?
SOLUTION
Avatar of johnsone
johnsone
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
As given by johnsone, If you want to know whats the type of that object.

You say

select object_type from all_objects where object_name = 'FU_ALLOCATE_INVENTORY'

Its all about these tables all_objects,all_source you gotta play with the fields.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
No luck so far........

SQL> select object_type from all_objects where object_name = 'FU_ALLOCATE_INVENTORY'
  2  ;

no rows selected

SQL> select object_type from all_objects where object_name = 'fu_allocate_inventory';

no rows selected


Is it possible this procedure/function could have been created outside the Oracle DB?
We use an application that was developed using PowerBuilder 6.5.  Could that function be a part of this application?
afytech1,

if you'r using a third party application and they've given you guys only the compiled version of read only objects to your db, looking at objects wont help you. You need to refer their manual (if they've given you one) only.

The catch is, though dba you could able to locate the object but then you wont be able to read the code and you've absolutely got nothing to do with their code. Either creating a ticket to them or analysing your own code that uses this function may be of help.
Can you give the exact error that is being received?  It could be that this function is part of a package too....
The complete ORA-xxxx message. or similar (PLS-xxxx, etc).
It's not an ORA-XXXXX error.

It looks like it is definitely an application error.  It's a long story, but the bottom line is, I have no support to turn to on this issue.  Our company is a spin off from a large company breakup, and those ties were severed years ago.  We're just limping along on this app until we get the data into another app anyway.  My boss says we have the PowerBuilder source and a version of PowerBuilder so I will try and get that installed.

the actual error text is:

Error number 6
Error text = Invalid DataWindow row/column specified.
Window/menu/Object = u_calculate_rawmaterial_availability
Error Object/Control = u_calculate_rawmaterial_availability
Script = fu_allocate_inventory
Line in Script = 65
Yep, this may be an oracle issue, but it is embedded in the application code. I hope it turns out to be not TOO painful to identify.
No wonder why after liseted to Corfman, I have got this doubt. Could you try for

select * from all_objects where object_name='U_CALCULATE_RAWMATERIAL_AVAILABILITY'
/

Yes as he says, your fu_allocate_inventory could be a function or a procedure inside this package and may not be visilbe on objects table.

If that also does not open the door, as you say always look from applicatoin perspective rather than looking at db level and see that will help :)
Unfortunately, I don't believe you will find u_calculate_rawmaterial_availability as an object in your database. The code object names are limited to 30 characters (I've bumped into this limit before) and that name is too long for Oracle. Seems pretty clear this is an application issue at this point.  It would certainly be easier if you didn't have to install a new development environment and work through the code. The good thing is (if there is a good thing here) is that EE also has a TA for powerbuilder :)
Ater I posted, I too realized that I made a stupid comment. Because if that was a package, then the oracle error tree would have given the line number of package too not only the procedure's line number. :)
Thanks to everyone for the help.  I guess I'll take this to the PB section and see what happens.