Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

how does the where clause work here?

User_68DIPDec 29 2020

SELECT ename, e.deptno, dname, loc
FROM emp e, dept d
WHERE e.deptno > d.deptno;

This post has been answered by Billy Verreynne on Dec 29 2020
Jump to Answer

Comments

Suri
Hi,

Maximum size for VARCHAR2 is 4000. So you cant add 32767 characters to a variable which you have defined with VARCHAR2 datatype.

Below will work
DECLARE
  LEN1  NUMBER;
  STR   VARCHAR2(32767);
BEGIN
  
  STR := RPAD('*', 4000, '*');
  
  SELECT LENGTH(STR) INTO LEN1 FROM DUAL;
  DBMS_OUTPUT.PUT_LINE('LEN1: '||LEN1);
 
END;
Thanks,
Suri

Edited by: Suri on Aug 8, 2012 1:50 PM

Edited by: Suri on Aug 8, 2012 1:52 PM
APC
In your first example the database engine overrode your length for the RPAD() call with the SQL limit (4000 characters). In your second example you presented the engine with a 32K string and it hurled. This is because the engine hasn't been programmed to truncate a string which is too long.

If anything I think the first example is inconsistent. Certainly that behaviour is [url http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions147.htm#i78723]not documented.

Cheers, APC
1 - 2

Post Details

Added on Dec 29 2020
4 comments
57 views