santhoshreddy podduturi, November 21, 2016 - 11:36 am UTC
Does SQL Have any special Scalar and Composite Datatypes Like Plsql Have Scalar types,object types?
November 21, 2016 - 5:39 pm UTC
"CREATE TYPE" is in the SQL ref, so yes you can use them in SQL ;)
What do you mean by "special scalar data types"?
A reader, November 22, 2016 - 4:52 am UTC
The NUMBER,VARCHAR2(),DATE are plsql Data types(scaler datatypes) Like plsql,Does SQL have any DATATYPES?
A reader, November 22, 2016 - 11:53 am UTC
But When Creation of table we use VARCAHR2,NUMBER(oracle predefined datatypes) we are not using character(n),national chatacter(n)(ANSI SQL Datatype).I tried them i did not get any errors also table created.but i never seen any one using
SQL datatypes.Will i get any performence issues if i use sql data types?
November 22, 2016 - 5:59 pm UTC
Varchar2 and number are SQL data types!
What precisely do you mean by "SQL data types"? Give us an example.
ANSI, DB2, and SQL/DS Data Types
Duke Ganote, November 22, 2016 - 8:49 pm UTC
A reader, November 23, 2016 - 3:32 am UTC
Difference BW SQL & PL/SQL Data Types
A reader, March 28, 2019 - 9:34 am UTC
March 28, 2019 - 11:27 am UTC
Yep, there are a few differences. If you have max_string_size = standard, the SQL limits for strings are much lower:
sho parameter max_string
NAME TYPE VALUE
--------------- ------ --------
max_string_size string STANDARD
create table t (
c1 varchar2(32767)
);
ORA-00910: specified length too long for its datatype
create table t (
c1 varchar2(4000)
);
Table T created.
declare
v varchar2(32767);
begin
v := lpad ( 'x', 32767, 'x' );
dbms_output.put_line ( length ( v ) );
end;
/
32767
A reader, March 28, 2019 - 3:46 pm UTC
In the link above i mentioned CHAR max size is 32767 and max string enabled works only for VARCHAR2,NVARCHAR2 and RAW data Types.
So Could you show one example using CHAR data type with length upto 32767 bytes.
March 29, 2019 - 5:00 pm UTC
The maximum size of CHAR is always 2,000 bytes in SQL.
CHAR size mentioned in docs is 32767 b6tes
A reader, March 29, 2019 - 5:11 pm UTC
April 01, 2019 - 10:14 am UTC
As Chiappa says below SQL & PL/SQL are different languages. Hence they're subject to different restrictions.
To "A reader"
J. Laurindo Chiappa, March 29, 2019 - 7:43 pm UTC
Hi, let me try a different approach : see, the SQL DATATYPES are used in SQL commands (such as CREATE TABLE), and the PL/SQL datatypes are related with PL/SQL things (such as local variables, created inside a PL/SQL program)... Both languages have a datatype called CHAR, but in SQL char have a limit of 2000 and in PL/SQL the CHAR datatype have another limit, no problem...
Example :
system@DESENV:SQL>create table T1 (col1 CHAR(2000));
Table created.
system@DESENV:SQL>create table T2 (col2 CHAR(2001));
create table T2 (col2 CHAR(2001))
*
ERROR in line 1:
ORA-00910: specified length too long for its datatype
system@DESENV:SQL>
and, in PL/SQL :
system@DESENV:SQL>DECLARE
2 x char(32767);
3 BEGIN
4 null;
5* END;
system@DESENV:SQL>l
1 DECLARE
2 x char(32767);
3 BEGIN
4 null;
5 END;
6 /
PL/SQL procedure successfully completed.
system@DESENV:SQL>DECLARE
2 x char(32768);
3 BEGIN
4 null;
5 END;
6 /
DECLARE
*
ERROR at line 1:
ORA-06550: line 2, column 10:
PLS-00215: String length constraints must be in range (1 .. 32767)
system@DESENV:SQL>
The NAME of the dataype is the same, but SQL and PL/SQL are DIFFERENT languagens, implementing their datatypes with DIFFERENT limits, Right ?
regards,
Chiappa
April 01, 2019 - 10:14 am UTC
Thanks for putting the demo together :)
David, April 01, 2019 - 11:58 am UTC
If you are interested by Oracle data types, this file is for you : $ORACLE_HOME/rdbms/admin/stdspec.sql
Many surprises inside it :-)
David D.
April 01, 2019 - 12:37 pm UTC
:)