Results 1 to 6 of 6

Thread: PL/SQL Error

  1. #1
    Join Date
    Apr 2006
    Posts
    6

    Unanswered: PL/SQL Error

    Hi,

    I am using functions in my select statement in a cursor. And further bulk collect and insert data after certain derivations.

    Now the problem is that while executing the code, I am getting the error:
    ----------------------------------------------------------------------
    ORA-06550: line 370, column 4:
    PLS-00103: Encountered the symbol "CURSOR" when expecting one of the following:

    begin function package pragma procedure form
    ----------------------------------------------------------------------

    Can anybody please let me know the exact cause for this error?

    The structure of the code is something like as below (Have removed major chunk as it there is a limit!!):

    DECLARE

    -----------------------------------------------------------------
    -- DECLARE VARIABLES
    -----------------------------------------------------------------
    ln_months_in_arrears NUMBER := 0;
    last_run_date VARCHAR2(10);

    -----------------------------------------------------------------
    -- DECLARE GLOBALS
    -----------------------------------------------------------------
    gb_defaulted BOOLEAN := FALSE;

    -----------------------------------------------------------------
    -- TYPE DECLARATION
    -----------------------------------------------------------------

    TYPE ntt_credit_bal_ind IS TABLE OF XIBU_W_05_EXPERT.exp_credit_bal_ind%TYPE;
    TYPE ntt_name_address IS TABLE OF XIBU_W_05_EXPERT.exp_name_address%TYPE;

    ------------------------------------------------------------------
    -- DECLARE ARRAYS
    ------------------------------------------------------------------

    lv_credit_bal_ind_array ntt_credit_bal_ind;
    lv_name_address_array ntt_name_address;

    -------------------------------------------------------------------
    -- FUNCTION f_get_name_address TO GET NAME AND ADDRESS
    -------------------------------------------------------------------

    FUNCTION f_get_name_address(pv_cst_title IN validation_table.vt_short_desc%TYPE,
    pv_cst_initial IN XIHI_D_CUSTOMER.cst_initials%TYPE,
    pv_cst_first_name IN XIHI_D_CUSTOMER.cst_first_name%TYPE,
    pv_cst_middle_name IN XIHI_D_CUSTOMER.cst_middle_names%TYPE,
    pv_cst_last_name IN XIHI_D_CUSTOMER.cst_last_name%TYPE,
    pv_cst_corr_address1 IN XIHI_D_CUSTOMER.cst_corr_address_1%TYPE,
    pv_cst_corr_address2 IN XIHI_D_CUSTOMER.cst_corr_address_2%TYPE,
    pv_cst_corr_address3 IN XIHI_D_CUSTOMER.cst_corr_address_3%TYPE,
    pv_cst_corr_address4 IN XIHI_D_CUSTOMER.cst_corr_address_4%TYPE,
    pv_cst_corr_address_pcode IN XIHI_D_CUSTOMER.cst_corr_address_pcode%TYPE
    )
    RETURN VARCHAR2 IS
    lv_initials XIHI_D_CUSTOMER.cst_initials%TYPE;
    lv_new_initial XIHI_D_CUSTOMER.cst_initials%TYPE;
    lv_name VARCHAR2(200);
    lv_address VARCHAR2(200);
    lv_return VARCHAR2(200);
    BEGIN
    lv_new_initial := pv_cst_initial;

    IF pv_cst_first_name IS NOT NULL AND pv_cst_first_name <> ' ' THEN
    lv_initials := REPLACE( SUBSTR( pv_cst_initial,2), ' ');
    ELSE
    lv_initials := REPLACE( SUBSTR( pv_cst_initial,1), ' ');
    END IF;

    IF NVL( LENGTH( lv_initials), 0) >= 1 THEN
    FOR i IN 1 .. LENGTH( lv_initials)
    LOOP
    IF ASCII( SUBSTR( lv_initials, i, 1)) BETWEEN 65 AND 90 OR
    ASCII( SUBSTR( lv_initials, i, 1)) BETWEEN 97 AND 122 THEN

    IF LENGTH( lv_new_initial) >= 1 THEN
    lv_new_initial := lv_new_initial||' ';
    END IF;

    lv_new_initial := lv_new_initial||SUBSTR( lv_initials, i, 1);

    IF LENGTH( lv_new_initial) >= 2 THEN
    EXIT;
    END IF;
    END IF;
    END LOOP;
    END IF;

    IF lv_new_initial IS NOT NULL THEN
    lv_new_initial := lv_new_initial||' ';
    END IF;

    lv_name := pv_cst_title||' '||pv_cst_first_name||' '||lv_new_initial||pv_cst_last_name;
    lv_name := RPAD( SUBSTR( lv_name, 1, 38), 38, ' ');

    lv_address := RPAD( NVL( pv_cst_corr_address1, ' '), 32, ' ')||
    RPAD( NVL( pv_cst_corr_address2, ' '), 32, ' ')||
    RPAD( NVL( pv_cst_corr_address3, ' '), 32, ' ')||
    RPAD( NVL( pv_cst_corr_address4, ' '), 32, ' ')||
    RPAD( NVL( pv_cst_corr_address_pcode, ' '), 8, ' ');

    lv_return := lv_name||' '||lv_address;
    RETURN lv_return;
    EXCEPTION WHEN OTHERS THEN
    RETURN NULL;
    END f_get_name_address;

    ---------------------------------------------------------------
    -- MAIN CURSOR TO GET ALL DATA
    ---------------------------------------------------------------

    CURSOR lcu_get_all_data IS
    SELECT
    CASE
    WHEN NVL(xikb.acc_pe_curr_true_balance, 0) >= 0 THEN
    ' '
    ELSE
    '-'
    END AS credit_bal_ind,
    f_get_name_address(vt9.vt_short_desc,
    xihi.cst_initials,
    xihi.cst_first_name,
    xihi.cst_middle_names,
    xihi.cst_last_name,
    xihi.cst_corr_address_1,
    xihi.cst_corr_address_2,
    xihi.cst_corr_address_3,
    xihi.cst_corr_address_4,
    xihi.cst_corr_address_pcode) AS name_address
    FROM
    --
    WHERE
    --

    BEGIN
    SELECT TO_CHAR(ctl_date,'DD/MM/YYYY')
    INTO last_run_date
    FROM CONTROL_DATE
    WHERE ctl_name = '05-TEST';

    OPEN lcu_get_all_data;
    LOOP
    FETCH lcu_get_all_data BULK COLLECT INTO
    lv_credit_bal_ind_array,
    lv_name_address_array
    LIMIT 700;

    FOR i IN 1..ln_soc_seqno_array.COUNT
    LOOP
    -- CHANGE DATA HERE ! --

    END LOOP;

    FORALL i IN 1..ln_soc_seqno_array.COUNT
    -- INSERT DATA ! --
    INSERT INTO XIBU_W_05_EXPERT (
    exp_credit_bal_ind,
    exp_name_address
    )
    VALUES (
    lv_credit_bal_ind_array(i),
    lv_name_address_array(i)
    );

    END LOOP;
    EXIT WHEN lcu_get_all_data%NOTFOUND;
    END LOOP;
    CLOSE lcu_get_all_data;

    END;

    Please help!

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    For beginning, rearrange this part of the code (last few lines in your example):
    Code:
    END LOOP;
    EXIT WHEN lcu_get_all_data%NOTFOUND;
    END LOOP;
    - Move EXIT statement right after FETCH statement (that's only a suggestion; the code would compile wherever you put this line)
    - remove one of the END LOOP statements (you have 2 loops here, but three END LOOPs)

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    It is a PL/SQL rule that local function and procedure declarations must go last in the DECLARE section. I don't know why. So move the function declaration to below the cursor declaration.

  4. #4
    Join Date
    Apr 2006
    Posts
    6
    Hi Tony,

    Thanks for the reply. But then would it allow me to use the function in the SELECT statement?

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I hadn't realised you were doing that. You can't: a SELECT statement may only use functions that have been stored in the database (either stand-alone or in a a package). So you can either do that, or move the function call out of the cursor and into the main body of code.

  6. #6
    Join Date
    Apr 2006
    Posts
    6
    Many Thanks Tony.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •