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
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.
ORA-00934: group function is not allowed here
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.
ORA-00979: not a GROUP BY expression
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.
ORA-01403: no data found
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.
ORA-01403: no data found exception from procedure
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>
ORA-01422: exact fetch returns more than requested number of rows
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.
ORA-01426: numeric overflow
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>
ORA-01839: date not valid for month specified
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>
ORA-06502: PL/SQL: numeric or value error
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>
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
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>
ORA-06502: PL/SQL: numeric or value error: number precision too large
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.
ORA-06503: PL/SQL: Function returned without value
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>
ORA-14551: cannot perform a DML operation inside a query
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>