Oracle XE-Data Uploading from CSV file

This is a step-by-step guide on the simplest of the simplest ways by which data can be uploaded from CSV file to Oracle table. I have done uploading using this way for as many as 10,000 records without any performance issues. It went very swiftly.

Login to OracleXE first and follow the instructions below:

Step 1 - Click Utilities

Step 2 - Click Data Load/Unload

Step 3 - Click Load

Step 4

Step 5 - Click Load Spreadsheet Data

Step 6 - Follow the screen

Step 7 - Follow the screen

Step 8 - Follow the screen

Step 9 - Follow the screen

Step 10 - Follow the screen

How to rename a table in Oracle?

There are two ways of renaming a table in Oracle:

Method 1: Simple

rename {old_table_name} to {new_table_name}

Example:

rename CUSTOMER to CUSTOMER_BACKUP

Method 2: Not so Complex

alter table {old_table_name} rename to {new_table_name};

Example:

alter table CUSTOMER rename to CUSTOMER_BACKUP;

The minimum version that supports table renaming is Oracle 8i. All the dependencies of the table will automatically updated. No need of updating them after wards.

How to validate values in PL/SQL

I chose to write on this topic in order to increase the readability of programs and to maintain a standard way of validating values from within PL/SQL blocks, whether be it Anonymous blocks or Stored Procedures or Stored Functions or Packages. There has been always need for writing efficient piece of code before delivering the final one.

Validating NULL Values

NVL Function
This function is boon to programmers. I always chose to use this function because of its simplicity. In Oracle one must always remember that while validating with a NULL value the result you are going to get is also a NULL. So in order to avoid this it is better to use NVL() function.

Syntax:
NVL(variable1, variable2)

Example:
SELECT NVL(NULL, 'This is the output') null_test FROM dual;

Result:
null_test
This is the output

Both the parameters are required for NVL function to perform. You can use this function in WHERE clause so that you are not going to omit any NULL values from your query.

NVL2 Function
NVL2 function is an extension of NVL function that it can return two values, one when the variable to be tested is NULL and when the variable to be tested is NOT NULL.

Syntax:
NVL2(variable1, variable1_if_not_null, variable1_if_null)

Example 1:
SELECT NVL2(NULL,'NOT NULL','NULL') nvl2_test FROM dual;

Result:
nvl2_test
NULL

Example 2:
SELECT NVL2('some value','NOT NULL','NULL') nvl2_test FROM dual;

Result:
nvl2_test
NOT NULL

Validating NOT NULL Values
While validating NOT NULL values there is no need to use such functions as the value will be present already in the variable. But if the check is for whether the value is null or not then NVL2 function will be best suitable for the purpose.

How to enter a single quotation mark in Oracle

Q: How to enter a single quotation mark in Oracle?

Ans: Although this may be a undervalued question, I got many a search for my blog with this question. This is where I wanted to address this question elaborately or rather in multiple ways.

Method 1
The most simple and most used way is to use a single quotation mark with two single quotation marks in both sides.

SELECT 'test single quote''' from dual;

The output of the above statement would be:
test single quote'

Simply stating you require an additional single quote character to print a single quote character. That is if you put two single quote characters Oracle will print one. The first one acts like an escape character.

This is the simplest way to print single quotation marks in Oracle. But it will get complex when you have to print a set of quotation marks instead of just one. In this situation the following method works fine. But it requires some more typing labour.

Method 2
I like this method personally because it is easy to read and less complex to understand. I append a single quote character either from a variable or use the CHR() function to insert the single quote character.

The same example inside PL/SQL I will use like following:

DECLARE
l_single_quote CHAR(1) := '''';
l_output VARCHAR2(20);
BEGIN
SELECT 'test single quote'||l_single_quote
INTO l_output FROM dual;
DBMS_OUTPUT.PUT_LINE(l_single_quote);
END;

The output above is same as the Method 1.

Now my favourite while in SQL is CHR(39) function. This is what I would have used personally:

SELECT 'test single quote'||CHR(39) FROM dual;

The output is same in all the cases.

Now don't ask me any other methods, when I come to know of any other methods I will share here.

Create your own Oracle OTN login for FREE

OTN Login enables you to download Oracle softwares, post in Oracle Forums etc. This is a unique email id which you will use across all Oracle websites excluding Oracle Metalink.

Click here to create your account

You will be shown a screen like the following:

Click the Create your Oracle account now. link.

Upon getting the screen enter your email address and a password for accessing the Oracle sites. (Not your email account password)

Fill in the mandatory fields in the following screen. Once you click Submit the following screen will be displayed:

Click Submit button in the Screen. You will be asked some confirmations hereafter, click them and you will be shown the login screen again. THIS IS ALL you need to do to create your FREE login. You may use this login for downloading softwares from otn website, login to oracle forum for posting queries or answering them.

How to design a database - The untold way

Rule #1
The design of a database must only be thought after the through analysis of the user requirements.

Rule #2
Never implement any functionalities of Oracle if it is not required. Always adhere Rule #1.

Rule #3
Never implement as per theory, but base your design on practical thought. Always adhere Rule #2.

What does this mean?
I will elaborate each and every point mentioned above. Also note that all the rules are related to one another. This ensures that the design is always as per user requirements.

Rule #1
The first rule says that never start or think of database design before understanding what exactly the user requires. This is the major and very important rule which must be adhered, whatever comes.

This rule thus verifies the importance of analysis of user requirements. Unless the requirements of user is fully understood, it will be nearly impossible to design the database.

Rule #2
This rule again double verifies the rule #1. The design must be using only using such oracle functionalities which are exactly required. Otherwise if a new feature is used without additional thought on it, it will affect the application performance and also may throw the application as useless.

For example, for an order entry screen there is one master-detail table identified as per user requirements. The normal way of going for design for such a scenario is for designing two tables with parent key (primary key) and child keys (foreign keys). But if we design the same structure say with Nested tables, it will affect the way the data is stored. It will also be very difficult for MIS applications which require just the child tables.

Rule #3
This rule reminds of the theory we learn when studying the database concepts. For example Normalization. We have all learned that all tables must be normalized. And normally we all adhere to this rule. But this is just a guideline rather than a rule.

For example in a small application (it is left to the user about what a small application is and what is a large application) the performance might not be of a big effect. But in large applications, this will prove to be fatal. We will take the case of normalization itself. If all the tables are normalized then oracle will be storing data in more than one tables for a transaction. This will cause more overhead for the database to fetch the data, maintain index storage, maintain data storage etc.

It is not though meant that normalization is bad. But normalization of tables which can be avoided should not be normalized. Do not think of your database design in which normal form, wheras think how well it can help the user in maintaining their data.

Thus the database design cirulates around these:
1. Collect necessary user requirements
2. Only after collecting user requirements think of database design
3. Design the database in such a way that it is user oriented (data oriented) rather than theory oriented
4. You need not use all the features that oracle offers.

This ends part 1 of Tuning guide. Next parts will be updated soon. Watch this space.

Download 11g - how to create oracle account - Reply

For this comment in : http://askanantha.blogspot.com/2007/11/download-oracle-11g.html post, I could not reply due to some technical difficulties. Find my answer in this post:

The question was:
Im a student and while creating the account in the oracle site its asks for office name...what should I write?

While creating the user you specify your role as Student. You can enter any name for the company. Oracle might have to think again for this question.

Cheers.
Anantha