在程序中创建VIEW时出错？ [英] Error while creating VIEW inside the procedure ?

问题描述

在一个程序中创建视图时出错





程序SP_TEST（

PI_SAAAA IN日期，

PI_EEEEE日期，

PI_SSSSS IN varchar2）IS







BEGIN



创建或更换视图V_TEST

选择a.xxxx，a.yyyy，b .hhhh



来自

table1 a，table2 b

Am getting error while creating the view inside one procedure


PROCEDURE SP_TEST(
PI_SAAAA IN date,
PI_EEEEE IN date,
PI_SSSSS IN varchar2) IS



BEGIN

CREATE OR REPLACE VIEW V_TEST AS
select a.xxxx,a.yyyy,b.hhhh

from
table1 a, table2 b

commit;

EXCEPTION
 WHEN OTHERS THEN
  Rollback;

END SP_TEST;

得到错误：

PACKAGE BODY CONV.PKG_TEST的编译错误



错误：PLS-00103：遇到了期望下列之一时符号CREATE：



开始案例声明结束异常退出goto如果循环mod

null pragma raise return选择更新，同时使用

<标识符> <双引号分隔标识符>

< a bind变量> <<关闭当前删除获取锁定插入

打开回滚保存点设置sql执行提交forall合并

管道

行：50

文字：创建或更换视图V_TEST1 AS







可能是什么原因？

Error am getting :
Compilation errors for PACKAGE BODY CONV.PKG_TEST

Error: PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:

begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << close current delete fetch lock insert
open rollback savepoint set sql execute commit forall merge
pipe
Line: 50
Text: CREATE OR REPLACE VIEW V_TEST1 AS



What could be the reason?

推荐答案

我不确定为什么你想要/需要在一个过程中创建一个视图，但我相信你需要使用动态SQL。例如：

I''m not sure why you''d want/need to create a view from within a procedure, but I believe you need to use dynamic SQL. For example:

CREATE OR REPLACE PROCEDURE create_view is
PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN
execute immediate 'CREATE VIEW just1 AS SELECT 1 as col1 from dual';
END;

然后执行它：

Then execute it:

BEGIN
  username.CREATE_VIEW;
END;

然后视图将存在。

Scott

Then the view will exist.
Scott

这篇关于在程序中创建VIEW时出错？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！