View based on function, correct query treated as bad

Question

This is code:

CREATE TABLE emp_where (where_clause VARCHAR2(4000));
INSERT INTO emp_where (where_clause)
VALUES ('first_name=''KING'' or department_id = 20');
commit;

CREATE OR REPLACE TYPE t_emp_rec AS OBJECT (
          EMPLOYEE_ID    NUMBER(4),
          FIRST_NAME    VARCHAR2(10),
          JOB_ID      VARCHAR2(9),
          MANAGER_ID      NUMBER(4),
          HIRE_DATE DATE,
          SALARY     NUMBER(7,2),
          DEPARTMENT_ID   NUMBER(2)
);
/
CREATE OR REPLACE TYPE t_emp_tab AS TABLE OF t_emp_rec;
 /

 CREATE OR REPLACE FUNCTION emp_fn RETURN t_emp_tab
 PIPELINED IS
   l_sql   VARCHAR2(32767);
   l_where VARCHAR2(4000);
   TYPE l_cur_type IS REF CURSOR;
   l_cur l_cur_type;
   l_rec employees%ROWTYPE;
 BEGIN
   SELECT where_clause INTO l_where FROM emp_where;
   l_sql := 'SELECT * FROM employees WHERE ' || l_where;
   OPEN l_cur FOR l_sql;
   LOOP
     FETCH l_cur
     INTO l_rec;
     EXIT WHEN l_cur%NOTFOUND;
     PIPE ROW(t_emp_rec(EMPLOYEE_ID    => l_rec.EMPLOYEE_ID
                       ,FIRST_NAME    => l_rec.FIRST_NAME
                       ,JOB_ID      => l_rec.JOB_ID
                       ,MANAGER_ID      => l_rec.MANAGER_ID
                       ,hire_date => l_rec.hire_date
                       ,SALARY      => l_rec.SALARY
                       ,DEPARTMENT_ID   => l_rec.DEPARTMENT_ID));
 END LOOP;

 RETURN;
EXCEPTION
 WHEN OTHERS THEN
   raise_application_error(-20000, SQLERRM || chr(10) || l_sql);
 END;
 /

 CREATE OR REPLACE VIEW emp_vw AS


UPDATE emp_where SET where_clause = 'EMPLOYEE_ID BETWEEN 100 and 200';
COMMIT;
SELECT * FROM emp_vw;

When I execute SELECT * FROM emp_vw; with this clause: EMPLOYEE_ID BETWEEN 100 and 200 Oracle gives me an error

ORA-20000: ORA-06502: PL/SQL: numeric or value error: number precision too large SELECT * FROM employees WHERE EMPLOYEE_ID BETWEEN 100 and 200

But when I execute query itself (SELECT * FROM employees WHERE EMPLOYEE_ID BETWEEN 100 and 200) there is no error. Another scenario - when clause is 'deparment_id = 20', executing a view is correct. But when I change '=' to '>' (department_id > 20) - numeric or value error: number precision too large. Can someone explain me how this is happening?

MT0 MT0 · Accepted Answer · 2016-06-28T07:49:39

If I run:

DESCRIBE employees

Then I get the output:

Name           Null     Type         
-------------- -------- ------------ 
EMPLOYEE_ID    NOT NULL NUMBER(6)    
FIRST_NAME              VARCHAR2(20) 
LAST_NAME      NOT NULL VARCHAR2(25) 
EMAIL          NOT NULL VARCHAR2(25) 
PHONE_NUMBER            VARCHAR2(20) 
HIRE_DATE      NOT NULL DATE         
JOB_ID         NOT NULL VARCHAR2(10) 
SALARY                  NUMBER(8,2)  
COMMISSION_PCT          NUMBER(2,2)  
MANAGER_ID              NUMBER(6)    
DEPARTMENT_ID           NUMBER(4)

If you compare it to your t_emp_rec object then you will see that most of the object attributes are a smaller size than the table columns.

Change the object to have the same sizes and it should work:

CREATE OR REPLACE TYPE t_emp_rec AS OBJECT (
  EMPLOYEE_ID     NUMBER(6),
  FIRST_NAME      VARCHAR2(20),
  JOB_ID          VARCHAR2(10),
  MANAGER_ID      NUMBER(6),
  HIRE_DATE       DATE,
  SALARY          NUMBER(8,2),
  DEPARTMENT_ID   NUMBER(4)
);
/

View based on function, correct query treated as bad

1 Answers