What you need to know about calling functions from SQL ↗

Tom Kyte:

When calling functions from SQL, you had better not rely on how often the functions get called, in what order, or whatever. In short, assume nothing. And remember – SQL rewrites kick in and we rewrite your SQL all of the time. Don’t rely on side effects.

★

Comments Off | Filed in Oracle, Tips | Tags: sql

Conventional (NOAPPEND) parallel inserts available in Oracle DB 11g ↗

Randolf Geist:

Oracle 11g obviously has added the capability to perform a conventional, non-direct-path insert in parallel.

Greg Rahn:

Parallel conventional (NOAPPEND) insert was an 11g new feature, though it seems to have escaped the new features list in the docs. It was added to support cases where parallel insert as select was desired, but the restrictions associated with direct path inserts were not desired.

★

Comments Off | Filed in Links, Oracle | Tags: 11g, sql

Join factorization, a new Oracle DB 11gR2 feature ↗

Hong Su from Inside the Oracle Optimizer:

The Join Factorization transformation was introduced in Oracle 11g Release 2 and applies to UNION ALL queries. Join factorization is a cost-based transformation. It can factorize common computations from branches in a UNION ALL query which can lead to huge performance improvement.

Yet another reason to upgrade to 11gR2.

★

Comments Off | Filed in Links, Oracle | Tags: performance, sql

SQL Hints and Table Aliases

When a hint is added to a query, the Oracle optimizer uses it to choose an execution plan, unless some condition exists that prevents the optimizer from doing so. This post draws your attention to one specific condition that will definitely prevent the optimizer from using the hint.

Note that it is recommended that hints should be used sparingly, and only after you have collected statistics on the relevant tables and evaluated the optimizer plan without hints using EXPLAIN PLAN.

If you have to use hints, however, you need to pay attention to this very basic rule if the hint requires a tablespec:

You must specify the table to be accessed exactly as it appears in the statement. If the statement uses an alias for the table, then use the alias rather than the table name in the hint. However, do not include the schema name with the table name within the hint, even if the schema name appears in the statement.

The following is an example that demonstrates the above rule.

eddie@DB112> SELECT banner FROM v$version;

BANNER
---------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

eddie@DB112> CREATE TABLE my_table
  2  NOPARALLEL
  3  AS
  4     SELECT *
  5       FROM all_objects
  6      WHERE ROWNUM <= 10000;

Table created.

eddie@DB112> EXECUTE dbms_stats.gather_table_stats(user,'MY_TABLE');

PL/SQL procedure successfully completed.

In the following query, the PARALLEL hint instructs the optimizer to use a parallel operation. Notice that the table has no alias and the hint uses the table name.

eddie@DB112> EXPLAIN PLAN
  2     FOR
  3        SELECT /*+ PARALLEL (my_table) */
  4              COUNT (*) FROM my_table;

Explained.

eddie@DB112> SELECT * FROM TABLE (DBMS_XPLAN.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
Plan hash value: 2272413588

--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |    23   (0)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          | 10000 |    23   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| MY_TABLE | 10000 |    23   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------

As expected, the plan shows that a parallel operation was indeed used. Now let’s add an alias to the table and keep everything else the same:

eddie@DB112> EXPLAIN PLAN
  2     FOR
  3        SELECT /*+ PARALLEL (my_table) */
  4              COUNT (*) FROM my_table t;

Explained.

eddie@DB112> SELECT * FROM TABLE (DBMS_XPLAN.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
Plan hash value: 3996063390

-----------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |    42   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |          |     1 |            |          |
|   2 |   TABLE ACCESS FULL| MY_TABLE | 10000 |    42   (0)| 00:00:01 |
-----------------------------------------------------------------------

Oops! No parallel operation was performed. The hint was totally ignored. The plan is the same as if the hint was not there:

eddie@DB112> EXPLAIN PLAN
  2     FOR
  3        SELECT COUNT (*) FROM my_table t;

Explained.

eddie@DB112> SELECT * FROM TABLE (DBMS_XPLAN.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
Plan hash value: 3996063390

-----------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |    42   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |          |     1 |            |          |
|   2 |   TABLE ACCESS FULL| MY_TABLE | 10000 |    42   (0)| 00:00:01 |
-----------------------------------------------------------------------

But, when we use the table alias instead of the table name, the optimizer obeys the hint:

eddie@DB112> EXPLAIN PLAN
  2     FOR
  3        SELECT /*+ PARALLEL (t) */
  4              COUNT (*) FROM my_table t;

Explained.

eddie@DB112> SELECT * FROM TABLE (DBMS_XPLAN.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
Plan hash value: 2272413588

--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |    23   (0)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          | 10000 |    23   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| MY_TABLE | 10000 |    23   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------

Imagine for example a data warehouse process that relies on the PARALLEL hint to finish its SQL aggregation just before the data is fed to a sales report used by management every morning. A developer changes an aggregation query adding an alias to the table but forgetting to change the hint. Oops!

Yes, this is a very basic rule but could have serious consequences if forgotten.

3 Comments | Filed in Oracle, Tips | Tags: hint, sql

Please Request a Reasonable Conversion

Here is something you need to be aware of when you use the EXECUTE IMMEDIATE … USING and OPEN FOR … USING statements in PL/SQL.

Let’s execute this simple anonymous PL/SQL block:

eddie@db11gr2> DECLARE
  2    l_string VARCHAR2 (4000);
  3    l_rc sys_refcursor;
  4  BEGIN
  5    l_string := RPAD (' ', 4000);
  6    OPEN l_rc FOR 'select :1 from dual' USING l_string;
  7    EXECUTE immediate 'select :1 from user_objects where rownum = 1'
  8      INTO l_string USING l_string;
  9  END;
 10  /

PL/SQL procedure successfully completed.

The EXECUTE IMMEDIATE and OPEN FOR statements are used with the USING clause. USING supplies a bind argument for the SQL string. In this example the value of the bind argument is a 4,000 character string.

Now, let’s pass a string greater than 4,000 characters to the OPEN FOR statement:

eddie@db11gr2> DECLARE
  2    l_string VARCHAR2 (4001);
  3    l_rc sys_refcursor;
  4  BEGIN
  5    l_string := RPAD (' ', 4001);
  6    OPEN l_rc FOR 'select :1 from dual' USING l_string;
  7  END;
  8  /
DECLARE
*
ERROR at line 1:
ORA-01460: unimplemented or unreasonable conversion requested
ORA-06512: at line 6

Oops, got an error. Let’s also try the EXECUTE IMMEDIATE statement with a string greater than 4,000 characters:

eddie@db11gr2> DECLARE
  2    l_string VARCHAR2 (4001);
  3  BEGIN
  4    l_string := RPAD (' ', 4001);
  5    EXECUTE immediate 'select :1 from user_objects where rownum = 1'
  6      INTO l_string USING l_string;
  7  END;
  8  /
DECLARE
*
ERROR at line 1:
ORA-01460: unimplemented or unreasonable conversion requested
ORA-06512: at line 5

Got the same error: “ORA-01460: unimplemented or unreasonable conversion requested”.

How about if we pass a NULL:

eddie@db11gr2> DECLARE
  2    l_rc sys_refcursor;
  3  BEGIN
  4    OPEN l_rc FOR 'select :1 from dual' USING NULL;
  5  END;
  6  /
  OPEN l_rc FOR 'select :1 from dual' USING NULL;
                                            *
ERROR at line 4:
ORA-06550: line 4, column 45:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 4, column 3:

In this case we get “PLS-00457: expressions have to be of SQL types”.

Well, there is a restriction on what values you can bind: When binding values to dynamic SQL, only SQL datatypes are supported. You can bind strings, numbers, dates, collections, LOBs, XML documents… However, you cannot bind values having a PL/SQL specific datatype such as Booleans, associative arrays and varchar2 values greater than 4000.

Make sure you keep the above restriction in mind when you use the EXECUTE IMMEDIATE … USING and OPEN FOR … USING statements.

Source and resources:

Comments Off | Filed in Oracle, Tips | Tags: pl/sql, sql

See How Easily You Can Post to Twitter From an Oracle DB

Posting to Twitter from inside an Oracle database is not something new (OraTweet, ORA_Tweet). However, what if you could post to Twitter by simply executing a SELECT statement without using any custom PL/SQL code?

Try the following in your SQL*Plus. It will ask you for your Twitter username and password and the status message that you want to post:

eddie@db11gr2> set define !
eddie@db11gr2> set verify off
eddie@db11gr2> set sqlterminator off
eddie@db11gr2> SELECT HTTPURITYPE(
  2    UriFactory.escapeUri(
  3      'http://query.yahooapis.com/v1/public/yql?q=
  4        USE "http://awads.net/yql/twitter.xml" as tw_t;
  5          INSERT INTO tw_t (status, username,password)
  6            VALUES("!status","!!username","!!password")
  7              &format=json
  8              &env=store://datatables.org/alltableswithkeys&format=xml&callback=cbfunc')).getclob()
  9    json_response
 10  FROM dual
 11  /
Enter value for status: Tweeting from SQL*Plus.
Enter value for username: sqlyql
Enter value for password: sqlyqlpw

JSON_RESPONSE
--------------------------------------------------------------------------------
cbfunc({"query":{"count":"1","created":"2010-05-02T02:02:14Z","lang":"en-US","re
sults":{"status":{"created_at":"Sun May 02 02:02:14 +0000 2010","id":"1322057475
5","text":"Tweeting from SQL*Plus.","source":"<a href=\"http://apiwiki.twitter.c
om/\" rel=\"nofollow\">API</a>","truncated":"false","in_reply_to_status_id":null
,"in_reply_to_user_id":null,"favorited":"false","in_reply_to_screen_name":null,"
user":{"id":"139255376","name":"sqlyql","screen_name":"sqlyql","location":null,"
description":null,"profile_image_url":"http://s.twimg.com/a/1272578449/images/de
fault_profile_1_normal.png","url":null,"protected":"false","followers_count":"1"
,"profile_background_color":"9ae4e8","profile_text_color":"000000","profile_link
_color":"0000ff","profile_sidebar_fill_color":"e0ff92","profile_sidebar_border_c
olor":"87bc44","friends_count":"0","created_at":"Sun May 02 01:59:28 +0000 2010"
,"favourites_count":"0","utc_offset":null,"time_zone":null,"profile_background_i
mage_url":"http://s.twimg.com/a/1272578449/images/themes/theme1/bg.png","profile
_background_tile":"false","notifications":"false","geo_enabled":"false","verifie
d":"false","following":"false","statuses_count":"1","lang":"en","contributors_en
abled":"false"},"geo":null,"coordinates":null,"place":null,"contributors":null}}
}});


Elapsed: 00:00:01.92

The above is made possible using HTTPURITYPE and YQL, the Yahoo! Query Language. Awesome combination!

Note that on June 30, 2010 Twitter will be shutting off basic authentication on the Twitter API. After that date, the above will stop working unless the YQL query is modified to use OAuth.

Are you on Twitter? Follow me at eddieawad and the rest of the Oracle Tweeple at eddieawad/oracle.

2 Comments | Filed in Oracle | Tags: sql, twitter, yql

Are You Soft on Your Deletes?

Frustrated with soft deletes in SQL, Reeditor writes:

In the past, I’ve added a deleted_at (datetime) column and appended “WHERE deleted_at IS NULL” to every query involving that table. But it’s a total pain in the ass, and it’s complicated as shit: there’s always a few queries that are missed, a few developers that forgot or didn’t know they had to check that the record wasn’t deleted, denormalized counts are hard to keep in sync, etc. etc. It always turns into an embarrassing fiasco rather than something you add once and then don’t have to worry about.

Good discussion followed which included a couple of interesting links: What is the best way to implement soft deletion? and The trouble with soft delete.

So, if there is a business need to retain deleted records, do you go soft or hard?

Comments Off | Filed in Oracle | Tags: sql

SQLSaturday Open for Registration

On the weekend of May 22, 2010, the Portland Development community is coming together in a way never experienced before.

Using the University of Portland campus, SQLSaturday, Portland Code Camp, and Portland Bar Camp are combining and coordinating efforts to bring 800-1000 regional technology professionals together for the opportunity to immerse themselves in seminars, presentations, group exploration, and networking. Participants will be able to engage in their preferred technology, as well as to 'sample' other options.

Admittance to this event is free, all costs are covered by donations and sponsorships. Please register soon as seating is limited, and let friends and colleagues know about the event.

>> Click here to register.

Portland SQLSaturday is encouraging presentations related to interoperability of any of the SQL platforms, including T-SQL (SQL Server), PostgreSQL, MySQL, and Oracle PL/SQL. Abstracts for Platform specific sessions are also encouraged. The event is scheduled on Saturday May 22, 2010 from 8:00 AM until 10:00 PM.

Most sessions will be one hour and fifteen minutes (1:15), and a few 'deep dive' sessions will be longer (2.5 hours). The 'deep dive' sessions will be scheduled for 7:00 PM (after the snacks and refreshments.) Also, throughout the day, there may be opportunities for 30 minute 'SQL snack' sessions.

>> Click here to submit your presentation abstract.

I encourage all Oracle SQL and PL/SQL enthusiasts living in the Pacific Northwest to present in and/or attend this not-to-be-missed event.

Posted via email from EddieAwad.com

Comments Off | Filed in Oracle | Tags: pl/sql, sql, sqlsaturday

SQL Injection Prevention Cheat Sheet

This article provides a set of simple techniques for preventing SQL Injection vulnerabilities. These techniques can be used with practically any kind of programming language with any type of database. There are other types of databases, like XML databases, which can have similar problems (e.g., XPath and XQuery injection) and these techniques can be used to protect them as well.More…

1 Comment | Filed in Links, Oracle, Security | Tags: injection, sql

Oracle Database Limits You May Not Know About

The following is a list of database limits which are divided into four categories in addition to the PL/SQL compiler limits. It is an aggregation of 5 separate web pages from the Oracle Database 11g Release 2 documentation library. I put them all here on one page for convenience.

Datatype Limits

Datatypes	Limit	Comments
`BFILE`	Maximum size: 4 GB Maximum size of a file name: 255 characters Maximum size of a directory name: 30 characters Maximum number of open BFILEs: see Comments	The maximum number of `BFILEs` is limited by the value of the `SESSION_MAX_OPEN_FILES` initialization parameter, which is itself limited by the maximum number of open files the operating system will allow.
`BLOB`	Maximum size: (4 GB – 1) * `DB_BLOCK_SIZE` initialization parameter (8 TB to 128 TB)	The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000).
`CHAR`	Maximum size: 2000 bytes	None
`CHAR VARYING`	Maximum size: 4000 bytes	None
`CLOB`	Maximum size: (4 GB – 1) * `DB_BLOCK_SIZE` initialization parameter (8 TB to 128 TB)	The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000).
Literals (characters or numbers in SQL or PL/SQL – more)	Maximum size: 4000 characters	None
`LONG`	Maximum size: 2 GB – 1	Only one `LONG` column is allowed per table.
`NCHAR`	Maximum size: 2000 bytes	None
`NCHAR VARYING`	Maximum size: 4000 bytes	None
`NCLOB`	Maximum size: (4 GB – 1) * `DB_BLOCK_SIZE` initialization parameter (8 TB to 128 TB)	The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000).
`NUMBER`	999…(38 9′s) x10¹²⁵ maximum value -999…(38 9′s) x10¹²⁵ minimum value	Can be represented to full 38-digit precision (the mantissa) Can be represented to full 38-digit precision (the mantissa)
Precision	38 significant digits	None
`RAW`	Maximum size: 2000 bytes	None
`VARCHAR`	Maximum size: 4000 bytes	None
`VARCHAR2`	Maximum size: 4000 bytes	None

Logical Database Limits

Item	Type of Limit	Limit Value
`GROUP BY` clause	Maximum length	The `GROUP BY` expression and all of the nondistinct aggregate functions (for example, `SUM`, `AVG`) must fit within a single database block.
Indexes	Maximum per table	Unlimited
Indexes	Total size of indexed column	75% of the database block size minus some overhead
Columns	Per table	1000 columns maximum
Columns	Per index (or clustered index)	32 columns maximum
Columns	Per bitmapped index	30 columns maximum
Constraints	Maximum per column	Unlimited
Subqueries	Maximum levels of subqueries in a SQL statement	Unlimited in the `FROM` clause of the top-level query `255` subqueries in the `WHERE` clause
Partitions	Maximum length of linear partitioning key	`4` KB – overhead
Partitions	Maximum number of columns in partition key	16 columns
Partitions	Maximum number of partitions allowed per table or index	1024K – 1
Rows	Maximum number per table	Unlimited
Stored Packages	Maximum size	PL/SQL and Developer/2000 may have limits on the size of stored procedures they can call. The limits typically range from `2000` to `3000` lines of code.
Trigger Cascade Limit	Maximum value	Operating system-dependent, typically `32`
Users and Roles	Maximum	`2,147,483,638`
Tables	Maximum per clustered table	32 tables
Tables	Maximum per database	Unlimited

Physical Database Limits

Item	Type of Limit	Limit Value
Database Block Size	Minimum	`2048` bytes; must be a multiple of operating system physical block size
Database Block Size	Maximum	Operating system dependent; never more than `32` KB
Database Blocks	Minimum in initial extent of a segment	2 blocks
Database Blocks	Maximum per datafile	Platform dependent; typically 2²² – 1 blocks
Controlfiles	Number of control files	`1` minimum; `2` or more (on separate devices) strongly recommended
Controlfiles	Size of a control file	Dependent on operating system and database creation options; maximum of `20,000` x (database block size)
Database files	Maximum per tablespace	Operating system dependent; usually `1022`
Database files	Maximum per database	65533 May be less on some operating systems Limited also by size of database blocks and by the `DB_FILES` initialization parameter for a particular instance
Database extents (more)	Maximum per dictionary managed tablespace	`4` GB * physical block size (with K/M modifier); `4` GB (without K/M modifier)
Database extents	Maximum per locally managed (uniform) tablespace	`2` GB * physical block size (with K/M modifier); `2` GB (without K/M modifier)
Database file size	Maximum	Operating system dependent. Limited by maximum operating system file size; typically 2²² or `4` MB blocks
`MAXEXTENTS`	Default value	Derived from tablespace default storage or `DB_BLOCK_SIZE` initialization parameter
`MAXEXTENTS`	Maximum	Unlimited
Redo Log Files	Maximum number of logfiles	Limited by value of `MAXLOGFILES` parameter in the `CREATE DATABASE` statement Control file can be resized to allow more entries; ultimately an operating system limit
Redo Log Files	Maximum number of logfiles per group	Unlimited
Redo Log File Size	Minimum size	`4 MB`
Redo Log File Size	Maximum Size	Operating system limit; typically `2` GB
Tablespaces	Maximum number per database	`64 K` Number of tablespaces cannot exceed the number of database files because each tablespace must include at least one file
Bigfile Tablespaces	Number of blocks	A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion ( 2³² ) blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32 K blocks and 32 TB for a tablespace with 8 K blocks.
Smallfile (traditional) Tablespaces	Number of blocks	A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (2²²) blocks.
External Tables file	Maximum size	Dependent on the operating system.An external table can be composed of multiple files.

Process and Runtime Limits

Item	Type of Limit	Limit Value
Instances per database	Maximum number of cluster database instances per database	Operating system-dependent
Locks	Row-level	Unlimited
Locks	Distributed Lock Manager	Operating system dependent
SGA size	Maximum value	Operating system-dependent; typically `2` to `4` GB for 32-bit operating systems, and > `4` GB for 64-bit operating systems
Advanced Queuing Processes	Maximum per instance	`10`
Job Queue Processes	Maximum per instance	`1000`
I/O Slave Processes	Maximum per background process (DBWR, LGWR, etc.)	15
I/O Slave Processes	Maximum per Backup session	15
Sessions	Maximum per instance	`32` KB; limited by the `PROCESSES` and `SESSIONS` initialization parameters
Global Cache Service Processes	Maximum per instance	`10`
Shared Servers	Maximum per instance	Unlimited within constraints set by the `PROCESSES` and `SESSIONS` initialization parameters, for instance
Dispatchers	Maximum per instance	Unlimited within constraints set by `PROCESSES` and `SESSIONS` initialization parameters, for instance
Parallel Execution Slaves	Maximum per instance	Unlimited within constraints set by `PROCESSES` and `SESSIONS` initialization parameters, for instance
Backup Sessions	Maximum per instance	Unlimited within constraints set by `PROCESSES` and `SESSIONS` initialization parameters, for instance

PL/SQL Compiler Limits

Item	Limit
bind variables passed to a program unit	32768
exception handlers in a program unit	65536
fields in a record	65536
levels of block nesting	255
levels of record nesting	32
levels of subquery nesting	254
levels of label nesting	98
levels of nested collections	no predefined limit
magnitude of a `PLS_INTEGER` or `BINARY_INTEGER`value	-2147483648..2147483647
number of formal parameters in an explicit cursor, function, or procedure	65536
objects referenced by a program unit	65536
precision of a `FLOAT` value (binary digits)	126
precision of a `NUMBER` value (decimal digits)	38
precision of a `REAL` value (binary digits)	63
size of an identifier (characters)	30
size of a string literal (bytes)	32767
size of a `CHAR` value (bytes)	32767
size of a `LONG` value (bytes)	32760
size of a `LONG` `RAW` value (bytes)	32760
size of a `RAW` value (bytes)	32767
size of a `VARCHAR2` value (bytes)	32767
size of an `NCHAR` value (bytes)	32767
size of an `NVARCHAR2` value (bytes)	32767
size of a `BFILE` value (bytes)	4G * value of DB_BLOCK_SIZE parameter
size of a `BLOB` value (bytes)	4G * value of DB_BLOCK_SIZE parameter
size of a `CLOB` value (bytes)	4G * value of DB_BLOCK_SIZE parameter
size of an `NCLOB` value (bytes)	4G * value of DB_BLOCK_SIZE parameter

Sources and Resources:

http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/limits001.htm

9 Comments | Filed in Oracle | Tags: database, limits, pl/sql, sql