我的函数出现以下错误(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=>':'))
型
2条答案
按热度按时间ahy6op9u1#
您正在将两个标量列提取到一个对象类型中。Oracle不知道如何将这些列Map到该对象类型。您必须在查询中使用对象构造函数:
字符串
i2byvkas2#
好的,我已经重写了我的查询和一切工作现在.感谢您的帮助:)
字符串