Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Execute Immediate vs. SQL*Plus syntax for CREATE OR REPLACE VIEW
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?
>
> The command string that is generated is as follows:
>
Try removing the field list before the "AS SELECT".
Still, it's not a good idea to be doing this in a proc.