sql - ORA-06553：PLS-801：内部错误 [55018]

Question

我想进行类似的函数调用，SELECT URUN_GETIR('test1') FROM DUAL;但我得到了ORA-06553: PLS-801: Internal Error [55018]。

我尝试了 ORA-06553: PLS-801: internal error [55018] 当测试函数返回 ROWTYPE 时，例如前 URUN_GETIR('test1').KULUSERNAME 但得到相同的错误。它对我不起作用。在此先感谢。

我的数据库表：

我的plsql函数代码：

create or replace FUNCTION URUN_GETIR(KULADI VARCHAR2)
RETURN URUN%ROWTYPE
AS
URUN_TABLO URUN%ROWTYPE;
BEGIN
SELECT * INTO URUN_TABLO FROM URUN ur WHERE ur.kulusername = KULADI;
RETURN URUN_TABLO;
END;

标签： sqldatabaseoraclefunctionplsql

Answer 1

您可以使用 表函数来实现您的目标。除此之外，您不能像您想要的那样直接调用您的函数。这是您的另一个选择明智地使用 Stackoverflow

CREATE TABLE URUN
(
   CREATED_BY     VARCHAR2 (50 CHAR),
   CREATED_DATE   DATE,
   UPDATED_BY     VARCHAR2 (50 CHAR),
   KULUSERNAME    VARCHAR2 (50 CHAR),
   ID             NUMBER (10)
);

INSERT INTO URUN (CREATED_BY, CREATED_DATE, UPDATED_BY, KULUSERNAME, ID) VALUES('TEST1',TO_DATE('19000101','YYYYMMDD') ,'TTEST1','USER1',1);COMMIT;
INSERT INTO URUN (CREATED_BY, CREATED_DATE, UPDATED_BY, KULUSERNAME, ID) VALUES('TEST2',TO_DATE('19000102','YYYYMMDD') ,'TTEST2','USER2',2);COMMIT;
INSERT INTO URUN (CREATED_BY, CREATED_DATE, UPDATED_BY, KULUSERNAME, ID) VALUES('TEST3',TO_DATE('19000103','YYYYMMDD') ,'TTEST3','USER3',3);COMMIT;
INSERT INTO URUN (CREATED_BY, CREATED_DATE, UPDATED_BY, KULUSERNAME, ID) VALUES('TEST4',TO_DATE('19000104','YYYYMMDD') ,'TTEST4','USER4',4);COMMIT;

CREATE OR REPLACE TYPE URUN_OBJ AS OBJECT
(
   CREATED_BY VARCHAR2 (50 CHAR),
   CREATED_DATE DATE,
   UPDATED_BY VARCHAR2 (50 CHAR),
   KULUSERNAME VARCHAR2 (50 CHAR),
   ID NUMBER (10)
);


CREATE OR REPLACE TYPE URUN_OBJ_TAB AS TABLE OF URUN_OBJ;

CREATE OR REPLACE FUNCTION URUN_GETIR (KULADI IN VARCHAR2)
   RETURN URUN_OBJ_TAB
   PIPELINED
AS
   REC_OBJ   URUN_OBJ;

   CURSOR DATA
   IS
      SELECT *
        FROM URUN UR
       WHERE UR.KULUSERNAME = KULADI;
BEGIN
   FOR REC IN DATA
   LOOP
      REC_OBJ :=
         URUN_OBJ (REC.CREATED_BY,
                   REC.CREATED_DATE,
                   REC.UPDATED_BY,
                   REC.KULUSERNAME,
                   REC.ID);
      PIPE ROW (REC_OBJ);
   END LOOP;

   RETURN;
END;
/


SELECT * FROM   TABLE(URUN_GETIR('USER1'));

CREATED_BY  CREATED_DATE    UPDATED_BY  KULUSERNAME ID
TEST1   1.01.1900   TTEST1  USER1   1