Oracle: проблема с несовместимым типом данных
Я получаю противоречивое сообщение об ошибке типа данных, и я не уверен, почему. Мне нужно некоторое руководство, чтобы понять это.
Я создаю два типа как:
В моей таблице юниверса есть следующие столбцы с типом столбца:
Column Name Data Type
PON VARCHAR2(25 BYTE)
RPON VARCHAR2(25 BYTE)
SUPPLIER_NAME VARCHAR2(255 BYTE)
SUB_SUPPLIER_NAME VARCHAR2(255 BYTE)
SOURCE_NO VARCHAR2(40 BYTE)
CKR VARCHAR2(200 BYTE)
LEC_ID VARCHAR2(200 BYTE)
ICSC VARCHAR2(10 BYTE)
ACTL_ST VARCHAR2(10 BYTE)
ADW_ST VARCHAR2(10 BYTE)
PROJ_ID VARCHAR2(100 BYTE)
MOVE_TO_INV_DT DATE
IE_DT DATE
DDD_DT DATE
EFF_BILL_DT DATE
ACTION VARCHAR2(10 BYTE)
SERVICE VARCHAR2(10 BYTE)
AFP VARCHAR2(10 BYTE)
ACNA VARCHAR2(10 BYTE)
SERVICE_NAME VARCHAR2(255 BYTE)
UPLOAD_DT DATE
PROGRAM VARCHAR2(50 BYTE)
INITIATIVE_ID NUMBER
ACOST NUMBER
ACOST_IND VARCHAR2(25 BYTE)
MAPFILE VARCHAR2(100 BYTE)
Тип строки
create or replace
TYPE test_COMP_REPORT_ROW_TYPE AS OBJECT (
PON VARCHAR2(25 BYTE),
RPON VARCHAR2(25 BYTE),
VENDOR VARCHAR2(255 BYTE),
SUB_SUPPLIER VARCHAR2(255 BYTE),
SOURCE_NO VARCHAR2(40 BYTE),
ATT_CKT_ID VARCHAR2(200 BYTE),
LEC_ID VARCHAR2(200 BYTE),
ICSC VARCHAR2(10 BYTE),
STATE VARCHAR2(10 BYTE),
PROJECT_ID VARCHAR2(100 BYTE),
ACTION VARCHAR2(10 BYTE),
SERVICE_SPEED VARCHAR2(10 BYTE),
SERVICE_NAME VARCHAR(255 BYTE),
INEFFECT_DATE DATE,
EVENT_DATE DATE,
DUE_DATE DATE,
ACOST NUMBER
)
Тип вкладки
create or replace type test_COMP_REPORT_TAB_TYPE
AS TABLE OF test_COMP_REPORT_ROW_TYPE
Вот функция, которая использует этот тип:
create or replace
FUNCTION test_comp_report_func
(
start_dt_h IN VARCHAR2 DEFAULT NULL,
end_dt_h IN VARCHAR2 DEFAULT NULL,
year_h IN VARCHAR2 DEFAULT NULL )
RETURN test_comp_report_tab_type pipelined
IS
e_sql LONG;
program_v VARCHAR2(10);
v_row test_comp_report_row_type := test_comp_report_row_type(NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
);
TYPE rectyp IS REF CURSOR;
rrc_rectyp rectyp;
TYPE recordvar IS RECORD
(
PON VARCHAR2(25 BYTE),
RPON VARCHAR2(25 BYTE),
VENDOR VARCHAR2(255 BYTE),
SUB_SUPPLIER VARCHAR2(255 BYTE),
SOURCE_NO VARCHAR2(40 BYTE),
ATT_CKT_ID VARCHAR2(200 BYTE),
LEC_ID VARCHAR2(200 BYTE),
ICSC VARCHAR2(10 BYTE),
STATE VARCHAR2(10 BYTE),
PROJECT_ID VARCHAR2(100 BYTE),
ACTION VARCHAR2(10 BYTE),
SERVICE_SPEED VARCHAR2(10 BYTE),
SERVICE_NAME VARCHAR(255 BYTE),
INEFFECT_DATE DATE,
EVENT_DATE DATE,
DUE_DATE DATE,
ACOST NUMBER
);
res_rec recordvar;
BEGIN
e_sql := e_sql || 'SELECT
PON,
RPON,
SUPPLIER_NAME VENDOR,
SUB_SUPPLIER_NAME SUB_SUPPLIER,
SOURCE_NO,
CKR,
LEC_ID,
ICSC,
ACTL_ST,
ADW_ST STATE,
PROJ_ID,
ACTION,
SERVICE SPEED,
AFP,
ACNA,
SERVICE_NAME,
IE_DT,
MOVE_TO_INV_DT EVENTDAT,
DDD_DT DUEDATE,
EFF_BILL_DT,
ACOST
FROM UNIVERSE
WHERE to_date(IE_DT) between to_date(nvl(''01/01/2000'', ''01/01/2000''), ''MM/DD/YYYY'')
and to_date(nvl(''12/31/2009'', to_char(trunc(add_months(sysdate, 12),''year'')-1,''MM/DD/YYYY'')), ''MM/DD/YYYY'')
AND PROGRAM = ''T45sONNET''
AND nvl(trim(ACOST_IND), ''NULL'') not in (''INVALID-2005'')
ORDER BY ACTION';
dbms_output.put_line(e_sql);
OPEN rrc_rectyp FOR e_sql;
LOOP
FETCH rrc_rectyp INTO res_rec;
EXIT WHEN rrc_rectyp%NOTFOUND;
v_row.PON := res_rec.PON;
v_row.RPON := res_rec.RPON;
v_row.VENDOR := res_rec.VENDOR;
v_row.SUB_SUPPLIER := res_rec.SUB_SUPPLIER;
v_row.SOURCE_NO := res_rec.SOURCE_NO;
v_row.ATT_CKT_ID := res_rec.ATT_CKT_ID;
v_row.LEC_ID := res_rec.LEC_ID;
v_row.ICSC := res_rec.ICSC;
v_row.STATE := res_rec.STATE;
v_row.PROJECT_ID := res_rec.PROJECT_ID;
v_row.ACTION := res_rec.ACTION;
v_row.SERVICE_SPEED := res_rec.SERVICE_SPEED;
v_row.SERVICE_NAME := res_rec.SERVICE_NAME;
v_row.INEFFECT_DATE := res_rec.INEFFECT_DATE;
v_row.EVENT_DATE := res_rec.EVENT_DATE;
v_row.DUE_DATE := res_rec.DUE_DATE;
v_row.ACOST := res_rec.ACOST;
pipe ROW(v_row);
END LOOP;
return;
end test_comp_report_func;
Я пытался отладить проблему, но все еще не смог найти выход, и был бы признателен, если бы Сообщество могло помочь.
2 ответа
Сначала я написал ответ, пытаясь воспроизвести вашу ошибку, но вы немного изменили свой вопрос, поэтому я снова начинаю с нуля.
Сначала несколько замечаний:
- По своей собственной учетной записи вы довольно новичок в PL/SQL, но используете довольно продвинутые функции: динамический SQL, конвейерные функции, объекты SQL. Давайте попробуем начать с чего-то более простого (я покажу вам, как вы можете работать со статическим SQL, этого будет достаточно в 99,9% случаев).
- Когда вы сталкиваетесь с проблемой, вам нужно разложить код, чтобы увидеть, что работает, а что нет. Обычно это означает упрощение вашего кода до тех пор, пока он не станет настолько простым, чтобы он начал работать, а затем возвращайте сложные элементы кода по одному, пока вы снова не столкнетесь с проблемой.
- Когда вы предоставляете тестовый пример, постарайтесь сделать его как можно более простым:) Людям будет легче помочь вам, но, что более важно, в большинстве случаев создание тестового набора поможет вам найти решение самостоятельно, так как заставит вас разложить ваш сложный код (см. предыдущий пункт). Мое эмпирическое правило (FWIW) заключается в том, что код, отображаемый с полосой прокрутки (горизонтальной или вертикальной) в SO, слишком велик для тестового случая и должен быть обрезан, если это возможно.
Я запустил ваш код и получил ORA-00932 на fetch линия. Когда я заменяю SQL на статический SQL, ошибка становится более явной:
SQL> CREATE OR REPLACE FUNCTION test_comp_report_func
2 RETURN test_comp_report_tab_type
3 PIPELINED IS
4 TYPE recordvar IS RECORD(
5 PON VARCHAR2(25 BYTE),
(...snip...)
21 ACOST NUMBER);
22 res_rec recordvar;
23 v_row test_COMP_REPORT_ROW_TYPE;
24 CURSOR rrc_rectyp IS
25 SELECT PON,
(...snip...)
45 ACOST
46 FROM UNIVERSE;
48 BEGIN
49 OPEN rrc_rectyp;
50 LOOP
51 FETCH rrc_rectyp
52 INTO res_rec;
54 EXIT WHEN rrc_rectyp%NOTFOUND;
55 /*...*/
56 PIPE ROW(v_row);
57 END LOOP;
58 RETURN;
59 END test_comp_report_func;
60 /
Warning: Function created with compilation errors.
LINE/COL ERROR
-------- -----------------------------------------------------------------
51/7 PL/SQL: SQL Statement ignored
52/15 PLS-00386: type mismatch found at 'RES_REC' between FETCH cursor
and INTO variables
Здесь проблема возникает из-за того, что ваш оператор выбора не имеет такое же количество столбцов, как количество полей в вашей записи. Ты можешь использовать %rowcount чтобы предотвратить это:
CREATE OR REPLACE FUNCTION test_comp_report_func
RETURN test_comp_report_tab_type
PIPELINED IS
v_row test_COMP_REPORT_ROW_TYPE;
CURSOR rrc_rectyp IS
SELECT PON, RPON, SUPPLIER_NAME VENDOR, SUB_SUPPLIER_NAME SUB_SUPPLIER,
SOURCE_NO, CKR, LEC_ID, ICSC, ACTL_ST, ADW_ST STATE, PROJ_ID,
ACTION, SERVICE SPEED, AFP, ACNA, SERVICE_NAME, IE_DT,
MOVE_TO_INV_DT EVENTDAT, DDD_DT DUEDATE, EFF_BILL_DT, ACOST
FROM UNIVERSE;
res_rec rrc_rectyp%ROWTYPE;
BEGIN
OPEN rrc_rectyp;
LOOP
FETCH rrc_rectyp
INTO res_rec;
EXIT WHEN rrc_rectyp%NOTFOUND;
v_row.pon := res_rec.pon;
/*...*/
PIPE ROW(v_row);
END LOOP;
RETURN;
END test_comp_report_func;
Вы даже можете получить объект SQL напрямую (с неявным курсором):
CREATE OR REPLACE FUNCTION test_comp_report_func
RETURN test_comp_report_tab_type
PIPELINED IS
BEGIN
FOR res_rec IN (SELECT test_comp_report_row_type(PON, RPON, SUPPLIER_NAME,
SUB_SUPPLIER_NAME,SOURCE_NO,
CKR, LEC_ID, ICSC, ACTL_ST,
PROJ_ID, ACTION, SERVICE,
SERVICE_NAME, IE_DT, DDD_DT,
EFF_BILL_DT, ACOST)my_object
FROM UNIVERSE) LOOP
PIPE ROW(res_rec.my_object);
END LOOP;
RETURN;
END test_comp_report_func;
Вы получаете ошибку, потому что запрос SQL в e_sql возвращает еще четыре значения, чем в res_rec, Курсор возвращает 21 столбец данных, но ваш recordvar Тип записи содержит только 17 полей.
Это выглядит как колонны ACTL_ST, AFP, ACNA а также EFF_BILL_DT не сопоставлять ни с чем в res_recи если вы удалите их из запроса, вы обнаружите, что ваша функция больше не сообщает inconsistent datatypes ошибка.
Я бы, наверное, реализовал функцию примерно так:
CREATE OR REPLACE FUNCTION test_comp_report_func_2 (
start_dt_h IN VARCHAR2 DEFAULT NULL,
end_dt_h IN VARCHAR2 DEFAULT NULL,
year_h IN VARCHAR2 DEFAULT NULL
) RETURN test_comp_report_tab_type PIPELINED
IS
CURSOR cur_res_rec IS
SELECT PON,
RPON,
SUPPLIER_NAME VENDOR,
SUB_SUPPLIER_NAME SUB_SUPPLIER,
SOURCE_NO,
CKR ATT_CKT_ID,
LEC_ID,
ICSC,
ACTL_ST,
ADW_ST STATE,
PROJ_ID AS PROJECT_ID,
ACTION,
SERVICE SERVICE_SPEED,
AFP,
ACNA,
SERVICE_NAME,
IE_DT INEFFECT_DATE,
MOVE_TO_INV_DT EVENT_DATE,
DDD_DT DUE_DATE,
EFF_BILL_DT,
ACOST
FROM UNIVERSE
WHERE TO_DATE(IE_DT) BETWEEN TO_DATE(NVL('01/01/2000', '01/01/2000'), 'MM/DD/YYYY')
AND TO_DATE(NVL('12/31/2009', TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE, 12),'year') - 1,'MM/DD/YYYY')), 'MM/DD/YYYY')
AND PROGRAM = 'T45sONNET'
AND NVL(TRIM(ACOST_IND), 'NULL') NOT IN ('INVALID-2005')
ORDER BY ACTION;
v_row test_comp_report_row_type := test_comp_report_row_type(NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
BEGIN
FOR res_rec IN cur_res_rec
LOOP
v_row.PON := res_rec.PON;
v_row.RPON := res_rec.RPON;
v_row.VENDOR := res_rec.VENDOR;
v_row.SUB_SUPPLIER := res_rec.SUB_SUPPLIER;
v_row.SOURCE_NO := res_rec.SOURCE_NO;
v_row.ATT_CKT_ID := res_rec.ATT_CKT_ID;
v_row.LEC_ID := res_rec.LEC_ID;
v_row.ICSC := res_rec.ICSC;
v_row.STATE := res_rec.STATE;
v_row.PROJECT_ID := res_rec.PROJECT_ID;
v_row.ACTION := res_rec.ACTION;
v_row.SERVICE_SPEED := res_rec.SERVICE_SPEED;
v_row.SERVICE_NAME := res_rec.SERVICE_NAME;
v_row.INEFFECT_DATE := res_rec.INEFFECT_DATE;
v_row.EVENT_DATE := res_rec.EVENT_DATE;
v_row.DUE_DATE := res_rec.DUE_DATE;
v_row.ACOST := res_rec.ACOST;
PIPE ROW(v_row);
END LOOP;
RETURN;
END test_comp_report_func_2;
/
Во-первых, я не могу честно увидеть причину, по которой вы используете динамический SQL. Вышеприведенная функция использует "статический" SQL-запрос и имеет преимущество в том, что Oracle проверяет, что этот запрос действителен при компиляции функции. Если с запросом произошла ошибка, функция не скомпилируется. С другой стороны, если у вас есть ошибка с динамическим запросом SQL, вы не обнаружите проблему, пока не запустите свою функцию.
Динамический SQL полезен, если вы хотите изменить структуру запроса, например, запустить его в разных таблицах или изменить столбцы, используемые в WHERE пункт. Тем не менее, в большинстве случаев вам не нужно это делать. Динамический SQL - это одна из тех вещей, которые вам действительно не следует использовать, если вам не нужно их использовать.
Кроме того, используя FOR some_record IN some_cursorМне не нужно возиться с открытием и закрытием курсора, и при этом мне не нужно проверять, остались ли еще какие-либо данные, и выходить из цикла, если это так. Это также исключает необходимость объявления переменной для записи строки (res_rec) или неправильно указали тип этой переменной. Это все сделано автоматически для меня.