Re: Problem creating stored procedures using OCI

[Quoted] Do u have procedure name in double-quotes? If so, that may be a problem.

Bozo wrote:

> Thanks, but it doesn't help. I am trying to run the following PL/SQL through
> OCI:
>
> CREATE OR REPLACE PROCEDURE schema."PROCNAME" (
> n_id number,
> v_name varchar2) AS
> begin
> update schema.TABNAME set name=v_name where id=n_id;
> end;
>
> It creates the procedure, but in an Invalid state, and when I run "show
> errors procedure schema.procname" all I get is this:
>
> LINE/COL ERROR
> -------- -----------------------------------------------------------------
> 1/32 PLS-00103: Encountered the symbol "" when expecting one of the
> following:
> <an identifier> <a double-quoted delimited-identifier>
> current
>
> I am logged in as the system user and I'm using Oracle 8.1.5 on Windows 2000
> Advanced Server with SQL*Plus 8.1.7. This works if I run it through SQL*Plus
> or recompile it in DBA Studio.
>
> What am I doing wrong????
>
> -Doug
>
> "Keith Boulton" <kboulton_at_ntlunspam-world.com> wrote in message
> news:Tqv87.14796$vN4.95765_at_news11-gui.server.ntli.net...
> > 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 Tue Jul 31 2001 - 07:29:19 CEST