Reserved words not alloewed to be declare as an Identifiers. But it is allowed when identifiers are sorrounded by Double quatotion(") mark. When you enclose quatotion mark by double quote the that Identifier becomes case sensitive.
Example:
10:59:03 SQL> ed
Wrote file afiedt.buf
1 declare
2 "select" varchar2(20) := 'select * from emp;';
3 begin
4 dbms_output.put_line("select");
5* end;
11:01:42 SQL> /
select * from emp;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.0311:01:43
See Another Example:
SQL> ed
Wrote file afiedt.buf
1 declare
2 "select" varchar2(20) := 'select * from emp;';
3 begin 4 dbms_output.put_line("Select"); -- Note S in 'Select' is in Upper Case.
5* end;
11:03:34 SQL> /
dbms_output.put_line("Select");
*
ERROR at line 4:ORA-06550: line 4,
column 23:PLS-00201: identifier 'Select' must be declaredORA-06550: line 4,
column 2:PL/SQL: Statement ignored
Elapsed: 00:00:00.04
11:03:35 SQL>
Monday, November 3, 2008
Friday, September 12, 2008
PL/SQL Datatype PART 3
DATE
You use the DATE datatype to store fixed-length datetimes, which include the time of day in seconds since midnight. Valid dates range from January 1, 4712 BC to December 31, 9999 AD. PL/SQL automatically converts character values in the default date format to DATE values. The default date format is set by the Oracle initialization parameter NLS_DATE_FORMAT.
TIMESTAMP
The datatype TIMESTAMP, which extends the datatype DATE, stores the year, month, day, hour, minute, and second. Precision specifies the number of digits in the fractional part of the seconds field. (While decalring TIMESTAMP type variable we can pass optional parameter Precision whose range between 0 to 9 and has default value as 6.)
You use the DATE datatype to store fixed-length datetimes, which include the time of day in seconds since midnight. Valid dates range from January 1, 4712 BC to December 31, 9999 AD. PL/SQL automatically converts character values in the default date format to DATE values. The default date format is set by the Oracle initialization parameter NLS_DATE_FORMAT.
TIMESTAMP
The datatype TIMESTAMP, which extends the datatype DATE, stores the year, month, day, hour, minute, and second. Precision specifies the number of digits in the fractional part of the seconds field. (While decalring TIMESTAMP type variable we can pass optional parameter Precision whose range between 0 to 9 and has default value as 6.)
Wednesday, September 10, 2008
PL/SQL Datatype PART 2
LOB Types
The LOB (large object) datatypes BFILE, BLOB, CLOB, and NCLOB let you store blocks of unstructured data (such as text, graphic images, video clips, and sound waveforms) up to four gigabytes in size. And, they allow efficient, random, piece-wise access to the data.
BFILE
You use the BFILE datatype to store large binary objects in operating system files outside the database. Every BFILE variable stores a file locator, which points to a large binary file on the server. BFILEs are read-only.
BLOB
You use the BLOB datatype to store large binary objects in the database, in-line or out-of-line. Every BLOB variable stores a locator, which points to a large binary object. BLOBs participate fully in transactions, are recoverable, and can be replicated. Changes made by package DBMS_LOB can be committed or rolled back.
CLOB
You use the CLOB datatype to store large blocks of character data in the database.
NCLOB
You use the NCLOB datatype to store large blocks of NCHAR data in the database.
Maximun size for all LOB's cannot exceed 4GB.
BOOLEAN
You use the BOOLEAN datatype to store the logical values TRUE, FALSE, and NULL . Only logic operations are allowed on BOOLEAN variables. You cannot insert the values TRUE and FALSE into a database column. Also, you cannot select or fetch column values into a BOOLEAN variable.
The LOB (large object) datatypes BFILE, BLOB, CLOB, and NCLOB let you store blocks of unstructured data (such as text, graphic images, video clips, and sound waveforms) up to four gigabytes in size. And, they allow efficient, random, piece-wise access to the data.
BFILE
You use the BFILE datatype to store large binary objects in operating system files outside the database. Every BFILE variable stores a file locator, which points to a large binary file on the server. BFILEs are read-only.
BLOB
You use the BLOB datatype to store large binary objects in the database, in-line or out-of-line. Every BLOB variable stores a locator, which points to a large binary object. BLOBs participate fully in transactions, are recoverable, and can be replicated. Changes made by package DBMS_LOB can be committed or rolled back.
CLOB
You use the CLOB datatype to store large blocks of character data in the database.
NCLOB
You use the NCLOB datatype to store large blocks of NCHAR data in the database.
Maximun size for all LOB's cannot exceed 4GB.
BOOLEAN
You use the BOOLEAN datatype to store the logical values TRUE, FALSE, and NULL . Only logic operations are allowed on BOOLEAN variables. You cannot insert the values TRUE and FALSE into a database column. Also, you cannot select or fetch column values into a BOOLEAN variable.
Thursday, August 21, 2008
PL/SQL Datatype PART 1
PLS_INTEGER and BINARY_INTEGER have the same magnitude range, they are not fully compatible. When a PLS_INTEGER calculation overflows, an exception is raised. However, when a BINARY_INTEGER calculation overflows, no exception is raised if the result is assigned to a NUMBER variable. Use PLS_INTEGER for better performance.
The CHAR datatype takes an optional parameter that lets you specify a maximum size up to 32767 bytes.
If you do not specify a maximum size, it defaults to 1. If you specify the maximum size in bytes rather than characters, a CHAR(n) variable might be too small to hold n multibyte characters. To avoid this possibility, use the notation CHAR(n CHAR)so that the variable can hold n characters in the database character set, even if some of those characters contain multiple bytes. When you specify the length in characters, the upper limit is still 32767 bytes. So for double-byte and multibyte character sets, you can only specify 1/2 or 1/3 as many characters as with a single-byte character set.
When you do not use the CHAR or BYTE qualifiers, the default is determined by the setting of the NLS_LENGTH_SEMANTICS initialization parameter. When a PL/SQL procedure is compiled, the setting of this parameter is recorded, so that the same setting is used when the procedure is recompiled after being invalidated.
You use the LONG datatype to store variable-length character strings. LONG RAW datatype to store binary data or byte strings. LONG RAW data is like LONG data, except that LONG RAW data is not interpreted by PL/SQL. Oracle recommends migrating any LONG data to the CLOB type, and any LONG RAW data to the BLOB type. You can reference LONG columns in UPDATE, INSERT, and (most) SELECT statements, but not in expressions, SQL function calls, or certain SQL clauses such as WHERE, GROUP BY, and CONNECT BY.
RAW
You use the RAW datatype to store binary data or byte strings. For example, a RAW variable might store a sequence of graphics characters or a digitized picture. Raw data is like VARCHAR2 data, except that PL/SQL does not interpret raw data.
ROWID and UROWID
Internally, every database table has a ROWID pseudocolumn, which stores binary values called rowids. Each rowid represents the storage address of a row. A physical rowid identifies a row in an ordinary table. A logical rowid identifies a row in an index-organized table. The ROWID datatype can store only physical rowids. However, the UROWID (universal rowid) datatype can store physical, logical, or foreign (non-Oracle) rowids.
VARCHAR2
You use the VARCHAR2 datatype to store variable-length character data. How the data is represented internally depends on the database character set. Small VARCHAR2 variables are optimized for performance, and larger ones are optimized for efficient memory use. The cutoff point is 2000 bytes. For a VARCHAR2 that is 2000 bytes or longer, PL/SQL dynamically allocates only enough memory to Predefined Datatypes hold the actual value. For a VARCHAR2 variable that is shorter than 2000 bytes,PL/SQL preallocates the full declared length of the variable. For example, if you assign the same 500-byte value to a VARCHAR2(2000 BYTE) variable and to a VARCHAR2(1999 BYTE) variable, the former takes up 500 bytes and the latter takes up 1999 bytes.
NCHAR
You use the NCHAR datatype to store fixed-length (blank-padded if necessary) national character data. How the data is represented internally depends on the national character set specified when the database was created.
NVARCHAR2
You use the NVARCHAR2 datatype to store variable-length Unicode character data. How the data is represented internally depends on the national character set.
DATATYPE MAXIMUM SIZE:
NUMBER 38
CHAR 2000
LONG 32760
LONG RAW 32760
RAW 32767
NCHAR 2000
NVARCHAR2 4000
The CHAR datatype takes an optional parameter that lets you specify a maximum size up to 32767 bytes.
If you do not specify a maximum size, it defaults to 1. If you specify the maximum size in bytes rather than characters, a CHAR(n) variable might be too small to hold n multibyte characters. To avoid this possibility, use the notation CHAR(n CHAR)so that the variable can hold n characters in the database character set, even if some of those characters contain multiple bytes. When you specify the length in characters, the upper limit is still 32767 bytes. So for double-byte and multibyte character sets, you can only specify 1/2 or 1/3 as many characters as with a single-byte character set.
When you do not use the CHAR or BYTE qualifiers, the default is determined by the setting of the NLS_LENGTH_SEMANTICS initialization parameter. When a PL/SQL procedure is compiled, the setting of this parameter is recorded, so that the same setting is used when the procedure is recompiled after being invalidated.
You use the LONG datatype to store variable-length character strings. LONG RAW datatype to store binary data or byte strings. LONG RAW data is like LONG data, except that LONG RAW data is not interpreted by PL/SQL. Oracle recommends migrating any LONG data to the CLOB type, and any LONG RAW data to the BLOB type. You can reference LONG columns in UPDATE, INSERT, and (most) SELECT statements, but not in expressions, SQL function calls, or certain SQL clauses such as WHERE, GROUP BY, and CONNECT BY.
RAW
You use the RAW datatype to store binary data or byte strings. For example, a RAW variable might store a sequence of graphics characters or a digitized picture. Raw data is like VARCHAR2 data, except that PL/SQL does not interpret raw data.
ROWID and UROWID
Internally, every database table has a ROWID pseudocolumn, which stores binary values called rowids. Each rowid represents the storage address of a row. A physical rowid identifies a row in an ordinary table. A logical rowid identifies a row in an index-organized table. The ROWID datatype can store only physical rowids. However, the UROWID (universal rowid) datatype can store physical, logical, or foreign (non-Oracle) rowids.
VARCHAR2
You use the VARCHAR2 datatype to store variable-length character data. How the data is represented internally depends on the database character set. Small VARCHAR2 variables are optimized for performance, and larger ones are optimized for efficient memory use. The cutoff point is 2000 bytes. For a VARCHAR2 that is 2000 bytes or longer, PL/SQL dynamically allocates only enough memory to Predefined Datatypes hold the actual value. For a VARCHAR2 variable that is shorter than 2000 bytes,PL/SQL preallocates the full declared length of the variable. For example, if you assign the same 500-byte value to a VARCHAR2(2000 BYTE) variable and to a VARCHAR2(1999 BYTE) variable, the former takes up 500 bytes and the latter takes up 1999 bytes.
NCHAR
You use the NCHAR datatype to store fixed-length (blank-padded if necessary) national character data. How the data is represented internally depends on the national character set specified when the database was created.
NVARCHAR2
You use the NVARCHAR2 datatype to store variable-length Unicode character data. How the data is represented internally depends on the national character set.
DATATYPE MAXIMUM SIZE:
NUMBER 38
CHAR 2000
LONG 32760
LONG RAW 32760
RAW 32767
NCHAR 2000
NVARCHAR2 4000
Subscribe to:
Posts (Atom)
