Liren Large Software Subsidy 5/05.iso/a/a524/37.ddi/demo/demo5_pls.sql

Text File | 1991-03-04 | 750 b | 24 lines

rem
rem $Header: demo5_pls.sql,v 6.1 90/02/16 18:40:16 nsalah Exp $ demo5_pls.sql Copyr (c) 1989 Oracle
rem
DECLARE
    last_name          emp.ename%TYPE;
    err_string         CHAR(48);
BEGIN
    SELECT ename INTO last_name FROM emp
      WHERE empno = &emp_num;
        /* &emp_num is a SQL*Plus variable */
    INSERT INTO temp (col1, message) VALUES (&emp_num, last_name);
    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        INSERT INTO temp (col1, message)
          VALUES (&emp_num, 'No employee with that id.');
        COMMIT;
    WHEN OTHERS THEN
        err_string := SUBSTR(SQLERRM, 12, 48);  -- extract error text
        INSERT INTO temp (col1, message)
          VALUES (&emp_num, 'Other: ' || err_string);
        COMMIT;
END;