Re: Problem creating stored procedures using OCI
Date: Tue, 31 Jul 2001 00:58:55 GMT
Message-ID: <jpn97.8083$Ke4.5327407_at_news1.sttln1.wa.home.com>
[Quoted] Thanks, but it doesn't help. I am trying to run the following PL/SQL through OCI: [Quoted] 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 [Quoted] Advanced Server with SQL*Plus 8.1.7. This works if I run it through SQL*Plus [Quoted] 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 - 02:58:55 CEST