Cursor issue with Stored Procedure

In the below oracle sql package body, I receive a "SQL Statement ignored" error and cannot determine the cause. Please see the SELECT statement of the dborLoans_cursor in the PROCEDURE updateLoansInLdcTab in the statement below:

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;

---

After quick glance at this (without running it), I would have though there are errors on the lines below as you have not put quotes around the value which is being concatenated in:

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;

However, from the comments above you seem to have gotten it working.

The bigger problem in this code is why are you using execute immediate at all? You don't need to, and worse you have created non scalable code as the three execute immediate statements above and the big update are not using bind variables. In PLSQL if you avoid using execute_immediate, you don't need to worry about binds at all, PLSQL does it all for you automagically.

Try converting the execute immediate select into something like:

select region_code
into v_region_code
from dbor.branch
where branch_name = v_dborLdcData.CHANNEL;

That will work find and fix the bind variable problem. Then do the same to the update:

update ldc.ldc_rel_tabtest 
set loan_id = v_LOAN_ID
    bankline = v_bank_line
    ...
    ...
where loan_id = v_LDC_LOANID;

The code will be simpler, easier to find errors in the SQL and more scalable.

---

Problem was due to missing grant on DBOR.WLOAN_PARTY.