Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2014
    Posts
    1

    Question Unanswered: Error with my procedure

    I'm trying to create a procedure which gets the module information by student id.
    SET SERVEROUTPUT ON;

    CREATE OR REPLACE PROCEDURE get_modules_info_by_student_id
    (student_id NUMBER)
    AS
    v_name modules.name%TYPE;
    v_created date modules.created date%TYPE;
    BEGIN
    SELECT name, created DATE
    INTO v_name, v_created DATE, v_hire_date, v_update_date
    FROM courses
    WHERE student_id = student_id;

    DBMS_OUTPUT.PUT_LINE('Student_ID: ' || student_id);
    DBMS_OUTPUT.PUT_LINE('name: ' || v_name);
    DBMS_OUTPUT.PUT_LINE('created_date: ' || v_created DATE);
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Student_ID ' || student_id || ' not found.');
    end;
    /
    Warning: Procedure created with compilation errors.

    SQL> SHOW ERROR;
    Errors for PROCEDURE GET_MODULES_INFO_BY_STUDENT_ID:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    5/19 PLS-00103: Encountered the symbol "MODULES" when expecting one of
    the following:
    := . ( @ % ; not null range with default character

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    why do you have extraneous " DATE " string interspersed within the procedure?
    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
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This:
    Code:
    v_created date modules.created date%TYPE;
    is wrong - as Anacedent already said. It should be either
    Code:
    v_created date;
    or
    v_created modules.created%type;
    or possibly
    v_created_date modules.created_date%type;
    It depends on MODULES table description.

    Furthermore, this:
    Code:
    SELECT name, created DATE
    INTO v_name, v_created DATE, v_hire_date, v_update_date
    is also wrong. DATE shouldn't be here (as we already know); additionally, you are selecting two (or three?) columns into 4 or 5 variables. Suppose that column name as well as variable name are CREATED_DATE and V_CREATED_DATE. Your SELECT statement then looks like
    Code:
    select name, created_date         --> 2 columns
    into v_name, v_created_date,      --> into 2 variables - that's OK
          v_hire_date, v_update_date  --> But, what are these two doing here?
    Number and data types of columns and variables must match. Therefore, fix it.

Posting Permissions

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