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

tojo <Tojo_at_hotmail.com> wrote in message news:<MPG.1addc1505c9d4d749896e9_at_news.t-online.de>...
> In article <df323d2.0404060919.3f14f5db_at_posting.google.com>,
> mark_freeman_at_asc.aon.com says...
> > 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. If I
> > take the content of that string and execute it directly in SQL*Plus
> > the view is successfully created. If I "EXECUTE IMMEDIATE" within a
> > procedure, I get an error (ORA-01730: invalid number of column names
> > specified). I tried adding "AUTHID CURRENT_USER" but it made no
> > difference. I have another similar procedure that also does a CREATE
> > OR REPLACE VIEW and it works, so I don't think this is a permissions
> > issue.
> >
> > Are there any syntax restrictions on EXECUTE IMMEDIATE within a
> > procedure that do not also apply to statements run directly?
> >
> Try removing the field list before the "AS SELECT".
>
> Still, it's not a good idea to be doing this in a proc.

I addressed the need for this in another message in this thread.

I will definitely look into the alternate syntax you suggested. I hate having to list the columns twice, and would much rather specify them and their aliases in one place. I thought that listing them in two places like that was a requirement.

Thanks for the reply.

• Mark