Re: Problem creating stored procedures using OCI

[Quoted] I don't know why the problem is happening. A procedure will be marked invalid if something it depends upon is changed. You could try recompiling the procedures at the end e.g. begin dbms_utility.compile_schema('<SCHEMA NAME>'); end;

"Bozo" <nobody_at_nospam.net> wrote in message news:5Jh87.3085$17.1353383_at_news1.sttln1.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 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 Sat Jul 28 2001 - 11:23:20 CEST