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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
ASKER
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.
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).
ASKER
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_av ailability
Error Object/Control = u_calculate_rawmaterial_av ailability
Script = fu_allocate_inventory
Line in Script = 65
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_av
Error Object/Control = u_calculate_rawmaterial_av
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_R AWMATERIAL _AVAILABIL ITY'
/
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 :)
select * from all_objects where object_name='U_CALCULATE_R
/
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_av ailability 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. :)
ASKER
Thanks to everyone for the help. I guess I'll take this to the PB section and see what happens.
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.