Nov 19, 2010

Drop foreign key from a Versioned Table

To drop a foreign key constraint from a version enable table .

EXECUTE DBMS_WM.BeginDDL('PARENT_TABLE');
EXECUTE DBMS_WM.BeginDDL('CHILD_TABLE');
ALTER TABLE CHILD_TABLE_LTS DROP CONSTRAINT FOREIGN_KEY_NAME
EXECUTE DBMS_WM.CommitDDL('CHILD_TABLE');
EXECUTE DBMS_WM.CommitDDL('PARENT_TABLE');


The foreign key constraints and its details of version enabled tables can be obtained from the view USER_WM_RIC_INFO or from
table wmsys.wm$ric_table rt

--------------------
Eg :
EXEC DBMS_WM.BEGINDDL('BILL');
EXEC DBMS_WM.BEGINDDL('BILL_DETAILS');
ALTER TABLE BILL_DETAILS_LTS DROP CONSTRAINT FK_BILL_DETAILS_CTS ;
EXEC DBMS_WM.COMMITDDL('BILL');
EXEC DBMS_WM.COMMITDDL('BILL_DETAILS');

Nov 12, 2010

Oracle GLOBALIZATION SUPPORT

GLOBALIZATION SUPPORT


Choosing a Character Set
---------------------------------
When computer systems process characters, they use numeric codes instead of the graphical representation of the character. For example, when the database stores the letter A, it actually stores a numeric code that is interpreted by software as that letter. Read on to know more about Globalization Support.


ForMoreDetails


NLS Data Dictionary Views
----------------------------
Applications can check the session, instance, and database NLS parameters by
querying the following data dictionary views:

NLS_SESSION_PARAMETERS shows the NLS parameters and their values for the
session that is querying the view. It does not show information about the character
set.

NLS_INSTANCE_PARAMETERS shows the current NLS instance parameters that
have been explicitly set and the values of the NLS instance parameters.

NLS_DATABASE_PARAMETERS shows the values of the NLS parameters for the
database. The values are stored in the database.
--------------------------------------------------------
NLS Dynamic Performance Views

V$NLS_VALID_VALUES lists values for the following NLS parameters
NLS_LANGUAGE, NLS_SORT, NLS_TERRITORY, NLS_CHARACTERSET

V$NLS_PARAMETERS shows current values of the following NLS parameters:
NLS_CALENDAR, NLS_CHARACTERSET, NLS_CURRENCY, NLS_DATE_FORMAT,
NLS_DATE_LANGUAGE, NLS_ISO_CURRENCY, NLS_LANGUAGE, NLS_NUMERIC_
CHARACTERS, NLS_SORT, NLS_TERRITORY, NLS_NCHAR_CHARACTERSET, NLS_COMP, NLS_LENGTH_SEMANTICS, NLS_NCHAR_CONV_EXP, NLS_TIMESTAMP_FORMAT, NLS_TIMESTAMP_TZ_FORMAT, NLS_TIME_FORMAT, NLS_TIME_TZ_FORMAT

--------

Nov 8, 2010

Oracle Golden Gate

           For Real-time data integration.High performing enterprises need real-time data access across heterogeneous systems for improved business intelligence.Enables zero-downtime migration and upgrades for mission critical Oracle Applications.Supports the vast majority of databases and operating systems.

Continuous Availability
-----------------------

• Faster recovery
• Minimal data loss

Fast, Accurate Decision
----------------------------

• Real-time
• Operational business intelligence

Value Across Multiple Solutions
-------------------------------
• Rapid deployment
• Single technology highly flexible



-------

Oct 29, 2010

To get only numeric values

To  retrieve only numeric values from both numeric& character value

In 10 g

Select  regexp_replace(regexp_replace('5RTYDDB','([[:punct:]])'),'([[:alpha:]])')
  from dual t 
 
In 9i  
 
 select replace('BBd3',TRANSLATE('BBd3', ' +-.0123456789', ' '),'') from dual

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