Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2012
    Posts
    5

    Unanswered: Cursor within a cursor

    I am trying to write a PL/SQL anonymous block that accepts an employee’s last name (last_name) from the user input and displays employee’s information.
    but If the employee’s last name is NOT in the PERSON table,my block should display all employee information. I have used nested cursors but I am recieving an error "PLS-00320: the declaration of the type of this expression is incomplete or malformed"
    Any help would be appreciated!!
    My Code is as follows.

    ACCEPT p_1 PROMPT 'Please enter the Last Name of the Person'

    DECLARE

    v_person_name person.last_name%TYPE := '&p_1';

    CURSOR per_cur IS
    Select first_name , last_name , emp_type , NVL(to_char(license_expiration_date , 'DD-MM-YYYY') , 'N/A')
    from person full outer join pilot on person.emp_id = pilot.emp_id;

    v_first_name person.first_name%TYPE;
    v_last_name person.last_name%TYPE;
    v_emp_type person.emp_type%TYPE;
    v_license_exp CHAR(15);

    CURSOR per_cur1 IS
    Select first_name , last_name , emp_type, NVL(to_char(license_expiration_date , 'DD-MM-YYYY') , 'N/A')
    into v_first_name1 , v_last_name1 , v_emp_type1 , v_license_exp1
    from person full outer join pilot on person.emp_id = pilot.emp_id where UPPER(last_name) = UPPER(v_person_name);

    v_first_name1 person.first_name%TYPE;
    v_last_name1 person.last_name%TYPE;
    v_emp_type1 person.emp_type%TYPE;
    v_license_exp1 CHAR(15);
    v_count NUMBER(2);

    BEGIN
    SELECT COUNT(*) into
    v_count from person where UPPER(last_name) = UPPER(v_person_name);

    OPEN per_cur;

    fetch per_cur into v_first_name , v_last_name , v_emp_type , v_license_exp;
    IF v_count = 0 THEN
    open per_cur1;

    fetch per_cur1 into v_first_name1 , v_last_name1 , v_emp_type1 , v_license_exp1;
    IF per_cur1%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME TYPE LICENSE EXPIRATION DATE');
    DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------');

    DBMS_OUTPUT.PUT_LINE(v_first_name1 ||' ' ||v_last_name1 || lpad(v_emp_type1 , 15) || lpad(v_license_exp1 , 20));
    END IF;
    close per_cur1;

    ELSE
    IF per_cur%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME TYPE LICENSE EXPIRATION DATE');
    DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------');

    WHILE per_cur%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE(v_first_name ||' ' ||v_last_name || lpad(v_emp_type , 15) || lpad(v_license_exp , 20));
    END LOOP;

    fetch per_cur into v_first_name , v_last_name , v_emp_type , v_license_exp;

    END IF;
    END IF;
    CLOSE per_cur;

    END;

    I fail to understand where exactly have I gone wrong
    Last edited by Gary12; 08-05-12 at 21:16.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    which line throws the error?
    COPY & PASTE the whole session back here

    Since we don't have your tables or data, we can not compile, run or test posted code.
    It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
    It would be helpful if you provided DML (INSERT INTO ...) for test data.
    It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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