PL/SQL 函数返回自定义类型异常 [英] PL/SQL Function return custom type exception

查看:82
本文介绍了PL/SQL 函数返回自定义类型异常的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我创建了一个包含自定义类型的包和一个返回自定义类型的函数,如下所示;

I have created a package which holds a custom type and a function that returns the custom type as below;

create or replace 
PACKAGE INHOUSE_CUST_API
AS
TYPE doc_rec
IS
  RECORD
  (
    doc_Title    doc_issue_reference.title%Type,
    doc_Number   DOC_ISSUE_REFERENCE.DOC_NO%TYPE,
    doc_Type    DOC_ISSUE_REFERENCE.FILE_TYPE%TYPE,
    doc_FileName DOC_ISSUE_REFERENCE.FILE_NAME%TYPE,
    doc_Path     DOC_ISSUE_REFERENCE.PATH%TYPE);
  FUNCTION Get_Budget_Doc(
      company             IN VARCHAR2,
      budget_process_id   IN VARCHAR2,
      budget_ptemplate_id IN VARCHAR2)
    RETURN doc_rec;
END INHOUSE_CUST_API;

之后,我创建了如下的函数体

after that, I created the body of the function as below

create or replace 
PACKAGE BODY INHOUSE_CUST_API
AS
FUNCTION Get_Budget_Doc(
    company             IN VARCHAR2,
    budget_process_id   IN VARCHAR2,
    budget_ptemplate_id IN VARCHAR2)
  RETURN doc_rec
IS
  enhDocItem ENHANCED_DOC_REFERENCE_OBJECT%ROWTYPE;
  docIssueRef DOC_ISSUE_REFERENCE%ROWTYPE;
  docKeyValue VARCHAR2(150);
  docIssueRef_rec  doc_rec;
BEGIN
  docKeyValue := company||'^'||budget_process_id||'^'||budget_ptemplate_id||'^';
  --dbms_output.put_line(docKeyValue);
  SELECT *
  INTO enhDocItem
  FROM ENHANCED_DOC_REFERENCE_OBJECT
  WHERE KEY_VALUE= docKeyValue;
  SELECT *
  INTO docIssueRef
  FROM DOC_ISSUE_REFERENCE
  WHERE DOC_NO               = enhDocItem.DOC_NO;
  docIssueRef_rec.doc_Title :=docIssueRef.Title;
  docIssueRef_rec.doc_Number:=docIssueRef.DOC_NO;
  docIssueRef_rec.doc_Type  :=docIssueRef.FILE_TYPE;
  docIssueRef_rec.doc_Path  :=docIssueRef.PATH;
  RETURN docIssueRef_rec;
END Get_Budget_Doc;
END INHOUSE_CUST_API;

当我尝试调用函数时select INHOUSE_CUST_API.Get_Budget_Doc('param1','param2','param3') from dual;

when I try to call the function as like select INHOUSE_CUST_API.Get_Budget_Doc('param1','param2','param3') from dual;

我收到此异常

ORA-00902: 无效的数据类型00902. 00000 - 无效的数据类型"*原因:
*动作:

ORA-00902: invalid datatype 00902. 00000 - "invalid datatype" *Cause:
*Action:

感谢任何帮助.

推荐答案

您可能想要使用表函数来返回您的自定义类型.这是一个非常简单的例子:

You might want to use a table function to return your custom type. Here is a very simple example:

CREATE OR REPLACE PACKAGE brianl.deleteme AS
TYPE doc_rec_t IS RECORD
(
    name    VARCHAR2( 10 )
  , age     NUMBER( 3 )
);

TYPE doc_rec_tt IS TABLE OF doc_rec_t;

FUNCTION age( p_name IN VARCHAR2, p_age IN NUMBER, p_years IN INTEGER )
    RETURN doc_rec_tt
    PIPELINED;
END deleteme;

CREATE OR REPLACE PACKAGE BODY brianl.deleteme AS
  FUNCTION age( p_name IN VARCHAR2, p_age IN NUMBER, p_years IN INTEGER )
      RETURN doc_rec_tt
      PIPELINED AS
      l_ret   doc_rec_t;
  BEGIN
    l_ret.name   := p_name;
    l_ret.age    := p_age;

    FOR i IN 1 .. p_years
    LOOP
        PIPE ROW (l_ret);
        l_ret.age   := l_ret.age + 1;
    END LOOP;
  END age;
END deleteme;

调用如下:

SELECT * FROM TABLE( brianl.deleteme.age( 'Brian', 67, 3 ) );

结果:

NAME    AGE
Brian   67
Brian   68
Brian   69

这篇关于PL/SQL 函数返回自定义类型异常的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆