HELP WITH THIS SCRIPT
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: HELP WITH THIS SCRIPT

Hybrid View

  1. #1
    Join Date
    Apr 2001
    Location
    Germantown, MD
    Posts
    14
    --PROGRAM TO ASK USER TO ENTER DATA, CALCULATE A PAYCHECK, DISPLAY A PAYCHECK
    --AND MAINTAIN A EMPLOYEE HISTORY
    DROP TABLE SAL_HIST;

    CREATE TABLE SAL_HIST(
    LASTNAME VARCHAR2(20),
    FIRSTNAME VARCHAR2(20),
    HOURS NUMBER(10),
    RATE NUMBER(10),
    DEPEN NUMBER(10),
    TAX NUMBER(10),
    BONUS NUMBER(10),
    NETSALARY NUMBER(10),
    YEARS NUMBER(2));

    SET SERVEROUTPUT ON;
    SET VERIFY OFF;

    --ASSIGN NEEDED VARIABLES

    DECLARE
    LASTNAME VARCHAR2(20);
    FIRSTNAME VARCHAR2(20);
    HOURS SAL_HIST.HOURS%TYPE;
    RATE SAL_HIST.RATE%TYPE;
    DEPEN SAL_HIST.DEPEN%TYPE;
    TAX SAL_HIST.TAX%TYPE;
    BONUS SAL_HIST.BONUS%TYPE;
    NETSALARY SAL_HIST.NETSALARY%TYPE;
    YEARS SAL_HIST.YEARS%TYPE;
    DATE VARCHAR2(12);
    BEGIN
    --ASK USER TO ENTER DATA

    LASTNAME := '&LASTNAME';
    FIRSTNAME := '&FIRSTNAME';
    HOURS := '&HOURS';
    RATE := '&RATE';
    DEPEN := '&NUMBEROFDEPENDENTS';
    YEARS := '&YEARSWORKED';

    --MAKE SALARY CALCULATIONS

    BONUS := YEARS * 30;
    TAX := (HOURS*RATE+BONUS) * .2 / DEPEN;
    NETSALARY := HOURS * RATE - TAX + BONUS;

    --CREATE HISTORY

    INSERT INTO SAL_HIST VALUES('LASTNAME', 'FIRSTNAME', HOURS, RATE, DEPEN, TAX,
    BONUS, YEARS, NETSALARY);
    SELECT SYSDATE INTO DATE FROM DUAL;
    --PRINT CHECK
    --DBMS_OUTPUT.PUT_LINE('DATE'||''||DATE);
    --DBMS_OUTPUT.PUT_LINE('PAY TO THE ORDER OF:'||''||LASTNAME||''|| FIRSTNAME
    -- ||'$'||NETSALARY);
    END;
    /
    I'm getting an error message on output that says, "ERROR at line 1:
    ORA-01438: value larger than specified precision allows for this column
    ORA-06512: at line 30"
    I can't seem to find the which column is too small can someone please help me?

  2. #2
    Join Date
    Nov 1999
    Location
    Kuwait
    Posts
    122

    Wink it worked..

    Dear blmartin,

    I have run the same procudure, and it worked with me...!here are results and the values which i keyed in...

    Enter value for lastname: khan
    Enter value for firstname: nabeel
    Enter value for hours: 5
    Enter value for rate: 5
    Enter value for numberofdependents: 5
    Enter value for yearsworked: 01

    PL/SQL procedure successfully completed.
    --in fact if I use these values I get the error..!
    Enter value for lastname: khan
    Enter value for firstname: sahil
    Enter value for hours: 8.5
    Enter value for rate: 20.5
    Enter value for numberofdependents: 5
    Enter value for yearsworked: 1
    DECLARE
    *
    ERROR at line 1:
    ORA-01438: value larger than specified precision allows for this column
    ORA-06512: at line 25
    --This was the example for whats was goin on right?
    So, dear the problem is in your insert statment which is...
    INSERT INTO SAL_HIST VALUES('LASTNAME', 'FIRSTNAME', HOURS, RATE, DEPEN, TAX,
    BONUS, YEARS, NETSALARY);
    on the other hand the table desc is
    CREATE TABLE SAL_HIST(
    LASTNAME VARCHAR2(20),
    FIRSTNAME VARCHAR2(20),
    HOURS NUMBER(10),
    RATE NUMBER(10),
    DEPEN NUMBER(10),
    TAX NUMBER(10),
    BONUS NUMBER(10),
    NETSALARY NUMBER(10),
    YEARS NUMBER(2));

    You are inserting NETSALARY IN THE YEARS COLUMN...change the statment and keep the years column after the netsal it will work fine!


    Take care..
    NK

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Originally posted by blmartin
    INSERT INTO SAL_HIST VALUES('LASTNAME', 'FIRSTNAME', HOURS, RATE, DEPEN, TAX,
    BONUS, YEARS, NETSALARY);
    Learn from this...*never* write an insert without specifying the column list.

    This should be:

    INSERT INTO SAL_HIST (
    LASTNAME, FIRSTNAME, HOURS, RATE, DEPEN, TAX,BONUS, YEARS, NETSALARY
    )
    VALUES(
    'LASTNAME', 'FIRSTNAME', HOURS, RATE, DEPEN, TAX,BONUS, YEARS, NETSALARY
    );

    - Chris

  4. #4
    Join Date
    Apr 2001
    Location
    Germantown, MD
    Posts
    14
    Thanks guys. This site is the best. I wish that I had found it earlier.

  5. #5
    Join Date
    Nov 1999
    Location
    Kuwait
    Posts
    122

    Most welcome

    Any time dude...

    Regards,
    NK

Posting Permissions

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



Click Here to Expand Forum to Full Width