Error: PL/SQL: ORA-01733: virtual column not allowed here

Line: 81

Text: MERGE INTO SvcOrderProfit



Error: PL/SQL: SQL Statement ignored

Line: 81
Text: MERGE INTO SvcOrderProfit


用merge into 的时候提示
REF  ：http://www.cnblogs.com/dongsheng/p/4384754.html



问题代码：
 WHEN MATCHED THEN

    UPDATE

       SET IncomeRMB                    = pos_2,

           CostRMB                      = pos_3;/*,

           v_dIncomeUSDFee              = pos_4,

           v_dIncomeUSDAccount          = pos_5,

           v_dlCostUSDFee               = pos_6,

           v_dlCostUSDAccount           = pos_7,

           v_dIncomeRMBSales            = pos_8,

           v_dCostRMBSales              = pos_9,



ROOT CAUSE 本来左边应该是表名，从SQL 转过来的时候却弄成了变量。所以报错 
：
solution  :


表字段的保留， 非表字段的SELECT INTO 到变量里面：
      SELECT NVL((SELECT SUM(SvcOrderFee.NoTaxFeeAmount)

                              FROM SvcOrderFee

                             WHERE SvcOrderFee.CurrencyType = 0

                               AND SvcOrderFee.CreditDebitType = 0

                               AND (SvcOrderFee.AccountType = 2 OR

                                   SvcOrderFee.AccountType = 3)

                               AND SvcOrderFee.OrderBillID = v_lOrderBillID),

                            0)
INTO         v_dCostRMBSales  

                   FROM DUAL;

 基表： hr.tt  scott.tt

 视图1： 基于 hr.tt  union all  scott.tt ---》 scott.ttt

 视图2： 基于 视图1-》scott.ttt 创建 system.tt



问题再现：

SQL> create table hr.tt (a number);

Table created.

SQL>  create table lixora.tt (a number);

Table created.


SQL> create view  scott.tt as select * from  hr.tt union all select * from lixora.tt;

View created.



SQL> create view system.tt as select * from scott.tt;

View created.


SQL> select * from system.tt

         A

----------

         1


更新视图2：

SQL> update  system.tt set a=2;

update  system.tt set a=2

                      *

ERROR at line 1:

ORA-01733: virtual column not allowed here




更新视图1：

SQL> update  scott.tt set A=2;

update  scott.tt set A=2

              *

ERROR at line 1:

ORA-01732: data manipulation operation not legal on this view



SQL> !oerr ora 1732

ORA-01733: virtual column not allowed here 

Cause: An attempt was made to use an INSERT, UPDATE, or DELETE statement on an expression in a view. 

Action: INSERT, UPDATE, or DELETE data in the base tables, instead of the view.

总结：
无法对包含表达式的view 进行dml 操作