Database Reference
In-Depth Information
The easiest way to change to 32k varchar2 fields is to apply the change in a PDB. The initialization parameter in
the PDB is independent of the value in the root, so you can make the change—maybe on a clone of the PDB—and test
the changed behavior. Here is an example of how the parameter has been changed in a Pluggable Database, PDB1:
[oracle@server1 ~]$ sqlplus sys/***@server1/pdb1 as sysdba
[...]
SQL> alter pluggable database PDB1 close immediate;
Pluggable database altered.
SQL> alter pluggable database pdb1 open upgrade;
Pluggable database altered.
SQL> show parameter max_string_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_string_size string EXTENDED
SQL> @?/rdbms/admin/utl32k
After the script terminates you should restart the PDB in NORMAL mode before you can make use of the
extended length as this example shows:
SQL> create table t2 (vc varchar2(32767));
Table created.
SQL> desc t2
Name Null? Type
------------------------------------ -------- -------------------------
VC VARCHAR2(32767)
Be warned though that you need a different tool other than SQL*Plus to insert that much data into the vc column.
The inherent limit of SQL*Plus is 2500 characters for a column.
Enhanced Support for Top-N Queries
Before Oracle 12c one had to perform a few tricks to get the top-n rows of a query, or to perform pagination of a
result set. It has been said many times that users entering a query are unlikely to move beyond the first few pages of
results. Fetching the complete result set of a query for a user interface usually does not yield much benefit. Oracle's
own SQLDeveloper for example fetches a limited subset of the query result set in the grid first. Only if the users scroll
further down in the result set are more records fetched. This approach keeps resource consumption low and results in
a better user experience.
Before explaining how the new support can benefit your application, let's briefly review how applications are
currently written in order to paginate a result set, such as displaying a search result in a web page. The following
example shows some code slightly adapted from an excellent example Tom Kyte gave in 2006. This example shows
objects owned by the user MARTIN. The objects are listed five at a time.
Search WWH ::
Custom Search