Problem creating stored procedures using OCI

From: Bozo <nobody_at_nospam.net>
Date: Thu, 19 Jul 2001 17:23:39 GMT
Message-ID: <vIE57.377303$p33.7568549_at_news1.sttls1.wa.home.com>


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.

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 text through SQL*Plus, the procedures are created and marked as Valid.

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" -- comments
(variables ) is
begin
 statements;
end;

Any ideas how to make procedures Valid when creating them in OCI?

Thanks,
Doug Received on Thu Jul 19 2001 - 19:23:39 CEST

Original text of this message