4 Replies Latest reply on Jun 12, 2008 3:49 PM by Brian Hill

    ORA-04067: not executed, stored procedure "PUBLIC." does not exist

      Here's one for eyes that have seen more than mine!

      I have a form that builds a pl/sql collection, passes it as an argument to a package procedure stored in the database, which in turn inserts records into a table using the FORALL methodology.

      Everything works fine when running the form via my OC4J instance (10g R1) on my (Windows) PC. But when the form is moved to our 10g iAS server (10g R2 on Unix), if fails. The error returned is

      ORA-04067: not executed, stored procedure "PUBLIC." does not exist

      This looks like something is misconfigured synonym- or privilege-wise on the database, but the form works fine against the same database instance (10g EE when run via OC4J.

      I've had to recode the form to do the inserts in a cursor FOR loop. This is not a problem for this piece of the application, because we are really only talking about literally a handful of records (10-12 tops). I coded it this way for consistency, not performance. But other pieces of the application coming down the pike will need to use this technique for 5k or more records, so I need to find a solution for this.
        • 1. Re: ORA-04067: not executed, stored procedure "PUBLIC." does not exist
          Do you happen to have two databases that mirror one another, one for production and the other for testing? If so, compare the tnsnames.ora file on the app server with the one on your PC. Make sure you don't have two different files. You may be logging on a database that doesn't have the procedure. Also check your on-logon trigger to see if there is an IF statement that is pointing to a different database.

          If you don't have two databases, then try recompiling the form on the UNIX machine using the Compile PLSQL -> All option.

          Message was edited by:
          Mark Roberts
          • 2. Re: ORA-04067: not executed, stored procedure "PUBLIC." does not exist
            Having just upgraded from forms 9i to 10g we are having exactly the same problem as begenwald.

            Did you ever find a solution?

            I can confirm that the code is definately connecting to the correct database and executing a package that DOES exist, so this is not a tnsnames issue or anything to do with the wrong database being used.

            If the strored package procedure being called from the form takes a record structure as a parameter then the "ORA-04067: not execute, stored procedure "PUBLIC." does not exist" error is raised, however, if I change the parameter to a simple scalar parameter e.g. a varchar2 then the error does not occur.

            This appears to be due to a bug in forms10g on unix.

            Any pointers would be appreciated as this is driving me mad, not to mention wasting a lot of my time.
            • 3. Re: ORA-04067: not executed, stored procedure "PUBLIC." does not exist

              I am using Oracle Developer 10 Release1. I tried to use a procedure which takes PL/SQL tables as arguments anf does FOR ALL. It failed. In my case the table was remote and I used DB link.

              Thanks and Regards,
              • 4. Re: ORA-04067: not executed, stored procedure "PUBLIC." does not exist
                Brian Hill
                Looks like bug #5123798. For more info, see MetaLink Note #5123798.8. Here are the work-arounds listed in that note:

                1) Use server-side PL/SQL rather than calling from client->server.
                2) Do not use late RPC name binding .
                3) Use a 32-bit client platform.