Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Ugent help with Script

Ugent help with Script

From: C.Lussier <clussier_at_istar.ca>
Date: Sun, 18 Apr 1999 13:27:54 -0400
Message-ID: <371A161A.2406F77B@istar.ca>


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 - 12:27:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US