Re: Ugent help with Script

Generally it looks ok.

However, you need to print off g_message1 after the procedure is complete. Or you may want to use DMBS_OUTPUT.put_line (:g_message1); in your exception section.

Marc Parkinson

"C.Lussier" wrote:

> I am of course a beginner with Oracle and have a project to complete for
> tomorrow. As I do not have Oracle set-up on my PC at home, I cannot
> verify if this script will run and what errors will be generated. Could
> someone please have a look at this script and tell me if it will run?
> The script should do the following:
> Create a PL/SQL script that will allow a technician to enter line
> details in a service call (assume that the service calls are entered by
> another person ie. a clerk) and that the service_call table is already
> filled by another program.
>
> Here is my script:
>
> REM script quest2 by C.Lussier
> SET SERVEROUTPUT ON SIZE 5000
> SET VERIFY OFF
> SELECT *
> FROM SC_DETAILS;
> VARIABLE g_message VARCHAR2(30)
> ACCEPT p_sc_number PROMPT 'Please enter the sc_number: '
> ACCEPT p_line_id PROMPT 'Please enter the line_id: '
> ACCEPT p_part_id PROMPT 'Please enter the part_id: '
> ACCEPT p_service PROMPT 'Please enter the service: '
> ACCEPT p_technician PROMPT 'Please enter the technician name: '
> ACCEPT p_hours PROMPT 'Please enter the number of hours: '
> DECLARE
> e_invalid_sc_number EXCEPTION;
> v_sc_number sc_details.sc_number%TYPE := &p_sc_number;
> e_invalid_line_id EXCEPTION;
> v_line_id sc_details.line_id%TYPE := &p_line_id;
> e_invalid_part_id EXCEPTION;
> v_part_id sc_details.part_id%TYPE := &p_part_id;
> e_invalid_service EXCEPTION;
> v_service sc_details.service%TYPE := &p_service;
> e_invalid_technician EXCEPTION;
> v_technician sc_details.technician%TYPE := &p_technician;
> e_invalid_hours EXCEPTION;
> v_hours sc_details.hours%TYPE := &p_hours;
> BEGIN
> UPDATE sc_details
> WHERE sc_number = v_sc_number
> AND line_id = v_line_id
> AND part_id = v_line_id
> AND service = v_service
> AND technician = v_technician
> AND hours = v_hours;
> EXCEPTION
> WHEN e_invalid_sc_number THEN
> :g_message1 := 'Invalid sc_number';
> WHEN e_invalid_line_id THEN
> :g_message2 := 'Invalid line_id';
> WHEN e_invalid_part_id THEN
> :g_message3 := 'Invalid part_id';
> WHEN e_invalid_service THEN
> :g_message4 := 'Invalid service';
> WHEN e_invalid_technician THEN
> :g_message5 := 'Invalid technician';
> WHEN e_invalid_hours THEN
> :g_message6 := 'Invalid hours';
> END;
> /
>
> Thank you for your help.
Received on Sun Apr 18 1999 - 21:21:23 CDT