Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Location
    Kansas City
    Posts
    3

    Unanswered: Trying to Understand Procedure

    I am learning Stored Procedure, but still not quite catch it.. What am I doing wrong here... How can I use insert when I am trying to find SSN (From DP260I.PATRON) and Scan_Code (From Collection). Thanks in advance.

    The Error Message:
    Errors for PACKAGE BODY P_P6_PROCESS_BOOKS:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    43/2 PLS-00428: an INTO clause is expected in this SELECT statement
    46/4 PLS-00428: an INTO clause is expected in this SELECT statement
    83/2 PL/SQL: Statement ignored
    83/5 PLS-00306: wrong number or types of arguments in call to
    'SP_CHECK_BOOK_OUT_FUNCTION'

    91/2 PL/SQL: Statement ignored
    91/5 PLS-00306: wrong number or types of arguments in call to
    'SP_CHECK_BOOK_OUT_FUNCTION'

    My Code:
    CREATE OR REPLACE PACKAGE BODY p_p6_process_books
    AS
    FUNCTION sp_check_book_in_function
    (
    scan_code_in IN CHAR
    )
    RETURN BOOLEAN
    IS
    BEGIN
    UPDATE Collection SET Book_Status = 'I' WHERE scan_code = scan_code_in;
    IF SQL%ROWCOUNT = 1 THEN
    RETURN TRUE;
    DELETE FROM CIRC WHERE scan_code = scan_code_in;
    ELSE
    RETURN FALSE;
    END IF;
    END;
    Procedure sp_check_book_in
    (
    scan_code_in IN CHAR,
    return_message OUT CHAR,
    return_sqlcode OUT NUMBER
    )
    AS
    BEGIN
    IF sp_check_book_in_function(scan_code_in) THEN
    return_sqlcode := 0;
    return_message := 'Scan Code: ' || scan_code_in || ' Checked In';
    COMMIT;
    ELSE
    return_sqlcode := +100;
    return_message := 'Scan Code: ' || scan_code_in || ' Not Found';
    END IF;
    END sp_check_book_in;
    FUNCTION sp_check_book_out_function
    (
    ssn_in IN CHAR,
    scan_code_in IN CHAR
    )
    RETURN BOOLEAN
    IS
    BEGIN
    SELECT ssn FROM DP260I.PATRON WHERE ssn IN ssn_in;
    IF SQL%ROWCOUNT = 1 THEN
    RETURN TRUE;
    SELECT scan_code FROM Collection WHERE scan_code IN scan_code_in;
    IF SQL%ROWCOUNT = 1 THEN
    RETURN TRUE;
    UPDATE Collection
    SET
    Book_Status = 'O',
    Date_Last_Out = (SELECT Todays_Date FROM Current_Date),
    Times_Checked_Out = (Times_Checked_Out + 1)
    WHERE scan_code = scan_code_in;
    INSERT INTO CIRC
    (
    ssn,
    scan_code,
    date_due
    )
    VALUES
    (
    ssn_in,
    scan_code_in,
    (SELECT Todays_Date FROM Current_Date)
    );
    ELSE
    RETURN FALSE;
    END IF;
    ELSE
    RETURN FALSE;
    END IF;
    END;
    Procedure sp_check_book_out
    (
    ssn_in IN CHAR,
    scan_code_in IN CHAR,
    return_message OUT CHAR,
    return_sqlcode OUT NUMBER
    )
    AS
    BEGIN
    IF sp_check_book_out_function(ssn_in) THEN
    return_sqlcode := 0;
    return_message := 'SSN: ' || ssn_in || ' Patron Found';
    COMMIT;
    ELSE
    return_sqlcode := +100;
    return_message := 'SSN: ' || ssn_in || ' Patron Not Found';
    END IF;
    IF sp_check_book_out_function(scan_code_in) THEN
    return_sqlcode := 0;
    return_message := 'Scan Code: ' || scan_code_in || ' Checked Out';
    COMMIT;
    ELSE
    return_sqlcode := +100;
    return_message := 'Scan Code: ' || scan_code_in || ' Not Found';
    END IF;
    END sp_check_book_out;
    END p_p6_process_books;
    /

    Any help and hints is welcome...

  2. #2
    Join Date
    Sep 2003
    Posts
    27
    1) sp_check_book_out_function has 2 inparams (ssn_in and scan_code_in).
    Therefore, you MUST pass in 2 params - even if 1 of them is null.

    2) SELECT statements in PL/SQL need an INTO clause into which the result of the select is stored.
    E.g.
    SELECT count(ssn)
    INTO <variable_name>
    FROM DP260I.PATRON
    WHERE ssn = ssn_in;

    IF (<variable_name> = 1) THEN .........

    3)If the SELECT doesn't find any rows then an EXCEPTION will be raised.
    You will need to add an exception handling section to deal with this.

    You'll need to find a PL/SQL book I think or look on web.
    Oracle OCP developer exam guide by Steve O'Hearn is one.

    Tim

Posting Permissions

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