Oracle PL/SQL/Stored Procedure Function/Show Error

Use "show errors" command to check the error in a stored procedure

 
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
  2                    ENAME VARCHAR2(10),
  3                    JOB VARCHAR2(9),
  4                    MGR NUMBER(4),
  5                    HIREDATE DATE,
  6                    SAL NUMBER(7, 2),
  7                    COMM NUMBER(7, 2),
  8                    DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK",    7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD",  "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER",  7839, TO_DATE("2-APR-1981",  "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> select * from emp;
     EMPNO Employee Name JOB              MGR HIREDATE      Salary       COMM     DEPTNO
---------- ------------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH         CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN         SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD          SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES         MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN        SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE         MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK         MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT         ANALYST         7566 09-DEC-82       3000                    20
      7839 KING          PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER        SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS         CLERK           7788 12-JAN-83       1100                    20
     EMPNO Employee Name JOB              MGR HIREDATE      Salary       COMM     DEPTNO
---------- ------------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES         CLERK           7698 03-DEC-81        950                    30
      7902 FORD          ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER        CLERK           7782 23-JAN-82       1300                    10
14 rows selected.
SQL>
SQL> create or replace procedure show_emp( p_empno in number )
  2      is
  3      begin
  4        for c1 in (select * from emp where empno = p_empno) loop
  5          dbms_output.put_line("Name: " || c1.ename);
  6          dbms_output.put_line("Job: " || c1.job);
  7          dbms_output.put_line("Salary: " || c1.sal);
  8         dbms_output.put_line("Commission: " || c1.rum);
  9       end;
 10     end show_emp;
 11     /
Warning: Procedure created with compilation errors.
SQL> show errors
Errors for PROCEDURE SHOW_EMP:
LINE/COL ERROR
-------- -----------------------------------------------------------------
9/9      PLS-00103: Encountered the symbol ";" when expecting one of the
         following:
         loop
         The symbol "loop" was substituted for ";" to continue.
SQL>
SQL> show_emp(7);
SP2-0158: unknown SHOW option "_emp(7)"
SQL>
SQL> drop table emp;
Table dropped.

Use "show errors" command to display error information for your stored procedure

 
SQL>
SQL> set serveroutput on
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
  2                    ENAME VARCHAR2(10),
  3                    JOB VARCHAR2(9),
  4                    MGR NUMBER(4),
  5                    HIREDATE DATE,
  6                    SAL NUMBER(7, 2),
  7                    COMM NUMBER(7, 2),
  8                    DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK",    7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD",  "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER",  7839, TO_DATE("2-APR-1981",  "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL> create or replace procedure show_emp( p_empno in number )
  2  is
  3  begin
  4    for c1 in (select *
  5                 from emp
  6                where empno = p_empno) loop
  7      dbms_output.put_line("Name: " || c1.ename);
  8      dbms_output.put_line("Job: " || c1.job);
  9      dbms_output.put_line("Salary: " || c1.sal);
 10      dbms_output.put_line("Commission: " || c1.rum);
 11    end loop;
 12  end show_emp;
 13  /
Procedure created.
SQL>
SQL> show errors
No errors.
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL>