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

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

Sam
Re: SubQuery [message #189856 is a reply to message #189855] Mon, 28 August 2006 02:27 Go to previous messageGo to next message
aorehek
Messages: 52
Registered: August 2006
Member
1. No
2. CLOB, BLOB
Re: SubQuery [message #189857 is a reply to message #189856] Mon, 28 August 2006 02:40 Go to previous message
gold_oracl
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  /
Test

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

Thanks,
Thangam
Previous Topic: Problem with the function
Next Topic: AUTHID
Goto Forum:
  


Current Time: Fri Dec 09 03:56:03 CST 2016

Total time taken to generate the page: 0.07797 seconds