ora-06553:pls-801:内部错误[55018]

mrwjdhj3  于 2021-07-26  发布在  Java
关注(0)|答案(1)|浏览(502)

我想做一个函数调用,比如 SELECT URUN_GETIR('test1') FROM DUAL; 但是我得到了ora-06553:pls-801:内部错误[55018]。
我尝试了ora-06553:pls-801:internal error[55018],当测试函数为ex 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;

g0czyy6m

g0czyy6m1#

使用表函数可以实现您的目标。除此之外,你不能直接调用你想要的函数。这里有另一个选择供您明智地使用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

相关问题