“无法从非嵌套表项访问行”的错

Question

我只是在存储过程中调用了下函数，为什么报这个错啊！各位有经验的朋友，一般这是怎么回事啊？

Answer 1

SELECT CCName
        INTO v_l_CCName
        FROM TABLE(NS_QX_GetClientByCG(v_CGNo))
       WHERE CCNo = v_CCNo AND ROWNUM <= 1;
我就是在存储过程中这么用的，其中NS_QX_GetClientByCG是个函数

Answer 2

SELECT   CCName 
                INTO   v_l_CCName 
                FROM   TABLE(NS_QX_GetClientByCG(v_CGNo)) 
              WHERE   CCNo   =   v_CCNo   AND   ROWNUM   <=   1; 
我就是在存储过程中这么用的，其中NS_QX_GetClientByCG是个函数

Answer 3

--你想用 table() 这种方式查询数据，你的函数不能是普通函数。
create table tb1(k number, v varchar2(10)); insert into tb1(k, v) values(100,'aaa'); 
insert into tb1(k, v) values(200,'bbb'); 
insert into tb1(k, v) values(200,'ccc'); select * from tb1; create type row_type1 as object(k number, v varchar2(10)); create type table_type1 as table of row_type1; create or replace function fun1 return table_type1 pipelined as 
v row_type1; 
begin 
     for myrow in (select k, v from tb1) loop 
       v := row_type1(myrow.k, myrow.v); 
       pipe row (v); 
     end loop; 
     return; 
end; select * from table(fun1); 

Answer 4

grant execute on <return type name of NS_QX_GetClientByCG> to ...

Answer 5

这是我的函数，也是这样啊，为什么不行呢
CREATE OR REPLACE FUNCTION NS_QX_GetClientByCG
(
  v_CGNo IN VARCHAR2
)
RETURN NS_QX_GetClientByCG_pkg.tt_v_Ret_54_type PIPELINED
AS
   v_temp SYS_REFCURSOR;
   v_temp_1 TT_V_RET_54%ROWTYPE;
BEGIN
   IF v_CGNo = '01' THEN
      INSERT INTO tt_v_Ret_54
        ( CLTNO, CLTNAME, TREENO, CCID, CGNO, CCNO, CCNAME, CCTREENO, "LEVEL", FULLNAME, MEMO )
        ( SELECT a.CltNo ,
                 a.CltName ,
                 a.TreeNo ,
                 b.CCID ,
                 b.CGNo ,
                 b.CCNo ,
                 b.CCName ,
                 b.TreeNo ,
                 b."Level" ,
                 b.FullName ,
                 b.Memo
          FROM NsClient a
                 JOIN VW_NsCltClass b
                  ON ( a.TradeID = b.CCID ) );
   ELSE
      IF v_CGNo = '02' THEN
         INSERT INTO tt_v_Ret_54
           ( CltNo, CltName, TreeNo, CCID, CGNo, CCNo, CCName, CCTreeNo, "LEVEL", FullName, Memo )
           ( SELECT a.CltNo ,
                    a.CltName ,
                    a.TreeNo ,
                    b.CCID ,
                    b.CGNo ,
                    b.CCNo ,
                    b.CCName ,
                    b.TreeNo ,
                    b."Level" ,
                    b.FullName ,
                    b.Memo
             FROM NsClient a
                    JOIN VW_NsCltClass b
                     ON ( a.AreaID = b.CCID ) );
      ELSE
         IF v_CGNo = '03' THEN
            INSERT INTO tt_v_Ret_54
              ( CltNo, CltName, TreeNo, CCID, CGNo, CCNo, CCName, CCTreeNo, "LEVEL", FullName, Memo )
              ( SELECT a.CltNo ,
                       a.CltName ,
                       a.TreeNo ,
                       b.CCID ,
                       b.CGNo ,
                       b.CCNo ,
                       b.CCName ,
                       b.TreeNo ,
                       b."Level",
                       b.FullName ,
                       b.Memo
                FROM NsClient a
                       JOIN VW_NsCltClass b
                        ON ( a.ClassGrp1 = b.CCID ) );
         ELSE
            IF v_CGNo = '04' THEN
               INSERT INTO tt_v_Ret_54
                 ( CltNo, CltName, TreeNo, CCID, CGNo, CCNo, CCName, CCTreeNo, "LEVEL", FullName, Memo )
                 ( SELECT a.CltNo ,
                          a.CltName ,
                          a.TreeNo ,
                          b.CCID ,
                          b.CGNo ,
                          b.CCNo ,
                          b.CCName ,
                          b.TreeNo ,
                          b."Level" ,
                          b.FullName ,
                          b.Memo
                   FROM NsClient a
                          JOIN VW_NsCltClass b
                           ON ( a.ClassGrp2 = b.CCID ) );
            ELSE
               IF v_CGNo = '05' THEN
                  INSERT INTO tt_v_Ret_54
                    ( CltNo, CltName, TreeNo, CCID, CGNo, CCNo, CCName, CCTreeNo, "LEVEL", FullName, Memo )
                    ( SELECT a.CltNo ,
                             a.CltName ,
                             a.TreeNo ,
                             b.CCID ,
                             b.CGNo ,
                             b.CCNo ,
                             b.CCName ,
                             b.TreeNo ,
                             b."Level" ,
                             b.FullName ,
                             b.Memo
                      FROM NsClient a
                             JOIN VW_NsCltClass b
                              ON ( a.ClassGrp3 = b.CCID ) );
               END IF;
            END IF;
         END IF;
      END IF;
   END IF;
   OPEN v_temp FOR
      SELECT *
        FROM tt_v_Ret_54;   LOOP
      FETCH v_temp INTO v_temp_1;
      EXIT WHEN v_temp%NOTFOUND;
      PIPE ROW ( v_temp_1 );
   END LOOP;
END;麻烦您再给看看吧，到底怎么回事啊？！！！！谢谢，谢谢！！！！！

Answer 6

谢谢你，可是，这是什么意思啊？是我还要给用户设置访问权限吗？（俺ora还是菜鸟中，希望您别嫌麻烦）