Problem creating stored procedures using OCI

[Quoted] Hello all, I'm fairly new to Oracle and have been adding support for it to [Quoted] [Quoted] our application. I'm using Oracle 8i (WinNT) and our client application uses [Quoted] the C-based Oracle Call Interface to both run the application and [Quoted] [Quoted] dynamically create new copies of our database on the fly by reading a SQL [Quoted] script and executing the statements one at a time.

[Quoted] Everything seems to work as far as using PL/SQL and oexec() to dynamically [Quoted] [Quoted] create tablespaces, new users, and tables, but all the stored procedures are [Quoted] [Quoted] created just fine -- without any error messages -- yet when I go to run them [Quoted] [Quoted] they are all in an "Invalid State" (sys.obj$ says the state for these [Quoted] procedures is 3). I'm trying to create procedures for tables that all belong [Quoted] to a single user using a dedicated tablespace.

[Quoted] [Quoted] Anyway, the really weird part is if I go into DBA Studio and bring up the [Quoted] 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 [Quoted] I run the same script through SQL*Plus, the procedures are created and marked
[Quoted] [Quoted] as Valid. (Using SQL*Plus or an external tool to create the databases is not [Quoted] an option)

[Quoted] [Quoted] However, whenever I create the procedures through OCI in our application, [Quoted] [Quoted] 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 [Quoted] and all they reference are previously-created tables.

For example:

[Quoted] CREATE TABLESPACE xyz ...
CREATE USER xyz ...
CREATE TABLE xyz.tabname ...
[Quoted] 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
[Quoted] [Quoted] statements in the procuedures to "insert into xyz.tabname" doesn't help.

Thanks,
Doug Received on Fri Jul 27 2001 - 19:41:21 CEST