Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Sep 2009
    Posts
    22

    Question Unanswered: Need help AGAIN... please.

    This is the problem...

    - Create a PL/SQL anonymous block program unit that displays the activity attribute value listed in the club_activity table along with the name of the clubs in the sporting_clubs table that provide the activity.

    Partial display of output is shown below:

    Hiking
    ---------
    Hillside mountain club
    Branson climbing club

    Canoeing
    -------------
    Cherokee rafting club
    white plains club




    This is what I currently have below but it is not executing correctly. Could someone help me? I would greatly appreciate it!




    SET SERVEROUTPUT ON

    DECLARE
    CURSOR activity_cursor IS
    SELECT DISTINCT activity
    FROM club_activity;
    activity_row activity_cursor%rowtype;
    CURSOR name_cursor IS
    SELECT club_id, name
    FROM sporting_clubs;
    name_row name_cursor%rowtype;
    activity_name club_activity.activity%type;
    club_name sporting_clubs.name%type;
    BEGIN
    OPEN activity_cursor;
    FETCH activity_cursor INTO activity_row;
    EXIT WHEN activity_cursor%notfound;
    IF activity = 'Hiking' THEN
    LOOP
    FETCH name_cursor INTO name_row;
    EXIT WHEN name_cursor%notfound;
    dbms_output.put_line (activity_row.activity);
    dbms_output.put_line (---------------------);
    IF activity_row.club_id = name_row.club_id THEN
    dbms_output.put_line (name_row.name);

    END IF;
    END IF;
    END LOOP;
    IF activity = 'Climbing' THEN
    LOOP
    FETCH name_cursor INTO name_row;
    EXIT WHEN name_cursor%notfound;
    dbms_output.put_line (activity_row.activity);
    dbms_output.put_line (---------------------);
    IF activity_row.club_id = name_row.club_id THEN
    dbms_output.put_line (name_row.name);

    END IF;
    END IF;
    END LOOP;
    IF activity = 'Walking' THEN
    LOOP
    FETCH name_cursor INTO name_row;
    EXIT WHEN name_cursor%notfound;
    dbms_output.put_line (activity_row.activity);
    dbms_output.put_line (---------------------);
    IF activity_row.club_id = name_row.club_id THEN
    dbms_output.put_line (name_row.name);

    END IF;
    END IF;
    END LOOP;
    IF activity = 'Conservation' THEN
    LOOP
    FETCH name_cursor INTO name_row;
    EXIT WHEN name_cursor%notfound;
    dbms_output.put_line (activity_row.activity);
    dbms_output.put_line (---------------------);
    IF activity_row.club_id = name_row.club_id THEN
    dbms_output.put_line (name_row.name);

    END IF;
    END IF;
    END LOOP;
    IF activity = 'Canoeing' THEN
    LOOP
    FETCH name_cursor INTO name_row;
    EXIT WHEN name_cursor%notfound;
    dbms_output.put_line (activity_row.activity);
    dbms_output.put_line (---------------------);
    IF activity_row.club_id = name_row.club_id THEN
    dbms_output.put_line (name_row.name);
    END IF;
    END IF;
    END LOOP;
    CLOSE name_cursor;

    CLOSE activity_cursor;
    END;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Could someone help me?
    When are you going start doing your own homework assignments?
    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.

  3. #3
    Join Date
    Sep 2009
    Posts
    22
    I am, trust me, but this class is unfortunately only offered online so I am on my own and I get far but then get stuck to point where I can't figure out the next step... People on here have been very helpful... I'm sorry, just trying to get help... My university does not have a tutor for this class...

    Isn't this what the dbforums is for? To get helped from other knowledgeable people?
    Last edited by Rachelk; 09-24-09 at 21:41.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It was good that you posted the PL/SQL.

    Realize that without DDL for the tables & DML for test data, the code is marginally useful.
    Folks can only desk check what has been presented.
    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.

  5. #5
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Having the code indented and inside of a code block will at least get me to look at it.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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.

  7. #7
    Join Date
    Sep 2009
    Posts
    22
    > Having the code indented and inside of a code block will at least get me to look at it.

    How do I do that? Sorry...

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1

    >Isn't this what the dbforums is for?

    >Isn't this what the dbforums is for?
    If you expect assistance, at least you could follow the Posting Guidelines as stated in URL below:

    http://www.dbforums.com/oracle/10316...s-posters.html
    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.

  9. #9
    Join Date
    Sep 2009
    Posts
    22
    Code:
    SET SERVEROUTPUT ON
    
    DECLARE
    CURSOR activity_cursor IS
    SELECT DISTINCT activity
    FROM club_activity;
    activity_row activity_cursor%rowtype;
    CURSOR name_cursor IS
    SELECT club_id, name
    FROM sporting_clubs;
    name_row name_cursor%rowtype;
    activity_name club_activity.activity%type;
    club_name sporting_clubs.name%type;
    BEGIN
    OPEN activity_cursor;
    FETCH activity_cursor INTO activity_row;
    EXIT WHEN activity_cursor%notfound;
    IF activity = 'Hiking' THEN
    LOOP
    FETCH name_cursor INTO name_row;
    EXIT WHEN name_cursor%notfound;
    dbms_output.put_line (activity_row.activity);
    dbms_output.put_line (---------------------);
    IF activity_row.club_id = name_row.club_id THEN
    dbms_output.put_line (name_row.name);
    
    END IF;
    END IF;
    END LOOP;
    IF activity = 'Climbing' THEN
    LOOP
    FETCH name_cursor INTO name_row;
    EXIT WHEN name_cursor%notfound;
    dbms_output.put_line (activity_row.activity);
    dbms_output.put_line (---------------------);
    IF activity_row.club_id = name_row.club_id THEN
    dbms_output.put_line (name_row.name);
    
    END IF;
    END IF;
    END LOOP;
    IF activity = 'Walking' THEN
    LOOP
    FETCH name_cursor INTO name_row;
    EXIT WHEN name_cursor%notfound;
    dbms_output.put_line (activity_row.activity);
    dbms_output.put_line (---------------------);
    IF activity_row.club_id = name_row.club_id THEN
    dbms_output.put_line (name_row.name);
    
    END IF;
    END IF;
    END LOOP;
    IF activity = 'Conservation' THEN
    LOOP
    FETCH name_cursor INTO name_row;
    EXIT WHEN name_cursor%notfound;
    dbms_output.put_line (activity_row.activity);
    dbms_output.put_line (---------------------);
    IF activity_row.club_id = name_row.club_id THEN
    dbms_output.put_line (name_row.name);
    
    END IF;
    END IF;
    END LOOP;
    IF activity = 'Canoeing' THEN
    LOOP
    FETCH name_cursor INTO name_row;
    EXIT WHEN name_cursor%notfound;
    dbms_output.put_line (activity_row.activity);
    dbms_output.put_line (---------------------);
    IF activity_row.club_id = name_row.club_id THEN
    dbms_output.put_line (name_row.name);
    END IF;
    END IF;
    END LOOP;
    CLOSE name_cursor;
    
    CLOSE activity_cursor;
    END;
    Edit/Delete Message

  10. #10
    Join Date
    Sep 2009
    Posts
    22
    Is there something wrong with my IF then statements? The error reads

    IF activity_row.club_id = name_row.club_id THEN
    *


    ERROR at line 22:
    ORA-06550: line 22, column 1:
    PLS-00103: Encountered the symbol "IF" when expecting one of the following:
    ( ) - + case mod new not null others <an identifier>
    <a double-quoted delimited-identifier> <a bind variable>
    table avg count current exists max min prior sql stddev sum
    variance execute multiset the both leading trailing forall
    merge year month DAY_ hour minute second timezone_hour
    timezone_minute timezone_region timezone_abbr time timestamp
    interval date
    <a string literal with character set specification>
    ORA-06550: line 26, column 5:
    PLS-00103: Encountered the symbol "IF" when expecting one of the following:
    ; <an identifier> <a double-quoted delimited-identifier>
    delete exists prior <a single-quoted SQL string>

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Is there something wrong with my IF then statements?
    The Oracle parser seems to think so, but it can be easily confused.
    Sometimes the real error is just before where the parser indicates.
    One way to debug by actually developing the code 1 line at a time from the top down.
    This way the error is isolated to the last line added.

    Once again without DDL for tables & DML for test data,
    nobody here can try to compile any code you post.
    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.

  12. #12
    Join Date
    Sep 2009
    Posts
    22
    I guess I am just a lost cause.

  13. #13
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Have you taken/passed any programming class?
    If so, what language?
    In order to write any program, you have to think like the computer.
    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.

  14. #14
    Join Date
    Sep 2009
    Posts
    22
    I've taken java classes, gotten A and B in them, and I've passed the first database class with a high B, but this plsql stuff is just not making sense, and without a tutor and it being only offered online, it's hard for me to teach myself...

  15. #15
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The post code has multiple syntax errors that you need to find & fix before you can start to deal with logic errors.
    If you comment out or remove all lines below & including the IF reported as in error, what do you learn?
    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
  •