Проблема с курсором в хранимой процедуре
В приведенном ниже теле пакета Oracle Oracle я получаю сообщение об ошибке "SQL Statement игнорируется" и не может определить причину. Пожалуйста, смотрите инструкцию SELECT dborLoans_cursor в PROCEDURE updateLoansInLdcTab в приведенной ниже инструкции:
create or replace
PACKAGE body PK_DBORUFT_SYNC AS
PROCEDURE mainProg(v_beginDate DATE, v_endDate DATE) IS
BEGIN
updateLoansInLdcTab(v_beginDate,v_endDate);
END mainProg;
FUNCTION searchForLdcToUpdate(v_beginDate DATE, v_endDate DATE) RETURN type_ref_cursor IS
v_ldcLoan type_ref_cursor;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
OPEN v_ldcLoan FOR
SELECT loan_id
FROM ldc.ldc_rel_tab
WHERE loan_id NOT IN
(SELECT loan_id
FROM dbor.vw_ldc_data dbor
WHERE dbor.closing_agent IS NOT NULL
AND dbor.closing_agent_phone IS NOT NULL
AND dbor.lock_expiration_date > sysdate)
AND TO_CHAR(request_date, 'MM/DD/YYYY') >= v_beginDate
AND TO_CHAR(request_date, 'MM/DD/YYYY') <= v_endDate;
RETURN v_ldcLoan;
END searchForLdcToUpdate;
PROCEDURE updateLoansInLdcTab(v_beginDate DATE, v_endDate DATE) is
TYPE dborLdcData IS TABLE OF dbor.vw_ldc_data%ROWTYPE;
v_ldcLoan_type_rec type_ref_cursor;
v_ldcLoanCursor_type ldcLoanCursor_type;
dborReference dborLdcData;
v_LDC_LOANID VARCHAR2(10);
v_LOAN_ID VARCHAR2(10);
v_BANKLINE VARCHAR2(20);
v_CHANNEL VARCHAR2(20);
v_PROPERTY_TYPE VARCHAR2(10);
v_STATE VARCHAR2(2);
v_STREET_NAME VARCHAR2(64);
v_FIRST_NAME VARCHAR2(64);
v_LAST_NAME VARCHAR2(64);
v_CLOSING_AGENT VARCHAR2(50);
v_CLOSING_AGENT_PHONE VARCHAR2(15);
v_REGION_CODE VARCHAR2(20);
v_CLPP_FLAG VARCHAR2(1);
v_INSTRUMENT_NAME VARCHAR2(30);
v_BROKER_OFFICER VARCHAR2(30);
v_COST_CENTER VARCHAR2(10);
v_PREPARED_BY VARCHAR2(30);
v_BUYPRICE NUMBER(9,4);
v_SRP NUMBER(8,3);
v_TOTAL_BUYPRICE NUMBER(9,4);
v_TOTAL_SRP NUMBER(8,3);
v_BRANCH_NAME VARCHAR2(30);
v_LOCK_EFFECTIVEDATE DATE;
dbor_count NUMBER;
CURSOR dborLoans_cursor IS
SELECT P.loan_id,
P.property_type,
P.state,
P.street_name,
P.close_date,
P.loan_purpose,
P.borrower_last_name,
P.borrower_first_name,
P.closing_agent,
P.closing_agent_phone,
P.region_code,
P.clpp,
P.instrument_name,
P.broker_officer,
P.lock_effective_date,
P.channel,
NVL(P.buyprice, 0) buyPrice
FROM dbor.vw_ldc_data P
LEFT JOIN dbor.wlnprogram W
ON upper(P.instrument_name) = W.ln_pgm
LEFT JOIN
(SELECT A.loan_id FROM ldc.ldc_rel_tab A WHERE A.ldc_status='LDC_PENDING'
) pend ON pend.loan_id = p.loan_id
LEFT JOIN
(SELECT DISTINCT A.loan_id
FROM ldc.ldc_rel_tab A, ldc.ldc_request_rel_tab B
WHERE A.ldc_status IN ('LDC_PENDING', 'DISBURSED','COMPLETE','RECON_PENDING','SUBMITTED','DISBURSEPAYOFF','VOIDREQUEST','FUNDS_REQUESTED')
AND A.ldc_id = B.ldc_id
AND (B.funding_reason = 'DL Payoff' OR B.funding_reason ='Original Disbursement')
) disbursed ON disbursed.loan_Id = p.loan_id
LEFT JOIN
(SELECT name, phone, agent_id, street1, city, zip, state FROM dbor.WCLOS_AGNT) wagnt
ON wagnt.agent_id=p.loan_id
LEFT JOIN
(SELECT loan,
company_name,
phone_phn,
street,
city,
zip_code,
state
FROM DBOR.WLOAN_PARTY
WHERE type='4'
) wloan ON wloan.loan =p.loan_id
WHERE P.closing_agent IS NOT NULL
AND p.loan_id not in (SELECT loan_id FROM ldc.ldc_rel_tab)
AND P.closing_agent_phone IS NOT NULL
AND P.lock_expiration_date > sysdate ;
v_dborLdcData dbor.vw_ldc_data%ROWTYPE;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
dborReference := dborLdcData();
v_ldcLoanCursor_type := searchForLdcToUpdate(v_beginDate, v_endDate);
dbor_count := 0;
WHILE dborLoans_cursor%FOUND LOOP
FETCH dborLoans_cursor INTO v_dborLdcData;
dbor_count := dbor_count + 1;
v_LOAN_ID := v_dborLdcData.LOAN_ID;
v_PROPERTY_TYPE := v_dborLdcData.property_type;
v_STATE := v_dborLdcData.state;
v_STREET_NAME := v_dborLdcData.street_name;
v_LAST_NAME := v_dborLdcData.borrower_last_name;
v_FIRST_NAME := v_dborLdcData.borrower_first_name;
v_CLOSING_AGENT := v_dborLdcData.closing_agent;
v_CLOSING_AGENT_PHONE := v_dborLdcData.closing_agent_phone;
v_CLPP_FLAG := v_dborLdcData.clpp;
v_INSTRUMENT_NAME := v_dborLdcData.INSTRUMENT_NAME;
v_BROKER_OFFICER := v_dborLdcData.BROKER_OFFICER;
v_CHANNEL := v_dborLdcData.CHANNEL;
EXECUTE IMMEDIATE 'SELECT region_code FROM dbor.Branch WHERE branch_name = '||v_dborLdcData.CHANNEL INTO v_REGION_CODE;
EXECUTE IMMEDIATE 'SELECT cost_center FROM ldc.REF_BANKLINE_REL WHERE channel = '||v_dborLdcData.CHANNEL INTO v_COST_CENTER;
EXECUTE IMMEDIATE 'SELECT bankline FROM ldc.REF_BANKLINE_REL WHERE channel = '||v_dborLdcData.CHANNEL INTO v_BANKLINE;
v_LOCK_EFFECTIVEDATE := v_dborLdcData.lock_effective_date;
v_BUYPRICE := v_dborLdcData.buyPrice;
LOOP
FETCH v_ldcLoan_type_rec INTO v_LDC_LOANID;
EXECUTE IMMEDIATE
'update ldc.ldc_rel_tabtest
set loan_id = ' ||''''|| v_LOAN_ID||''''||
',bankline = ' ||''''|| v_BANKLINE||''''||
',channel = ' ||''''||v_CHANNEL||''''||
',PROPERTY_TYPE= ' ||''''||v_PROPERTY_TYPE||''''||
',STATE = ' ||''''||v_STATE||''''||
',STREET_NAME = ' ||''''||v_STREET_NAME||''''||
',BORROWER_NAME = ' ||''''||v_LAST_NAME||','||''''||v_FIRST_NAME||''''||
',CLOSING_AGENT = ' ||''''||v_CLOSING_AGENT||''''||
',CLOSING_AGENT_PHONE = ' ||''''||v_CLOSING_AGENT_PHONE||''''||
',REGION_CODE = ' ||''''||v_REGION_CODE||''''||
',CLPP_FLAG = ' ||''''||v_CLPP_FLAG||''''||
',INSTRUMENT_NAME = ' ||''''||v_INSTRUMENT_NAME||''''||
',BROKER_OFFICER = ' ||''''||v_BROKER_OFFICER||''''||
',COST_CENTER = ' ||''''||v_COST_CENTER||''''||
',BUYPRICE = ' ||v_BUYPRICE ||
' where loan_id = ' ||v_LDC_LOANID;
END LOOP;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END updateLoansInLdcTab;
END PK_DBORUFT_SYNC;
2 ответа
Решение
После быстрого взгляда на это (без запуска), я бы обнаружил ошибки в строках ниже, так как вы не ставили кавычки вокруг значения, которое объединяется в:
EXECUTE IMMEDIATE 'SELECT region_code FROM dbor.Branch WHERE branch_name = '||v_dborLdcData.CHANNEL INTO v_REGION_CODE;
EXECUTE IMMEDIATE 'SELECT cost_center FROM ldc.REF_BANKLINE_REL WHERE channel = '||v_dborLdcData.CHANNEL INTO v_COST_CENTER;
EXECUTE IMMEDIATE 'SELECT bankline FROM ldc.REF_BANKLINE_REL WHERE channel = '||v_dborLdcData.CHANNEL INTO v_BANKLINE;
Тем не менее, из приведенных выше комментариев вы, кажется, получили это работает.
Большая проблема в этом коде заключается в том, почему вы вообще используете execute немедленно? Вам это не нужно, и, что еще хуже, вы создали немасштабируемый код, поскольку три выполняющих немедленных оператора выше и большое обновление не используют переменные связывания. В PLSQL, если вы избегаете использования execute_immediate, вам вообще не нужно беспокоиться о связывании, PLSQL делает все это за вас автоматически.
Попробуйте преобразовать команду "Выполнить немедленный выбор" во что-то вроде:
select region_code
into v_region_code
from dbor.branch
where branch_name = v_dborLdcData.CHANNEL;
Это поможет найти и исправить проблему с переменной связывания. Затем сделайте то же самое с обновлением:
update ldc.ldc_rel_tabtest
set loan_id = v_LOAN_ID
bankline = v_bank_line
...
...
where loan_id = v_LDC_LOANID;
Код будет проще, легче находить ошибки в SQL и более масштабируемым.