Home » SQL & PL/SQL » SQL & PL/SQL » SubQuery
SubQuery [message #189855] Mon, 28 August 2006 02:18 Go to next message
Messages: 29
Registered: August 2006
Location: Bangalore
Junior Member

Is there any difference in size of varchar2 in SQL and PL/SQL
Is there any data type used other than data type LONG where
size more than 4000 bytes

Re: SubQuery [message #189856 is a reply to message #189855] Mon, 28 August 2006 02:27 Go to previous messageGo to next message
Messages: 52
Registered: August 2006
1. No
Re: SubQuery [message #189857 is a reply to message #189856] Mon, 28 August 2006 02:40 Go to previous message
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Small corrections in your reply..

1) Yes. there is the difference in length b/w SQL and PL/SQL.

Check below
SQL> create table testtab(a varchar2(4000));

Table created.

SQL> drop table testtab;  

Table dropped.

SQL>  create table testtab(a varchar2(4001));
 create table testtab(a varchar2(4001))
ERROR at line 1:
ORA-00910: specified length too long for its datatype

SQL> set serveroutput on
SQL> declare  
  2  a varchar2(32767) := 'Test';
  3  begin
  4  dbms_output.put_line(a);
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> declare
  2  a varchar2(32768) := 'Test';
  3  begin
  4  dbms_output.put_line(a);
  5* end;
SQL> /
a varchar2(32768) := 'Test';
ERROR at line 2:
ORA-06550: line 2, column 12:
PLS-00215: String length constraints must be in range (1 .. 32767)

Hence Maximum size of Varchar2 in SQL is 4000 and in PL/SQL is 32767

Previous Topic: Problem with the function
Next Topic: AUTHID
Goto Forum:

Current Time: Sun Jul 23 09:58:49 CDT 2017

Total time taken to generate the page: 0.06559 seconds