oracle

Localization settings can be set on Database level, Instance level (imagine geographically despersed cluster) , Session level

Environmental variable NLS_LANG allows to override Language settings on a session level.

For example you have an application deployment in Italy, obviously you're expecting data to come in an Italian/European format here is the settings for your Unix box:

NLS_LANG=ITALIAN_.WE8MSWIN1252

Database language settings

To find out your setting use this handy query:

select * from nls_database_parameters;

Will you quite a list of information on your settings, such as:

NLS_LANGUAGE ITALIAN

NLS_TERRITORY ITALY

NLS_CURRENCY <euro sign here>

NLS_ISO_CURRENCY ITALY

More information can be found at Oracle:
http://www.oracle.com/technology/tech/globalization/htdocs/nls_lang%20faq.htm#_Toc110410543

Read more

Often enough when debugging or viewing your logs in your Java or .NET (or other) apps you'd stumble upon something with a name "SYS_C005049" that would say Constraint violated.

You'd ask yourself, why couldn't they provide a meaningful name, well that happens when you don't name table constraints with something proper that would indicate the table name and its referencing table name.

Fret not!

Execute this query: select * from ALL_CONS_COLUMNS where table_name = 'Mytable';

With a list of columns: OWNER, CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, POSITION

To Search by constraint name:  select * from ALL_CONS_COLUMNS where constraint_name = 'SYS_C005049'

Enjoy

Read more

Oracle Express is indeed a great way developing for Oracle database, it provides Apex (run on a small application server) that allows you to be DBA and a normal user without using any other tools for most tasks, even monitoring or executing jobs, scripts,etc. It can tuned (or de-tuned) to use less RAM (memory) on your workstation by changing SGA and PGA (PGA can be heavily reduced) parameters (still using a web tool). Of course this product comes with a limit of 1Gb max of RAM and few other limits which simply make sense.

Recycle Bin

Before you start creating tables and then droping them, you should realize that Oracle XE (express edition) has something called Recycle Bin.

Whenever you drop a table , a copy of that object is not really purged permanently , it is stored in a recycle bin, pretty much a simplified backup in case your app did something wrong.

Here comes the Tip

If you are completely sure that you droped the right table(s) , then Purge It with this command:

purge recyclebin;

Read more

Most developers end up writing their DDL (data definition ) scripts that create various objects related to their design. However there is a great feature in Oracle (existed for a while) that allows the script act as single unit of work (much like a transaction but on Definition level) which is really useful when the script is big and contains quite a few tables and/or views.

Note:Only Create table, create view and grant statements are allowed to be executed as a single operation (it hasn't changed in Oracle 11g

A good example of use (from Oracle):

CREATE SCHEMA AUTHORIZATION scott
    CREATE TABLE dept (
        deptno NUMBER(3,0) PRIMARY KEY,
        dname VARCHAR2(15),
        loc VARCHAR2(25))
    CREATE TABLE emp (
        empno NUMBER(5,0) PRIMARY KEY,
        ename VARCHAR2(15) NOT NULL,
        job VARCHAR2(10),
        mgr NUMBER(5,0),
        hiredate DATE DEFAULT (sysdate),
        sal NUMBER(7,2),
        comm NUMBER(7,2),
        deptno NUMBER(3,0) NOT NULL
        CONSTRAINT dept_fkey REFERENCES dept)
   CREATE VIEW sales_staff AS
        SELECT empno, ename, sal, comm
        FROM emp
        WHERE deptno = 30
        WITH CHECK OPTION CONSTRAINT sales_staff_cnst
        GRANT SELECT ON sales_staff TO human_resources;

If something fails , say a column is too long or default value is of unacceptable type, the whole operation will be rolled back.

Read more

Testing your tnsnames.ora is essential before assuming anything else.

In most cases one would not use a Net Manager or Net Config Assistant to setup naming services, you'd just copy the records from other files.

First if you have several Oracle client installation, make sure you're aware which one is in your SYSTEM path (for Windows users), for Unix/Linux , your $ORACLE_HOME should be set in your profile and $PATH would/should contain appropriate records to $ORACLE_HOME/bin , etc.

Best tool to confirm wheather your configuration is correct is this:

> tnsping <type ALIAS NAME here>

This is a  TNS Ping Utility, it will print out any issues you have with specific alias or simply state that its "OK".

Read more