Oct 8, 2012



N is used as escape character for national character set

In 9.2, Oracle has to convert your entire SQL statement to the database character set before executing it. In 10.2, you can set ORA_NCHAR_LITERAL_REPLACE to TRUE which would avoid converting N' escaped literals to the database character set,

If you are using 10g and you care about Chinese you should seriously consider using AL32UTF8.

AL32UTF8 contains a large number of additional Chinese characters. The most important ones are a slew of Hong Kong specific characters that are used most frequently in names. All HK systems for HK government are required to support these characters.

But if you do this, be aware that Dev 6i does not support AL32UTF8.

The chinese is a MULTIBYTE character and only UTF8 can handle this.

When creating the database using the UTF8 character set, the Chinese character can be stored in and extracted from the varchar2 data type column. (NCHAR is AL16UTF16).



------------------------------ ----------------------------------------



UTF8, ALT32UTF8 would be capable of storing Japanese symbols, WE8ISO8859P1 wouldn't

Any new system being built for anything more important than a school project should beUTF-8.

Use lengthb in these cases.

The 'N' Variant

So, of what use are the NVARCHAR2 and NCHAR (for completeness)? They are used in systems where the need to manage and store multiple character sets arises. This typically happens in a database where the predominant character set is a single-byte fixed-width one (such as WE8ISO8859P1), but the need arises to maintain and store some multibyte data. There are many systems that have legacy data but need to support multibyte data for some new applications, or systems that want the efficiency of a single-byte character set for most operations (string operations on a fixed-width string are more efficient than on a string where each character may store a different number of bytes), but need the flexibility of multibyte data at some points.

The NVARCHAR2 and NCHAR datatypes support this need. They are generally the same as their VARCHAR2 and CHAR counterparts, with the following exceptions:

* Their text is stored and managed in the databases national character set, not the default character set.

* Their lengths are always provided in characters, whereas a CHAR/VARCHAR2 may specify either bytes or characters.

In Oracle9i and above, the database¿s national character set may take one of two values: UTF8 or AL16UTF16 (UTF-16 in 9i; AL16UTF16 in 10g). This makes the NCHAR and NVARCHAR types suitable for storing only multibyte data, which is a change from earlier releases of the database (Oracle8i and earlier allowed you to choose any character set for the national character set).

select dump('xo'),ascii('x'),ascii('o') from dual;

select unistr('\8349') from dual;


All the tables/plsql package are defined with the default byte semantics for e.g. Customer_Name VARCHAR2(80). Now the issue is because of the new incoming chinese characters it throws error "too large value" errors (though they are less then 80 charactes in case of  Customer_Name

To resolve this change the COLUMN definition from VARCHAR2(80) to VARCHAR2(80 CHAR)

Some more Details -----------------

Sep 28, 2012

Oracle Histogram

                       Histograms are used to predict cardinality and the number of rows returned to a query .The Oracle Query Optimizer uses histograms to predict better query plans. The ANALYZE command or DBMS_STATS package can be used to compute these histograms.A histogram is a frequency distribution meta-data that describes the distribution of data values within a column of a table.A histogram is a collection of information about the distribution of values within a column.

In some cases, the distribution of values within a column of a table will affect the optimizers decision to use an index vs. perform a full-table scan. This scenario occurs when the value with a where clause has a disproportional amount of values, making a full-table scan cheaper than index access.

Histograms are also important for determine the optimal table join order.

Sep 21, 2012

PLSQL Compiler

SQL compilation implies execution plans generation
PL/SQL compilation implies P-code generation

The Front-end:
If compiler front-end founds any error, it outputs an error-report and compilation of the unit is aborted. The output of front-end is an internal representation that exactly captures the source code’s semantics. PL/SQL compiler uses DIANA (Descriptive Intermediate Attributed Notation for Ada) for its internal representation. The front-end guarantees that when it does not report an error, the DIANA it generates is correct and needs no further correctness checking.

It is an abstract data type such that each object of the type is a representation of an intermediate form of an Ada program

The Back-end (Code-Generator):
Back-end takes DIANA as a input and generates an executable representation of the program in the machine code of the target machine. The compiler back-end plays a critical role in generating fast run-time code. The output is the code for PL/SQL Virtual machine.

PL/SQL Virtual Machine:
The instruction set of the PVM is exactly analogous to that of a computer chip. The difference, of course, is that the PVM is implemented in software (it is written in C and linked into the ORACLE executable) while the instruction set of a chip is implemented directly in hardware. A computer implemented in software is commonly called a virtual machine and any interpreted language has such a machine.

Native Compilation of PL/SQL:

In the native mode, it translates the Machine Code into C source code with the same semantics.

For more details


May 9, 2012

Workspace Error : ORA-20229


ORA-20229: statement 'delete from mdsys.sdo_geom_metadata_table' failed during EnableVersioning. Error:
ORA-20229: statement 'select count(*)
       from mdsys.sdo_geom_metadata_table
       where upper(
ORA-06512: at "WMSYS.LT", line 9175
ORA-06512: at line 1

when this error occurs

delete the FACEBOOKBOUNDARY_lt  entry from user_sdo_geom_metadata table
and then execute


it will works


Oracle Discoverer - I

     If you want to create new worksheets and analyze data from both relational and multi-dimensional data sources across the Web, you will use Discoverer Plus  if you want to create new worksheets and analyze data from relational data sources using a Windows application on a PC, you will use Discoverer Desktop
 if you want to analyze data in existing worksheets, you will use Discoverer Viewer or Discoverer portlets (that have been added to an Oracle Portal page using Discoverer Portlet Provider)

: If you want to display Discoverer worksheets as gauges in dashboard-style portals, you will use Discoverer Portlet Provider and Discoverer portlets  if you want to manage the end user view of relational data, you will use Discoverer Administrator

 Oracle BI Discoverer’s powerful and intuitive user interface enables you to:  find data that you know is in the database  access data quickly without waiting for the computer to search through the entire database
 view data in a familiar spreadsheet-style format that is easy to read and understand  analyze data using a variety of powerful techniques including:  drilling up and down through data  finding data that meets certain conditions or that falls within ranges that you specify  sorting data  prepare reports showing the results of your analysis  share data with other people, and in other applications (e.g. Microsoft Excel)

Your ultimate goal in using OracleBI Discoverer is to analyze data to arrive at a profitable business decision

Which telephone lines need greater bandwidth?
pivoting data to create comparisons  drilling up and down in data to see consolidated or more detailed information  drilling out to analyze data in other applications  creating totals, calculating percentages, and creating custom calculations  displaying data visually in graphs and charts

A business area is a collection of related information in the database. The Discoverer manager works with the different departments in your organization to identify the information that each department requires from the database. A folder is similar to a database table or view. Indeed, a folder can be based directly on a database table or view an item is similar to a column in a database table.

In a relational data source, data is organized in tables. A table is a data structure with columns and rows. Multidimensional data is data that is organized by one or more dimensions. These multidimensional data structures are often referred to as cubes.

A cube in a multidimensional data source has the following components:

A measure, which is the name given to the data itself; that is, to the data that you track such as sales figures or cost numbers.

 One or more dimensions. Dimension is the name given to the parts of the cube that categorize the data, such as Product, Geography, and Time. Dimensions have dimension members, dimension hierarchies, and attributes.

In OLAP metadata, measures represent data that can be examined and analyzed in crosstabs and graphs.

The Discoverer Catalog is a repository for storing and retrieving definitions of objects for Discoverer Plus OLAP and can be used by applications that are built with Oracle Business Intelligence Beans (BI Beans). With Discoverer Plus OLAP, you use the Catalog to store objects such as workbooks, calculations, and saved selections and to share objects with others who have access to the Catalog. For example, you can use a BI Beans application to create a graph and store it in the Catalog. If another user has appropriate access, that user can retrieve the graph that you stored in the Catalog and insert the graph into a new worksheet in Discoverer Plus OLAP.

You can maximize Discoverer performance.

 use tabular reports rather than cross-tabular reports
 minimize the number of page items in reports
 avoid wide cross tabular report
 avoid creating reports that return tens of thousands of rows
 provide parameters to reduce the amount of data produced
 minimize the number of worksheets in workbooks
 remove extraneous worksheets from workbooks