Oracle PL/SQL/System Packages/ORA Error
Содержание
- 1 ORA-00918: column ambiguously defined
- 2 ORA-00934: group function is not allowed here
- 3 ORA-00979: not a GROUP BY expression
- 4 ORA-01403: no data found
- 5 ORA-01403: no data found exception from procedure
- 6 ORA-01422: exact fetch returns more than requested number of rows
- 7 ORA-01426: numeric overflow
- 8 ORA-01839: date not valid for month specified
- 9 ORA-06502: PL/SQL: numeric or value error
- 10 ORA-06502: PL/SQL: numeric or value error: character to number conversion error
- 11 ORA-06502: PL/SQL: numeric or value error: number precision too large
- 12 ORA-06503: PL/SQL: Function returned without value
- 13 ORA-14551: cannot perform a DML operation inside a query
ORA-00918: column ambiguously defined
<source lang="sql">
SQL> SQL> SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key 3 , ename VARCHAR2(8) 4 , init VARCHAR2(5) 5 , job VARCHAR2(8) 6 , mgr NUMBER(4) 7 , bdate DATE 8 , sal NUMBER(6,2) 9 , comm NUMBER(6,2) 10 , deptno NUMBER(2) default 10 11 ) ;
Table created. SQL> insert into emp values(1,"Tom","N", "Coder", 13,date "1965-12-17", 800 , NULL, 20); 1 row created. SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30); 1 row created. SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30); 1 row created. SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20); 1 row created. SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30); 1 row created. SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30); 1 row created. SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10); 1 row created. SQL> insert into emp values(8,"Smart","SCJ", "Coder", 4,date "1959-11-26", 3000, NULL, 20); 1 row created. SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10); 1 row created. SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30); 1 row created. SQL> insert into emp values(11,"Ana","AA", "Coder", 8,date "1966-12-30", 1100, NULL, 20); 1 row created. SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30); 1 row created. SQL> insert into emp values(13,"Fake","MG", "Coder", 4,date "1959-02-13", 3000, NULL, 20); 1 row created. SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10); 1 row created. SQL> SQL> create table departments
2 ( deptno NUMBER(2) constraint D_PK 3 primary key 4 , dname VARCHAR2(10) 5 , location VARCHAR2(8) 6 , mgr NUMBER(4) 7 ) ;
Table created. SQL> SQL> insert into departments values (10,"ACCOUNTING","NEW YORK",7); 1 row created. SQL> insert into departments values (20,"TRAINING", "DALLAS", 4); 1 row created. SQL> insert into departments values (30,"SALES", "CHICAGO", 6); 1 row created. SQL> insert into departments values (40,"HR", "BOSTON", 9); 1 row created. SQL> SQL> SQL> SQL> select deptno, location, ename, init
2 from emp, departments;
select deptno, location, ename, init
*
ERROR at line 1: ORA-00918: column ambiguously defined
SQL> SQL> drop table emp; Table dropped. SQL> drop table departments; Table dropped.
</source>
ORA-00934: group function is not allowed here
<source lang="sql">
SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key 3 , ename VARCHAR2(8) 4 , init VARCHAR2(5) 5 , job VARCHAR2(8) 6 , mgr NUMBER(4) 7 , bdate DATE 8 , sal NUMBER(6,2) 9 , comm NUMBER(6,2) 10 , deptno NUMBER(2) default 10 11 ) ;
Table created. SQL> insert into emp values(1,"Tom","N", "TRAINER", 13,date "1965-12-17", 800 , NULL, 20); 1 row created. SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30); 1 row created. SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30); 1 row created. SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20); 1 row created. SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30); 1 row created. SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30); 1 row created. SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10); 1 row created. SQL> insert into emp values(8,"Smart","SCJ", "TRAINER", 4,date "1959-11-26", 3000, NULL, 20); 1 row created. SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10); 1 row created. SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30); 1 row created. SQL> insert into emp values(11,"Ana","AA", "TRAINER", 8,date "1966-12-30", 1100, NULL, 20); 1 row created. SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30); 1 row created. SQL> insert into emp values(13,"Fake","MG", "TRAINER", 4,date "1959-02-13", 3000, NULL, 20); 1 row created. SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10); 1 row created. SQL> SQL> SQL> select empno
2 from emp 3 where sal > avg(sal);
where sal > avg(sal)
*
ERROR at line 3: ORA-00934: group function is not allowed here
SQL> SQL> SQL> drop table emp; Table dropped.
</source>
ORA-00979: not a GROUP BY expression
<source lang="sql">
SQL> create table registrations
2 ( attendee NUMBER(4) 3 , course VARCHAR2(6) 4 , begindate DATE 5 , evaluation NUMBER(1) 6 , constraint R_PK primary key (attendee,course,begindate) 7 ) ;
Table created. SQL> SQL> insert into registrations values (2,"SQL",date "2009-04-12",4 ); 1 row created. SQL> insert into registrations values (14,"SQL",date "2009-04-12",5 ); 1 row created. SQL> insert into registrations values (6,"SQL",date "2009-04-12",4 ); 1 row created. SQL> insert into registrations values (11,"SQL",date "2009-04-12",2 ); 1 row created. SQL> insert into registrations values (8,"SQL",date "2009-10-04",NULL); 1 row created. SQL> insert into registrations values (9,"SQL",date "2009-10-04",3 ); 1 row created. SQL> insert into registrations values (13,"SQL",date "2009-10-04",4 ); 1 row created. SQL> insert into registrations values (13,"SQL",date "2009-12-13",NULL); 1 row created. SQL> insert into registrations values (6,"SQL",date "2009-12-13",NULL); 1 row created. SQL> insert into registrations values (3,"OAU",date "2009-08-10",4 ); 1 row created. SQL> insert into registrations values (12,"OAU",date "2009-08-10",4 ); 1 row created. SQL> insert into registrations values (13,"OAU",date "2009-08-10",5 ); 1 row created. SQL> SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key 3 , ename VARCHAR2(8) 4 , init VARCHAR2(5) 5 , job VARCHAR2(8) 6 , mgr NUMBER(4) 7 , bdate DATE 8 , sal NUMBER(6,2) 9 , comm NUMBER(6,2) 10 , deptno NUMBER(2) default 10 11 ) ;
Table created. SQL> insert into emp values(1,"Tom","N", "TRAINER", 13,date "1965-12-17", 800 , NULL, 20); 1 row created. SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30); 1 row created. SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30); 1 row created. SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20); 1 row created. SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30); 1 row created. SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30); 1 row created. SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10); 1 row created. SQL> insert into emp values(8,"Smart","SCJ", "TRAINER", 4,date "1959-11-26", 3000, NULL, 20); 1 row created. SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10); 1 row created. SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30); 1 row created. SQL> insert into emp values(11,"Ana","AA", "TRAINER", 8,date "1966-12-30", 1100, NULL, 20); 1 row created. SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30); 1 row created. SQL> insert into emp values(13,"Fake","MG", "TRAINER", 4,date "1959-02-13", 3000, NULL, 20); 1 row created. SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10); 1 row created. SQL> SQL> select e.empno, e.ename, count(*)
2 from emp e 3 join 4 registrations r 5 on (e.empno = r.attendee) 6 group by e.empno;
select e.empno, e.ename, count(*)
*
ERROR at line 1: ORA-00979: not a GROUP BY expression
SQL> SQL> SQL> SQL> drop table emp; Table dropped. SQL> drop table registrations; Table dropped.
</source>
ORA-01403: no data found
<source lang="sql">
SQL> SQL> SQL> SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
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> CREATE TABLE DEPT(
2 DEPTNO NUMBER(2), 3 DNAME VARCHAR2(14), 4 LOC VARCHAR2(13) 5 );
Table created. SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> SQL> declare
2 l_ename emp.ename%type; 3 begin 4 for x in (select deptno, dname from dept order by deptno) 5 loop 6 select ename into l_ename 7 from emp 8 where deptno = x.deptno 9 and sal = (select max(sal)from emp where deptno = x.deptno) and rownum = 1; 10 dbms_output.put_line( x.deptno||"," || x.dname || ", " || l_ename ); 11 end loop; 12 end; 13 /
declare
ERROR at line 1: ORA-01403: no data found ORA-06512: at line 6
SQL> SQL> drop table dept; Table dropped. SQL> drop table emp; Table dropped.
</source>
ORA-01403: no data found exception from procedure
<source lang="sql">
SQL> CREATE TABLE emp (
2 id NUMBER PRIMARY KEY, 3 fname VARCHAR2(50), 4 lname VARCHAR2(50) 5 );
Table created. SQL> SQL> INSERT INTO emp (id, fname, lname)VALUES (1, "A", "B"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (2, "C", "D"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (3, "Enn", "F"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (4, "G", "H"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (5, "G", "Z"); 1 row created. SQL> SQL> PROMPT as bind variables as bind variables SQL> SQL> CREATE OR REPLACE PROCEDURE bind_test (i_emp_fname IN emp.fname%TYPE)
2 IS 3 v_emp_lname emp.lname%TYPE; 4 BEGIN 5 SELECT lname INTO v_emp_lname FROM emp WHERE fname = i_emp_fname; 6 7 DBMS_OUTPUT.PUT_LINE(i_emp_fname||" has a last name of "||v_emp_lname); 8 EXCEPTION 9 WHEN OTHERS 10 THEN 11 DBMS_OUTPUT.PUT_LINE(sqlerrm); 12 END; 13 /
Procedure created. SQL> SQL> SQL> ALTER SESSION SET SQL_TRACE = TRUE; Session altered. SQL> EXEC bind_test("Ron") ORA-01403: no data found PL/SQL procedure successfully completed. SQL> EXEC bind_test("Mike") ORA-01403: no data found PL/SQL procedure successfully completed. SQL> ALTER SESSION SET SQL_TRACE = FALSE; Session altered. SQL> SQL> drop table emp; Table dropped. SQL>
</source>
ORA-01422: exact fetch returns more than requested number of rows
<source lang="sql">
SQL> SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
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> CREATE TABLE DEPT(
2 DEPTNO NUMBER(2), 3 DNAME VARCHAR2(14), 4 LOC VARCHAR2(13) 5 );
Table created. SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> SQL> SQL> declare
2 l_ename emp.ename%type; 3 begin 4 for x in (select deptno, dname from dept order by deptno) 5 loop 6 select ename into l_ename 7 from emp 8 where deptno = x.deptno 9 and sal = (select max(sal) from emp where deptno = x.deptno); 10 dbms_output.put_line( x.deptno || ", " || x.dname || ", " || l_ename ); 11 end loop; 12 end; 13 /
declare
ERROR at line 1: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 6
SQL> drop table dept; Table dropped. SQL> drop table emp; Table dropped.
</source>
ORA-01426: numeric overflow
<source lang="sql">
SQL> SQL> begin
2 for i in power(2,31) .. power(2,31)+10 loop 3 null; 4 end loop; 5 end; 6 /
begin
ERROR at line 1: ORA-01426: numeric overflow ORA-06512: at line 2
SQL> SQL>
</source>
ORA-01839: date not valid for month specified
<source lang="sql">
SQL> select date "1996-01-29" + interval "1" month as col_1
2 , date "1997-01-29" + interval "1" month as col_2 3 , date "1997-08-11" - interval "3" month as col_3 4 from dual;
, date "1997-01-29" + interval "1" month as col_2
*
ERROR at line 2: ORA-01839: date not valid for month specified
SQL> SQL> select date "1996-01-29" + interval "1" month as col_1
2 , date "1997-01-28" + interval "1" month as col_2 3 , date "1997-08-11" - interval "3" month as col_3 4 from dual;
Enter... 29-02-1996 28-02-1997 11-05-1997 1 row selected. SQL>
</source>
ORA-06502: PL/SQL: numeric or value error
<source lang="sql">
SQL> SQL> DECLARE
2 TYPE months_varray IS VARRAY(12) OF STRING(9 CHAR);
3
4 TYPE calendar_table IS TABLE OF VARCHAR2(9 CHAR) INDEX BY BINARY_INTEGER;
5
6 month MONTHS_VARRAY := months_varray("January","February","March","April","May","June","July","August","September","October","November","December");
7
8 calendar CALENDAR_TABLE;
9 BEGIN
10 IF calendar.COUNT = 0 THEN
11 FOR i IN calendar.FIRST..calendar.LAST LOOP
12 DBMS_OUTPUT.PUT_LINE("Index ["||i||"] is ["||calendar(i)||"]");
13 END LOOP;
14 END IF;
15 END;
16 /
DECLARE
ERROR at line 1: ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 11
SQL>
</source>
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
<source lang="sql">
SQL> CREATE TABLE myTable2(
2 e INTEGER, 3 f INTEGER 4 );
Table created. SQL> SQL> INSERT INTO myTable2 VALUES(1, 3); 1 row created. SQL> INSERT INTO myTable2 VALUES(2, 4); 1 row created. SQL> SQL> CREATE or replace PROCEDURE addtuple2(
2 x myTable2.e%TYPE, 3 y myTable2.f%TYPE) 4 AS 5 BEGIN 6 INSERT INTO myTable2(e, f)VALUES(x, y); 7 END addtuple2; 8 /
Procedure created. SQL> SQL> show errors No errors. SQL> SQL> BEGIN
2 addtuple2(10, "abc"); 3 END; 4 /
BEGIN
ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at line 2
SQL> SQL> drop table myTable2; Table dropped. SQL> SQL> SQL> SQL>
</source>
ORA-06502: PL/SQL: numeric or value error: number precision too large
<source lang="sql">
SQL> create table emp (
2 id number(6) );
Table created. SQL> SQL> alter table emp
2 add constraint emp_pk 3 primary key (id);
Table altered. SQL> SQL> SQL> create or replace procedure gen_emp is
2 v_new_cid emp.id%type; 3 begin 4 loop 5 begin 6 v_new_cid := round(dbms_random.value(1000000,9999999)); 7 insert into emp values (v_new_cid); 8 exit; 9 exception when dup_val_on_index then 10 null; 11 end; 12 end loop; 13 end; 14 /
Procedure created. SQL> SQL> set timing on SQL> begin
2 gen_emp; 3 commit; 4 end; 5 /
begin
ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: number precision too large ORA-06512: at "sqle.GEN_EMP", line 6 ORA-06512: at line 2
Elapsed: 00:00:00.07 SQL> SQL> begin
2 for i in 1 .. 100000 loop 3 gen_emp; 4 end loop; 5 commit; 6 end; 7 /
begin
ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: number precision too large ORA-06512: at "sqle.GEN_EMP", line 6 ORA-06512: at line 3
Elapsed: 00:00:00.06 SQL> SQL> set timing off SQL> SQL> drop table emp; Table dropped.
</source>
ORA-06503: PL/SQL: Function returned without value
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE emp
2 (emp_id INTEGER NOT NULL 3 ,fname VARCHAR2(30 CHAR) NOT NULL 4 ,mid_name VARCHAR2(1 CHAR) 5 ,lname VARCHAR2(30 CHAR) NOT NULL 6 ,CONSTRAINT emp_pk PRIMARY KEY (emp_id));
Table created. SQL> SQL> SQL> SQL> SET ECHO ON SQL> SQL> SQL> SQL> DECLARE
2 TYPE emp_record IS RECORD (emp_id INTEGER,fname VARCHAR2(30 CHAR),mid_name VARCHAR2(1 CHAR),lname VARCHAR2(30 CHAR));
3
4 emp emp_RECORD;
5
6 FUNCTION get_row (emp_id_in INTEGER)
7 RETURN emp_RECORD IS
8 CURSOR c (emp_id_cursor INTEGER) IS SELECT * FROM emp WHERE emp_id = emp_id_cursor;
9
10 BEGIN
11 FOR i IN c(emp_id_in) LOOP
12 RETURN i;
13 END LOOP;
14
15 END get_row;
16
17 BEGIN
18 emp := get_row(1);
19 dbms_output.put_line(CHR(10));
20 dbms_output.put_line("emp_ID : "||emp.emp_id);
21 dbms_output.put_line("fname : "||emp.fname);
22 dbms_output.put_line("mid_name : "||emp.mid_name);
23 dbms_output.put_line("lname : "||emp.lname);
24
25 END;
26 /
DECLARE
ERROR at line 1: ORA-06503: PL/SQL: Function returned without value ORA-06512: at line 15 ORA-06512: at line 18
SQL> SQL> SQL> SQL> SQL> drop table emp; Table dropped. SQL> SQL>
</source>
ORA-14551: cannot perform a DML operation inside a query
<source lang="sql">
SQL> SQL> CREATE TABLE myTable
2 (num_col NUMBER 3 ,char_col VARCHAR2(60));
Table created. SQL> SQL> SQL> SQL> SQL> SQL> CREATE TABLE emp (
2 id NUMBER PRIMARY KEY, 3 fname VARCHAR2(50), 4 lname VARCHAR2(50) 5 );
Table created. SQL> SQL> SQL> SQL> SQL> INSERT INTO emp (id, fname, lname)VALUES (1, "A", "B"); 1 row created. SQL> SQL> SQL> SQL> INSERT INTO emp (id, fname, lname)VALUES (2, "C", "D"); 1 row created. SQL> SQL> SQL> SQL> INSERT INTO emp (id, fname, lname)VALUES (3, "Enn", "F"); 1 row created. SQL> SQL> SQL> SQL> INSERT INTO emp (id, fname, lname)VALUES (4, "G", "H"); 1 row created. SQL> SQL> SQL> SQL> INSERT INTO emp (id, fname, lname)VALUES (5, "G", "Z"); 1 row created. SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> COLUMN char_col format a60 SQL> SELECT * FROM myTable ORDER BY num_col; no rows selected SQL> SQL> SQL> SQL> CREATE OR REPLACE FUNCTION FullName (p_empID emp.ID%TYPE)
2 RETURN VARCHAR2 IS 3 v_Result VARCHAR2(100); 4 BEGIN 5 SELECT fname || " " || lname INTO v_Result FROM emp WHERE ID = p_empID; 6 7 INSERT INTO myTable (num_col, char_col)VALUES (p_empID, "called by FullName!"); 8 9 RETURN v_Result; 10 END FullName; 11 /
Function created. SQL> SQL> show errors No errors. SQL> SQL> SQL> -- The same query will now raise an error. SQL> SELECT FullName(ID) full_name
2 FROM emp 3 ORDER BY full_name;
SELECT FullName(ID) full_name
*
ERROR at line 1: ORA-14551: cannot perform a DML operation inside a query ORA-06512: at "sqle.FULLNAME", line 7
SQL> SQL> SQL> SQL> SQL> drop table myTable; Table dropped. SQL> SQL> SQL> SQL> SQL> drop table emp; Table dropped. SQL> SQL> SQL>
</source>
