oracle PL/SQL ORA-00932:不一致的数据库:预期-得到-

k5hmc34c  于 11个月前  发布在  Oracle
关注(0)|答案(2)|浏览(130)

我的函数出现以下错误(ORA-00932:inconsistent databases:expected - got -)任何人都可以帮助我,我做错了什么?
我将感激任何建议。
亲切问候
耶戈尔

CREATE TABLE TEST_TABLE (ID NUMBER, COLUMN1 VARCHAR2(200));

Insert into TEST_TABLE(ID, COLUMN1) Values(1,'200:300');
Insert into TEST_TABLE(ID, COLUMN1) Values(2,'5');

create or replace TYPE  "DUMMY_RECORD_TEST" as object (
    ID                           NUMBER,
    COLUMN_LIST                  varchar2(100))

create or replace TYPE  "DUMMY_TABLE_TEST" as table of DUMMY_RECORD_TEST

FUNCTION DUMMYFUNCTION(
    p_id_name             in VARCHAR2,
    p_column_name         in VARCHAR2,
    p_tablename           in VARCHAR2,
    p_type                in VARCHAR2,
    p_delimter            in VARCHAR2
)
return DUMMY_TABLE_TEST

as 

sql_qry     VARCHAR2(4000);
csr         SYS_REFCURSOR;  
dummy_records DUMMY_TABLE_TEST;

BEGIN   

    sql_qry := 'Select DUMMY_RECORD_TEST(' || p_id_name || ',
      trim(regexp_substr(' || p_column_name || ',''[^:]+'', 1, lines.column_value)))
        From ' || p_tablename || ',
             Table (CAST (Multiset( Select 
                                        Level 
                                    From dual 
                                    Connect by instr(' || p_column_name || ','':'', 1, Level - 1) > 0
                        ) as sys.ODCIVARCHAR2LIST 
                    )
                ) lines fetch first 1 rows only';

    Open csr For sql_qry;
    Loop
        Fetch csr Bulk collect into dummy_records Limit 3;
            Exit When csr%NOTFOUND;
        End Loop;
        Close csr;
        return dummy_records;
    END DUMMYFUNCTION;

字符串
我的选择声明

Select 
     * 
 From table(DUMMYFUNCTION(p_id_name=>'ID', p_column_name=>'COLUMN1', p_tablename=>'TEST_TABLE', p_type=>'ODCIVARCHAR2LIST', p_delimter=>':'))

ahy6op9u

ahy6op9u1#

您正在将两个标量列提取到一个对象类型中。Oracle不知道如何将这些列Map到该对象类型。您必须在查询中使用对象构造函数:

sql_qry := 'Select DUMMY_RECORD_TEST(' || p_id_name || ',
  trim(regexp_substr(' || p_column_name || ',''[^:]+'', 1, lines.column_value))
                                    )
    From ' || p_tablename || ',

字符串

i2byvkas

i2byvkas2#

好的,我已经重写了我的查询和一切工作现在.感谢您的帮助:)

CREATE TABLE TEST_TABLE (ID NUMBER, COLUMN1 VARCHAR2(200));

Insert into TEST_TABLE(ID, COLUMN1) Values(1,'200:300');
Insert into TEST_TABLE(ID, COLUMN1) Values(2,'5');

create or replace TYPE  "DUMMY_RECORD_TEST" as object (
    ID                           NUMBER,
    COLUMN_LIST                  varchar2(100))

create or replace TYPE  "DUMMY_TABLE_TEST" as table of DUMMY_RECORD_TEST

FUNCTION DUMMYFUNCTION(
    p_id_name             in VARCHAR2,
    p_column_name         in VARCHAR2,
    p_tablename           in VARCHAR2,
    p_type                in VARCHAR2,
    p_delimter            in VARCHAR2
)
return DUMMY_TABLE_TEST

as 
  
dummy_records DUMMY_TABLE_TEST;

BEGIN   

      Execute immediate
      'Select DUMMY_RECORD_TEST(' || p_id_name || ',
                trim(regexp_substr(' || p_column_name || ',''[^:]+'', 1, lines.column_value)))
       From TEST_TABLE,
       Table (CAST (Multiset( Select 
                                   Level 
                              From dual 
                              Connect by instr(' || p_column_name || ',''' || p_delimter || ''', 1, Level - 1) > 0
                    ) as sys.' || p_type ||' 
                )
            ) lines'
            Bulk collect into dummy_records; 

       For indx in 1 .. dummy_records.COUNT
       Loop 
          Pipe row (dummy_records (indx));
       End Loop;
END DUMMYFUNCTION;

字符串

相关问题