#oracle #plsql #ora-00932 #ora-06512
#Oracle #plsql #ora-00932 #ora-06512
Вопрос:
Я получаю сообщение об ошибке непоследовательного типа данных, и я не уверен, почему. Мне нужны некоторые рекомендации, чтобы разобраться в этом.
Я создаю два типа как:
В моей таблице юниверса есть следующие столбцы с типом столбца:
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;
Я пытался отладить проблему, но все еще не могу найти выход и был бы признателен, если сообщество SO сможет помочь.
Комментарии:
1. есть какие-либо подсказки или предложения относительно того, что может вызывать эти проблемы?
2. Что такое тип данных
UNIVERSE.PON?3. У LONG есть куча проблем, вероятно, вам следует использовать что-то вроде varchar2 (32767) для E_SQL.
4. @jonearles — почему у long какие-то проблемы?
5. @Rachel: Длинные значения были заменены на LOBs, и они не обновлялись в течение десятилетия или двух. У них много ограничений, на этой странице есть несколько примеров: oracle-developer.net/display.php?id=430
Ответ №1:
Сначала я написал ответ, пытаясь воспроизвести вашу ошибку, но вы немного изменили свой вопрос, поэтому я начинаю снова с нуля.
Сначала несколько замечаний:
- По вашему собственному признанию, вы новичок в 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
Здесь проблема возникает из-за того, что в вашем операторе select количество столбцов не совпадает с количеством полей в вашей записи. Вы можете использовать %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;
Комментарии:
1. Я делал нечто подобное. Можете ли вы предложить несколько указаний, на которые мне следует обратить особое внимание при попытке найти проблему здесь?
2. @Rachel: можете ли вы предоставить полный тестовый пример, в котором это действительно приводит к ORA-00932?
3.
select * from table(test_comp_report_func('01/01/2000','12/31/2009', null))если вы видите мою функцию, она принимает начальную дату, конечную дату и год, и в запросе я нигде не использую значение year. Также, когда я запускаю запрос непосредственно из редактора разработчика sql, я могу получить данные, но когда я вызываю функцию с помощью инструкции select выше, это выдает мне указанную ошибку.4. Позвольте мне обновить вопрос фактическими значениями, которые я использую, чтобы вы могли воспроизвести ошибку на своей стороне.
5. Я обновил вопрос и теперь получаю ошибку ORA-00932 в
LOOPпосле открытия rec_cursor, не уверен, почему? Есть предложения.
Ответ №2:
Вы получаете ошибку, потому что 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 ) или неправильно указывать тип этой переменной. Для меня все это делается автоматически.
Комментарии:
1. Спасибо Luke за информацию. Как я упоминал в другом комментарии, я довольно новичок в PL / SQL и поэтому не знаю, каковы наилучшие методы и какой из них использовать в этом случае, но это, безусловно, помогло бы и помогло бы добавить некоторые знания.