Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Problem creating stored procedures using OCI
Hello all, I'm fairly new to Oracle and have been adding support for it to our application. I'm using Oracle 8i (WinNT) and our client application uses the C-based Oracle Call Interface to both run the application and dynamically create new copies of our database on the fly by reading a SQL script and executing the statements one at a time.
Everything seems to work as far as using PL/SQL and oexec() to dynamically create tablespaces, new users, and tables, but all the stored procedures are created just fine -- without any error messages -- yet when I go to run them they are all in an "Invalid State" (sys.obj$ says the state for these procedures is 3). I'm trying to create procedures for tables that all belong to a single user using a dedicated tablespace.
Anyway, the really weird part is if I go into DBA Studio and bring up the
source for the procedures marked as invalid, make a slight change (add a
space for instance), and recompile, then they are marked as valid. Also, if
I run the same script through SQL*Plus, the procedures are created and
marked
as Valid. (Using SQL*Plus or an external tool to create the databases is not
an option)
However, whenever I create the procedures through OCI in our application, the same procedures are always marked as Invalid. I'm using the "system" user to create all the objects and I've checked the objects for dependencies and all they reference are previously-created tables.
For example:
CREATE TABLESPACE xyz ...
CREATE USER xyz ...
CREATE TABLE xyz.tabname ...
CREATE OR REPLACE PROCEDURE xyz."procname"
-- some comments
(variables ) is
begin
insert into tabname ...;
end;
Any ideas how to make procedures Valid when creating them in OCI? Changing
the
statements in the procuedures to "insert into xyz.tabname" doesn't help.
Thanks,
Doug
Received on Fri Jul 27 2001 - 12:41:21 CDT