Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Execute Immediate vs. SQL*Plus syntax for CREATE OR REPLACE VIEW

Re: Execute Immediate vs. SQL*Plus syntax for CREATE OR REPLACE VIEW

From: Mark Freeman <mark_freeman_at_asc.aon.com>
Date: 8 Apr 2004 07:20:19 -0700
Message-ID: <df323d2.0404080620.43e0902e@posting.google.com>


Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1081317432.663717_at_yasure>...
> Mark Freeman wrote:
>
> > I am using Oracle 8i. I have code that creates a long (but less than
> > 32k) string that contains a CREATE OR REPLACE VIEW statement.
> >
> > Any ideas?
> >
> > Thanks!
>
> Just one ... stop performing DDL in a proc.
>
> There is no justification for doing this so just don't. Build the
> view and then leave it alone.

There is actually a need for this. The view includes different columns depending on the contents of tables whose content occasionally changes. The procedure will run nightly to ensure that the view is in sync with the data.

I broke down the code that generated the CREATE OR REPLACE VIEW statement into modules, made them as consistent with each other as possible, simplified the use of variables and cursors, and now the generated code works fine.

I never did figure out why the original statement would execute directly but not via the EXECUTE IMMEDIATE, but I'm not going to worry about it now.

Thanks for the reply.

Received on Thu Apr 08 2004 - 09:20:19 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US