Oracle sql script list of all countries of the world

Oracle sql script list of all countries of the world

only 5$

yasser.hassan@yandex.com

Sql list of all cities and Governorates in Egypt

Here I have Oracle Sql script for list of all cities and Governorates in Egypt
it is cost 5$
email me to got it
yasser.hassan@yandex.com

Oracle Delete dublicated rows

DELETE FROM your_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM your_table
GROUP BY column1, column2, column3);

character set problems

You just installed Oracle Database XE version (10g or 11g) and try to connect with forms 6i. When you put the connection string and hit ENTER to connect then Forms/Reports builder hang and "Forcefully Closed".

What happen ?

It's just about the CHARACTER SET mismatch.  Typically XE database version most surelly 
11g XE use Unicode CHARACTER SET, which is "AL32UTF8".

Forms/Reports 6i doesn't support this and supports ("UTF8","WE8MSWIN1252")

Moreover,

Oracle Database 11g Express Edition has no provisions to change/use other charactersets than AL32UTF8 as NLS_CHARACTERSET and AL16UTF16 as NLS_NCHAR_CHARACTERSET
The NLS_CHARACTERSET is used for CHAR, VARCHAR2, LONG and CLOB columns;
The NLS_NCHAR_CHARACTERSET is used for NCHAR, NVARCHAR2 and NCLOB columns.

So, if you migrate data from any previous version of Database like 10g with special character, you will see the bellow problem when importing. 

SQL> create table t( c varchar2(3) );

SQL> insert into t values( 'abç' );
insert into t values( 'abç' )
*
ERROR at line 1:
ORA-12899: value too large for column "SCH"."T"."C" (actual: 4, maximum: 3)


What to do ?
Simply alter the CHARACTER SET to previously supported one.

[Script]

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Windows\system32>CD C:\oraclexe\app\oracle\product\11.2.0\server\bin

C:\oraclexe\app\oracle\product\11.2.0\server\bin>SET ORACLE_HOME=C:\oraclexe\app
\oracle\product\11.2.0\server

C:\oraclexe\app\oracle\product\11.2.0\server\bin>SET ORACLE_SID=XE

C:\oraclexe\app\oracle\product\11.2.0\server\bin>echo %ORACLE_SID%
XE

C:\oraclexe\app\oracle\product\11.2.0\server\bin>SQLPLUS/NOLOG

SQL*Plus: Release 11.2.0.2.0 Production on Fri May 23 19:53:42 2014

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

SQL> CONN SYS/SYSTEM11g AS SYSDBA
Connected.
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> STARTUP RESTRICT
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1384760 bytes
Variable Size             272633544 bytes
Database Buffers          255852544 bytes
Redo Buffers                5791744 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE WE8MSWIN1252;

Database altered.

SQL>
SQL>
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> STARTUP
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1384760 bytes
Variable Size             272633544 bytes
Database Buffers          255852544 bytes
Redo Buffers                5791744 bytes
Database mounted.
Database opened.
SQL>SQL> select * from v$nls_parameters where parameter like '%CHARACTERSET%';

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_CHARACTERSET
WE8MSWIN1252

NLS_NCHAR_CHARACTERSET
AL16UTF16

Select source of view

select text from user_views
where view_name = upper('pay_emp_types_v');

How to check character set

One of the requirements for samePage is that the character set should be UTF-8. 

You can either check with your DBA or run the following SQL to determine whether your database character set is UTF-8. 

SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;

It should return the value AL32UTF8. 

Another option is to run the following SQL :

SELECT * FROM NLS_DATABASE_PARAMETERS

The value against NLS_CHARACTERSET should be UTF8.

Using PL/SQL Collections and Records

Oracle Tnsnames.ora

net_service_name= 
 (DESCRIPTION= 
   (ADDRESS=(protocol_address_information))
   (CONNECT_DATA= 
     (SERVICE_NAME=service_name))) 

Oracle import dump

imp system/sys@orcl FULL=Y FILE=FULL.DMP

Oracle export dump

exp exp system/sys@orcl FULL=Y FILE=FULL.DMP

Diff Between Post – Change, When-Validate-Item, Post-Test-Item

Diff Between Post – Change, When-Validate-Item, Post-Test-Item

Post-Test-Item” Trigger fires every time when focus moves from one item to another item.

“Post – Change”, ”When-Validate-Item” à Both Triggers Have the same functionality except
that Post-Change doesn’t fire in one case when existing value is changed to NULL While in that case When-Validate-Item trigger fires. And all other functionality of these both triggers are same in the manner that both fires when the status of the item becomes ‘CHANGED’. If status remains ‘UNCHANGED’ then these both trigger doesn’t fires.

Find out the nth-highest salary from table

select * from 
(
    select sal, rank() over (order by sal DESC/ASC) rnk 
    from emp
) 
where rnk = 1/2/3/4/5/6/...;

Learn Oracle with me

Oracle developer

Yasser.hassan@yandex.com

Oracle select primary key name

Dear my readers

here I present example for how to select the primary key name which existing inside the table user_constraints

select constraint_name
from user_constraints
where constraint_type = 'P'
and table_name = upper('emp_eff')
/

hope useful for all

for any question you can email me on yasser.hassan@yandex.com
Yasser