PROCEDURE DOESN'T EXIST

[tekpr00]

Hi,

I created and compiled a procedure in oracle 10g. When I looked at the status of the procedure. It tells me that it is VALID.

PROCEDURE
GET_MANUAL_LOAD
OBJECT_TYPE
-------------------
OBJECT_NAME
---------------------
STATUS
-------
VALID

However, when I ran my apache webserver script to call this procedure from the database. The Apache error log tells says that "PROCEDURE DOESN'T EXIST"

I have granted PUBLIC to the procedure.

Please help

 

[BJCooperIT]

When trying to run it, are you logged into the database under the schema who owns the procedure? If not, you will need to reference the procedure as SCHEMA_NAME.PROCEDURE_NAME or you will need to create a public synonym to use to call it by its name alone.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[tekpr00]

Yes, I was logged in as the user who owns the object. That is why I could see it when I query user_objects view.

However, the Apache conf file could not see it, that is why I am getting PROCEDURE DOESN'T EXIST.

 

[dstxaix]

Ask your DBA to create a public synonym for this stored proc. Apache will not give you this error.

 

[tekpr00]

I am the DBA and I have created the public as follows:

create public synonym GET_MANUAL_LOAD for

http://WWW_CRPROD.GET_MANUAL_LOAD

create public synonym GET_MANUAL_LOAD for

http://WWW_CRTRAIN.GET_MANUAL_LOAD

Perharps, I need to grant read, write and execute to users

http://www_crprod

and

http://www-cr_crtrain

on the GET_MANUAL_PAGE

 

[SantaMufasa]

Tekpr00,

If your code really reads:

create public synonym GET_MANUAL_LOAD for [URL unfurl="true"]WWW_CRPROD.GET_MANUAL_LOAD[/URL]

create public synonym GET_MANUAL_LOAD for WWW_CRTRAIN.GET_MANUAL_LOAD

...then your problem is that you are destroying the first synonym with the second synonym since the synonym names are identical.

And, yes, you must grant at least EXECUTE privilege to your intended users..."READ" (i.e. SELECT) and "WRITE" (i.e., UPDATE or DELETE) are illegal on procedures:

grant select, update, execute on prime to public
                                 *
ERROR at line 1:
ORA-02225: only EXECUTE and DEBUG privileges are valid for procedures

grant execute on prime to public;

Grant succeeded.

Let us know if this was helpful.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”