Oracle in World: February 2009

Saturday, February 28, 2009

What is NLS_LANG environmental variable?

NLS_LANG is a client side environmental variable. To specify the locale behavior- setting the NLS_LANG environment parameter is the simplest way.

With the setting of NLS_LANG parameter on client machine it is specified the language, territory and character set used by the client application. As through NLS_LANG parameter, client character set is also specified so oracle has an idea which is the character set for data entered or displayed by a client program as well as Oracle can do (if needed) conversion from the client's character set to the database character set.

On UNIX machine NLS_LANG parameter is an environmental variable and on windows machine this value comes from registry settings.

The parameter NLS_LANG holds the following format.
NLS_LANG=[Language]_[Territory].[clients character set]
The default value of NLS_LANG is AMERICAN_AMERICA.US7ASCII which indicates that
The language is AMERICAN,
the territory is AMERICA, and
the character set is US7ASCII.

The first part of NLS_LANG parameter is language and it is used for Oracle Database messages, sorting, day names, and month names. Each language has a unique name.
The language specifies default values for territory and character set so if language is specified then the other two arguments can be omitted. Language can have the value like AMERICAN, GERMAN, FRENCH, JAPANESE etc. The default value is AMERICAN.

The second part of NLS_LANG parameter is territory and it is used for default date, monetary, and numeric formats. Each territory has a unique name. Territory can have the value like AMERICA, FRANCE, JAPAN, CANADA etc. If the territory is not specified, then the value is derived from the language value.

The third part of NLS_LANG parameter is the client character set. It specifies the character set that is used by the client application. The client character set used for Oracle should be equivalent to the character set supported for the client machine. This character set should also be equivalent to or a subset of the character set used for your database so that every character input through the terminal has a matching character to map to in the database. Example of client character set is US7ASCII, WE8ISO8859P1, WE8DEC, WE8MSWIN1252 etc.

It is important to note that all three parts of NLS_LANG environmental variable/parameter are optional. This means if any of the parts are not specified then default value is used- may be the default value is derived value. You can specify Territory and/or character set without language value; in this case your must include the preceding delimiter -underscore (_) for territory and period (.) for character set. If you don't include the delimiter then the whole value is parsed as a language name.

For example you can only set territory portion by,
NLS_LANG=_FRANCE

You can only set client character set portion by,
NLS_LANG=.WE8MSWIN1252

The three parts of NLS_LANG can be specified in many combination but all of the combination may not work properly. Like,
NLS_LANG = JAPANESE_JAPAN.WE8ISO8859P1

This combination can be will not work properly. Beacuse the specification will try to support Japanese by using a Western European character set but WE8ISO8859P1 character set does not support any Japanese characters.

So if you set your NLS_LANG environmental variable above then you can't store or display Japanese character.

Some logical combination,
NLS_LANG = AMERICAN_AMERICA.WE8MSWIN1252
NLS_LANG = FRENCH_CANADA.WE8ISO8859P1
NLS_LANG = JAPANESE_JAPAN.JA16EUC

In server machine there is no need to set NLS_LANG environmental variable. This variable is only needed for client machine. The character set defined for NLS_LANG environmental variable should be the subset or equal to the database character set so that oracle can aware of each character set and thus can convert client character set correctly. It is also important that character set value of NLS_LANG variable should reflect client machine supported character set so that client machine can display that properly. For example if japanese character set is not installed in client machine but NLS_LANG parameter is set as JAPANESE_JAPAN.JA16EUC then client will not be able to see JAPANESE characters properly.

Important Notes About NLS_LANG Parameter
1)NLS_LANG is used to let Oracle know what character set client's OS is using so that Oracle can do (if needed) conversion from the client's character set to the database characterset.

2)Don't think that NLS_LANG needs to be the same as the database characterset.

3)The characterset defined with the NLS_LANG parameter does not change your client's character set. You cannot change the characterset of your client by using a different NLS_LANG setting. NLS_LANG is used to let Oracle know what characterset you are using on the client side.

4)Don't think that, if you don't set the NLS_LANG on the client it uses the NLS_LANG of the server (which is not true). If you don't set it then default NLS_LANG as described earlier in this post is used.

5)If the NLS_LANG variable match with database character set then oracle will perform no validation on the character set; and thus incorrect NLS_LANG settings may cause to enter garbage data into the database.

Related Documents
Unicode characterset in Oracle database.
What is database character set and how to check it
Different ways to set up NLS parameters
What is national character set / NLS_NCHAR_CHARACTERSET?
Which datatypes use the National Character Set?
What is character set and character set encoding

Different ways to set up NLS parameters

The word NLS means National Language Support. The NLS_* parameters determine the
locale-specific behavior on both the client and the server; where * of NLS_* is for various strings which make various NLS parameters.

There are many NLS_* parameters like NLS_SORT, NLS_LANGUAGE, NLS_CHARACTERSET, NLS_DATE_LANGUAGE etc. In this post I will show how the NLS parameters can be set based on their setting of priority.

1)In SQL functions:
If you set NLS_* parameters inside SQL functions then that setting has the highest priority.

You can set in SQL functions like,
TO_CHAR(sysdate, 'DD/MON/YYYY', 'nls_date_language = FRENCH')

Below is an example. Note that in my client machine FRENCH language is not installed so it might not display properly.


SQL> select sysdate from dual;

SYSDATE
---------
07-FEB-09

SQL> select TO_CHAR(sysdate, 'DD/MON/YYYY', 'nls_date_language = FRENCH') from dual;

TO_CHAR(SYSDA
-------------
07/F╔VR./2009

Setting in this way (inside sql functions) overrides the default values that are set for the session in the initialization parameter file, set for the client with environment variables, or set for the session by the ALTER SESSION statement.

2)With the ALTER SESSION statement:
Setting through ALTER SESSION parameter has the second highest priority. Setting by an ALTER SESSION statement override the default values that are set for the session in the initialization parameter file or set by the client with environment variables.

Below is an example. As in my client machine Japanese language is not installed so displaying in Japanese character might not work properly.


SQL> select sysdate from dual;

SYSDATE
---------
07-FEB-09

SQL> alter session set NLS_DATE_LANGUAGE=JAPANESE;

Session altered.

SQL> select sysdate from dual;

SYSDATE
----------
07-2┐  -09

3)Through Environmental variable on the client machine:
This setting has the third highest priority. Through OS environmental variable you can set NLS_* parameters. Setting of environmental variable is platform specific. On windows machine you can set by,
C:>set NLS_*=value;
On unix machine
$export NLS_*=value (bash shell)
$setenv NLS_*=value (c shell)

Below is an example on my windows client machine.
C:\>set NLS_SORT=FRENCH

4)As initialization parameters on the server:
You can set the NLS_* parameters in the server machine inside the initialization parameter file. Setting in the initialization parameter specify a default session NLS environment. Setting in this way has no effect on the client side, they control only the server's behavior.
For example, if you use spfile then you can set NLS_TERRITORY parameter by below,

SQL> ALTER SYSTEM SET NLS_TERRITORY = "CZECH REPUBLIC" scope=spfile;

System altered.
Then in order to effect bounce database.

If I draw a table based on priority and ways to do then it will be like,


Priority         Ways to do the task.
-----------      -----------------------------------------
1 (highest)      Set in SQL functions
2                Set by an ALTER SESSION statement
3                Set as an environment variable
4                Specified in the initialization parameter file
5 (lowest)       Default

Related Documents
Unicode characterset in Oracle database.
What is NLS_LANG environmental variable?
What is database character set and how to check it
What is national character set / NLS_NCHAR_CHARACTERSET?
Which datatypes use the National Character Set?
What is character set and character set encoding

How to know whether there is N-type columns on database

Below query will return the name of the owner and the table whether there is N-type columns in the database.


SQL> select distinct OWNER, TABLE_NAME from DBA_TAB_COLUMNS where DATA_TYPE
in ('NCHAR','NVARCHAR2', 'NCLOB') order by 1;

OWNER                          TABLE_NAME
------------------------------ ------------------------------
SYS                            ALL_REPPRIORITY
SYS                            DBA_AUDIT_EXISTS
SYS                            DBA_AUDIT_OBJECT
SYS                            DBA_AUDIT_STATEMENT
SYS                            DBA_AUDIT_TRAIL
SYS                            DBA_COMMON_AUDIT_TRAIL
SYS                            DBA_FGA_AUDIT_TRAIL
SYS                            DBA_REPPRIORITY
SYS                            DEFLOB
SYS                            STREAMS$_DEF_PROC
SYS                            USER_AUDIT_OBJECT
SYS                            USER_AUDIT_STATEMENT
SYS                            USER_AUDIT_TRAIL
SYS                            USER_REPPRIORITY
SYSTEM                         DEF$_LOB
SYSTEM                         DEF$_TEMP$LOB
SYSTEM                         REPCAT$_PRIORITY

17 rows selected.

The DBA_FGA_AUDIT_TRAIL comes for Fine Grained Auditing.

ALL_REPPRIORITY, DBA_REPPRIORITY, USER_REPPRIORITY, DEF$_TEMP$LOB , DEF$_TEMP$LOB and REPCAT$_PRIORITY comes for Advanced Replication.

DEFLOB comes for Deferred Transactions functionality.

STREAMS$_DEF_PROC comes for Oracle Streams.

Related Documents
Unicode characterset in Oracle database.
What is NLS_LANG environmental variable?
What is database character set and how to check it
Different ways to set up NLS parameters
What is national character set / NLS_NCHAR_CHARACTERSET?
Which datatypes use the National Character Set?
What is character set and character set encoding

Friday, February 27, 2009

Which datatypes use the National Character Set?

There are three datatypes which can store data in the national character set.

1)NCHAR: It is fixed length national character set- character datatype. This datatype uses CHAR length semantics, that is, the length of the NCHAR datatype column is defined in characters.

2)NVARCHAR2: It is variable length national character set- character datatype. This datatype uses CHAR length semantics, that is, the length of the NVARCHAR2 datatype column is defined in characters.

3)NCLOB: It stores national character set data up to four gigabytes. Data is always stored in UCS2 or AL16UTF16, even if the NLS_NCHAR_CHARACTERSET is UTF8.

If you use NCHAR/NVARCHAR2/NCLOB data type then, use the (N'...') syntax when coding these data type so that literals are denoted as being in the national character set by prefixing letter 'N'.

Below is an example.


SQL> create table t_test(col1 NVARCHAR2(30));

Table created.

SQL> insert into t_test values(N'This is NLS_NCHAR_CHARACTERSET');

1 row created.

Related Documents
Unicode characterset in Oracle database.
What is NLS_LANG environmental variable?
What is database character set and how to check it
Different ways to set up NLS parameters
What is national character set / NLS_NCHAR_CHARACTERSET?
What is character set and character set encoding

What is national character set / NLS_NCHAR_CHARACTERSET?

The national character set is the character set which is defined in oracle database in addition to normal character set.

The normal character set is defined by the parameter NLS_CHARACTERSET and the national character set is defined by the parameter NLS_NCHAR_CHARACTERSET.

The national character set is used for data stored in NCHAR, NVARCHAR2 and NCLOB columns while the normal character set is used for data stored in CHAR, VARCHAR2, CLOB columns.

You can get the value of national character set or NLS_NCHAR_CHARACTERSET by,


SQL> select value from nls_database_parameters where parameter='NLS_NCHAR_CHARACTERSET';

VALUE
----------------------------------------
AL16UTF16

SQL> select value$ from sys.props$ where name='NLS_NCHAR_CHARACTERSET';

VALUE$
--------------------------------------------------------------------------------
AL16UTF16

SQL> select property_value from database_properties where property_name
='NLS_NCHAR_CHARACTERSET';

PROPERTY_VALUE
--------------------------------------------------------------------------------
AL16UTF16

NLS_NCHAR_CHARACTERSET is defined when the database is created and specified with the CREATE DATABASE command.

The default value of NLS_NCHAR_CHARACTERSET is AL16UTF16.

From Oracle 9i onwards the NLS_NCHAR_CHARACTERSET can have only 2 values, either UTF8 or AL16UTF16 and both are unicode character sets.

National character set are always defined in CHAR length semantics and you cannot define them in BYTE. That means if you defines NCHAR(5) then 5 maximum characters can be stored regardless of how many bytes they can hold.

Many one thinks that they need to use the NLS_NCHAR_CHARACTERSET to have UNICODE support in oracle but this is not true. One can always use UNICODE in either two ways. Storing data into NCHAR, NVARCHAR2 or NCLOB columns or you can perfectly use "normal" CHAR and VARCHAR2 columns for storing unicode in a database who has a AL32UTF8 / UTF8 NLS_CHARACTERSET.

Thursday, February 26, 2009

What is Oracle Globalization Support

The term Oracle Globalization Support is used for oracle database as oracle database now support to store, process, and retrieve data from all languages. It also ensures that database utilities, error messages, date, time, monetary, numeric, and calendar conventions automatically adapt to any native language and locale in oracle.

Before 9i the term Oracle Globalization Support term was referred as National Language Support(NLS) features. From 9i onwards, NLS is actually a subset of globalization support. NLS is the ability to choose a national language and store data in a specific character set.

The oracle globalization support feature enables you to develop multilingual applications and software products which can be accessed from anywhere in the world and in any languages. In the database you can now store any language you wish.

Related Documents
Unicode characterset in Oracle database.
What is NLS_LANG environmental variable?
What is database character set and how to check it
Different ways to set up NLS parameters
What is national character set / NLS_NCHAR_CHARACTERSET?
Which datatypes use the National Character Set?
What is character set and character set encoding

What is database character set and how to check it

Note that database character set refers to the term character set encoding and in oracle database the terms character set and character set encoding are often used interchangeably.

The database character set in oracle determines the set of characters can be stored in the database. It is also used to determine the character set to be used for object identifiers and PL/SQL variables and for storing PL/SQL program source.

The database character set information is stored in the data dictionary tables named SYS.PROPS$.

You can get the character set used in the database by SYS.PROPS$ table or any other views (like database_properties/ nls_database_parameters) exist in the database. The parameter NLS_CHARACTERSET value contains the database character set name. Get it from,


SQL> select value$ from sys.props$ where name='NLS_CHARACTERSET';

VALUE$
--------------------------------------------------------------------------------
WE8MSWIN1252

SQL> select property_value from database_properties where property_name=
'NLS_CHARACTERSET';

PROPERTY_VALUE
--------------------------------------------------------------------------------
WE8MSWIN1252

SQL> select value from nls_database_parameters where parameter='NLS_CHARACTERSET';

VALUE
----------------------------------------
WE8MSWIN1252

Related Documents

Unicode characterset in Oracle database.

What is NLS_LANG environmental variable?

Different ways to set up NLS parameters

What is national character set / NLS_NCHAR_CHARACTERSET?

Which datatypes use the National Character Set?

What is character set and character set encoding

What is character set and character set encoding

The term character set indicates the set of characters used by a particular encoding system. It does not represent the numeric assignments of the characters nor the order of the characters but just the set of characters under an encoding system.

The term character set encoding refers how each character of a character set is represented under an encoding system. In order words character set encoding is the mapping of characters to binary values. It pairs the character from the character set with a natural number. Suppose, in 8-bit character set encoding each character from the character set is mapped to the values range from 0-255.

For example capital A will be encoded to 65. Every time you press A from keyboard it will be interpreted as 65. This system also named as encoding scheme.

Related Documents
Unicode characterset in Oracle database.
What is NLS_LANG environmental variable?
What is database character set and how to check it
Different ways to set up NLS parameters
What is national character set / NLS_NCHAR_CHARACTERSET?
Which datatypes use the National Character Set?

Wednesday, February 25, 2009

ORA-00904: "CNVTYPE" CSS-08888: failed to update conversion type

Problem Description
To check all character data in the database and tests for the effects and problems of changing the character set encoding we ran csscan before character set migration but csscan fails with
ORA-00904: "CNVTYPE": invalid identifier

CSS-08888: failed to update conversion type

as below.

C:\>csscan arju/a@orcl user=arju process=2 array=1024000 TOCHAR=AL32UTF8

Character Set Scanner v2.2 : Release 11.1.0.6.0 - Production on Tue Feb 3 17:15:46 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Enumerating tables to scan...

. process 1 scanning ARJU.TEST_VARCHAR[AAAaieAAEAAAPiBAAA]
ORA-00904: "CNVTYPE": invalid identifier

CSS-08888: failed to update conversion type

Scanner terminated unsuccessfully.
Cause of the problem
The ORA-00904: "CNVTYPE": invalid identifier in csscan occurred due to version mismatch between csscan utility and database. From the above output we see Character Set Scanner v2.2 : Release 11.1.0.6.0 that is csscan version is 2.2 and it is of release 11g while it connects to database 10.2g. In order to connect to oracle database 10g it is recommended to use csscan tool of version 2.1. Hence we connect 10.2g database with 11g csscan so above error comes.

Solution of the problem
In order to connect to 10.2g database use 10.2g csscan that is csscan of version 2.1. So from 10g ORACLE_HOME I explicitly selected csscan of version 2.1 and run the operation which goes successful.

C:\>j:\oracle\product\10.2.0\db_1\BIN\csscan.exe arju/a@orcl user=arju process=2 array=1024000 TOCHAR=AL32UTF8

Character Set Scanner v2.1 : Release 10.2.0.0.0 - Production on Tue Feb 3 17:30:02 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Enumerating tables to scan...
. process 1 scanning ARJU.TEST_VARCHAR[AAAaieAAEAAAPiBAAA]

Creating Database Scan Summary Report...

Creating Individual Exception Report...

Scanner terminated successfully.

Related Documents
CSSCAN fails with CSS-00151: failed to enumerate user tables CSS-00120
CSSCAN fails with error while loading shared libraries: libclntsh.so.10.1
How to run csscan in the background as a sysdba
CSSCAN fails with CSS-00107: Character set migration utility schema not installed
CSSCAN fails with ORA-00600, CSS-00152, CSS-00120

Tuesday, February 24, 2009

CSSCAN fails with CSS-00107: Character set migration utility schema not installed

Problem Description
While running csscan in order to check all character data in the database and tests for the effects and problems of changing the character set encoding, "CSS-00107: Character set migration utility schema not installed" error returned as below.


C:\>csscan arju/a@san user=arju array=1024000 TOCHAR=AL32UTF8 process=2


Character Set Scanner v2.2 : Release 11.1.0.6.0 - Production on Mon Feb 2 18:31:14 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

CSS-00107: Character set migration utility schema not installed

Scanner terminated unsuccessfully.

Cause of the problem
In order to run character set scanner in the database it is needed to create tables for Database Character Set Migration Utility. The tables are required to run csscan utility successfully. These tables are created under csmig user. The script named $ORACLE_HOME/rdbms/admin/csminst.sql contains all the tables/synonyms/grants that is requied for cssan.

Solution of the problem
As sys user run the csminst.sql script under $ORACLE_HOME/rdbms/admin directory.
1)Connect as sys.
C:\>sqlplus sys/a@san as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Feb 2 18:32:38 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

2)Execute csminst.sql.
Here my $ORACLE_HOME is F:\app\Administrator\product\11.1.0\db_1.


SQL> @F:\app\Administrator\product\11.1.0\db_1\RDBMS\ADMIN\csminst.sql
Grant succeeded.


Grant succeeded.

drop user csmig cascade
        *
ERROR at line 1:
ORA-01918: user 'CSMIG' does not exist


Please create password for user CSMIG:
Enter value for csmig_passwd: a
old   1: create user csmig identified by &csmig_passwd
new   1: create user csmig identified by a

User created.


Grant succeeded.


Grant succeeded.
drop public synonym csmv$triggers
                  *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist



Synonym created.


View created.


View created.


Commit complete.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

While execute the script it will prompt password of csscan. Type password as you like and then press enter.

3)Now run csscan


C:\>csscan arju/a@san user=arju array=1024000 TOCHAR=AL32UTF8 process=2


Character Set Scanner v2.2 : Release 11.1.0.6.0 - Production on Mon Feb 2 18:35:50 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Enumerating tables to scan...
.
.
.

And everything goes fine.

Monday, February 23, 2009

Split, Merge, Add, Drop, Exchange, Modify, Rename Partition syntax.

If you look for partition related posts in my blog then have a look at,

How to do partition
Partitioning using online re-definition
How to make partitioning in Oracle more Quickly
Example of global partitioned, global non-partitioned and local Indexes
In this post I will write about syntax/example of partitioning related aspects.

1)Split Table Partition Example:

Partition activity_06_2007 of table user_activity split into two partitions at date 15-JUN-2007. Data before date 15-JUN-2007 is stored into partition activity_06_2007a. Data after date 15-JUN-2007 is stored into partition activity_06_2007b.

ALTER TABLE user_activity SPLIT PARTITION activity_06_2007
AT (TO_DATE('15-JUN-2007','DD-MON-YYYY'))
INTO (PARTITION activity_06_2007a, PARTITION activity_06_2007b);

The above example is for split range partition.
The following example is for split partition of list partition.

ALTER TABLE list_country SPLIT PARTITION rest
VALUES ('BANGLADESH', 'PAKISTAN')
INTO (PARTITION asia, partition rest);

2)Merge Table Partition Example:
In part 1) merge range partition as before by,
ALTER TABLE user_activity MERGE PARTITIONS activity_06_2007a, activity_06_2007b
INTO PARTITION activity_06_2007;

In part 1) merge list partition as before by,

ALTER TABLE list_country
MERGE PARTITIONS asia, rest INTO PARTITION rest;

3)Add Table Partition with LOB Example:
Add a new partition into user_activity table and also store lob column into another tablespace data03.
ALTER TABLE user_activity ADD partition prest values less than (MAXVALUE)
LOB (log, description) STORE AS (TABLESPACE data03);

4)Drop Table Partition Example:
ALTER TABLE DROP PARTITION p1;
Drop and update global indexes in one statement.
ALTER TABLE DROP PARTITION p1 UPDATE GLOBAL INDEXES;

5)Exchange Table Partition Example:
Create the same table as of structure(only column and data type is mandatory) of the partitioned table.

And then run command,
ALTER TABLE user_activity
EXCHANGE PARTITION P_JUN_2007 WITH TABLE UA_JUN_2007;

Note that UA_JUN_2007 must be created prior to execute ALTER TABLE ... EXCHANGE command.

6)Modify Table Partitions Example:
Marking local indexes of partition P3 unusable of table user_activity.
ALTER TABLE user_activity MODIFY PARTITION P3
UNUSABLE LOCAL INDEXES;

Rebuilds all the local index partitions that were marked UNUSABLE,
ALTER TABLE user_activity MODIFY PARTITION P3
REBUILD UNUSABLE LOCAL INDEXES;

7)Move Table Partitions Example:
The following statement will move partition P3 of table user_activity to a new tablespace data04;

ALTER TABLE user_activity MOVE PARTITION p3 TABLESPACE data04;

8)Rename Table Partitions Example:
The partition P3 of user_activity table will be renamed as MAR_2008.
ALTER TABLE sales RENAME PARTITION p3 TO MAR_2008;

9)Truncating Table Partitions Example:
Deletes all the data in the MAR_2008 partition of user_activity table and deallocates the freed space,

ALTER TABLE user_activity
TRUNCATE PARTITION mar_2008 DROP STORAGE;

Related Documents

How to do partition
Partitioning using online re-definition
How to make partitioning in Oracle more Quickly
Example of global partitioned, global non-partitioned and local Indexes

Sunday, February 22, 2009

File manipulation in oracle with UTL_FILE package -Part 2

In the post http://arjudba.blogspot.com/2009/02/file-manipulation-in-oracle-with.html I already discussed about the subprograms FOPEN, FOPEN_NCHAR, FREMOVE, FRENAME, FCOPY, FCLOSE, FCLOSE_ALL of UTL_FILE package. Those subprograms were about the basic file handling operation such as opening, closing, renaming, moving, copying files. In this post I will write about the subprograms that are used to write contents inside OS files.

1)NEW_LINE procedure: The NEW_LINE procedure writes one or more new line terminator to the file. The syntax to use this procedure is,

UTL_FILE.NEW_LINE (
file IN FILE_TYPE,
lines IN NATURAL := 1);

file is the name of the file handle that is open by FOPEN/FOPEN_NCHAR function.
lines is the number of line terminators written to the file.

2)PUT procedure: The PUT procedure writes the text string stored in the buffer parameter to the open file identified by the file handle. The file must be opened by FOPEN/FOPEN_NCHAR function for write operation. The syntax is,

UTL_FILE.PUT (
file IN FILE_TYPE,
buffer IN VARCHAR2);

The maximum size of the buffer parameter is 32767 bytes. The default value is 1024 bytes.

Note that the sum of all sequential PUT calls cannot exceed 32767 without intermediate buffer flushes.

3)PUT_LINE procedure: The PUT_LINE procedure writes the text string stored in the buffer parameter to the open file identified by the file handle. This procedure terminates the line with the line terminator. The syntax to use of this procedure is,

UTL_FILE.PUT_LINE (
file IN FILE_TYPE,
buffer IN VARCHAR2,
autoflush IN BOOLEAN DEFAULT FALSE);

file and buffer is same as PUT procedure. Autoflash determines whether to flush to disk after write operation.

4)PUT_LINE_NCHAR procedure: The PUT_LINE_NCHAR procedure is used to write in unicode instead of database character set text string into text file. The syntax of this procedure is,

UTL_FILE.PUT_LINE_NCHAR (
file IN FILE_TYPE,
buffer IN NVARCHAR2);

5)PUT_NCHAR procedure: The PUT_NCHAR is used to write in unicode instead of database character set text string into text file. The syntax for using this procedure is,

UTL_FILE.PUT_NCHAR (
file IN FILE_TYPE,
buffer IN NVARCHAR2);

6)PUTF procedure: The PUTF procedure is like PUT procedure but while writing to file you can format string with the PUTF procedure. The syntax is,

UTL_FILE.PUTF (
file IN FILE_TYPE,
format IN VARCHAR2,
[arg1 IN VARCHAR2 DEFAULT NULL,
arg2 IN VARCHAR2 DEFAULT NULL
arg3 IN VARCHAR2 DEFAULT NULL
arg4 IN VARCHAR2 DEFAULT NULL
arg5 IN VARCHAR2 DEFAULT NULL]);

The Format parameter can contain text as well as the formatting characters \n and %s.

The \n is the line terminator.
The %s is the substitute with the string value of the next argument in the argument list.

7)PUTF_NCHAR procedure: The PUTF_NCHAR procedure is like PUT_NCHAR procedure but while writing to file you can format string with the PUTF_NCHAR procedure. The syntax is,

UTL_FILE.PUTF_NCHAR (
file IN FILE_TYPE,
format IN NVARCHAR2,
[arg1 IN NVARCHAR2 DEFAULT NULL,
. . .
arg5 IN NVARCHAR2 DEFAULT NULL]);

8)PUT_RAW function: The PUT_RAW function accepts as input a RAW data value and writes the value to the output buffer. The syntax of this function is,

UTL_FILE.PUT_RAW (
fid IN utl_file.file_type,
r IN RAW,
autoflush IN BOOLEAN DEFAULT FALSE);

Example with these functions and procedures

Saturday, February 21, 2009

File manipulation in oracle with UTL_FILE package -Part 1

UTL_FILE package has various subprograms which helps us file manipulation in oracle.
In this part file opening and closing functions and file managing functions will be discussed. Below is the list of the subprograms of UTL_FILE package along with their works related in this area.

1)FOPEN function: The FOPEN function of UTL_FILE package opens a file. You can open 50 files at a time.
The syntax of FOPEN function is,

UTL_FILE.FOPEN (
location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2,
max_linesize IN BINARY_INTEGER)
RETURN file_type;

where, location is the directory name of the file.

file_name is the name of the file without directory path.

open_mode can have value of 'r' or 'w' or 'a' or 'rb' or 'wb'. Where,
r = read text
w = write text
a = append text
rb = read byte mode
wb = write byte mode
ab = append byte mode

If the file does not exists on the file system and yet you try to open the file with "a" or "ab" mode then new file is created and opened in write mode.

The max_linesize parameter specify the maximum number of characters in each line including new line character. The default value of this parameter is 1024 which means if the linesize is greater than 1024 characters then only 1024 characters will be read. The maximum value of this parameter can be specified to 32767.

The returning file handler must be specified of type UTL_FILE.FILE_TYPE.

2)FOPEN_NCHAR function: The FOPEN_NCHAR function is similar to FOPEN function in terms of parameters and return types but it is used to open a file in Unicode for input or output. The syntax is,

UTL_FILE.FOPEN_NCHAR (
location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2,
max_linesize IN BINARY_INTEGER)
RETURN file_type;

3)FREMOVE procedure: The FREMOVE procedure remove a file from OS file system. The syntax is,
UTL_FILE.FREMOVE (
location IN VARCHAR2,
filename IN VARCHAR2);

4)FRENAME procedure: The FRENAME procedure rename an existing file to a new name. It is just like "mv" command on unix, rename/ren command on windows. The syntax is,

UTL_FILE.FRENAME (
location IN VARCHAR2,
filename IN VARCHAR2,
dest_dir IN VARCHAR2,
dest_file IN VARCHAR2,
overwrite IN BOOLEAN DEFAULT FALSE);

The default is no overwrite if one already exist in the destination directory.

5)FCOPY procedure: The FCOPY procedure copy contents from one file to another newly created file. By default whole contents of the file is copied if start_line and end_line parameters are not specified of the procedure. The syntax is,

UTL_FILE.FCOPY (
src_location IN VARCHAR2,
src_filename IN VARCHAR2,
dest_location IN VARCHAR2,
dest_filename IN VARCHAR2,
start_line IN BINARY_INTEGER DEFAULT 1,
end_line IN BINARY_INTEGER DEFAULT NULL);

6)FCLOSE procedure: The FCLOSE procedure closes an open file identified by a file handle. The syntax is,

UTL_FILE.FCLOSE (
file IN OUT FILE_TYPE);

Note that, if there is buffered data yet to be written when FCLOSE runs, then you may receive a WRITE_ERROR exception when closing a file.

7)FCLOSE_ALL Procedure: The FCLOSE_ALL procedure closes all open file handles for the session. The syntax is,

UTL_FILE.FCLOSE_ALL;

Example with these procedures/functions:

Create two directory S_DIR and D_DIR which is corresponds C and D drive respectively. Then a PL/SQL which at first create a file under S_DIR named file1.txt using FOPEN and FCLOSE.
Copy file from S_DIR/file1.txt to D_DIR/copy_of_file1.txt.
Move file from S_DIR/file1.txt to to D_DIR/copy2_of_file1.txt.
Finally remove the file from D_DIR/copy_of_file1.txt
At last we will get copy2_of_file1.txt under D: drive.


SQL> create or replace directory S_DIR as 'C:';

Directory created.

SQL> create or replace directory D_DIR as 'D:';

Directory created.

SQL> DECLARE
2    fileHandler1 UTL_FILE.FILE_TYPE;
3  BEGIN
4    fileHandler1 := UTL_FILE.FOPEN('S_DIR', 'file1.txt', 'a');
5    UTL_FILE.FCLOSE(fileHandler1);
6    UTL_FILE.fcopy('S_DIR','file1.txt','D_DIR','copy_of_file1.txt');
7    UTL_FILE.FRENAME('S_DIR','file1.txt','D_DIR','copy2_of_file1.txt',TRUE);
8    UTL_FILE.FREMOVE('D_DIR','copy_of_file1.txt');
9  END;
10  /

PL/SQL procedure successfully completed.

How to run ddl statements within pl/sql

You can't simply run any DDL statements within PL/SQL. Running simply DDL will return PLS-00103 as below.


SQL> begin
  2  create table t(a number);
  3  end;
  4  /
create table t(a number);
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe

There are various methods by which you can run DDL statements within PL/SQL. Starting with oracle 8i you can run DDL statements by simply writing "execute immediate" and then DDL statements within single quote as below.


SQL> begin
  2  execute immediate'create table t(a number)';
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> desc t
 Name       Null?    Type
------      ------   -------------
 A                   NUMBER

If you are inside a procedure then simply execute immediate will not enough to perform DDL operation. Execution of the procedure will return ORA-01031: insufficient privileges as below.


SQL> create or replace procedure p as
  2  begin
  3  execute immediate'create table t2(a number)';
  4  end;
  5  /

Procedure created.

SQL> exec p
BEGIN p; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "ARJU.P", line 3
ORA-06512: at line 1

To overcome this error just add "grant create table to user_name" statement before actual DDL statements. Note that if you run your procedure as SYS user then this will not result error but to run as other user extra grant permission is needed before execution. Remember that it is very bad practice to use SYS user as normal operation.


SQL> create or replace procedure p as
  2      begin
  3      execute immediate'grant create table to arju';
  4      execute immediate'create table t2(a number)';
  5      end;
  6      /

Procedure created.

SQL> exec p

PL/SQL procedure successfully completed.

SQL> desc t2
 Name      Null?     Type
 --------- --------- -------- 
 A                   NUMBER

Another method is using of DBMS_SQL package.


SQL> CREATE OR REPLACE PROCEDURE dynamic_sql AS
  2       v_cursor integer;
  3       row_process  integer;
  4     BEGIN
  5       v_cursor := DBMS_SQL.OPEN_CURSOR;
  6            DBMS_SQL.PARSE(v_cursor, 'GRANT CREATE TABLE TO ARJU',DBMS_SQL.NATIVE);
  7            row_process := DBMS_SQL.EXECUTE(v_cursor);
  8       DBMS_SQL.PARSE(v_cursor, 'CREATE TABLE Test_SQL (col1 DATE)',DBMS_SQL.NATIVE);
  9       row_process := DBMS_SQL.EXECUTE(v_cursor);
 10       DBMS_SQL.CLOSE_CURSOR(v_cursor);
 11     END;
 12     /

Procedure created.

SQL> exec dynamic_sql

PL/SQL procedure successfully completed.

SQL> desc test_sql
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               DATE

How to read/write file in oracle with pl/sql

The UTL_FILE package can be used to read or write file from operating system. The UTL_FILE package has different subprograms which will help to read and write file from/to OS.

Steps for writing to a OS file system through PL/SQL
Note that this example is under windows file system.
Step 01: Create directory.


SQL>Create or replace directory "FILE_DIR" as 'G:\Logs';

Note that FILE_DIR name is the alias of the physically existing directory of G:\Logs. So there must exists Logs directory under G directory. And to create directory the user must have DBA role or create directory privilege.

Step 02: Give permission to the user who will use the directory FILE_DIR.
If read permission is given then following user can read file from the directory. If write permission is given then following user can write to the directory. Here I have given read and write permission to user ARJU on directory FILE_DIR by two statements.


SQL> GRANT READ ON DIRECTORY FILE_DIR TO ARJU;
SQL> GRANT WRITE ON DIRECTORY FILE_DIR TO ARJU;

You can give both permission by only one statement as,


SQL> GRANT READ, WRITE ON DIRECTORY FILE_DIR TO ARJU;

Also you can give permission to all database users by,


SQL> GRANT READ, WRITE ON DIRECTORY FILE_DIR TO PUBLIC;

Step 03: Provide access to UTL_FILE package


SQL>GRANT EXECUTE ON UTL_FILE TO ARJU;

Step 04: As user ARJU write to the file.


SQL>CONN ARJU/a

SQL>DECLARE
  fileHandler UTL_FILE.FILE_TYPE;
  BEGIN
  fileHandler := UTL_FILE.FOPEN('FILE_DIR', 'myfile.txt', 'w');
  UTL_FILE.PUTF(fileHandler, 'This is the first Line. \nThis is the second line.');
  UTL_FILE.FCLOSE(fileHandler);
END;
/

After executing above PL/SQL, under G:\Logs myfile.txt would be created and contents of it would be,

This is the first Line.
This is the second line.

Steps for reading file from OS file system through PL/SQL.
After step 1,2 and 3 of above execute following PL/SQL.


SQL>set serverout on
SQL>DECLARE
  fileHandler UTL_FILE.FILE_TYPE;
  buffer    CLOB;
BEGIN
  fileHandler := UTL_FILE.FOPEN('FILE_DIR', 'myfile.txt', 'r');
  UTL_FILE.GET_LINE(fileHandler, buffer);
  dbms_output.put_line('File Data: '||buffer);
  UTL_FILE.GET_LINE(fileHandler, buffer);
  dbms_output.put_line(buffer);
  UTL_FILE.FCLOSE(fileHandler);
END;
/

Here for printing two lines I call UTL_FILE.GET_LINE two times as UTL_FILE.GET_LINE stops reading whenever it finds a newline or by default up to 1024 bytes.

Thursday, February 19, 2009

How to know dependent objects/which objects are dependent under an objects

From dba_dependencies/user_dependencies/all_dependencies view you can know about dependencies between procedures, packages, functions, package bodies, and triggers.
You can know the dependent objects of a procedure ERROR_CODE by following query.
On which objects an object depends

SQL> col OBJECT_NAME for a30
SQL> SELECT object_name, object_type, owner, status, last_ddl_time FROM
           dba_objects WHERE ( object_name, object_type ) IN ( SELECT
           referenced_name, referenced_type FROM dba_dependencies WHERE name =
           'ERROR_CODE' ); 

OBJECT_NAME                    OBJECT_TYPE         OWNER                          STATUS  LAST_DDL_
------------------------------ ------------------- ------------------------------ ------- ---------
DUAL                           SYNONYM             PUBLIC                         VALID   14-OCT-07
STANDARD                       PACKAGE             SYS                            VALID   14-OCT-07
SYS_STUB_FOR_PURITY_ANALYSIS   PACKAGE             SYS                            VALID   14-OCT-07
DBMS_OUTPUT                    SYNONYM             PUBLIC                         VALID   14-OCT-07
REGISTERED_PHONES              TABLE               MAXIM                          VALID   15-FEB-09
SUBSCRIBERS                    TABLE               MAXIM                          VALID   20-JAN-09
FIRST_LEG_SQ                   SEQUENCE            MAXIM                          VALID   20-JAN-09
ACCESS_NUMBER                  TABLE               MAXIM                          VALID   20-JAN-09
BLACKLIST_CALLER               TABLE               MAXIM                          VALID   20-JAN-09
DO_NOT_DIAL                    TABLE               MAXIM                          VALID   20-JAN-09
HISTORY                        TABLE               MAXIM                          VALID   20-JAN-09
RATE_PLAN_M                    TABLE               MAXIM                          VALID   08-FEB-09
CHECK_ABILITY                  FUNCTION            MAXIM                          VALID   20-JAN-09
SIGNUP                         FUNCTION            MAXIM                          VALID   20-JAN-09
FIRST_LEG_ACC                  TABLE               MAXIM                          VALID   25-JAN-09

15 rows selected.

Which Objects are Dependent on an object
Also, you can know the which objects are dependent on an object by using,

SQL> SELECT object_name, object_type, owner, status, last_ddl_time FROM
           dba_objects WHERE ( object_name,object_type) in(SELECT name, type from
           dba_dependencies
           WHERE referenced_name='SUBSCRIBERS'); 

OBJECT_NAME                    OBJECT_TYPE         OWNER                          STATUS  LAST_DDL_
------------------------------ ------------------- ------------------------------ ------- ---------
AUTH_ERROR_CODE                FUNCTION            MAXIM                          VALID   25-JAN-09
CALRATE                        FUNCTION            MAXIM                          VALID   01-FEB-09
CALRATEDUMMY                   FUNCTION            MAXIM                          VALID   20-JAN-09
CALRATEDUMMY_800               FUNCTION            MAXIM                          VALID   20-JAN-09
CALRATE_800                    FUNCTION            MAXIM                          VALID   20-JAN-09
CHECK_ABILITY                  FUNCTION            MAXIM                          VALID   20-JAN-09
CHECK_ABILITY_800              FUNCTION            MAXIM                          VALID   20-JAN-09
CREDIT_AMT_OR_TIME             FUNCTION            MAXIM                          VALID   20-JAN-09
GETCOUNTRYNAME                 FUNCTION            MAXIM                          VALID   20-JAN-09
SIGNUP                         FUNCTION            MAXIM                          VALID   20-JAN-09
SIGNUP_800                     FUNCTION            MAXIM                          VALID   08-FEB-09
MAP_TO_SPEEDDIAL               PROCEDURE           MAXIM                          VALID   20-JAN-09
UPDATE_USER_STATUS             TRIGGER             MAXIM                          VALID   19-FEB-09
UPDATE_HISTORY_FOR_REFUND      TRIGGER             MAXIM                          VALID   20-JAN-09
INSERT_HISTORY_DETAIL          TRIGGER             MAXIM                          VALID   20-JAN-09
DELETE_SUBSCRIBERS             TRIGGER             MAXIM                          VALID   20-JAN-09

16 rows selected.

How to search a string/key value from PL/SQL code

From dba_source/all_source/user_source view you can find the text source of objects such as FUNCTION, JAVA SOURCE, PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER, TYPE and TYPE BODY. You can query from these views in order to find any string/key value inside the objects.

By following query you can find any tables or columns or any strings or expressions referenced in your PL/SQL code.

SELECT type, name, line
FROM user_source
WHERE UPPER(text) LIKE UPPER('%&KEYWORD%');

If you run above query from Sql*Plus then enter the string you are searching for when prompted for KEYWORD. If you use another tool to run above Sql then replace &KEYWORD with the string you are searching for.

Example: I search for Testing string and I found testing word is found inside P_TEST procedure at line number 4.

SQL> SELECT type, name, line
2    FROM   user_source
3   WHERE  UPPER(text) LIKE UPPER('%&KEYWORD%');
Enter value for keyword: Testing
old   3:  WHERE  UPPER(text) LIKE UPPER('%&KEYWORD%')
new   3:  WHERE  UPPER(text) LIKE UPPER('%Testing%')

TYPE         NAME                                 LINE
------------ ------------------------------ ----------
PROCEDURE    P_TEST                                  4

How to know when a table/object's ddl/code was last modified

From the dba_objects/user_objects/all_objects view you can know about the information about when an object(table,index,function etc) was created or when the last DDL operation was done against the table or when last compilation was done.

As of other oracle views.
DBA_OBJECTS contains all database objects.
USER_OBJECTS contains all objects that is owned by the current user.
ALL_OBJECTS contains all objects on which current user has access.

Note that obj is the synonym of USER_OBJECTS view.

The CREATED column of the view contains date about when an object was created.

The LAST_DDL_TIME column of the view contains date about when the object was last modified by a DDL statement. Note that this column value also contain the timing of revoke and grant that was issued against the object. Similarly on procedure, function, trigger if you compile the object then only LAST_DDL_TIME is only modified.

The TIMESTAMP column of the view contains timestamp of the last ddl time excluding any grants, revoke or any compile time.

Before proceed let's set nls_date_format on sql*plus so that we can see the timings of date data type.

SQL> alter session set nls_date_format='DD-MON-YY HH24:MI:SS';

Session altered.

SQL> select created, last_ddl_time, timestamp, status from obj where object_name='TEST' 
and object_type='TABLE';

CREATED            LAST_DDL_TIME      TIMESTAMP           STATUS
------------------ ------------------ ------------------- -------
21-SEP-08 00:43:11 21-SEP-08 00:49:16 2008-09-21:00:43:11 VALID

Now I am adding a column to the table. After adding column see the LAST_DDL_TIME
and TIMESTAMP column value is changed.

SQL> alter table test add col2 number;

Table altered.

SQL>  select created, last_ddl_time, timestamp, status from obj where object_name='TEST'
and object_type='TABLE';

CREATED            LAST_DDL_TIME      TIMESTAMP           STATUS
------------------ ------------------ ------------------- -------
21-SEP-08 00:43:11 20-FEB-09 11:02:29 2009-02-20:11:02:29 VALID

SQL> select sysdate from dual;

SYSDATE
------------------
20-FEB-09 11:02:47

Now I grant select on test table to user arju. After grant note that LAST_DDL_TIME is changed but TIMESTAMP value is not changed.

SQL> grant select on test to arju;

Grant succeeded.

SQL>  select created, last_ddl_time, timestamp, status from obj where object_name='TEST' 
and object_type='TABLE';

CREATED            LAST_DDL_TIME      TIMESTAMP           STATUS
------------------ ------------------ ------------------- -------
21-SEP-08 00:43:11 20-FEB-09 11:12:33 2009-02-20:11:02:29 VALID

SQL>  select created, last_ddl_time, timestamp, status from obj where object_name='P_TEST' 
and object_type='PROCEDURE';

CREATED            LAST_DDL_TIME      TIMESTAMP           STATUS
------------------ ------------------ ------------------- -------
20-FEB-09 10:42:11 20-FEB-09 10:42:11 2009-02-20:10:42:11 VALID

SQL> alter procedure p_test compile;

Procedure altered.

SQL>  select created, last_ddl_time, timestamp, status from obj where object_name='P_TEST' 
and object_type='PROCEDURE';

CREATED            LAST_DDL_TIME      TIMESTAMP           STATUS
------------------ ------------------ ------------------- -------
20-FEB-09 10:42:11 20-FEB-09 11:18:41 2009-02-20:10:42:11 VALID

Avoid huge whitespaces above table in blogger

Problem Description
This problem is common issue while displaying table on your blogspot/blogger post. Whenever you construct table using HTML inside blogger, above table a huge blank lines/white spaces are printed. In this post I will test it along with writings of how to remove those blank spaces.

Problem Demonstration
Below is the html of a simple table structure. If you are new in html arena then tag
<table> indicates you are going to draw a table.
<th> indicates table header.
<tr> indicates table row.
<td> indicates table data.
Table html is:


<table border="1">
<th>Heading 01</th>
<th>Heading 02</th>
<th>Heading 03</th>
<tr><td>Row 01 Data01</td>
<td>Row 01 Data 02</td>
<td>Row 01 Data 03</td>
</tr>
<tr><td>Row 02 Data 01</td>
<td>Row 02 Data 02</td>
<td>Row 03 Data 03</td>
</tr>
</table>

And the output of above html is

Heading 01	Heading 02	Heading 03
Row 01 Data01	Row 01 Data 02	Row 01 Data 03
Row 02 Data 01	Row 02 Data 02	Row 03 Data 03

From the output you see above table large amount of blank lines is printed. This happens on your blog post. On your own machine above html shows ok and no space is printed there but in case of blogger post a large blank space is printed.

Cause of the Problem
While typing html you pressed ENTER key many times and this cause the Blogger software to add a line breaks tags for each time you press the ENTER key. That's why you are seeing a lot of white spaces above the table.

In fact the number of line breaks printed before the table, is the amount of line breaks inside your script. Blogger generated line breaks is printed before the table html.

Solution of the Problem
You can avoid blogger generated line breaks by three ways. Inside the html no line breaks avoid print line breaks in your blog post.
Method 1)Writing HTML without pressing any ENTER key.
<table border="1"><th>Heading 01</th><th>Heading 02</th><th>Heading 03</th><tr><td>Row 01 Data01</td><td>Row 01 Data 02</td><td>Row 01 Data 03</td></tr><tr><td>Row 02 Data 01</td><td>Row 02 Data 02</td><td>Row 03 Data 03</td></tr></table>

Above html output is,

Heading 01	Heading 02	Heading 03
Row 01 Data01	Row 01 Data 02	Row 01 Data 03
Row 02 Data 01	Row 02 Data 02	Row 03 Data 03

Note that no white spaces are printed after html as no we did not keep any line breaks inside out html. But writing code in this way is not user friendly and also a cumbersome thing. Below is the way of using <div> which in fact turns the line breaks off inside blogger post.

Method 2)Change blogger Dashboard Setting:

i)Sign into Dashboard

ii)Select the blog that you want to change. You may have many blogs and choose as you like.

iii)Click the SETTINGS > FORMATTING tab.

iv)In the middle of the page find the "Convert line breaks" keyword by default which is set to "Yes". Make the setting "No".

v)After change it in the bottom click Save Settings.

And now you no longer find any blank lines above table in your html.

But note that by doing so, you may play havoc with the formatting of your posts such as no paragraphs. Also setting in this way will affect all posts in your blogger. So it is global setting.

Method 3: Using of style and div tag as below.


<style type="text/css">.nobrtable br { display: none }</style>
<div class="nobrtable">
<table border="1">
<th>Heading 01</th>
<th>Heading 02</th>
<th>Heading 03</th>
<tr><td>Row 01 Data01</td>
<td>Row 01 Data 02</td>
<td>Row 01 Data 03</td>
</tr>
<tr><td>Row 02 Data 01</td>
<td>Row 02 Data 02</td>
<td>Row 03 Data 03</td>
</tr>
</table>
</div>

Below is the output of this html,

Heading 01	Heading 02	Heading 03
Row 01 Data01	Row 01 Data 02	Row 01 Data 03
Row 02 Data 01	Row 02 Data 02	Row 03 Data 03

Related Documents
Different types of web hosting services based on application
Which database does google use?
What is web hosting service?
List of Available Advertising Network Companies
The security certificate has expired or is not yet valid.

Wednesday, February 11, 2009

Compatibility Table of Oracle Data Pump with oracle database versions

In this post I am going to make a table that will cover all compatibility aspects regarding oracle data pump up to release Oracle 11g. At first let's have a look at data pump dump file set versions. When your use data pump export client a dump file is created. Dump file set version is dependent on database compatibility. Below is the chart.

A)Database Compatibility and Dump file Set Version.

Database compatibility Setting	Dump file set version	Can be imported into 10.1.x	Can be imported into 10.2.x	Can be imported into 11.1.x
10.1.x	0.1	Yes	Yes	Yes
10.2.x	1.1	No	Yes	Yes
11.1.x	2.1	No	No	Yes

B)Overview of using VERSION parameter while data pump export
As you might know you need to use VERSION parameter while doing data pump export if you decide to import into lower compatible database from the higher compatible database parameter setting.

Source Database compatibility Setting	Need to import to compatible setting 9.2.0.x	Need to import to compatible setting 10.1.0.x	Need to import to compatible setting 10.2.0.x	Need to import to compatible setting 11.1.0.x
10.1.0.x	VERSION=9.2	N/A	N/A	N/A
10.2.0.x	VERSION=9.2	VERSION=10.1	N/A	N/A
11.1.0.x	VERSION=9.2	VERSION=10.1	VERSION=10.2	N/A

C)Overview of data pump Client/Server Compatibility Can you connect with your 11.1.0.6 client expdp/impdp into 10g database version? This part will cover those things.

expdp and impdp client version	Connecting to database 10gR1(10.1.x)	Connecting to database 10gR2(10.2.x)	Connecting to database 11gR1(11.1.x)
10.1.0.x	Supported	Supported	Supported
10.2.0.x	Not Supported	Supported	Supported
11.1.0.x	Not Supported	Not Supported	Supported

Monday, February 9, 2009

How to take data pump export dumpfile to another machine

In this example I will show you how to take data pump export so that dump file will be created in another database rather than the database from which you are doing data pump export.

In this example I will do all operations currently sitting my own client machine.
I will take a data pump export from database ORCL and dumpfile will be written to database TIGER on another machine.

Below is the steps.
Step 01: Connect to the tiger database and create database link.
C:\>sqlplus arju/a@tiger

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 10 01:11:21 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create database link orcl.net using 'ORCL';

Database link created.

Note that TNS alias entry of ORCL must reside on the TIGER machine. You can't use your own TNS alias entry here. After creating database link make sure you can connect to orcl database.

SQL> select count(*) from user_tables@orcl.net;

COUNT(*)
----------
2

Without TNS entry you can still create your database link. Like below.

SQL> create database link orcl2.net connect to arju identified by a
using '(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST =10.4.12.233) (PORT=1521))
(connect_data=(service_name=orcl)))';

Database link created.

test by,
SQL> select count(*) from user_tables@orcl2.net;

COUNT(*)
----------
2
Step 02: After successful database link creation run expdp on the tiger machine with NETWORK_LINK parameter. Dump file will be created on tiger machine and metadata and data will be exported through database link.

In this example optionally directory=d is provided. You can create it as ,

SQL> create directory d as 'your expected location';

If no directory parameter is provided by default in the directory DATA_PUMP_DIR dump file is exported.
SQL> $expdp arju/a@tiger directory=d schemas=arju dumpfile=arju_dump_from_orcl.dmp network_link=orcl2.net

Export: Release 10.2.0.1.0 - Production on Tuesday, 10 February, 2009 1:53:34

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "ARJU"."SYS_EXPORT_SCHEMA_01": arju/********@tiger directory=d schemas=arju dumpfile=arju_dump_from_orcl.dmp network_link=orcl2.net
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ARJU"."EMP" 4.921 KB 2 rows
. . exported "ARJU"."TEST" 4.914 KB 1 rows
Master table "ARJU"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ARJU.SYS_EXPORT_SCHEMA_01 is:
D:\ARJU_DUMP_FROM_ORCL.DMP
Job "ARJU"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:54:59
Related Documents
http://arjudba.blogspot.com/2009/02/important-guideline-about-data-pump.html
http://arjudba.blogspot.com/2009/02/how-to-do-data-pump-import-from-higher.html
http://arjudba.blogspot.com/2008/04/data-pump-exportimport-how-we-can-do-it.html
http://arjudba.blogspot.com/2009/02/ude-00018-data-pump-client-is.html
http://arjudba.blogspot.com/2008/04/exportimport-datapump-parameter-query.html
http://arjudba.blogspot.com/2008/04/improve-data-pump-performance.html
http://arjudba.blogspot.com/2009/02/compatibility-table-of-oracle-data-pump.html
http://arjudba.blogspot.com/2008/04/datapump-parameters-include-and-exclude.html